Run Stored Procedure in SQL Developer?

Question

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

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.

Thanks in advance!

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: 

Answer 1

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; 

Answer 2

Executing easy. Getting the results can be hard.

Take a look at this question I asked Best way/tool to get the results from an oracle package procedure

The summary of it goes like this.

Assuming you had a Package named mypackage and procedure called getQuestions. It returns a refcursor and takes in string user name.

All you have to do is create new SQL File (file new). Set the connection and paste in the following and execute.

var r refcursor; exec mypackage.getquestions(:r, 'OMG Ponies'); print r;