cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] best way to detect and recover broken connection?

[Miguel M. <mi...@yo...>]

Greetings...
On Mon, 04 Dec 2006 00:37:28 -0800, Mark Harrison <mh...@pi...> wrote:
> Here's kind of a general connection question.
> 
> I've got an application server (xml-rpc based) with
> a long-lived persistent connection.
Me too... :-)
> 
> Of course, the appserver needs to handle the eventuality
> that the connection will be closed at some time.
> 
> What's the best way to handle this?  Is there some nice
> way to globally catch a disconnection and automatically
> reconnect, or some such?
> 
> Many TIA!
> Mark
> 
I just set a class variable with errors I think I should reconnect on,

    # TNS: 12154 12541 12543 12540
    # Database: 1014 1033 1034 1035 1089 1090 1092
    # Network: 3113 3114
    RECONNECTERRORLIST = [ 12154,12541,12543,12540,1014,1033,1034,
                           1035,1089,1090,1092,3113,3114,3106 ]

then in the method that actually performs the Oracle calls I catch the
DatabaseError exceptions and check if the Oracle error is in the list
(which is probably incomplete) and try to reconnect and redo the
query.
Hope that's useful.

							Miguel

C. Miguel Marques, Assistant Manager, Software Systems Developer
Development Services, Computing & Network Services 
York University, Toronto, Ontario, Canada
e-mail: miguel at yorku.ca, voice: (416)736-2100x22684

Re: [cx-oracle-users] best way to detect and recover broken connection?

[Anthony T. <ant...@gm...>]

No, there is no way to globally catch a disconnection. The first time
you will be aware of it is when you make a query and the exception
"not connected to database" (or something similar) is raised. A number
of people perform a "ping" first (execute the statement "select 1 from
dual") before attempting their own query. If that query fails, they
estabilish a new connection and use that instead. I'm not aware of any
other techniques so if anyone else has any to share, please do so.

On 12/4/06, Mark Harrison <mh...@pi...> wrote:
> Here's kind of a general connection question.
>
> I've got an application server (xml-rpc based) with
> a long-lived persistent connection.
>
> Of course, the appserver needs to handle the eventuality
> that the connection will be closed at some time.
>
> What's the best way to handle this?  Is there some nice
> way to globally catch a disconnection and automatically
> reconnect, or some such?
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] best way to detect and recover broken connection?

[Mark H. <mh...@pi...>]

Here's kind of a general connection question.

I've got an application server (xml-rpc based) with
a long-lived persistent connection.

Of course, the appserver needs to handle the eventuality
that the connection will be closed at some time.

What's the best way to handle this?  Is there some nice
way to globally catch a disconnection and automatically
reconnect, or some such?

Many TIA!
Mark

-- 
Mark Harrison
Pixar Animation Studios

Re: [cx-oracle-users] rows modified returned after execute call

[Frank v. L. <fvl...@ca...>]

I found the answer while looking at the db api spec, it is curs.rowcount.

