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

Re: [cx-oracle-users] Re: cx_Oracle Python utility

[Geoff G. <ge...@ge...>]

cx_Oracle 3.1 builds acceptably under Python 2.1.3, but fails to run.
Apparently 3.1 has used PyString_FromFormat, and that doesn't exist
prior to some version of 2.2.

I have a patch -- it's not exactly elegant, but it gets the job done
and appears to run correctly. I would submit as a bug and a proposed
patch on sourceforge (and will if you tell me to) but I wasn't sure it
wasn't actually a "feature".

Patch included below .sig.

Thanks,
--G.

-- 
Geoff Gerrietts <geoff at gerrietts dot net>      http://www.gerrietts.net/
   "If I were two-faced, would I be wearing this one?" --Abraham Lincoln


Index: cx_Oracle.c
===================================================================
RCS file: /src/3rdparty/cxoracle/cx_Oracle.c,v
retrieving revision 1.1.1.1
diff -u -r1.1.1.1 cx_Oracle.c
--- cx_Oracle.c 2003/08/22 13:37:37 1.1.1.1
+++ cx_Oracle.c 2003/08/29 23:15:27
@@ -146,9 +146,10 @@
         return NULL;
          
               // return the formatted string
               -    return
                    PyString_FromFormat("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS="
                    -            "(PROTOCOL=TCP)(HOST=%s)(PORT=%d)))(CONNECT_DATA=(SID=%s)))",
                    -            host, port, sid);
                    +    return PyString_Format(PyString_FromString(
                    +
                    "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS="
                    +
                    "(PROTOCOL=TCP)(HOST=%s)(PORT=%d)))(CONNECT_DATA=(SID=%s)))"),
                    +            args);
                     }

Re: [cx-oracle-users] Integers in oracle

[Anthony T. <an...@co...>]

Unfortunately, in the example that started this thread, the result would
be "1." when printed (in other words it doesn't work as well for
integers as it does for "floating point" numbers) and the use of this
module would also induce considerable overhead. Worse still, I'd have to
"port" this to C since cx_Oracle uses C. I could (I suppose) include
fixedpoint.py in the distribution of cx_Oracle and import it directly
but that is less palatable. I most definitely would not want to do this
as the default but perhaps as an option like OPT_NumbersAsFixedPoint or
something like that. If there is enough interest in this, I am willing
to pursue this. Anyone else got any comments or ideas?

On Thu, 2003-08-28 at 08:12, Marcos Sánchez Provencio wrote:
> I think that the right way is to have a decimal type, with the same
> features as the oracle number.
> 
> We are using Tim Peters' fixedpoint module for that.
> 
> http://fixedpoint.sourceforge.net/
> 
> 
> 
> El jue, 28-08-2003 a las 15:59, Anthony Tuininga escribi:
> > Unfortunately, Oracle declares the "integer" to be without scale and
> > precision. Internally, cx_Oracle checks the scale and precision to
> > determine whether or not an integer ought to be returned.
> Specifically,
> > if the precision is between 1 and 9 and the scale is 0, an integer is
> > returned; otherwise, a float is returned.
> > 
> > This has always annoyed me but I don't know a good way around it.
> Oracle
> > has a numeric precision exceeding floating point by a great deal so a
> > while ago I added a "switch" OPT_NumbersAsStrings which, if set, will
> > return all numbers as strings instead of integers or floating point
> > numbers. This is useful for retaining the precision of the Oracle
> > numbers. You could use that, if you wish but it still requires you to
> > run int() on the result.
> > 
> > I have considered a method for telling cx_Oracle that the values ought
> > to be returned as integers, ignoring the precision and scale, but I
> > haven't come up with anything useful yet. Got any suggestions? One
> > possibility is the following:
> > 
> > vars = cursor.execute("select cast(1 as int) from dual")
> > vars[0].returntype = cx_Oracle.INTEGER
> > result, = cursor.fetchone()
> > 
> > Another is:
> > 
> > cursor.define(1, cx_Oracle.INTEGER)
> > cursor.execute("select cast(1 as int) from dual")
> > result, = cursor.fetchone()
> > 
> > At this point, result would be an integer. Neither is particularly
> > pleasant but perhaps better than the current situation. Any thoughts?
> > 
> > On Thu, 2003-08-28 at 04:05, Marcos Snchez Provencio wrote:
> > > Why is it that I get 1.0 when I ask for 
> > > 
> > > select cast(1 as int) from dual
> > > ?
> > > 
> > > Is this Oracle?, Python?, me?
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] Integers in oracle

