[cx-oracle-users] SQL Type Object...

[Pablo S. <pab...@gm...>]

Hi again,

I saw a similar question from 2005, so I would like to know if
something changed in the meantime:

We want to use python with Oracle Advanced Queuing, and I would like
to know if it possible (and how) to return oracle objects in python
bind variables. I saw tests returning objects from selects but nothing
from PL/SQL blocks.

In the following example the message is returned using each attribute,
and I would like to return the whole message as an object

The message definition:

CREATE TYPE message_resp_typ AS object(
  success                NUMBER,
  stdout                 BLOB,
  stderr                 BLOB,
  attachment             BLOB
  )
/

the python code:

   c = db.cursor()
   l_success = c.var(cx_Oracle.NUMBER)
   l_stdout = c.var(cx_Oracle.BLOB)
   l_stderr = c.var(cx_Oracle.BLOB)
   l_attachment = c.var(cx_Oracle.BLOB)
   c.execute('''
      DECLARE
          dequeue_options     DBMS_AQ.dequeue_options_t;
          message_properties  DBMS_AQ.message_properties_t;
          message_handle      RAW(16);
          message             message_resp_typ;
      BEGIN
         dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
         dequeue_options.correlation := :v_correlation;
         DBMS_AQ.DEQUEUE(
            queue_name          =>     :v_queue,
            dequeue_options     =>     dequeue_options,
            message_properties  =>     message_properties,
            payload             =>     message,
            msgid               =>     message_handle);
         :v_success := message.success;
         :v_stdout := message.stdout;
         :v_stderr := message.stderr;
         :v_attachment := message.attachment;
      END;''',
      v_queue = queue,
      v_correlation = id,
      v_success = l_success,
      v_stdout = l_stdout,
      v_stderr = l_stderr,
      v_attachment = l_attachment
   )

Can please someone give me some advice or an example?

Thanks in advance
Pablo