[Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR

Maan M. Hamze mmhamze at pleiades.net
Sat Mar 17 18:32:07 EDT 2007


"m.banaouas" banaouas.medialog at wanadoo.fr:
options = "(2714, " + "'" + "99999999" + "'," + "36, 1, c2)"

it's wrong!
you must give a sequence as second parameter of execute method.

So you do like this:
options = (2714, '99999999',36, 1, c2)
c1.execute(sql, options)
------------------
I tried it both ways.
With what you suggest:
Import DCOracle2
db = DCOracle2.connect(connectionString)
c1 = db.cursor()
c2 = db.cursor()
options = (2714, '99999999', 36, 1, c2)
sql = "storedProcedureName(:INparam1, :INparam2, :INparam3, :INparam4,
:OUTparam)
c1.execute(sql, options)

I am getting now:
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/opt/python/lib/python2.4/site-packages/DCOracle2/DCOracle2.py",
line 98
7, in execute
    self._cursor.bindbypos(i, p)
ValueError: invalid data type bound

Printing options yield:
>> print options
(5920, '99999999', 36, 1, <DCOracle2.DCOracle2.cursor instance at
0x19eee0>)
Would this be causing the invalid data type bound error above?

Also, should not the following work:
c2 = c1.storedProcedureName(2714, '99999999', 36, 1)  ??
Maan


Maan M. Hamze a écrit :
 > Hello -
 > Thanks for your help.  I am still getting errors -
 > You wrote:
 > sql = "sp1(INparam1, :INparam2, :INparam3, :INparam4, :ref_cur)"
 >
 > Did you mean:
 > sql = "sp1(:INparam1, :INparam2, :INparam3, :INparam4, :ref_cur)"
 > (notice :INparam1 instead of INparam1)
 >
 > Assume sp1 is hrpofficial, INparam1 = 2714, INparam2 = '99999999',
 > INparam3 = 36, and INparam4 = 1
 >
 > db = DCOracle2.connection(connectionString)
 > c1 = db.cursor()
 > c2 = db.cursor()
 > sql = "hrpofficial(:INparam1, :INparam2, :INparam3, :INparam4,
 > :ref_cur)"
 > options = "(2714, " + "'" + "99999999" + "'," + "36, 1, c2)"
 > c1.execute(sql, options)
 >
 > I am getting an error:
 > DatabaseError: (900, 'ORA-00900: invalid SQL statement')
 >
 > Any hints?
 > Thanks again,
 > Maan
 >
 > for row in C2:
 >   ...
 >
 > Maan M. Hamze a écrit :
 >> I am using DCOCralce2 with Python 2.41, and Oracle 9.
 >> I have a stored procedure (sp1) that takes 4 IN parameters, with one
 > OUT
 >> parameter.  The OUT parameter is a **ref_cursor** that holds a data
 > set.
 >> I am doing the following:
 >> db = DCOracle2.connection(connectionString)
 >> C1 = db.cursor()
 >> C2 = db.cursor()
 >> #I run the following holding the result into the cursor C2
 >> #since the OUT param is a ref_cur
 >> C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)
 >>
 >> I expect to get a data set
 >> I know there is data when sp1 is run
 >> But I am getting an empty data set when I fetch data via C2 cursor.
 >> Do you have any idea how to make this work when a stored procedure
has
 > a
 >> ref_cur OUT parameter?
 >> Thanks,
 >> Maan
 >>
 >>
 >> _______________________________________________
 >> Zope-DB mailing list
 >> Zope-DB at zope.org
 >> http://mail.zope.org/mailman/listinfo/zope-db
 >>





------------------------------

_______________________________________________
Zope-DB mailing list
Zope-DB at zope.org
http://mail.zope.org/mailman/listinfo/zope-db


End of Zope-DB Digest, Vol 44, Issue 9
**************************************




More information about the Zope-DB mailing list