[Marcos P. <msa...@gr...>]

I think that the right way is to have a decimal type, with the same
features as the oracle number.

We are using Tim Peters' fixedpoint module for that.

http://fixedpoint.sourceforge.net/



El jue, 28-08-2003 a las 15:59, Anthony Tuininga escribi=F3:
> Unfortunately, Oracle declares the "integer" to be without scale and
> precision. Internally, cx_Oracle checks the scale and precision to
> determine whether or not an integer ought to be returned. Specifically,
> if the precision is between 1 and 9 and the scale is 0, an integer is
> returned; otherwise, a float is returned.
>=20
> This has always annoyed me but I don't know a good way around it. Oracle
> has a numeric precision exceeding floating point by a great deal so a
> while ago I added a "switch" OPT_NumbersAsStrings which, if set, will
> return all numbers as strings instead of integers or floating point
> numbers. This is useful for retaining the precision of the Oracle
> numbers. You could use that, if you wish but it still requires you to
> run int() on the result.
>=20
> I have considered a method for telling cx_Oracle that the values ought
> to be returned as integers, ignoring the precision and scale, but I
> haven't come up with anything useful yet. Got any suggestions? One
> possibility is the following:
>=20
> vars =3D cursor.execute("select cast(1 as int) from dual")
> vars[0].returntype =3D cx_Oracle.INTEGER
> result, =3D cursor.fetchone()
>=20
> Another is:
>=20
> cursor.define(1, cx_Oracle.INTEGER)
> cursor.execute("select cast(1 as int) from dual")
> result, =3D cursor.fetchone()
>=20
> At this point, result would be an integer. Neither is particularly
> pleasant but perhaps better than the current situation. Any thoughts?
>=20
> On Thu, 2003-08-28 at 04:05, Marcos S=E1nchez Provencio wrote:
> > Why is it that I get 1.0 when I ask for=20
> >=20
> > select cast(1 as int) from dual
> > ?
> >=20
> > Is this Oracle?, Python?, me?
--=20
gpg --recv-keys --keyserver wwwkeys.pgp.net B9AD9B1B

Re: [cx-oracle-users] Integers in oracle

[Anthony T. <an...@co...>]

Unfortunately, Oracle declares the "integer" to be without scale and
precision. Internally, cx_Oracle checks the scale and precision to
determine whether or not an integer ought to be returned. Specifically,
if the precision is between 1 and 9 and the scale is 0, an integer is
returned; otherwise, a float is returned.

This has always annoyed me but I don't know a good way around it. Oracle
has a numeric precision exceeding floating point by a great deal so a
while ago I added a "switch" OPT_NumbersAsStrings which, if set, will
return all numbers as strings instead of integers or floating point
numbers. This is useful for retaining the precision of the Oracle
numbers. You could use that, if you wish but it still requires you to
run int() on the result.

I have considered a method for telling cx_Oracle that the values ought
to be returned as integers, ignoring the precision and scale, but I
haven't come up with anything useful yet. Got any suggestions? One
possibility is the following:

vars = cursor.execute("select cast(1 as int) from dual")
vars[0].returntype = cx_Oracle.INTEGER
result, = cursor.fetchone()

Another is:

cursor.define(1, cx_Oracle.INTEGER)
cursor.execute("select cast(1 as int) from dual")
result, = cursor.fetchone()

At this point, result would be an integer. Neither is particularly
pleasant but perhaps better than the current situation. Any thoughts?

On Thu, 2003-08-28 at 04:05, Marcos Sánchez Provencio wrote:
> Why is it that I get 1.0 when I ask for 
> 
> select cast(1 as int) from dual
> ?
> 
> Is this Oracle?, Python?, me?
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] Integers in oracle

[Marcos P. <msa...@gr...>]

=BFWhy is it that I get 1.0 when I ask for=20

