[cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to Oracle procedure from python code

Discussion:

Guy Pilosof

2008-06-27 06:27:27 UTC

heyI'm using cx_Oracle to connect with my python code to Oracle database,and i wanted to know how can i pass PL/SQL-BOOLEAN argument to Oracle procedure from my python code (as followed) ?my code is something like "cursor.execute(query,varList)"where query is "begin SYS.MYPACKAGE.MYPROCEDURE(varcharArg => :1,booleanArg => :2); end;" (my Oracle function get two values:1.varchar value,2.boolean value)and varList is my argument list, and the second argument need to be of type BOOLEAN, how can i achieve that ?thanks !
_________________________________________________________________
Windows Live Hotmail is giving away Zunes. Enter for your chance to win.
http://www.windowslive-hotmail.com/ZuneADay/?locale=en-US&ocid=TXT_TAGLM_Mobile_Zune_V3

Andreas Mock

2008-06-27 12:31:55 UTC

Permalink

Hi,

we had that issue several times:
Boolean is not a vlaid SQL-Type, therefor there is no equivalent with cx_Oracle.
You have to call your PL/SQL-Procedure with an anonymous wrapper mapping
1 => TRUE and 0 => False or something like that.

Best regards
Andreas Mock

-----Ursprüngliche Nachricht-----
Gesendet: 27.06.08 08:27:42
Betreff: [cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to Oracle procedure from python code

hey

I'm using cx_Oracle to connect with my python code to Oracle database,
and i wanted to know how can i pass PL/SQL-BOOLEAN argument to Oracle
procedure from my python code (as followed) ?
my code is something like "cursor.execute(query,varList)"
where query is "begin SYS.MYPACKAGE.MYPROCEDURE(varcharArg => :1,
booleanArg => :2); end;" (my Oracle function get two values:1.varchar
value,2.boolean value)
and varList is my argument list, and the second argument need to be
of type BOOLEAN, how can i achieve that ?
thanks !
Windows Live Hotmail is giving away Zunes. Enter for your chance to
win. Enter Now!-------------------------------------------------------
------------------ Check out the new SourceForge.net Marketplace. It'
s the best place to buy or sell services for just about anything Open
Source. http://sourceforge.net/services/buy/index.php_________________
______________________________ cx-oracle-users mailing list cx-oracle-
listinfo/cx-oracle-users

Anthony Tuininga

2008-06-27 13:57:17 UTC

Permalink

cx_Oracle DOES have partial support for that in the callproc() interface.

If you use cursor.callproc("Fred", False) the value will be passed as
a true boolean to PL/SQL. There are no other situations where that
works at the moment. Essentially what I do is change the generated SQL
that is being called to use :2 = 1 and bind in an integer 0 or 1 as
appropriate. Make sense?

Anthony

Post by Andreas Mock
Hi,
Boolean is not a vlaid SQL-Type, therefor there is no equivalent with cx_Oracle.
You have to call your PL/SQL-Procedure with an anonymous wrapper mapping
1 => TRUE and 0 => False or something like that.
Best regards
Andreas Mock

-----Ursprüngliche Nachricht-----
Gesendet: 27.06.08 08:27:42
Betreff: [cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to Oracle procedure from python code

hey

-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
cx-oracle-users mailing list
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Andreas Mock

2008-06-27 20:02:25 UTC

Permalink

Hi Anthony,

sorry for being inaccurate and misleading... :-)
That means: cx_Oracle generates the appropriate wrapper code,
but there is no boolean type in SQL only in PL/SQL, isn't it?

Best regards
Andreas Mock

-----Ursprüngliche Nachricht-----
Gesendet: 27.06.08 15:57:26
Betreff: Re: [cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to Oracle procedure from python code
cx_Oracle DOES have partial support for that in the callproc() interface.
If you use cursor.callproc("Fred", False) the value will be passed as
a true boolean to PL/SQL. There are no other situations where that
works at the moment. Essentially what I do is change the generated SQL
that is being called to use :2 = 1 and bind in an integer 0 or 1 as
appropriate. Make sense?
Anthony

-----Ursprüngliche Nachricht-----
Gesendet: 27.06.08 08:27:42
Betreff: [cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to Oracle procedure from python code

hey

Anthony Tuininga

2008-06-27 21:30:32 UTC

Permalink

Post by Andreas Mock
Hi Anthony,
sorry for being inaccurate and misleading... :-)

No problem at all. I appreciate you coming in and filling in for me.
:-) I can always add a bit more information if I feel that is helpful.

Post by Andreas Mock
That means: cx_Oracle generates the appropriate wrapper code,
but there is no boolean type in SQL only in PL/SQL, isn't it?

There is no boolean type in SQL, only in PL/SQL -- that is correct,
unfortunately. What I generate is

begin proc_name(:1, :2 = 1); end;

for the situation where the second parameter is a boolean parameter.
Does that clarify things further?

Anthony

Post by Andreas Mock
Best regards
Andreas Mock

-----Ursprüngliche Nachricht-----
Gesendet: 27.06.08 08:27:42
Betreff: [cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to Oracle procedure from python code

hey

Tamás Gulácsi

2008-06-28 05:19:08 UTC

Permalink

Hi,

You should convert it yourself at call time:
cur.execute('''
DECLARE
v_in_bool CONSTANT BOOLEAN := :2 = 1;
v_out_bool PLS_INTEGER;
BEGIN
SYS.MYPACKAGE.MYPROCEDURE(varcharArg => :1, booleanArg => v_bool);
IF v_bool THEN v_out_bool := 1;
ELSE v_out_bool := 0;
END IF;
END;
''', ['vc1', int(abool)])

This way even PL/SQL record/table (associative array of a record etc -
but only "INDEX BY PLS_INTEGER"!) types can be passed to (and retrieved
from) stored procedures.

Tamás Gulácsi

Date: Fri, 27 Jun 2008 06:27:27 +0000
Subject: [cx-oracle-users] how to pass PL/SQL-BOOLEAN argument to
Oracle procedure from python code
Content-Type: text/plain; charset="iso-8859-1"
heyI'm using cx_Oracle to connect with my python code to Oracle database,and i wanted to know how can i pass PL/SQL-BOOLEAN argument to Oracle procedure from my python code (as followed) ?my code is something like "cursor.execute(query,varList)"where query is "begin SYS.MYPACKAGE.MYPROCEDURE(varcharArg => :1,booleanArg => :2); end;" (my Oracle function get two values:1.varchar value,2.boolean value)and varList is my argument list, and the second argument need to be of type BOOLEAN, how can i achieve that ?thanks !