CODEWITHSUNDEEP
pythonpython-3.xoracle-databasecx-oracle
Gober
Gober

Reputation: 167

Expresssion is of wrong type when calling function using cx_Oracle

I'm trying to call FND_CONCURRENT.WAIT_FOR_REQUEST from the PL/SQL API for Concurrent Processing using python and cx_Oracle. In order to call WAIT_FOR_REQUEST the following parameters need to be provided :

(         request_id IN number default NULL,
          interval   IN number default 60,
          max_wait   IN number default 0,
          phase      OUT varchar2,
          status     OUT varchar2,
          dev_phase  OUT varchar2,
          dev_status OUT varchar2,
          message    OUT varchar2) return  boolean;

So my python code looks like:

submitted_request = cursor.var(cx_Oracle.STRING)
phase = cursor.var(cx_Oracle.STRING)
status = cursor.var(cx_Oracle.STRING)
dev_phase = cursor.var(cx_Oracle.STRING)
dev_status = cursor.var(cx_Oracle.STRING)
message = cursor.var(cx_Oracle.STRING)
cursor.callfunc('fnd_concurrent.wait_for_request', submitted_request,
                [141116467,
                 1,
                 1,
                 phase,
                 status,
                 dev_phase,
                 dev_status,
                 message])

But when I try to run the code the following error appears:

cx_Oracle.DatabaseError: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type

My insights are that submitted_request is causing the problem but I cannot find in the documentation how to get a boolean from a function (I've already tried int but no luck so far).

Thanks in advance.

Upvotes: 2

Views: 376

Answers (2)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

You can use boolean directly as follows:

phase = cursor.var(cx_Oracle.STRING)
status = cursor.var(cx_Oracle.STRING)
dev_phase = cursor.var(cx_Oracle.STRING)
dev_status = cursor.var(cx_Oracle.STRING)
message = cursor.var(cx_Oracle.STRING)
submitted_request = cursor.callfunc('fnd_concurrent.wait_for_request', bool,
                [141116467,
                 1,
                 1,
                 phase,
                 status,
                 dev_phase,
                 dev_status,
                 message])

Note that cursor.callfunc() will internally create a variable of the type specified as the return type (bool in this case) and then return the value of that variable as the return value of cursor.callfunc().

Upvotes: 1

Gober
Gober

Reputation: 167

Following kfinity's suggestion about using an anonymous block wrapper and a pretty similar question, the following code is going to work:

outVal = cursor.var(int)
phase = cursor.var(cx_Oracle.STRING)
status = cursor.var(cx_Oracle.STRING)
dev_phase = cursor.var(cx_Oracle.STRING)
dev_status = cursor.var(cx_Oracle.STRING)
message = cursor.var(cx_Oracle.STRING)
sql="""
  begin
    :outVal := sys.diutil.bool_to_int(
        fnd_concurrent.wait_for_request(
            :id,
            :interval, 
            :max_wait,
            :phase,
            :status,
            :dev_phase,
            :dev_status,
            :message
        )
    );
  end;
  """
cursor.execute( 
    sql,
    outVal=outVal,
    id='141116467',
    interval='1',
    max_wait='1',
    phase=phase,
    status=status,
    dev_phase=dev_phase,
    dev_status=dev_status,
    message=message
)
print(outVal.getvalue())

Upvotes: 2

Related Questions