Frank.
Frank van Lingen wrote:
> The Python MySQL cursor object returns the number of rows modified when 
> the execute method is called. Does cx_oracle have something comparable 
> (it seems not to return anything when doing an curs.execute("....")?
> 
> The Python db API does not require anything to be returned from the 
> execute method, but I like the feature. I mainly use it to see if 
> something was modified rather than actual looking at how many rows where 
> modified.
> 
> Frank.


-- 
--------------------------------------
Frank van Lingen
California Institute of Technology
CA 91125 Pasadena
United States
--------------------------------------
Mail Code:356-48
bld      :340 Lauritsen
email    :fvl...@ca...
im(aim)  :marcellus0872
tel      :(+1) 626 395 3862
url      :http://www.vanlingen.name/
--------------------------------------

[cx-oracle-users] rows modified returned after execute call

[Frank v. L. <fvl...@ca...>]

The Python MySQL cursor object returns the number of rows modified when 
the execute method is called. Does cx_oracle have something comparable 
(it seems not to return anything when doing an curs.execute("....")?

The Python db API does not require anything to be returned from the 
execute method, but I like the feature. I mainly use it to see if 
something was modified rather than actual looking at how many rows where 
modified.

Frank.
-- 
--------------------------------------
Frank van Lingen
California Institute of Technology
CA 91125 Pasadena
United States
--------------------------------------
Mail Code:356-48
bld      :340 Lauritsen
email    :fvl...@ca...
im(aim)  :marcellus0872
tel      :(+1) 626 395 3862
url      :http://www.vanlingen.name/
--------------------------------------

Re: [cx-oracle-users] How do you pass a boolean to a stored procusing cx_Oracle?

[JG <z28...@gm...>]

Thank you!

I will try the workaround you mention below.  I think that would be what I 
need.  In the meantime, I have just changed the stored proc to take an 
number instead of the boolean. I will try your solution.

thanks a bunch!

regards,
joe



"Anthony Tuininga" 
<ant...@gm...> wrote in message 
news:703...@ma......
> Passing booleans with callproc() is not currently supported __BUT__ I
> do have code in the currently unreleased version which does support
> it. I'm not sure exactly when I intend to release that code yet. If
> you're desperate for it I can provide you the latest source. :-)
>
> If you want to call it without the latest code you need to do
> something like the following instead:
>
> cursor.execute("""
>        begin
>            testproc(:1, :2 = 1);
>        end;""",
>        [123, int(True)])
>
> On 11/22/06, JG <z28...@gm...> 
> wrote:
>> Hi,
>> I am using Python 2.4 and cx_Oracle.  I have a stored proc that takes two
>> arguments.  First is an NUMBER, second is a BOOLEAN.  How do you call 
>> that
>> stored procedure?
>>
>> After properly extablishing a connection, I have something like this:
>>
>> cursor = con.cursor()
>> cursor.callproc("testproc",[123,True])
>>
>> The problem I have ran into is that I keep getting an error from Oracle
>> stating I don't have the variables defined in with the proper type.  I
>> changed to proc just to test it, so that it would take two numbers.  I 
>> was
>> able to make it run properly.
>>
>> So, what is the trick to pass a boolean?
>>
>> Thanks,
>> joe
>>
>>
>>
>>
>> -------------------------------------------------------------------------
>> Take Surveys. Earn Cash. Influence the Future of IT
>> Join SourceForge.net's Techsay panel and you'll get the chance to share 
>> your
>> opinions on IT & business topics through brief surveys - and earn cash
>> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share 
> your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV 

Re: [cx-oracle-users] How do you pass a boolean to a stored proc using cx_Oracle?

[Anthony T. <ant...@gm...>]

Passing booleans with callproc() is not currently supported __BUT__ I
do have code in the currently unreleased version which does support
it. I'm not sure exactly when I intend to release that code yet. If
you're desperate for it I can provide you the latest source. :-)

If you want to call it without the latest code you need to do
something like the following instead:

cursor.execute("""
        begin
            testproc(:1, :2 = 1);
        end;""",
        [123, int(True)])

On 11/22/06, JG <z28...@gm...> wrote:
> Hi,
> I am using Python 2.4 and cx_Oracle.  I have a stored proc that takes two
> arguments.  First is an NUMBER, second is a BOOLEAN.  How do you call that
> stored procedure?
>
> After properly extablishing a connection, I have something like this:
>
> cursor = con.cursor()
> cursor.callproc("testproc",[123,True])
>
> The problem I have ran into is that I keep getting an error from Oracle
> stating I don't have the variables defined in with the proper type.  I
> changed to proc just to test it, so that it would take two numbers.  I was
> able to make it run properly.
>
> So, what is the trick to pass a boolean?
>
> Thanks,
> joe
>
>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] How do you pass a boolean to a stored proc using cx_Oracle?

[JG <z28...@gm...>]

Hi,
I am using Python 2.4 and cx_Oracle.  I have a stored proc that takes two 
arguments.  First is an NUMBER, second is a BOOLEAN.  How do you call that 
stored procedure?

After properly extablishing a connection, I have something like this:

cursor = con.cursor()
cursor.callproc("testproc",[123,True])

The problem I have ran into is that I keep getting an error from Oracle 
stating I don't have the variables defined in with the proper type.  I 
changed to proc just to test it, so that it would take two numbers.  I was 
able to make it run properly.

So, what is the trick to pass a boolean?

Thanks,
joe 

Re: [cx-oracle-users] How to insert a Blob or Clob using cx_oracle?

[Frank v. L. <fvl...@ca...>]

Thanks,

This was very helpful. It was indeed the ";" and also the size of the 
binary. I can now write and read CLOB objects.

I guess MySQL is a little bit more forgiving in entering this type of data.

Frank.
Amaury Forgeot d'Arc wrote:
> Hello,
> 
> Frank van Lingen wrote:
>> I am trying to insert a file into oracle. I first do a
>> base64.encodestring and then insert it. The sql string for it looks like
>> the one below. If I copy this string to a sqlplus command line it is
>> being inserted. However if I do it within Python using cx_oracle it
>> gives me an error:
>> cx_Oracle.DatabaseError: ORA-00911: invalid character
> 
> First try to remove the semicolon at the end of your query.
> The semicolon is not part of the SQL language; it is used by sqlplus
> and pl/sql to delimit statements.
> 
> Otherwise your code seems correct. I suspect there will be a limit
> (65000?) to the length of the string you can insert, though.
> 
> To pass bigger values you could use something like:
> curs = orcl.cursor()
> curs.setinputsizes(
>     workflow_spec=cx_Oracle.CLOB,
>     policies=cx_Oracle.CLOB,
>     parameters=cx_Oracle.CLOB)
> sqlStr="INSERT INTO
> ws_request(string_id,owner,workflow_spec,policies,parameters,request_type)
> VALUES ('myOwnRequestID0','fvlingen',:workflow_spec,:policies,:parameters,'event')"
> curs.execute(sqlStr,
>     workflow_spec="<long text>",
>     policies="<another long text>",
>     parameters="<another very long text>")
> 
> Hope this helps,
> 


-- 
--------------------------------------
Frank van Lingen
California Institute of Technology
CA 91125 Pasadena
United States
--------------------------------------
Mail Code:356-48
bld      :340 Lauritsen
email    :fvl...@ca...
im(aim)  :marcellus0872
tel      :(+1) 626 395 3862
mobile   :(+1) 310 968 5584
url      :http://www.van-lingen.name/
--------------------------------------

Re: [cx-oracle-users] How to insert a Blob or Clob using cx_oracle?

[Amaury F. d'A. <ama...@gm...>]

Hello,

Frank van Lingen wrote:
> I am trying to insert a file into oracle. I first do a
> base64.encodestring and then insert it. The sql string for it looks like
> the one below. If I copy this string to a sqlplus command line it is
> being inserted. However if I do it within Python using cx_oracle it
> gives me an error:
> cx_Oracle.DatabaseError: ORA-00911: invalid character

First try to remove the semicolon at the end of your query.
The semicolon is not part of the SQL language; it is used by sqlplus
and pl/sql to delimit statements.

Otherwise your code seems correct. I suspect there will be a limit
(65000?) to the length of the string you can insert, though.

To pass bigger values you could use something like:
curs = orcl.cursor()
curs.setinputsizes(
    workflow_spec=cx_Oracle.CLOB,
    policies=cx_Oracle.CLOB,
    parameters=cx_Oracle.CLOB)
sqlStr="INSERT INTO
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type)
VALUES ('myOwnRequestID0','fvlingen',:workflow_spec,:policies,:parameters,'event')"
curs.execute(sqlStr,
    workflow_spec="<long text>",
    policies="<another long text>",
    parameters="<another very long text>")

Hope this helps,

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] How to insert a Blob or Clob using cx_oracle?