select cast(1 as int) from dual
?

Is this Oracle?, Python?, me?
--=20
gpg --recv-keys --keyserver wwwkeys.pgp.net B9AD9B1B

[cx-oracle-users] Re: cx_Oracle Python utility

[Anthony T. <an...@co...>]

I hope you don't mind me sending this to the mailing list for cx_Oracle
for their enlightenment as well.

The method you are using is not supported. It could be, but there is a
simpler method that works. :-) I just added another test to the suite
which demonstrates how this is done (which will be released with the
next version).

The snippet assumes that you have the table TestStrings from the
cx_Oracle test suite created and populated along with the following
package:

create or replace package cx_Oracle.pkg_TestOutCursors as
                                                                                
  type udt_RefCursor is ref cursor;
                                                                                
  procedure TestOutCursor (
    a_MaxIntValue               number,
    a_Cursor                    out udt_RefCursor
  );
                                                                                
end;
/
                                                                                
create or replace package body cx_Oracle.pkg_TestOutCursors as
                                                                                
  procedure TestOutCursor (
    a_MaxIntValue               number,
    a_Cursor                    out udt_RefCursor
  ) is
  begin
    open a_Cursor for
      select
        IntCol,
        StringCol
      from TestStrings
      where IntCol <= a_MaxIntValue
      order by IntCol;
  end;
                                                                                
end;
/

Then the following snippet of Python code does the trick:

import cx_Oracle
                                                                                
connection = cx_Oracle.connect("cx_Oracle/pw@dsn")
cursor = connection.cursor()
refcursor = connection.cursor()
cursor.callproc("pkg_TestOutCursors.TestOutCursor", (2, refcursor))
print "Desc:", refcursor.description
print "Results:", refcursor.fetchall()

In essence, instead of calling

refcursorvar = cursor.var(cx_Oracle.CURSOR)
refcursor = refcursorvar.getvalue()

simply use

refcursor = connection.cursor()

and bind the cursor directly.

Make sense?

On Mon, 2003-08-25 at 10:28, Schedler, Kurt wrote:
> Hi Anthony,
> 
> I'm working on a Python module which uses cx_Oracle for connecting to
> our Oracle 9i database.  I'm working on calling stored procedures
> which return cursors via an OUT parameter.  I'm having problems
> creating refcursor variables with cx_Oracle:
> 
> >>> import cx_Oracle as ora
> >>> ora.version
> '3.1'
> >>> con = ora.connect("###/######@####")
> >>> cur = con.cursor()
> >>> refcur = cur.var(ora.CURSOR)
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> NotSupportedError: Variable_TypeByPythonType(): unhandled data type
> >>> 
> 
> Is this a bug or am I doing something wrong?
> 
> Thanks for your help!
> 
> Kurt.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] cx_Oracle 3.1

[Anthony T. <an...@co...>]

What is cx_Oracle?

cx_Oracle is a Python extension module that allows access to Oracle and
conforms to the Python database API 2.0 specifications with a few
exceptions.


Where do I get it?

http://starship.python.net/crew/atuining
http://www.computronix.com/utilities.shtml
(it may be a few days before the second site is updated)


What's new?
 1) Added support for connecting with SYSDBA and SYSOPER access which is
     needed for connecting as sys in Oracle 9i.
 2) Only check the dictionary size if the variable is not NULL;
     otherwise, an error takes place which is not caught or cleared;
this
     eliminates a spurious "Objects/dictobject.c:1258: bad argument to
     internal function" in Python 2.3.
 3) Add support for session pooling. This is only support for Oracle 9i
     but is amazingly fast -- about 100 times faster than connecting.
 4) Add support for statement caching when pooling sessions, this
     reduces the parse time considerably. Unfortunately, the Oracle OCI
     does not allow this to be easily turned on for normal sessions.
 5) Add method trim() on CLOB and BLOB variables for trimming the size.
 6) Add support for externally identified users; to use this feature
     leave the username and password fields empty when connecting.
 7) Add method cancel() on connection objects to cancel long running
     queries. Note that this only works on non-Windows platforms.
 8) Add method callfunc() on cursor objects to allow calling a function
     without using an anonymous PL/SQL block.
 9) Added documentation on objects that were not documented. At this
     point all objects, methods and constants in cx_Oracle have been
     documented.
