11

I was able to create a stored procedure for an Oracle database, but now I can't figure out how to run it. I'm using SQuirrel SQL and this worked to create the procedure:

CREATE OR REPLACE PROCEDURE MyProc(label IN varchar2, results OUT sys_refcursor) AS
BEGIN
OPEN results FOR
    SELECT Label, Count, Timestamp 
        FROM table1
            LEFT JOIN table2 ON table1.Name=table2.Name 
    WHERE table1.Label=label
    ORDER BY Timestamp;
END;
/

I want to be able to get and display the result set. I've tried using call MyProc('param'), but this doesn't work (wrong number of arguments error). I've searched extensively on this site and others but nothing has been useful. Please help!

zb226
  • 9,586
  • 6
  • 49
  • 79
Kayeight
  • 111
  • 1
  • 1
  • 3
  • @dovka's answer appears to be the only one which answers the question. Kayeight wasn't asking how to call procedures from Squirrel. The question's current title, "Calling an Oracle stored procedure in Squirrel SQL", is misleading. – Lambart Mar 10 '15 at 23:50

5 Answers5

18

The following works to run a stored procedure:

begin
   procedurename;
end;
/

Yes, the slash at the end is necessary!

The begin...end; declares a PL/SQL block (Oracle-specific). The slash is the command to run the block.

/(slash)

Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

[...]

Oracle Database Online Documentation, 10g Release 2 (10.2) / SQL*Plus® User's Guide and Reference

sleske
  • 81,358
  • 34
  • 189
  • 227
Kai
  • 38,985
  • 14
  • 88
  • 103
  • How does this answer the question? @Kayeight's stored procedure has two parameters: one in, and one out. So how does one call a stored procedure with an 'out' parameter, via Squirrel SQL? If the `begin procedurename; end; /` syntax is superior to `call procedurename;`, please explain why. – Lambart Mar 10 '15 at 23:46
  • _..is necessary.._ as are the 2 rather than 3 semicolons! – nsandersen Jul 07 '17 at 14:31
4

The only syntax I get working in Squirrel SQL is PL/SQL block:

declare
v_label  varchar2:='SOMELABEL';
TYPE ref_cursor IS REF CURSOR;
v_cur_results ref_cursor;
begin
MyProc (v_label, v_cur_results)
end;
/
dovka
  • 971
  • 1
  • 8
  • 20
1

Like this article explains, using "call" instead of "execute" should solve the problem.

Marcone
  • 27
  • 2
0

I have struggled with this for a long time but have managed to make it work like this :

{call DBMS_SESSION.SET_CONTEXT  ( namespace => 'clientcontext', attribute => 'foo', value     => 'bar' )}
kon5ad
  • 43
  • 1
  • 4
0
var v_result sys_refcursor

exec MyProc ('test label',:v_result)
Shamit Verma
  • 3,839
  • 23
  • 22