[Frank v. L. <fvl...@ca...>]

I am trying to insert a file into oracle. I first do a 
base64.encodestring and then insert it. The sql string for it looks like 
the one below. If I copy this string to a sqlplus command line it is 
being inserted. However if I do it within Python using cx_oracle it 
gives me an error:
cx_Oracle.DatabaseError: ORA-00911: invalid character

The python code can be found below and below that the table definition. 
  I tried both blob and clob but neither works. What is the proper way 
to insert (text) files and pickled objects in Oracle using cx_oracle? In 
MySQL I use the mediumtext object, and the approach below does not give 
any problems.


INSERT INTO 
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type) 
VALUES ('myOwnRequestID0','fvlingen','c21hbGwgc3BlYw==
','KGRwMQpTJ0ZhaWx1cmUnCnAyClMnUmF0aW9CYXNlZCcKcDMKc1MnSm9icy5Bc3NpZ25tZW50Jwpw
NApTJ0V2dFNpbmdsZUNvbnQnCnA1CnNTJ1JlcXVlc3QuQ29tcGxldGVuZXNzJwpwNgpTJ0V2dFBl
cmNlbnRhZ2UnCnA3CnNTJ0FsbG9jYXRpb24uQXNzaWdubWVudCcKcDgKUydFdnRCYXNlZEFsbG9j
YXRpb24nCnA5CnNTJ0FsbG9jYXRpb24uQ29tcGxldGVuZXNzJwpwMTAKZzcKcy4=
','KGRwMQpTJ0ZhaWx1cmUnCnAyCihkcDMKUydyZXF1ZXN0UmF0aW8nCnA0CkYwLjE0OTk5OTk5OTk5
OTk5OTk5CnNTJ21pbkpvYnMnCnA1Ckk0CnNTJ293bmVyUmF0aW8nCnA2CkYwLjE0OTk5OTk5OTk5
OTk5OTk5CnNzUydSZXF1ZXN0LkNvbXBsZXRlbmVzcycKcDcKKGRwOApTJ3RyZXNob2xkJwpwOQpG
MC41OTk5OTk5OTk5OTk5OTk5OApzc1MnQWxsb2NhdGlvbi5Db21wbGV0ZW5lc3MnCnAxMAooZHAx
MQpnOQpGMC42OTk5OTk5OTk5OTk5OTk5Ngpzcy4=
','event');


import cx_Oracle
orcl = cx_Oracle.connect('user/passwd')
curs = orcl.cursor()
sqlStr='INSERT INTO ws_request(string_id,owner,request_type) VALUES 
("myOwnRequestID0","fvlingen","event");'
sqlStr=" INSERT INTO 
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type) 
VALUES 
('myOwnRequestID0','fvlingen','c21hbGwgc3BlYw==','KGRwMQpTJ0ZhaWx1cmUnCnAyClMnUmF0aW9CYXNlZCcKcDMKc1MnSm9icy5Bc3NpZ25tZW50JwpwNApTJ0V2dFNpbmdsZUNvbnQnCnA1CnNTJ1JlcXVlc3QuQ29tcGxldGVuZXNzJwpwNgpTJ0V2dFBlcmNlbnRhZ2UnCnA3CnNTJ0FsbG9jYXRpb24uQXNzaWdubWVudCcKcDgKUydFdnRCYXNlZEFsbG9jYXRpb24nCnA5CnNTJ0FsbG9jYXRpb24uQ29tcGxldGVuZXNzJwpwMTAKZzcKcy4=','KGRwMQpTJ0ZhaWx1cmUnCnAyCihkcDMKUydyZXF1ZXN0UmF0aW8nCnA0CkYwLjE0OTk5OTk5OTk5OTk5OTk5CnNTJ21pbkpvYnMnCnA1Ckk0CnNTJ293bmVyUmF0aW8nCnA2CkYwLjE0OTk5OTk5OTk5OTk5OTk5CnNzUydSZXF1ZXN0LkNvbXBsZXRlbmVzcycKcDcKKGRwOApTJ3RyZXNob2xkJwpwOQpGMC41OTk5OTk5OTk5OTk5OTk5OApzc1MnQWxsb2NhdGlvbi5Db21wbGV0ZW5lc3MnCnAxMAooZHAxMQpnOQpGMC42OTk5OTk5OTk5OTk5OTk5Ngpzcy4=','event');"