10) Added support for timestamp columns in Oracle 9i.
11) Added module level method makedsn() which creates a data source name
     given the host, port and SID.
12) Added constant "buildtime" which is the time when the module was
     built as an additional means of identifying the build that is in
     use.
13) Binding a value that is incompatible to the previous value that was
     bound (data types do not match or array size is larger) will now
     result in a new bind taking place. This is more consistent with the
     DB API although it does imply a performance penalty when used.

-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

RE: [cx-oracle-users] calling Oracle functions and getting result s back

[Anthony T. <an...@co...>]

It does seem simple and elegant but as soon as I started looking into
coding it, I ran into one little problem. Specifically, the return type
is unknown and needs to be specified. On a standalone function, this
could fairly reasonably be determined by either using the
OCIDescribeAny() functionality in OCI or by reading the data dictionary.
The problem comes with packaged functions because Oracle then allows
overloads. And duplicating Oracle's method of deciding which overloaded
function to execute doesn't seem worthwhile. So I am now considering the
following:

value = cursor.callfunc(funcName, returnType, parameters = [])

where returnType is one of the values accepted by setinputsizes() or is
a variable already created by the cursor.var() or cursor.arrayvar()
methods. How does that sound?

On Fri, 2003-08-01 at 04:06, McCafferty, Chris wrote:
> Hi,
> 
> >>value = cursor.callfunc(funcName, parameters)
> 
> This seems simple and elegant, and mirrors calling a procedure. After all,
> to the caller, a function is just a procedure that returns a value. I know
> that there are more subtleties than that in Oracle, but that really is for
> the function writer, not the API caller.
> 
> Let me know if you add this and I'll be more than happy to run some tests
> against it.
> 
> Cheers,
> 
> Chris
> 
> -----Original Message-----
> From: Anthony Tuininga [mailto:an...@co...]
> Sent: 25 July 2003 18:31
> To: McCafferty, Chris; cx_Oracle Mailing List
> Subject: RE: [cx-oracle-users] calling Oracle functions and getting
> result s back
> 
> 
> You can certainly vote for it. But do you have any idea of the interface
> that would make sense?
> 
> May I propose the following?
> 
> value = cursor.callfunc(funcName, parameters)
> 
> The value that would be returned would be the return value of the
> function. If you needed out variables in addition, you could do the
> following:
> 
> outVar = cursor.var(cx_Oracle.NUMBER)
> value = cursor.callfunc(funcName, (1, outVar))
> print "Value is:", value
> print "OutVar is:", outVar.getvalue()
> 
> How does that sound? I realize this is different from callproc() but
> since this isn't covered by the DB API it is not necessarily bad. It is
> certainly better than the anonymous PL/SQL block method for the common
> case, wouldn't you agree? Any comments?
> 
> On Fri, 2003-07-25 at 11:22, McCafferty, Chris wrote:
> > Hi,
> > 
> > I see - you do it through execute. Thanks.
> > May i vote for a callFunc extension? It feels more 'pythonic'. But I see
> > your point about having to set up the out var before hand - that kind of
> > messes it up.
> > 
> > Cheers,
> > 
> > Chris
> > 
> > -----Original Message-----
> > From: Anthony Tuininga [mailto:an...@co...]
> > Sent: 24 July 2003 14:42
> > To: McCafferty, Chris
> > Cc: 'cx-...@li...'
> > Subject: Re: [cx-oracle-users] calling Oracle functions and getting
> > results back
> > 
> > 
> > You can do this with cx_Oracle but unfortunately the DB API does not
> > cover the situation with functions. And Oracle provides less than
> > stellar diagnostics when you attempt to call a function as a procedure.
> > :-)
> > 
> > The (simplest) way you can do this is as follows:
> > 
> > import cx_Oracle
> > 
> > connection = cx_Oracle.connect("user/pw@tns")
> > cursor = connection.cursor()
> > resultVar = cursor.var(cx_Oracle.NUMBER)
> > cursor.execute("""
> >     begin
> >         :result := test_func(:param);
> >     end;""",
> >     param = 1,
> >     result = resultVar)
> > print "Result:", resultVar.getvalue()
> > 
> > I have considered extending the DB API to have a "callfunc" or something
> > similar that would return the return value and the tuple of (possibly
> > modified) values but that hasn't seemed all that useful to me. The only
> > other possibility is to modify the tuple that is returned to include the
> > return value at the beginning (or end) of the values. That doesn't
> > appeal to me either.
> > 
> > The DB API does not really handle the possibility of "out" values very
> > well either and has no understanding of anonymous PL/SQL blocks. Thus
> > the reason for the "var" call above.
> > 
> > On Thu, 2003-07-24 at 03:32, McCafferty, Chris wrote:
> > > Hi,
> > > 
> > > Looking through the Aspn archives, there is discussion of returning
> > results
> > > from stored procedures, including Oracle procedures, but no clear-cut
> > > example of calling a function. Is this possible using cx_oracle?
> > > 
> > > For example, if I have this function, which simply increments the number
> > > passed in:
> > > 
> > > CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
> > >     RETURN NUMBER
> > > AS
> > >     j INTEGER := NULL;
> > > BEGIN
> > >     j := v_int + 1;
> > >     RETURN j;
> > > END;
> > > /
> > > 
> > > If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
> > > exist (where mTo below is a cursor created in the normal way):
> > > >>> mTo.callproc('TEST_FUNC', [1])
> > > Traceback (most recent call last):
> > >   File "<stdin>", line 1, in ?
> > > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> > > PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
> > > ORA-06550: line 1, column 7:
> > > PL/SQL: Statement ignored
> > > 
> > > Do I have to use cursor.execute() instead? In which case what format
> > should
> > > the sql string passed to execute take?
> > > 
> > > Note that if I have a procedure that does the same thing (but doesn't
> > > obviously return anything), then I can call it successfully.
> > > 
> > > Can anyone help?
> > > 
> > > Regards,
> > > 
> > > Chris
> > > 
> > > 
> > > ----------------------------------------------------------------------
> > > If you have received this e-mail in error or wish to read our e-mail 
> > > disclaimer statement and monitoring policy, please refer to 
> > > http://www.drkw.com/disc/email/ or contact the sender.
> > > ----------------------------------------------------------------------
> > > 
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> > > Data Reports, E-commerce, Portals, and Forums are available now.
> > > Download today and enter to win an XBOX or Visual Studio .NET.
> > >
> >
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga <an...@co...>

