Wednesday, February 9, 2011

PL/SQL: How to execute an SP which preforms a DML and has a return value?

I have a stored procedure with the following header:

FUNCTION SaveShipment (p_user_id IN INTEGER, p_transaction_id IN INTEGER, p_vehicle_code IN VARCHAR2 DEFAULT NULL, p_seals IN VARCHAR2 DEFAULT NULL) RETURN INTEGER;

And I am having trouble running it from TOAD's Editor. I cannot run it as part of a select from dual statement because it preforms DML, but if I try the following syntax which I saw recommended on some forum:

var c integer;
exec :c := orm_helper.orm_helper.SAVESHIPMENT (9999, 31896, NULL, '');
print c;

I get:

ORA-01008: not all variables bound
Details:
BEGIN :c := orm_helper.orm_helper.saveshipment (9999, 31896, null, ''); END;
Error at line 2
ORA-01008: not all variables bound

What's the proper syntax to run this sp manually?

  • Are you calling the stored procedure from another SP?

    I think the syntax is (if I recall correctly):

    declare
         c integer;
    begin
    
    c:=storedProc(...parameters...);
    

    Hope this helps.

    George Mauer : ah yes, that woudl work, thanks
    From friol
  • you could probably SELECT orm_helper.orm_helper.SAVESHIPMENT (9999, 31896, NULL, '') FROM DUAL.

0 comments:

Post a Comment