Run Stored Procedure in SQL Developer?

Question

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 |  | 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.

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:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

user_7339305 · Answer

To run procedure from SQL developer-only execute following command

EXECUTE PROCEDURE_NAME;

user_45563 · Answer

I had a stored procedure that returned a cursor, in my case it was actually of a custom package type (T_CURSOR, looks like a convention to me) that is defined as REF CURSOR.

There may be a better way to do this, but I defined variables for all the columns of the table that the cursor was iterating, looped the cursor fetching each row into those variables, then printed them out.

SET serveroutput on;
DECLARE
  testvar number;
  v_cur  SYS_REFCURSOR;
  ORIGINAL_EMP_NUM  NUMBER;
  TEMPORARY_EMP_NUM NUMBER;
  ORG_UNIT_CODE VARCHAR2(2 BYTE);
  MRU_CODE  VARCHAR2(10 BYTE);
  CTRL_COMPANY_CODE VARCHAR2(10 BYTE);
  IS_TEMP_FLAG  VARCHAR2(1 BYTE);
BEGIN
  testvar := 420;
  foo.updates.get_temporary_authorisations(testvar, v_cur);
  dbms_output.enable;
  dbms_output.put_line(testvar);
  
  LOOP
    FETCH v_cur INTO ORIGINAL_EMP_NUM, TEMPORARY_EMP_NUM, ORG_UNIT_CODE, MRU_CODE, CTRL_COMPANY_CODE, IS_TEMP_FLAG;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(ORIGINAL_EMP_NUM || ',' || TEMPORARY_EMP_NUM || ',' || ORG_UNIT_CODE || ',' || MRU_CODE|| ',' || CTRL_COMPANY_CODE|| ',' || IS_TEMP_FLAG);
  END LOOP;
  CLOSE v_cur;
END;

user_8064000 · Answer

--for setting buffer size needed most of time to avoid `anonymous block completed` message
set serveroutput on size 30000;

-- declaration block in case output need to catch
DECLARE
--declaration for in and out parameter
  V_OUT_1 NUMBER;
  V_OUT_2 VARCHAR2(200);
BEGIN

--your stored procedure name
   schema.package.procedure(
  --declaration for in and out parameter
    V_OUT_1 => V_OUT_1,
    V_OUT_2 => V_OUT_2
  );
  V_OUT_1 := V_OUT_1;
  V_OUT_2 := V_OUT_2;
  -- console output, no need to open DBMS OUTPUT seperatly
  -- also no need to print each output on seperat line 
  DBMS_OUTPUT.PUT_LINE('Ouput => ' || V_OUT_1 || ': ' || V_OUT_2);
END;

Run Stored Procedure in SQL Developer?

15 Answers