curs.execute(sqlStr)


CREATE TABLE ws_request
      (
      allocations            integer default 0,
      completed_events       integer default 0,
      end_event              integer default -1,
      id                     integer,
      CONSTRAINT cons_ws_request_id primary key(id),
      failed_jobs            integer default 0,
      owner                  varchar2(255),
      parameters             clob,
      policies               clob,
      priority               integer default 0,
      processed_jobs         integer default 0,
      request_type           varchar2(100) not null,
      CONSTRAINT cons_ws_request_request_type CHECK (request_type IN 
('event', 'file')),
      run_number             integer default 0,
      start_event            integer default 0,
      status                 varchar2(100) default 'new',
      CONSTRAINT cons_ws_request_status CHECK(status IN 
('new','active','finished','failed')),
      string_id              varchar2(100) not null,
      workflow_spec          clob,
      CONSTRAINT cons_ws_request_string_id unique(string_id)
      );

CREATE SEQUENCE ws_request_seq
start with 1
increment by 1
nomaxvalue;

CREATE TRIGGER ws_request_autoincrement
BEFORE INSERT ON ws_request
FOR EACH ROW
     DECLARE m_no INTEGER;
BEGIN
     SELECT ws_request_seq.nextval INTO :new.id FROM dual;
END;
.
run;

Any help would be appreciated.

Frank


-- 
--------------------------------------
Frank van Lingen
California Institute of Technology
CA 91125 Pasadena
United States
--------------------------------------
Mail Code:356-48
bld      :340 Lauritsen
email    :fvl...@ca...
im(aim)  :marcellus0872
tel      :(+1) 626 395 3862
mobile   :(+1) 310 968 5584
url      :http://www.van-lingen.name/
--------------------------------------

[cx-oracle-users] Problems on HP-UX

[anuj s. <anu...@gm...>]

Hi,
I am trying to install cx_Oracle on HP for 64 bit python.
The build was clean but the module fails to load giving following errors -

