Reputation: 21
I am trying to passing the Oracle Cursor from python to Oracle Stored Procedure(SP). The requirement is that SP fetch the data and insert the data into the table. The SP take two parameters- p1 and p2, p1->VARCHAR2 and p2 is sys_refcursor.
Below is the Stored Procedure details:
p2 is sys_refcursor, the data will be in concatenated format (col1|col2|...)
CREATE OR REPLACE PROCEDURE pt1 (p1 IN VARCHAR2,
p2 IN sys_refcursor)
IS
t VARCHAR2(32767);
v_err_cd NUMBER;
v_err_msg VARCHAR2(32767);
BEGIN
LOOP
fetch p2
into t;
INSERT INTO LIBRA.test_2(col1,col2)
VALUES(p1,t);
EXIT WHEN p2%NOTFOUND;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
rollback;
v_err_cd := SQLCODE;
v_err_msg := v_err_msg || 'SQL Error:' || SUBSTR(dbms_utility.format_error_stack() || chr(10) || dbms_utility.format_error_backtrace(),1,300);
RAISE_APPLICATION_ERROR (-20101,'Error Code : ' || v_err_cd || 'Exception : ' || v_err_msg );
END pt1;
srcqry = "select col1 || '|' || col2 from TEST rownum < 2"
oraconn = qz.core.sqldbs.connect(DBContext.getOracleConnectionName()) ##TODO Add a cursor in dbcontext
qzdb = qz.data.qztable_dbapi.QzTableSql(oraconn)
cur = qzdb.conn.cursor()
result = cur.execute(srcqry)
print "type of result",type(result) # type 'OracleCursor'
lineStr = 'Call1'
cur.callproc("LIBRA.pt1",[linestr,result])
cur.close()
qzdb.conn.close()
Getting "DatabaseError: ORA-01036: illegal variable name/number" in the line cur.callproc("LIBRA.pt1",[linestr,result])
Please help!
Thanks, Bala
Upvotes: 2
Views: 885
Reputation: 50017
All database object names are UPPERCASE by default in Oracle, even if they're lowercase in your source code. The solution is to change your invocation of cur.callproc
to use "LIBRA.PT1"
instead of "LIBRA.pt1"
.
Best of luck.
Upvotes: 0