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

possible duplicate of [Best way/tool to get the results from an oracle package procedure](http://stackoverflow.com/questions/3526798/best-way-tool-to-get-the-results-from-an-oracle-package-procedure) — OMG Ponies, Oct 21 '10 at 20:52
Try putting the OUT variables inside the BEGIN, before the procedure execution statement. — OMG Ponies, Oct 21 '10 at 21:56
You don't need the `execute`; in PL/SQL that's interpreted as the start of `execute immediate`, which is different to SQL `execute`. — Alex Poole, Oct 21 '10 at 22:08
Removing "execute" doesn't make a difference. I still get "Bind Variable...is NOT DECLARED". — sdoca, Oct 21 '10 at 22:17
If the the DECLARE section is inside the begin block (i.e. BEGIN DECLARE ... EXECUTE... END, I still get the same bind variable error. — sdoca, Oct 21 '10 at 22:19
@sdoca: you're confusing two approaches now; with your edit 2 version just remove the word `execute`. The `declare` should be before the `begin`. What I think @OMG meant was that you can declare the variables in SQL Developer before the anonymous block with the `variable` keyword, and then use the `:out1` syntax as you had it originally, in which case you don't have a `declare` section at all. But you're mixing the two up from your last comment. — Alex Poole, Oct 21 '10 at 22:24
Yep, I knew I was confused, but wasn't quite sure where/how. — sdoca, Oct 22 '10 at 16:14

Alex Poole · Accepted Answer · 2010-10-21T22:33:56.523

81

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;

edited Oct 21 '10 at 22:33

answered Oct 21 '10 at 21:45

Alex Poole

183,384
11
179
318

1

+1 nice answer. Out of curiosity do you know which is preferred? – Conrad Frix Oct 21 '10 at 23:05
@Conrad: I imagine it's a preference thing, though there might be more context switching the `:var` way. I'd use the `declare` way by default if I was doing anything with PL/SQL; but I might use `:var` if, say, I was using a bit of existing code copied in from Pro*C which already had that syntax and I didn't want to touch the parms in the call. – Alex Poole Oct 22 '10 at 06:41
2

Thanks for the help and the detailed answer. I'm sure it will be a help to others as well. One thing to note is that these must be run as scripts not statements. – sdoca Oct 22 '10 at 16:15
1

When running in sql developer, I didn't have to prefix the variable names with `:` (in fact, it didn't work with it). – haridsv Mar 20 '13 at 12:48
How do I know the parameters type if I didn't write the procedure (and I've not access to it)? – Christian Vincenzo Traina Oct 24 '22 at 11:41
@ChristianVincenzoTraina - look in [the `all_arguments` view](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_ARGUMENTS.html). – Alex Poole Oct 24 '22 at 12:05

score 30 · Answer 2 · edited May 23 '17 at 11:47

30

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;

edited May 23 '17 at 11:47

Community

1
1

answered Oct 21 '10 at 20:48

Conrad Frix

51,984
12
96
155

3

I had to use the full word "execute" rather than "exec" – Patrick Nov 21 '14 at 23:01

score 26 · Answer 3 · answered Jun 03 '15 at 06:37

For those using SqlDeveloper 3+, in case you missed that:

SqlDeveloper has feature to execute stored proc/function directly, and output are displayed in a easy-to-read manner.

Just right click on the package/stored proc/ stored function, Click on Run and choose target to be the proc/func you want to execute, SqlDeveloper will generate the code snippet to execute (so that you can put your input parameters). Once executed, output parameters are displayed in lower half of the dialog box, and it even have built-in support for ref cursor: result of cursor will be displayed as a separate output tab.

This should be the selected answer. – EvilTeach Jun 14 '17 at 19:36 — EvilTeach, Jun 14 '17 at 19:36

score 14 · Answer 4 · answered Jan 05 '12 at 20:42

14

Open the procedure in SQL Developer and run it from there. SQL Developer displays the SQL that it runs.

BEGIN
  PROCEEDURE_NAME_HERE();
END;

answered Jan 05 '12 at 20:42

Noel

437
6
9

score 7 · Answer 5 · answered Oct 21 '10 at 20:49

7

Use:

BEGIN

  PACKAGE_NAME.PROCEDURE_NAME(parameter_value, ...);

END;

Replace "PACKAGE_NAME", "PROCEDURE_NAME", and "parameter_value" with what you need. OUT parameters will need to be declared prior to.

answered Oct 21 '10 at 20:49

OMG Ponies

325,700
82
523
502

score 3 · Answer 6 · answered Mar 13 '18 at 18:46

Though this question is quite old, I keep stumbling into same result without finding an easy way to run from sql developer. After couple of tries, I found an easy way to execute the stored procedure from sql developer itself.

Under packages, select your desired package and right click on the package name (not on the stored procedure name).
You will find option to run. Select that and supply the required arguments. Click OK and you can see the output in output variables section below

I'm using SQL developer version 4.1.3.20

