Search code examples
oracle-databasestored-proceduresoracle-sqldeveloper

Run Stored Procedure in SQL Developer?


I am trying to run a stored procedure that has multiple in and out parameters. The procedure can only be viewed in my Connections panel by navigating

Other Users | <user> | Packages | <package> | <procedure>

If I right click , the menu items are "Order Members By..." and "Create Unit Test" (greyed out). The ability to "Run" the procedure does not seem possible when it's accessed by user.

I have been trying to find an example of how to create an anonymous block so that I can run the procedure as a SQL file, but haven't found anything that works.

Does anyone know how I can execute this procedure from SQL Developer? I am using Version 2.1.1.64.

EDIT 1:

The procedure I want to call has this signature:

user.package.procedure(
   p_1 IN  NUMBER,
   p_2 IN  NUMBER,
   p_3 OUT VARCHAR2,
   p_4 OUT VARCHAR2,
   p_5 OUT VARCHAR2,
   p_6 OUT NUMBER)

If I write my anonymous block like this:

DECLARE
   out1 VARCHAR2(100);
   out2 VARCHAR2(100);
   out3 VARCHAR2(100);
   out4 NUMBER(100);
BEGIN
   EXECUTE user.package.procedure (33,89, :out1, :out2, :out3, :out4);
END;

I get the error:

Bind Varialbe "out1" is NOT DECLCARED
anonymous block completed

I've tried initializing the out* variables:

   out1 VARCHAR2(100) := '';

but get the same error:

EDIT 2:

Based on Alex's answer, I tried removing the colons from in front of the params and get this:

Error starting at line 1 in command:
DECLARE
   out1 VARCHAR2(100);
   out2 VARCHAR2(100);
   out3 VARCHAR2(100);
   out4 NUMBER(100);
BEGIN
   EXECUTE user.package.procedure (33,89, out1, out2, out3, out4);
END;
Error report:
ORA-06550: line 13, column 17:
PLS-00103: Encountered the symbol "USER" when expecting one of the following:

   := . ( @ % ; immediate
The symbol ":=" was substituted for "USER" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution

  • With simple parameter types (i.e. not refcursors etc.) you can do something like this:

    SET serveroutput on;
    DECLARE
        InParam1 number;
        InParam2 number;
        OutParam1 varchar2(100);
        OutParam2 varchar2(100);
        OutParam3 varchar2(100);
        OutParam4 number;
    BEGIN
        /* Assign values to IN parameters */
        InParam1 := 33;
        InParam2 := 89;
    
        /* Call procedure within package, identifying schema if necessary */
        schema.package.procedure(InParam1, InParam2,
            OutParam1, OutParam2, OutParam3, OutParam4);
    
        /* Display OUT parameters */
        dbms_output.put_line('OutParam1: ' || OutParam1);
        dbms_output.put_line('OutParam2: ' || OutParam2);
        dbms_output.put_line('OutParam3: ' || OutParam3);
        dbms_output.put_line('OutParam4: ' || OutParam4);
    END;
    /
    


    Edited to use the OP's spec, and with an alternative approach to utilise :var bind variables:

    var InParam1 number;
    var InParam2 number;
    var OutParam1 varchar2(100);
    var OutParam2 varchar2(100);
    var OutParam3 varchar2(100);
    var OutParam4 number;
    
    BEGIN
        /* Assign values to IN parameters */
        :InParam1 := 33;
        :InParam2 := 89;
    
        /* Call procedure within package, identifying schema if necessary */
        schema.package.procedure(:InParam1, :InParam2,
            :OutParam1, :OutParam2, :OutParam3, :OutParam4);
    END;
    /
    
    -- Display OUT parameters
    print :OutParam1;
    print :OutParam2;
    print :OutParam3;
    print :OutParam4;