RE: [cx-oracle-users] calling Oracle functions and getting result s back

[McCafferty, C. <Chr...@Dr...>]

Hi,

>>value = cursor.callfunc(funcName, parameters)

This seems simple and elegant, and mirrors calling a procedure. After all,
to the caller, a function is just a procedure that returns a value. I know
that there are more subtleties than that in Oracle, but that really is for
the function writer, not the API caller.

Let me know if you add this and I'll be more than happy to run some tests
against it.

Cheers,

Chris

-----Original Message-----
From: Anthony Tuininga [mailto:an...@co...]
Sent: 25 July 2003 18:31
To: McCafferty, Chris; cx_Oracle Mailing List
Subject: RE: [cx-oracle-users] calling Oracle functions and getting
result s back


You can certainly vote for it. But do you have any idea of the interface
that would make sense?

May I propose the following?

value = cursor.callfunc(funcName, parameters)

The value that would be returned would be the return value of the
function. If you needed out variables in addition, you could do the
following:

outVar = cursor.var(cx_Oracle.NUMBER)
value = cursor.callfunc(funcName, (1, outVar))
print "Value is:", value
print "OutVar is:", outVar.getvalue()

How does that sound? I realize this is different from callproc() but
since this isn't covered by the DB API it is not necessarily bad. It is
certainly better than the anonymous PL/SQL block method for the common
case, wouldn't you agree? Any comments?