vapcguy · Answer 7 · 2016-08-30T21:22:46.630

None of these other answers worked for me. Here's what I had to do to run a procedure in SQL Developer 3.2.20.10:

SET serveroutput on;
DECLARE
  testvar varchar(100);
BEGIN
  testvar := 'dude';
  schema.MY_PROC(testvar);
  dbms_output.enable;
  dbms_output.put_line(testvar);
END;

And then you'd have to go check the table for whatever your proc was supposed to do with that passed-in variable -- the output will just confirm that the variable received the value (and theoretically, passed it to the proc).

NOTE (differences with mine vs. others):

No : prior to the variable name
No putting .package. or .packages. between the schema name and the procedure name
No having to put an & in the variable's value.
No using print anywhere
No using var to declare the variable

All of these problems left me scratching my head for the longest and these answers that have these egregious errors out to be taken out and tarred and feathered.

score 1 · Answer 8 · answered Jul 02 '13 at 13:10

1

Can't believe, this won't execute in SQL Developer:

var r refcursor;
exec PCK.SOME_SP(:r,
 '02619857');

print r;

BUT this will:

var r refcursor;
exec TAPI_OVLASCENJA.ARH_SELECT_NAKON_PRESTANKA_REG(:r, '02619857');

print r;

Obviously everything has to be in one line..

answered Jul 02 '13 at 13:10

Amel Music

119
2
9

3

The [SQL*Plus docs for the `execute` command] state that. This is not an answer to the question that was asked though, and has been covered [in more relevant answers to other questions](http://stackoverflow.com/a/15649958/266304) before anyway. – Alex Poole Jul 05 '13 at 10:55
Was able to use it in SQL Developer. Thanks – Sergejs Sep 16 '15 at 09:18

score 1 · Answer 9 · edited Jul 25 '14 at 20:05

1

Using SQL Developer Version 4.0.2.15 Build 15.21 the following works:

SET SERVEROUTPUT ON
var InParam1 varchar2(100)
var InParam2 varchar2(100)
var InParam3 varchar2(100)
var OutParam1 varchar2(100)

BEGIN
    /* Assign values to IN parameters */
    :InParam1 := 'one';
    :InParam2 := 'two';
    :InParam3 := 'three';

    /* Call procedure within package, identifying schema if necessary */
    schema.package.procedure(:InParam1, :InParam2, :InParam3, :OutParam1);
    dbms_output.enable;
    dbms_output.put_line('OutParam1: ' || :OutParam1);
END;
/

edited Jul 25 '14 at 20:05

JasonMArcher

14,195
22
56
52

answered Jul 25 '14 at 20:04

Jack

21
1

No, sorry -- `var` before the variables DOES NOT work - at least in SQL Developer 3.2.20.10, and shouldn't use colons in front of them - no need for it, again, at least in 3.2.20.10 (only reason I didn't vote this down or edit it). Also would need a semi-colon after `SET SERVEROUTPUT ON`. – vapcguy Aug 30 '16 at 21:17
works as expected – Raghav Apr 07 '22 at 11:56

score 0 · Answer 10 · answered Apr 16 '20 at 08:03

0

To run procedure from SQL developer-only execute following command

EXECUTE PROCEDURE_NAME;

answered Apr 16 '20 at 08:03

AmolRavan

33
6

"missing SELECT keyword" error – Alexander Sep 28 '21 at 15:01

score 0 · Answer 11 · answered Oct 26 '21 at 05:36

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;

score -1 · Answer 12 · answered Sep 21 '13 at 19:01

I wasn't able to get @Alex Poole answers working. However, by trial and error, I found the following works (using SQL Developer version 3.0.04). Posting it here in case it helps others:

SET serveroutput on;

DECLARE
    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);

    /* 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;

That's the same as the first version in my answer, except you've added a `var` to each variable in the `declare` block, which is invalid. Trying to run this gives 'PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following ...', and similar errors against the other five variables. — Alex Poole, Nov 20 '13 at 13:24
I agree with Alex. Downvoted because the `var`, and I did not need to use `.package.` in my call, at least in 3.2.20.10, which should not have been so different, and got errors when I did. Wasted lots of time with this answer. — vapcguy, Aug 30 '16 at 21:22

score -1 · Answer 13 · answered May 30 '19 at 04:16

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

score -2 · Answer 14 · answered Dec 11 '15 at 15:25

Creating Pl/SQL block can be painful if you have a lot of procedures which have a lot of parameters. There is an application written on python that do it for you. It parses the file with procedure declarations and creates the web app for convenient procedure invocations.

score -3 · Answer 15 · answered Sep 05 '12 at 19:29

-3

var out_para_name refcursor; 
execute package_name.procedure_name(inpu_para_val1,input_para_val2,... ,:out_para_name);
print :out_para_name;

answered Sep 05 '12 at 19:29

Helper

1

Run Stored Procedure in SQL Developer?

15 Answers15

Linked