How to create Oracle user defined type in python cx_Oracle?

I have a short write up on using cx_Oracle for CRUD functionality, and in the insert segment I use the following to retrieve the value of the returning clause.

new_id = cur.var(cx_Oracle.NUMBER)

statement = 'insert into cx_people(name, age, notes) values (:1, :2, :3) returning id into :4'
cur.execute(statement, ('Sandy', 31, 'I like horses', new_id))

sandy_id = new_id.getvalue()

I have used this same method with out parameters from procudures both for single values cur.var(cx_Oracle.NUMBER) and arrays cur.arrayvar(cx_Oracle.NUMBER,1000);

I haven't specifically used the cx_Oracle.OBJECT type, but I assume it works similar to other types.

Docs: cx_Oracle.OBJECT and Variable.getvalue

Try this

job_status_out = cursor.var(cx_Oracle.OBJECT)

get_status_params = {'handle': job_handler,
                     'mask': 1,
                     'timeout': -1,
                     'job_state': job_state,
                     'status': job_status_out
                     }
cursor.callproc('dbms_datapump.get_status', keywordParameters=get_status_params)

job_status = job_status_out.getvalue()

Worst case, if you have problems with getting the value from the cur.var(cx_Oracle.NUMBER) variable; you could wrap dbms_datapump.get_status in a pl/sql function that returns the value as a string and call that instead. I know that works.

This answer an alternate to the other answer I gave. The other answer is a better solution assuming there aren't any issues with the return type.

This solution will work with any language that can make an SQL call to the database. So you can still get what you need, even if the application language you're using doesn't support calling PL/SQL functions.

If you are unable to get something from an Oracle database such as a custom type or complex data set, you can write a pipelined table function.

A pipelined table function allows you to preform whatever actions you need on the pl/sql side and format/return the results in a tabular format of your choice.

Then on the application layer, you can select from this function as if it were a table.

I have a post here explaining how to use this in a NodeJS call, but I've modified this example to use Python.

In PL/SQL:

(this doesn't have to be in a package, it's just the example I use)

/* defined in package spec */
TYPE SearchType IS RECORD (
 member_id NUMBER,
 dino_name VARCHAR2(400) );

 TYPE SearchTypeSet IS TABLE OF SearchType;


/* defined in package body */
FUNCTION text_only(
  member_id_p IN INTEGER,
  search_string IN VARCHAR2)
  RETURN SearchTypeSet PIPELINED
IS
 retSet SearchType;
BEGIN
  FOR v_rec IN (
     SELECT member_id,
            dino_name
       FROM dd_members
      WHERE contains (about_yourself, 
                         search_string) > 0
        AND member_id != member_id_p) LOOP
            retSet.dino_name := v_rec.dino_name;
     retSet.member_id := v_rec.member_id;
     pipe ROW (retSet);
  END LOOP;
  RETURN;
END;

After that it's a simple select statment from the application.

In Python:

cur = con.cursor()
statement = 'SELECT member_id, dino_name FROM TABLE(text_only(:memberId, :keywords)) ORDER BY dino_name'
cur.execute(statement, (35, 'Rocks'))
res = cur.fetchall()
print (res)

For your specific issue, you could wrap the call to dbms_datapump.get_status in a pipelined function instead of using the select in the example.