On Fri, 2003-07-25 at 11:22, McCafferty, Chris wrote:
> Hi,
> 
> I see - you do it through execute. Thanks.
> May i vote for a callFunc extension? It feels more 'pythonic'. But I see
> your point about having to set up the out var before hand - that kind of
> messes it up.
> 
> Cheers,
> 
> Chris
> 
> -----Original Message-----
> From: Anthony Tuininga [mailto:an...@co...]
> Sent: 24 July 2003 14:42
> To: McCafferty, Chris
> Cc: 'cx-...@li...'
> Subject: Re: [cx-oracle-users] calling Oracle functions and getting
> results back
> 
> 
> You can do this with cx_Oracle but unfortunately the DB API does not
> cover the situation with functions. And Oracle provides less than
> stellar diagnostics when you attempt to call a function as a procedure.
> :-)
> 
> The (simplest) way you can do this is as follows:
> 
> import cx_Oracle
> 
> connection = cx_Oracle.connect("user/pw@tns")
> cursor = connection.cursor()
> resultVar = cursor.var(cx_Oracle.NUMBER)
> cursor.execute("""
>     begin
>         :result := test_func(:param);
>     end;""",
>     param = 1,
>     result = resultVar)
> print "Result:", resultVar.getvalue()
> 
> I have considered extending the DB API to have a "callfunc" or something
> similar that would return the return value and the tuple of (possibly
> modified) values but that hasn't seemed all that useful to me. The only
> other possibility is to modify the tuple that is returned to include the
> return value at the beginning (or end) of the values. That doesn't
> appeal to me either.
> 
> The DB API does not really handle the possibility of "out" values very
> well either and has no understanding of anonymous PL/SQL blocks. Thus
> the reason for the "var" call above.
> 
> On Thu, 2003-07-24 at 03:32, McCafferty, Chris wrote:
> > Hi,
> > 
> > Looking through the Aspn archives, there is discussion of returning
> results
> > from stored procedures, including Oracle procedures, but no clear-cut
> > example of calling a function. Is this possible using cx_oracle?
> > 
> > For example, if I have this function, which simply increments the number
> > passed in:
> > 
> > CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
> >     RETURN NUMBER
> > AS
> >     j INTEGER := NULL;
> > BEGIN
> >     j := v_int + 1;
> >     RETURN j;
> > END;
> > /
> > 
> > If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
> > exist (where mTo below is a cursor created in the normal way):
> > >>> mTo.callproc('TEST_FUNC', [1])
> > Traceback (most recent call last):
> >   File "<stdin>", line 1, in ?
> > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> > PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
> > ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> > 
> > Do I have to use cursor.execute() instead? In which case what format
> should
> > the sql string passed to execute take?
> > 
> > Note that if I have a procedure that does the same thing (but doesn't
> > obviously return anything), then I can call it successfully.
> > 
> > Can anyone help?
> > 
> > Regards,
> > 
> > Chris
> > 
> > 
> > ----------------------------------------------------------------------
> > If you have received this e-mail in error or wish to read our e-mail 
> > disclaimer statement and monitoring policy, please refer to 
> > http://www.drkw.com/disc/email/ or contact the sender.
> > ----------------------------------------------------------------------
> > 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> > Data Reports, E-commerce, Portals, and Forums are available now.
> > Download today and enter to win an XBOX or Visual Studio .NET.
> >
>
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com


----------------------------------------------------------------------
If you have received this e-mail in error or wish to read our e-mail 
disclaimer statement and monitoring policy, please refer to 
http://www.drkw.com/disc/email/ or contact the sender.
----------------------------------------------------------------------

RE: [cx-oracle-users] calling Oracle functions and getting result s back

[Anthony T. <an...@co...>]

You can certainly vote for it. But do you have any idea of the interface
that would make sense?

May I propose the following?

value = cursor.callfunc(funcName, parameters)

The value that would be returned would be the return value of the
function. If you needed out variables in addition, you could do the
following:

outVar = cursor.var(cx_Oracle.NUMBER)
value = cursor.callfunc(funcName, (1, outVar))
print "Value is:", value
print "OutVar is:", outVar.getvalue()

How does that sound? I realize this is different from callproc() but
since this isn't covered by the DB API it is not necessarily bad. It is
certainly better than the anonymous PL/SQL block method for the common
case, wouldn't you agree? Any comments?

