To give an update: To run a stored procedure which returns a ref cursor, I tried: c1 = db.cursor() c2 = db.cursor() sql = "storedProcedureName(:inparam1, :inparam2, etccc, :outparam)" options = (inparam1, inparam2, etcccc, c2) c1.execute(sql, options) As recommended below. This did not work.
However, this worked: c1 = db.cursor() c2 = db.cursor() c2 = c1.procedures.storedProcedureName(INparam1, INparam2, etc..) Only the INparams are given as arguments. c2 is returned as a cursor OUTparam. Then, r = c2.fetchall(), or r = c2.fetchone() Etc..... This was causing a segmentation fault on a Sun Solaris box. On Windows, I got an actual error message. While fetching, the cursor has a field of ora datatype TimeStamp. This was crashing DCOracle2. a to_char solved the issue. I am using DCOracle2 and Oracle 10 on Solaris (and Windows XP). Did anyone run into issues of handling TimeStamp oracle data type with DCOracle2? Maan -----Original Message----- From: Maan M. Hamze [mailto:[EMAIL PROTECTED] Sent: Saturday, March 17, 2007 5:32 PM To: [email protected] Subject: Re: [Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR "m.banaouas" [EMAIL PROTECTED]: 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 >> [email protected] >> http://mail.zope.org/mailman/listinfo/zope-db >> ------------------------------ _______________________________________________ Zope-DB mailing list [email protected] http://mail.zope.org/mailman/listinfo/zope-db End of Zope-DB Digest, Vol 44, Issue 9 ************************************** _______________________________________________ Zope-DB mailing list [email protected] http://mail.zope.org/mailman/listinfo/zope-db
