Expresssion is of wrong type when calling function using cx_Oracle

Question

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.

See this answer to a similar question - I was also going to suggest an anonymous block wrapper to return a valid SQL type. stackoverflow.com/questions/64217140/… — kfinity
– kfinity, Commented Jul 14, 2021 at 18:59
Is this a homework question? It was also asked (and answered) at stackoverflow.com/questions/68379599/… — Christopher Jones
– Christopher Jones, Commented Jul 14, 2021 at 23:21

Gober · Accepted Answer · 2021-07-14 20:34:39Z

2

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())

answered Jul 14, 2021 at 20:34

Gober

1672 gold badges3 silver badges13 bronze badges

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

2 Comments

Christopher Jones Over a year ago

In any not-even-so-recent version of cx_Oracle and the Oracle Client, there's no need to map the boolean, see stackoverflow.com/a/68386070/4799035

alaptiko Over a year ago

I'm using cx_Oracle v 8.2.1. And bool conversion is a solution for me. What is version where conversion is not required?

Anthony Tuininga · Accepted Answer · 2021-07-16 22:41:12Z

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().

Collectives™ on Stack Overflow

Expresssion is of wrong type when calling function using cx_Oracle

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related