On Fri, 2003-07-25 at 11:22, McCafferty, Chris wrote:
> Hi,
> 
> I see - you do it through execute. Thanks.
> May i vote for a callFunc extension? It feels more 'pythonic'. But I see
> your point about having to set up the out var before hand - that kind of
> messes it up.
> 
> Cheers,
> 
> Chris
> 
> -----Original Message-----
> From: Anthony Tuininga [mailto:an...@co...]
> Sent: 24 July 2003 14:42
> To: McCafferty, Chris
> Cc: 'cx-...@li...'
> Subject: Re: [cx-oracle-users] calling Oracle functions and getting
> results back
> 
> 
> You can do this with cx_Oracle but unfortunately the DB API does not
> cover the situation with functions. And Oracle provides less than
> stellar diagnostics when you attempt to call a function as a procedure.
> :-)
> 
> The (simplest) way you can do this is as follows:
> 
> import cx_Oracle
> 
> connection = cx_Oracle.connect("user/pw@tns")
> cursor = connection.cursor()
> resultVar = cursor.var(cx_Oracle.NUMBER)
> cursor.execute("""
>     begin
>         :result := test_func(:param);
>     end;""",
>     param = 1,
>     result = resultVar)
> print "Result:", resultVar.getvalue()
> 
> I have considered extending the DB API to have a "callfunc" or something
> similar that would return the return value and the tuple of (possibly
> modified) values but that hasn't seemed all that useful to me. The only
> other possibility is to modify the tuple that is returned to include the
> return value at the beginning (or end) of the values. That doesn't
> appeal to me either.
> 
> The DB API does not really handle the possibility of "out" values very
> well either and has no understanding of anonymous PL/SQL blocks. Thus
> the reason for the "var" call above.
> 
> On Thu, 2003-07-24 at 03:32, McCafferty, Chris wrote:
> > Hi,
> > 
> > Looking through the Aspn archives, there is discussion of returning
> results
> > from stored procedures, including Oracle procedures, but no clear-cut
> > example of calling a function. Is this possible using cx_oracle?
> > 
> > For example, if I have this function, which simply increments the number
> > passed in:
> > 
> > CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
> >     RETURN NUMBER
> > AS
> >     j INTEGER := NULL;
> > BEGIN
> >     j := v_int + 1;
> >     RETURN j;
> > END;
> > /
> > 
> > If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
> > exist (where mTo below is a cursor created in the normal way):
> > >>> mTo.callproc('TEST_FUNC', [1])
> > Traceback (most recent call last):
> >   File "<stdin>", line 1, in ?
> > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> > PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
> > ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> > 
> > Do I have to use cursor.execute() instead? In which case what format
> should
> > the sql string passed to execute take?
> > 
> > Note that if I have a procedure that does the same thing (but doesn't
> > obviously return anything), then I can call it successfully.
> > 
> > Can anyone help?
> > 
> > Regards,
> > 
> > Chris
> > 
> > 
> > ----------------------------------------------------------------------
> > If you have received this e-mail in error or wish to read our e-mail 
> > disclaimer statement and monitoring policy, please refer to 
> > http://www.drkw.com/disc/email/ or contact the sender.
> > ----------------------------------------------------------------------
> > 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> > Data Reports, E-commerce, Portals, and Forums are available now.
> > Download today and enter to win an XBOX or Visual Studio .NET.
> >
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] calling Oracle functions and getting results back

[Anthony T. <an...@co...>]

You can do this with cx_Oracle but unfortunately the DB API does not
cover the situation with functions. And Oracle provides less than
stellar diagnostics when you attempt to call a function as a procedure.
:-)

The (simplest) way you can do this is as follows:

import cx_Oracle

connection = cx_Oracle.connect("user/pw@tns")
cursor = connection.cursor()
resultVar = cursor.var(cx_Oracle.NUMBER)
cursor.execute("""
    begin
        :result := test_func(:param);
    end;""",
    param = 1,
    result = resultVar)
print "Result:", resultVar.getvalue()

I have considered extending the DB API to have a "callfunc" or something
similar that would return the return value and the tuple of (possibly
modified) values but that hasn't seemed all that useful to me. The only
other possibility is to modify the tuple that is returned to include the
return value at the beginning (or end) of the values. That doesn't
appeal to me either.

