returning multiple rows from an oracle stored procedure

Question

0.00/5 (No votes)

See more:

Hello All,

I have created a procedure to display rows from a table like following:

SQL

CREATE PROCEDURE SPGETDEPT
(P_CUR OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_CUR FOR
SELECT * FROM DEPT;
END;
/

and to run it:

SQL

var cur REFCURSOR
exec spgetdept(:cur);
print cur;

My question is how to club the above two parts so that output can be obtained by executing one procedure only:

SQL

execute newStoredProcedure;

Any help or idea is highly appreciated....
Thank you...!!

Posted 16-Aug-15 5:55am

planetz

Updated 15-Aug-17 18:05pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-08-16T06:20:00

Solution 1

If I understand your question correctly, instead of using a stored procedure, I'd go with a table function, possibly pipelined if the amount of data is larger. A table function can return a result set that can be used also in a query.

Have a look at topics:
- Function Returning a Result Set
- Creating a Pipelined Function
in Examples for Creating Oracle Functions[^]

Posted 16-Aug-15 6:20am

Wendelius

Comments

planetz 16-Aug-15 14:21pm

Hello Mika,

What I wanted to do is connect this procedure to dropdownlist at design time through sqlDataSource to show the dept name in the drop down. Just for practice. But there is no option to include function. Is there any way to create procedure for same?

Wendelius 16-Aug-15 14:47pm

Ok, I see. Have a look at Using Oracle Stored Procedures in SqlDataSource Select Command[^]. Would that be what you're after?

Wendelius 16-Aug-15 23:51pm

Just for a clarification, you could try using syntax

SELECT ... FROM TABLE(FunctionName...)

So from the SqlDataSource point of view this would be a simple SELECT, not a function nor a stored procedure.

planetz · Answer 2 · 2015-08-19T23:55:00

hey..i tried something like this:

SQL

CREATE OR REPLACE PROCEDURE sp AS
DEPT_REC     tbldept%TABLETYPE;
CURSOR p_cur IS SELECT * FROM tbldept;
BEGIN
OPEN p_cur;
DBMS_OUTPUT.PUT_LINE('HI');
LOOP
    FETCH p_cur INTO DEPT_REC;
    EXIT WHEN p_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(DEPT_REC);            
  END LOOP;
CLOSE p_cur; 
END;

output is coming as expected but the column headers are not coming in output..i guess this wont serve my purpose...