New to Oracle (sql server background). Simple "select" stored proc doesn't execute

Question

I'm new to Oracle and trying to return a simple select into my calling application.

create or replace PROCEDURE testSelect 
( 
  column1 OUT table.column1%TYPE,
  column2 OUT table.column2%TYPE
)

IS

BEGIN

    SELECT column1, column2
    INTO column1, column2
    FROM table 

EXCEPTION
    WHEN OTHERS THEN RAISE;

END testSelect;

This compiles but when I try to execute the proc "EXEC testSelect ();", I get the following message: "wrong number or types of arguments in call to".

Thanks for your help and time.

possible duplicate of [PL/SQL Procedure: How return a select statement?](http://stackoverflow.com/questions/8987886/pl-sql-procedure-how-return-a-select-statement) — Gerrat, Jan 23 '14 at 13:46
I wouldn't say this is a dup even though the question is worded nearly the same. The answer in that thread involves using sys_refcursor, which I've tried in-place of the method above and still did not work. The example above looks almost identical to the Oracle documentation but still doesn't execute. — user3219570, Jan 23 '14 at 13:51
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551171813078805685 — xQbert, Jan 23 '14 at 13:58
Your `exec` call has no parameters, but your procedure has two. You need to define a variable to use in the call. Where are you running this from? This will only allow a single value in each OUT parameter anyway, and it sounds like you're trying to get multiple rows back - so you should look again at `sys_refcursor`, or a pipelined function. — Alex Poole, Jan 23 '14 at 13:59
@AlexPoole - Thanks... I tried running from both SQL Developer and Toad. Would it be more efficient to use "results OUT sys_refcursor" and modify the select. I actually tried this but I got the same result when trying to execute the proc. — user3219570, Jan 23 '14 at 14:00
If you have a function returning a ref cursor, you need to assign the return to something - a SQL Developer `varaiable`, say. It depends what you're really trying to achieve and what you'll do with the result set. (Does [this](http://stackoverflow.com/a/3527037/266304) help?) — Alex Poole, Jan 23 '14 at 14:02
@Gerrat 11g .. Tried to execute in both SQL Developer and Toad — user3219570, Jan 23 '14 at 14:02
@AlexPoole There is a C# application calling this proc... I have a SQL Server background so I'm still reading-up/learning the best practices for Oracle. — user3219570, Jan 23 '14 at 14:03

score 1 · Accepted Answer · edited Jan 28 '14 at 06:38

Changed to a func... Here is an example with how to execute in SQL Developer:

create or replace FUNCTION testSelect

RETURN SYS_REFCURSOR AS ret_cursor SYS_REFCURSOR;

BEGIN
    OPEN ret_cursor FOR
      SELECT column1, column2
      FROM table
    RETURN ret_cursor;
EXCEPTION
    WHEN OTHERS THEN RAISE;

END testSelect

To execute the query in SQL Developer, I used:

variable v_ref_cursor refcursor;
exec :v_ref_cursor := testSelect(); 
print :v_ref_cursor;

Edit

Adding example proc code with code to execute in SQL Developer:

create or replace PROCEDURE sp_test (RESULTS OUT SYS_REFCURSOR)

AS

BEGIN
    OPEN RESULTS FOR
      SELECT columnA, columnB, columnC, columnD
      FROM test;
   EXCEPTION
    WHEN OTHERS THEN RAISE;

END sp_test;

Execute:

var rc refcursor
exec sp_test(:rc)
print rc

I'm still very new to Oracle (SQL Server background). Thanks for the tip. I'll go do some reading. — user3219570, Jan 28 '14 at 14:39

New to Oracle (sql server background). Simple "select" stored proc doesn't execute

1 Answers1