The DB API does not really handle the possibility of "out" values very
well either and has no understanding of anonymous PL/SQL blocks. Thus
the reason for the "var" call above.

On Thu, 2003-07-24 at 03:32, McCafferty, Chris wrote:
> Hi,
> 
> Looking through the Aspn archives, there is discussion of returning results
> from stored procedures, including Oracle procedures, but no clear-cut
> example of calling a function. Is this possible using cx_oracle?
> 
> For example, if I have this function, which simply increments the number
> passed in:
> 
> CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
>     RETURN NUMBER
> AS
>     j INTEGER := NULL;
> BEGIN
>     j := v_int + 1;
>     RETURN j;
> END;
> /
> 
> If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
> exist (where mTo below is a cursor created in the normal way):
> >>> mTo.callproc('TEST_FUNC', [1])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> 
> Do I have to use cursor.execute() instead? In which case what format should
> the sql string passed to execute take?
> 
> Note that if I have a procedure that does the same thing (but doesn't
> obviously return anything), then I can call it successfully.
> 
> Can anyone help?
> 
> Regards,
> 
> Chris
> 
> 
> ----------------------------------------------------------------------
> If you have received this e-mail in error or wish to read our e-mail 
> disclaimer statement and monitoring policy, please refer to 
> http://www.drkw.com/disc/email/ or contact the sender.
> ----------------------------------------------------------------------
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] calling Oracle functions and getting results back

[McCafferty, C. <Chr...@Dr...>]

Hi,

Looking through the Aspn archives, there is discussion of returning results
from stored procedures, including Oracle procedures, but no clear-cut
example of calling a function. Is this possible using cx_oracle?

For example, if I have this function, which simply increments the number
passed in:

CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
    RETURN NUMBER
AS
    j INTEGER := NULL;
BEGIN
    j := v_int + 1;
    RETURN j;
END;
/

If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
exist (where mTo below is a cursor created in the normal way):
>>> mTo.callproc('TEST_FUNC', [1])
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Do I have to use cursor.execute() instead? In which case what format should
the sql string passed to execute take?

Note that if I have a procedure that does the same thing (but doesn't
obviously return anything), then I can call it successfully.

Can anyone help?

Regards,

Chris


----------------------------------------------------------------------
If you have received this e-mail in error or wish to read our e-mail 
disclaimer statement and monitoring policy, please refer to 
http://www.drkw.com/disc/email/ or contact the sender.
----------------------------------------------------------------------

Re: [cx-oracle-users] Bug/report cx_Oracle

[Anthony T. <an...@co...>]

If you are referring to this situation:

import cx_Oracle

connection = cx_Oracle.connect("user/pw@tns")
cursor = connection.cursor()
cursor.arraysize = 5
cursor.execute("select someblob from sometable")
rows = cursor.fetchall()

where the number of rows returned is greater than 5, then yes, you are
correct. Unfortunately, that is the way that Oracle works. Internally a
LOB locator is what is actually fetched but it is only valid as long as
no additional fetches take place. The arraysize you specify defines the
number of rows that are fetched in each internal fetch. If you ask for
all of the rows, as many internal fetches will take place as needed and
so the original LOB locators will be overwritten. A couple of other
people have mentioned this problem to me as well so perhaps I ought to
add some code to check for this situation and at least raise an error,
eh? Any thoughts on that?

In the meantime, your problem will disappear completely if you use the
following method:

while 1:
    row = cursor.fetchone()
    if row is None:
        break
    do_some_stuff()

OR, if you are in Python 2.2 and running cx_Oracle 3.0 or greater

for row in cursor:
    do_some_stuff()

On Mon, 2003-07-07 at 08:07, Birgit Dreyer wrote:
> Hello,
> 
> if you select LOBs with an arraysize smaller than the resulted select
> lists, your LOB Locators points to the LOBs of the first array.
> 
> regards,
> 
> Birgit
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100006ave/direct;at.asp_061203_01/01
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] Bug/report cx_Oracle

[Birgit D. <bi...@pr...>]

Hello,

if you select LOBs with an arraysize smaller than the resulted select
lists, your LOB Locators points to the LOBs of the first array.

regards,

Birgit
=