Python 2.4.4 (#1, Nov  3 2006, 21:49:47) [C] on hp-ux11
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
shl_load
/bmcuser6.pi005/bmc/olc/nikhilg/python64/lib/python2.4/site-packages/cx_Oracle.sl
/usr/lib/pa20_64/dld.sl: Unsatisfied data symbol 'kpggwcx_' in load module
'/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'LhtStrSearch' in load
module '/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'kpggGetPG' in load module
'/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'LhtStrInsert' in load
module '/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'kpuhhalo' in load module
'/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'kpuhhfre' in load module
'/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'LhtStrCreate' in load
module '/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
/usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'kpugdr' in load module
'/oravl01/oracle/9.2.0.6/lib/libwtc9.sl'.
shl_findsym initcx_Oracle

OS Details - HP-UX  B.11.11 U 9000/800 3653939180 unlimited-user license
Oracle Version - 9.2.0.6

Any help would be appreciated since i am really stuck on this

Thanks,
Anuj.

Re: [cx-oracle-users] Problem with Python 2.5 and named bind variables

[Anthony T. <ant...@gm...>]

Yes, I noticed that myself and have already fixed it. I hope to have a
new release in the next few weeks that will include this and a few
other things.

On 11/16/06, Paul Metcalfe <pau...@gm...> wrote:
> I have just run into this problem on Linux x86_64, with the symptoms
> exactly as reported previously.
>
> I suspect that it comes down to the difference between int and
> Py_ssize_t.  Building cx_Oracle-4.2.1 produces (among others) the
> warnings
>
> Cursor.c: In function 'Cursor_SetBindVariables':
> Cursor.c:937: warning: passing argument 2 of 'PyDict_Next' from
> incompatible pointer type
> Cursor.c: In function 'Cursor_PerformBind':
> Cursor.c:972: warning: passing argument 2 of 'PyDict_Next' from
> incompatible pointer type
> Cursor.c: In function 'Cursor_SetInputSizes':
> Cursor.c:1793: warning: passing argument 2 of 'PyDict_Next' from
> incompatible pointer type
>
> Changing all of these int's to Py_ssize_t's fixes the bind variable problem.
>
> [ System information:
>
> Linux xp-paulm 2.6.18-1-amd64 #1 SMP Tue Oct 10 00:09:20 UTC 2006
> x86_64 GNU/Linux
>
> Python 2.5 (release25-maint, Oct 29 2006, 12:14:25)
> [GCC 4.1.2 20061028 (prerelease) (Debian 4.1.1-19)] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>
> cx_Oracle.version = 4.2.1 ]
>
> Hope this helps,
>
> --
> pdm
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Problem with Python 2.5 and named bind variables

[Paul M. <pau...@gm...>]

I have just run into this problem on Linux x86_64, with the symptoms
exactly as reported previously.

I suspect that it comes down to the difference between int and
Py_ssize_t.  Building cx_Oracle-4.2.1 produces (among others) the
warnings

Cursor.c: In function 'Cursor_SetBindVariables':
Cursor.c:937: warning: passing argument 2 of 'PyDict_Next' from
incompatible pointer type
Cursor.c: In function 'Cursor_PerformBind':
Cursor.c:972: warning: passing argument 2 of 'PyDict_Next' from
incompatible pointer type
Cursor.c: In function 'Cursor_SetInputSizes':
Cursor.c:1793: warning: passing argument 2 of 'PyDict_Next' from
incompatible pointer type

Changing all of these int's to Py_ssize_t's fixes the bind variable problem.

[ System information:

Linux xp-paulm 2.6.18-1-amd64 #1 SMP Tue Oct 10 00:09:20 UTC 2006
x86_64 GNU/Linux

Python 2.5 (release25-maint, Oct 29 2006, 12:14:25)
[GCC 4.1.2 20061028 (prerelease) (Debian 4.1.1-19)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

cx_Oracle.version = 4.2.1 ]

Hope this helps,

-- 
pdm

Re: [cx-oracle-users] Fwd: cx_Oracle and NCLOBs

[Anthony T. <ant...@gm...>]

Are you using the setinputsizes() call to specify that you are
interested in CLOB (or NCLOB)?

cursor.setinputsizes(fred = cx_Oracle.CLOB)
cursor.setinputsizes(fred = cx_Oracle.NCLOB)

If you don't, Oracle assumes you want LONG and barfs appropriately. :-)

On 11/10/06, Nuno NiTrO <12....@gm...> wrote:
> Greetings,
>
> I'm currently working in a Python project to create a multi-platform
> database replication tool, and cx_Oracle was the chosen provider to
> handle the Oracle connections. So far the results have been very
> interesting, but I've been experiencing some problems when inserting
> CLOB objects through executemany (meaning, using a pre-prepared
> statement and a dynamic list of parameters). Namely, somehow the
> provider seems to be converting the data to LONG rather than to CLOB,
> even though the statement explicitly calls out the TO_NCLOB function;
> therefore, we get ORA-01461 errors.
> Now, this may possibly not be an error, but instead a misusage (not
> many examples available, anyways). Any help/suggestion you can
> provide?
>
> Best regards,
> Nitro
>
> --
> NiTrO. From Portugal, with love.
>
>
> --
> NiTrO. From Portugal, with love.
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Call setinputsizes() syntax

[Anthony T. <ant...@gm...>]

Can you be a little more clear as to what you mean by "array"? Are you
referring to a PL/SQL table (array) or multiple executions or
something else? Can you give an example of what you are trying to do
(minus the call to setinputsizes, of course)? Thank you.

On 11/10/06, Sergey A. Lisovoy <s.l...@sp...> wrote:
> I'm using cx_Oracle to insert long data values (as CLOB) into Oracle db.
> Of course, before executing statement I call setinputsizes() similar as
> folowing:
>
> ...
> cur.setinputsizes(v1 = cx_Oracle.CLOB);
> cur.execute("insert ... values(:v1)", v1 = myLargeData);
> ...
>
> But in some cases I need to call setinputsize by passing array as size
> arguments. What calling syntax of setinputsizes() in this case?
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Fwd: cx_Oracle and NCLOBs

[Nuno \NiTrO\ <12....@gm...>]

Greetings,

I'm currently working in a Python project to create a multi-platform
database replication tool, and cx_Oracle was the chosen provider to
handle the Oracle connections. So far the results have been very
interesting, but I've been experiencing some problems when inserting
CLOB objects through executemany (meaning, using a pre-prepared
statement and a dynamic list of parameters). Namely, somehow the
provider seems to be converting the data to LONG rather than to CLOB,
even though the statement explicitly calls out the TO_NCLOB function;
therefore, we get ORA-01461 errors.
Now, this may possibly not be an error, but instead a misusage (not
many examples available, anyways). Any help/suggestion you can
provide?

Best regards,
Nitro

--
NiTrO. From Portugal, with love.


-- 
NiTrO. From Portugal, with love.

[cx-oracle-users] Call setinputsizes() syntax

[Sergey A. L. <s.l...@sp...>]

I'm using cx_Oracle to insert long data values (as CLOB) into Oracle db. 
Of course, before executing statement I call setinputsizes() similar as 
folowing:

...
cur.setinputsizes(v1 = cx_Oracle.CLOB);
cur.execute("insert ... values(:v1)", v1 = myLargeData);
...

But in some cases I need to call setinputsize by passing array as size 
arguments. What calling syntax of setinputsizes() in this case?

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Amaury F. d'A. <ama...@gm...>]

Hello Anthony,

Anthony Tuininga wrote:
> There is no way of hooking into the returning of date objects. You'll
> have to hook the entire result set. You can do that quite easily by
> subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> and fetchall() to transform any datetime.datetime entities into Zopes
> DateTime objects. Clearly there is a performance penalty in doing
> this. If you can suggest an alternative that would eliminate the
> performance penalty I'd be happy to entertain it. Perhaps something
> along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> which would then call <YourTypeOrMethod> with a particular set of
> parameters? Any thoughts on this?

Some wild thoughts, without any consideration of the diffulty to implement:

cx_Oracle types should be subclassable: the Cursor class and the Variable types.
When subclassing, users should be able to provide:

- OracleCursor.variableFactory(self, oracleType, <dataLength_and
others?>): the base implementation ends up by calling the current C
function "Variable_New" and returns its value.
Of course, when subclassing, users will want to substitute their own
udt_Variable class.

- OracleCursor.createRow(self): the base implementation is "Cursor_CreateTuple".
As an example of subclassing, I could copy it into a subclass of
tuple, with a custom __getattribute__ handling the column names...

- xxxVariable.getvalue and setvalue. Here can take place the
conversions to/from Zope datatypes.


Now, for the implementation:
The setValueProc and getValueProc members move from udt_VariableType
to udt_Variable.
Variable_New then checks for derived methods: if so, it replaces
setValueProc and getValueProc with appropriate wrappers like:
PyObject *derivedGetValue(udt_Variable *var, unsigned pos)
{
    return PyObject_CallMethod(var, "getvalue", "i", pos);
}
The advantage of this method is that advanced users can write their
own C code, derive DateTimeVar in C, and directly access the OCIDate*
buffer, with no performance penalty.

Thank you for having read so far.
Now my brain is too hot, I'd better go to bed...

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Amaury F. d'A. <ama...@gm...>]

Hello,

Andy Hird wrote:
> One thought I had after getting some sleep is that passing in the types
> cx_Oracle normally returns means you end up constructing the returned object
> twice anyway and you lose some of the performance benefits of doing this.
> I'm not sure whether building a string to pass into settype is much less
> work or not.

Yes, this is inherent to any method derivation : you have one more
function to call, one more list of arguments to pass, new values to
build... In python IIRC, the first two are the most significant for
performance.
At least you don't have to keep the two lists of rows in memory.

For advanced users, we should provide way to derive their own types in
C code. The Variable object would have access to the raw OCI buffer,
to be as fast as the original type.

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Anthony T. <ant...@gm...>]

If you pass in the datetime variable then yes, you construct the
returned object twice. Passing a string would imply parsing would be
necessary which is less than ideal. Passing in a series of integers
(year, month, day, hour, minute, second, fsecond) would imply the
construction of six integers -- which may or may not be faster than
the construction of a datetime variable.

Regardless of all that, however, it would perform better than
subclassing a cursor and massaging all of the data that is
returned.... Anyone else care to comment?

On 11/9/06, Andy Hird <and...@gm...> wrote:
> One thought I had after getting some sleep is that passing in the types
> cx_Oracle normally returns means you end up constructing the returned object
> twice anyway and you lose some of the performance benefits of doing this.
> I'm not sure whether building a string to pass into settype is much less
> work or not.
>
> Andy
>
>
>
> >
> > Which really is pretty much the same as your settype(cx_Oracle.DATETIME,
> <YourTypeOrMethod>). I guess the one thing they do in common is pass a
> string to the conversion method, although it'd probably be as simple to pass
> cx_Oracles native value for the given type, i.e. a datetime (or
> cx_Oracle.Date) for cx_Oracle.DATETIME and so on. After all if I'm defining
> the conversion methods then I should know what I'd be getting.
> >
> > Thanks again in advance. Sorry for going on so long there.
> >
> > Andy
> >
> >
> >
> >
> > On 11/9/06, Anthony Tuininga < ant...@gm...> wrote:
> > > There is no way of hooking into the returning of date objects. You'll
> > > have to hook the entire result set. You can do that quite easily by
> > > subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> > > and fetchall() to transform any datetime.datetime entities into Zopes
> > > DateTime objects. Clearly there is a performance penalty in doing
> > > this. If you can suggest an alternative that would eliminate the
> > > performance penalty I'd be happy to entertain it. Perhaps something
> > > along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> > > which would then call <YourTypeOrMethod> with a particular set of
> > > parameters? Any thoughts on this?
> >
> >
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>

Re: [cx-oracle-users] Modifying fetched data python types for zope adaptor

[Andy H. <and...@gm...>]

One thought I had after getting some sleep is that passing in the types
cx_Oracle normally returns means you end up constructing the returned object
twice anyway and you lose some of the performance benefits of doing this.
I'm not sure whether building a string to pass into settype is much less
work or not.

Andy


> Which really is pretty much the same as your settype(cx_Oracle.DATETIME,
> <YourTypeOrMethod>). I guess the one thing they do in common is pass a
> string to the conversion method, although it'd probably be as simple to pass
> cx_Oracles native value for the given type, i.e. a datetime (or
> cx_Oracle.Date) for cx_Oracle.DATETIME and so on. After all if I'm defining
> the conversion methods then I should know what I'd be getting.
>
> Thanks again in advance. Sorry for going on so long there.
>
> Andy
>
>
> On 11/9/06, Anthony Tuininga <ant...@gm...> wrote:
> >
> > There is no way of hooking into the returning of date objects. You'll
> > have to hook the entire result set. You can do that quite easily by
> > subclassing cx_Oracle.Cursor and overriding fetchone(), fetchmany()
> > and fetchall() to transform any datetime.datetime entities into Zopes
> > DateTime objects. Clearly there is a performance penalty in doing
> > this. If you can suggest an alternative that would eliminate the
> > performance penalty I'd be happy to entertain it. Perhaps something
> > along the lines of settype(cx_Oracle.DATETIME, <YourTypeOrMethod>)
> > which would then call <YourTypeOrMethod> with a particular set of
> > parameters? Any thoughts on this?
>
>
>

Re: [cx-oracle-users] Results as dict

[Andy H. <and...@gm...>]

Another one for your list is db_row which I've used quite a lot:
http://opensource.theopalgroup.com/

Does all the normal things you'd expect - lets you reference results by
either index, name or attribute. It does it in a space efficient manner, by
only having a single dict for the whole result set (everything else is just
stored as the tuple returned by the DBAPI). There's also a C extension
module for slightly speedier performance.

Andy

Re: [cx-oracle-users] Results as dict

[Anthony T. <ant...@gm...>]

Hmm, in that case perhaps a simple reference to these things in the
README would suffice. Any others that people have used that I should
reference?

On 11/9/06, Hancock, David (DHANCOCK) <DHA...@ar...> wrote:
>
>  We've been using dtuple for several years to do the same thing. You can =
get
> it at:
>
>  http://www.lyra.org/greg/python/dtuple.py
>
>  Cheers!
>  --
>  David Hancock | dha...@ar...
>
>
>  ________________________________
> From: Walter D=F6rwald <wa...@li...>
>  Organization: LivingLogic AG, Bayreuth/Germany
>  Reply-To: <cx-...@li...>
>  Date: Thu, 9 Nov 2006 03:59:38 -0500
>  To: <cx-...@li...>
>  Conversation: [cx-oracle-users] Results as dict
>  Subject: Re: [cx-oracle-users] Results as dict
>
>  Chris Dunscombe wrote:
>  >
>  > --- Anthony Tuininga <ant...@gm...> wrote:
>  >
>  >> Ah, I see. You can do the same thing with cx_Oracle (create a
>  >> subclassed cursor that returns rows as dictionaries) quite easily. It=
s
>  >> simply not "out of the box" as you say. There is an implicit
>  >> performance penalty involved (dictionaries cost a great deal more to
>  >> build than tuples do relatively speaking) so I've tended to avoid suc=
h
>  >> techniques. My personal preference for when performance is not an
>  >> issue is to return objects with attributes defined for each of the
>  >> columns. Both of these techniques are possible to use without any hel=
p
>  >> from any module author and should be available globally as a recipe o=
r
>  >> some such. Would there be appreciation for including such things as
>  >> part of documentation or sample files?
>  >
>  > I would appreciate these sort things very much.
>
>  ll-orasql (http://www.livinglogic.de/Python/orasql/)
> implements that
>  kind of functionality (i.e. column access via row.column and
> row["column"]).
>
>  Servus,
>      Walter
>
>
> -------------------------------------------------------------------------
>  Using Tomcat but need to do more? Need to support web services, security=
?
>  Get stuff done quickly with pre-integrated technology to make your job
> easier
>  Download IBM WebSphere Application Server v.1.0.1 based on Apache Geroni=
mo
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk=3D120709=3D263057=3D121642
> <http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642>
>  _______________________________________________
>  cx-oracle-users mailing list
>  cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronim=
o
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>

Re: [cx-oracle-users] Results as dict

[Hancock, D. (DHANCOCK) <DHA...@ar...>]

We've been using dtuple for several years to do the same thing. You can get
it at:

http://www.lyra.org/greg/python/dtuple.py

Cheers!
--=20
David Hancock | dha...@ar...



From: Walter D=F6rwald <wa...@li...>
Organization: LivingLogic AG, Bayreuth/Germany
Reply-To: <cx-...@li...>
Date: Thu, 9 Nov 2006 03:59:38 -0500
To: <cx-...@li...>
Conversation: [cx-oracle-users] Results as dict
Subject: Re: [cx-oracle-users] Results as dict

Chris Dunscombe wrote:
>
> --- Anthony Tuininga <ant...@gm...> wrote:
>
>> Ah, I see. You can do the same thing with cx_Oracle (create a
>> subclassed cursor that returns rows as dictionaries) quite easily. Its
>> simply not "out of the box" as you say. There is an implicit
>> performance penalty involved (dictionaries cost a great deal more to
>> build than tuples do relatively speaking) so I've tended to avoid such
>> techniques. My personal preference for when performance is not an
>> issue is to return objects with attributes defined for each of the
>> columns. Both of these techniques are possible to use without any help
>> from any module author and should be available globally as a recipe or
>> some such. Would there be appreciation for including such things as
>> part of documentation or sample files?
>
> I would appreciate these sort things very much.

ll-orasql (http://www.livinglogic.de/Python/orasql/) implements that
kind of functionality (i.e. column access via row.column and row["column"])=
.

Servus,
    Walter


-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job
easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=3Dlnk=3D120709=3D263057=3D121642
<http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=3D121642>
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users