3 Replies Latest reply: Jun 10, 2007 10:38 AM by 484861 RSS

    Passing NULLs to a stored procedure?

    581187
      Ok.
      I guess I can use the following:
      cursor.execute("CALL myproc('foo',NULL)")

      To call my stored procedure that has an optional second parameter.

      How do I do this using the cursor.callproc() method?

      TIA!

      -Mike
        • 1. Re: Passing NULLs to a stored procedure?
          484861
          Just omit the last parameter.
          Connected to:
          Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

          PP@xe> set timing off
          PP@xe> create table p1 (x number, y number);

          Table created.

          PP@xe> create or replace procedure proc1 (x number, y number default 99) as
            2  begin
            3  insert into p1 values(x,y);
            4  commit;
            5  end;
            6 /

          Procedure created.
          And now into Python:
          >>> import cx_Oracle
          
          db = cx_Oracle.connect('pp', 'pp', 'XE')
          cursor = db.cursor()
          cursor.callproc('proc1', [1,2])
          [1, 2]
          PP@xe> select * from p1;

                   X          Y
          ---------- ----------
                   1          2
          Python interpreter again:
          >>> cursor.callproc('proc1', [1])
          [1]
          PP@xe> select * from p1;

                   X          Y
          ---------- ----------
                   1          2
                   1         99
          • 2. Re: Passing NULLs to a stored procedure?
            581187
            Ok...
            That doesn't quite match what I'm doing.

            You're assigning a default value of 99 for the second parameter.

            In my case, the stored procedure takes either an SDO.GEOMETRY or a NULL.
            If I don't include the second parameter, I get an error that not enough parameters were passed in.

            TIA!
            • 3. Re: Passing NULLs to a stored procedure?
              484861
              Could you post the header of the procedure called?

              Other than that you could just supply None in place of required nulls for callproc.