CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle11gsqlplusprocedure
Vidhi Sinha
Vidhi Sinha

Reputation: 93

stored procedure for select query not giving output

I am using sqlplus and have a table named users from which I wish to retrieve all values with the help of a stored procedure in oracle. Here is what I am trying to do - 

create or replace procedure getall(prc out sys_refcursor)
is
begin
open prc for select * from users
end;
/

When I hit return after this, I get the following error - 

Warning: Procedure created with compilation errors.

Why does this happen? And how do I get the desired output? Help much appreciated!

Upvotes: 1

Views: 751

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

To see the compilation errors use the show errors‌​ SQL*Plus command (which also works in SQL Developer), or query the user_errors view which works with any client. You can also query all_errors to see problems with objects that are not in your schema.

But you are just missing a semicolon after the select:

create or replace procedure getall(prc out sys_refcursor)
is
begin
  open prc for select * from users;
end;
/

You'll need a bind variable to be able to see the output in SQL*Plus, e.g.:

variable rc refcursor;
exec getall(:rc);
print rc

Notice the colon before the rc in the procedure call, which shows it's a bind variable reference. And exec is a shorthand anonymous block.

You might find it simpler to have a function that returns a ref cursor, or a pipelined function; or just query the table directly of course.

Upvotes: 1

Related Questions