How can I use Oracle SQL developer to run stored procedures?

Question

* EDIT6: * This is what ended up working for me (from accepted answer):

var ret1 number
var tran_cnt number
var msg_cnt number
var rc refcursor
exec :tran_cnt := 0
exec :msg_cnt := 123
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => :rc)
print :tran_cnt
print :msg_cnt
print :rc

---

The SQL Developer makes this super-hard / impossible?. I do not care if the utility is command-line-based; I just want to be able to run and view it quickly. It would be nice if it captured errors well as well. it would be nice to be able to log in gradually(interactively), as well as specifying everything at once (similar to how a typical ftp / sftp cmd-based client works).

My platform is Windows Server 2008 + Cygwin.

EDIT: Perhaps you would know how to script this using Python?

EDIT 2: In MSFT SQL server I can simply type this:

get_user 1;

then highlight it and hit F5, and I get:

login   name    
NULL    Somename

printed to the output window. The Oracle SQL developer does not help with this at all. I am not sure how to pass in a 1, I am not sure how to see the actual rows/records that come back.

EDIT3: When I type just var rc refcursor; and select it and run it, I get this error (GUI):

An error was encountered performing the requested operation:

ORA-00900: invalid SQL statement
00900.00000 - "invalid SQL statement"
* Cause:
* Action:
Vendor code 900Error at Line: 2

EDIT4:

I am trying to run a procedure the definition of which starts like this:

create or replace procedure get_account
(
    Vret_val out number,
    Vtran_count in out number,
    Vmessage_count in out number,
    Vaccount_id     IN NUMBER
    , rc1 in out sys_refcursor
)as
begin
...

I get an error:

Error starting at line 2 in command:
exec :rc := get_account(1) 
Error report:
ORA-06550: line 1, column 24:
PLS-00306: wrong number or types of arguments in call to 'GET_ACCOUNT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
rc
------

I am so close ... please help.

* EDIT 5: *

Script that I am running (functionally the same), the error was always the same:

var ret1 number
var tran_cnt number
var msg_cnt number
var rc refcursor
exec :tran_cnt := 0
exec :msg_cnt := 123
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => rc)

Script Output (on F5) (Could be several messages from several runs.):

Error report:
ORA-06550: line 1, column 134:
PLS-00201: identifier 'RC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
anonymous block completed
anonymous block completed

Error starting at line 7 in command:
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => rc)
Error report:
ORA-06550: line 1, column 134:
PLS-00201: identifier 'RC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
anonymous block completed
anonymous block completed

Error starting at line 7 in command:
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => rc)
Error report:
ORA-06550: line 1, column 134:
PLS-00201: identifier 'RC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Why does it say line 1, column 134? No line stretched that far ...

Answer 1

Not only is there a way to do this, there is more than one way to do this (which I concede is not very Pythonic, but then SQL*Developer is written in Java ).

I have a procedure with this signature: get_maxsal_by_dept( dno number, maxsal out number).

I highlight it in the SQL*Developer Object Navigator, invoke the right-click menu and chose Run. (I could use ctrl+F11.) This spawns a pop-up window with a test harness. (Note: If the stored procedure lives in a package, you'll need to right-click the package, not the icon below the package containing the procedure's name; you will then select the sproc from the package's "Target" list when the test harness appears.) In this example, the test harness will display the following:

DECLARE
  DNO NUMBER;
  MAXSAL NUMBER;
BEGIN
  DNO := NULL;

  GET_MAXSAL_BY_DEPT(
    DNO => DNO,
    MAXSAL => MAXSAL
  );
  DBMS_OUTPUT.PUT_LINE('MAXSAL = ' || MAXSAL);
END;

I set the variable DNO to 50 and press okay. In the Running - Log pane (bottom right-hand corner unless you've closed/moved/hidden it) I can see the following output:

Connecting to the database apc.
MAXSAL = 4500
Process exited.
Disconnecting from the database apc. 

To be fair the runner is less friendly for functions which return a Ref Cursor, like this one: get_emps_by_dept (dno number) return sys_refcursor.

DECLARE
  DNO NUMBER;
  v_Return sys_refcursor;
BEGIN
  DNO := 50;

  v_Return := GET_EMPS_BY_DEPT(
    DNO => DNO
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

However, at least it offers the chance to save any changes to file, so we can retain our investment in tweaking the harness...

DECLARE
  DNO NUMBER;
  v_Return sys_refcursor;
  v_rec emp%rowtype;
BEGIN
  DNO := 50;

  v_Return := GET_EMPS_BY_DEPT(
    DNO => DNO
  );

  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('name = ' || v_rec.ename);
  end loop;
END;

The output from the same location:

Connecting to the database apc.
name = TRICHLER
name = VERREYNNE
name = FEUERSTEIN
name = PODER
Process exited.
Disconnecting from the database apc. 

---

Alternatively we can use the old SQLPLus commands in the SQLDeveloper worksheet:

var rc refcursor 
exec :rc := get_emps_by_dept(30) 
print rc

In that case the output appears in Script Output pane (default location is the tab to the right of the Results tab).

The very earliest versions of the IDE did not support much in the way of SQL*Plus. However, all of the above commands have been supported since 1.2.1. Refer to the matrix in the online documentation for more info.

---

"When I type just var rc refcursor; and select it and run it, I get this error (GUI):"

There is a feature - or a bug - in the way the worksheet interprets SQLPlus commands. It presumes SQLPlus commands are part of a script. So, if we enter a line of SQL*Plus, say var rc refcursor and click Execute Statement (or F9 ) the worksheet hurls ORA-900 because that is not an executable statement i.e. it's not SQL . What we need to do is click Run Script (or F5 ), even for a single line of SQL*Plus.

---

"I am so close ... please help."

You program is a procedure with a signature of five mandatory parameters. You are getting an error because you are calling it as a function, and with just the one parameter:

exec :rc := get_account(1)

What you need is something like the following. I have used the named notation for clarity.

var ret1 number
var tran_cnt number
var msg_cnt number
var rc refcursor

exec :tran_cnt := 0
exec :msg_cnt := 123

exec get_account (Vret_val => :ret1, 
                  Vtran_count => :tran_cnt, 
                  Vmessage_count => :msg_cnt, 
                  Vaccount_id   => 1,
                  rc1 => :rc )

print tran_count 
print rc

That is, you need a variable for each OUT or IN OUT parameter. IN parameters can be passed as literals. The first two EXEC statements assign values to a couple of the IN OUT parameters. The third EXEC calls the procedure. Procedures don't return a value (unlike functions) so we don't use an assignment syntax. Lastly this script displays the value of a couple of the variables mapped to OUT parameters.

Answer 2

I am not sure how to see the actual rows/records that come back.

Stored procedures do not return records. They may have a cursor as an output parameter, which is a pointer to a select statement. But it requires additional action to actually bring back rows from that cursor.

In SQL Developer, you can execute a procedure that returns a ref cursor as follows

var rc refcursor
exec proc_name(:rc)

After that, if you execute the following, it will show the results from the cursor:

print rc