CODEWITHSUNDEEP
javaoracle-databasesquirrel-sql
Kayeight
Kayeight

Reputation: 111

Calling an Oracle stored procedure in Squirrel SQL

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!

Upvotes: 11

Views: 22016

Answers (5)

kon5ad
kon5ad

Reputation: 43

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' )}

Upvotes: 0

Kai
Kai

Reputation: 39651

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

Upvotes: 18

Marcone
Marcone

Reputation: 27

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

Upvotes: 1

dovka
dovka

Reputation: 1061

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

Upvotes: 4

Shamit Verma
Shamit Verma

Reputation: 3827

var v_result sys_refcursor

exec MyProc ('test label',:v_result)

Upvotes: 0

Related Questions