Call oracle stored procedure with cursor output parameter from python script

Question

I am trying to call a oracle stored procedure with 2 in and 1 out parameter from python script. The problem I am having is passing a cursor out-parameter.

The Oracle stored procedure is essentially:

  PROCEDURE ci_lac_state 
     (LAC_ID_IN IN  VARCHAR2,  
      CI_ID_IN  IN  VARCHAR2 DEFAULT NULL,
      CGI_ID    OUT SYS_REFCURSOR)
  AS
  BEGIN
      OPEN cgi_id FOR
      ...
  END;

The python code calling to the database is:

  #! /usr/bin/python

  import cx_Oracle

  lac='11508'
  ci='9312'

  try:
      my_connection=cx_Oracle.Connection('login/passwd@db_name')
  except cx_Oracle.DatabaseError,info:
      print "Logon Error:",info
      sys.exit()

  my_cursor=my_connection.cursor()
  cur_var=my_cursor.var(cx_Oracle.CURSOR)

  my_cursor.callproc("cgi_info.ci_lac_state", [lac, ci, cur_var])

  print cur_var.getvalue()

And I get such cursor value as the result:

  <__builtin__.OracleCursor on <cx_Oracle.Connection to login@db_name>>

What am I doing wrong?

Thanks.

did you try print cur_var.getvalue(pos=0) ? or may be pos=2 if you need to retreive cur_var which is CGI_ID — user1593705
– user1593705, Commented Sep 30, 2013 at 13:54
possible duplicate of Python-Oracle Passing in a Cursor Out Parameter — ThinkJet
– ThinkJet, Commented Sep 30, 2013 at 14:02
To FoxMaSk. Yes. I've tried to do this "print cur_var.getvalue(pos=0)", but I get the same result "<__builtin__.OracleCursor on <cx_Oracle.Connection to login@db_name>>". The "pos=2" lead to new errors: print cur_var.getvalue(pos=1) IndexError: Variable_GetSingleValue: array size exceeded — user2831245
– user2831245, Commented Sep 30, 2013 at 14:15
To ThinkJet. Unfortunately, my problem is different to post "Python-Oracle Passing in a Cursor Out Parameter" — user2831245
– user2831245, Commented Sep 30, 2013 at 14:46

kpater87 · Accepted Answer · 2016-07-01 15:16:08Z

1

I've just had similar issue. cur_var has type <type 'cx_Oracle.CURSOR'> and cur_var.getvalue() gets object of type <type 'OracleCursor'>. To get data you have to fetched them from the OracleCursor object. Try for example:

print cur_var.getvalue().fetchall()

To see more function of OracleCursor object just check its directory:

dir(cur_var.getvalue())

Hope this will help you!

answered Jul 1, 2016 at 15:16

kpater87

1,2701 gold badge14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ulrik Larsen · Accepted Answer · 2020-08-18 08:05:48Z

0

You can define your output and then loop through using an implicit cursor like this:

outVal = cursor.var(cx_Oracle.CURSOR)

something = cursor.callproc(<your procedure name>, [outVal,<input variable here>])

for implicitCursor in cursor.getimplicitresults():
    for row in implicitCursor:
        
        fout.write(''.join(row)) # note: tuple conversion inside ()
        fout.write('\n')

answered Aug 18, 2020 at 8:05

Ulrik Larsen

1391 silver badge13 bronze badges

Collectives™ on Stack Overflow

Call oracle stored procedure with cursor output parameter from python script

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related