Easy Stored Procedure Output
January 15th, 2008 by The Oracle AlchemistI answered a question on the DBA Forum today and I thought it was a common enough question to warrant a blog posting.
You can find the entire thread here. For my blog, I’ll post a condensed version of the question.
Question: I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data.
…
On a more generic note, can anyone explain to me why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:
CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO
and viola, a nice result set spits out in Query Analyzer (or a .net application).
Answer:
Before I go on, let me say I agree that PL/SQL is more powerful (read the original post to see the debate). That being said, here are your options.
1. Test it with REFCURSOR using a FUNCTION and selecting from dual:
SQL> create or replace function testfunc return sys_refcursor
2 as
3 c_test sys_refcursor;
4 begin
5 open c_test for select first_name, last_name, email from employees where rownum < 10;
6 return c_test;
7 end;
8 /
Function created.
SQL> select testfunc() from dual;
TESTFUNC()
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
FIRST_NAME LAST_NAME EMAIL
-------------------- ------------------------- -------------------------
Steven King SKING
Neena Kochhar NKOCHHAR
Lex De Haan LDEHAAN
Alexander Hunold AHUNOLD
Bruce Ernst BERNST
David Austin DAUSTIN
Valli Pataballa VPATABAL
Diana Lorentz DLORENTZ
Nancy Greenberg NGREENBE
9 rows selected.2. Use the same function and return it into a variable:
SQL> variable rc refcursor
SQL> exec :rc := testfunc()
PL/SQL procedure successfully completed.
SQL> print rc
FIRST_NAME LAST_NAME EMAIL
-------------------- ------------------------- -------------------------
Steven King SKING
Neena Kochhar NKOCHHAR
Lex De Haan LDEHAAN
Alexander Hunold AHUNOLD
Bruce Ernst BERNST
David Austin DAUSTIN
Valli Pataballa VPATABAL
Diana Lorentz DLORENTZ
Nancy Greenberg NGREENBE
9 rows selected.3. Use your procedure with a variable:
SQL> create or replace procedure testproc(c_test out sys_refcursor) is
2 begin
3 open c_test for select first_name, last_name, email from employees where rownum < 10;
4 end;
5 /
Procedure created.
SQL> variable rc2 refcursor
SQL> exec testproc(:rc2);
PL/SQL procedure successfully completed.
SQL> print rc2
FIRST_NAME LAST_NAME EMAIL
-------------------- ------------------------- -------------------------
Steven King SKING
Neena Kochhar NKOCHHAR
Lex De Haan LDEHAAN
Alexander Hunold AHUNOLD
Bruce Ernst BERNST
David Austin DAUSTIN
Valli Pataballa VPATABAL
Diana Lorentz DLORENTZ
Nancy Greenberg NGREENBE
9 rows selected.#3 is more in-line with your original needs. Personally I’m a fan of #1 and #2 because of the capabilities of returning a refcursor as a function, like passing it into DBMS_XMLGEN.GETXML.
November 7th, 2008 at 2:01 am
Hey steve, thanks for the link you provided in DBA forums to this place. looks interesting! well i want to run a query several times with different inputs and store all outputs into a cursor/ record set and return to front end.
I don’t clearly know how to implement this from the front end, planning to test with a sample today .
Please can you comment if you have any ideas in this ?
December 29th, 2009 at 6:42 pm
Hello Steve,
if I need just a select statement, do you think I still should prefer using out sys_refcursor in a Stored Porcedure for performance, or Should I prefer a View instead of a Procedure.
which one could be faster.
My Select statement does not have a variable parameter, I mean if I write a procedure there will be no input parameters. There are just some constants for some criteria.
August 12th, 2010 at 7:14 am
Hi Steven,
the above workaround given by you is really help full.
I want to do some error handling like say NO-DATA-FOUND in the procedure.
Could you please advise how should I proceed for?
August 12th, 2010 at 9:41 am
Hi ,
Can anybody help me
why I am getting the bellow error while I am trying to use Bulk Collect functionality in my code
1 11 3 Execution ORA-06550: line 11, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored
my code
Create or replace TYPE TESTTYPE AS OBJECT (
POLID varchar2(20),
INCEPTDATE Date,
SUBID number(10));
—————————————————
create or replace type emptype is table of TESTTYPE
————————————————–
Declare
vout emptype;
Begin
select policyno,
policy_inception_date,
submission_id
bulk collect into vout
from mi_policy,mi_submission
where policyno = policy_no
and rownum<1000;
for i in vout.first..vout.last
loop
dbms_output.put_line(vout(i).polid);
end loop;
END ;
August 20th, 2010 at 8:35 am
Hi i have question. How do i copy a table from one table to another created table of the same type using stored procedure without using cursor?
October 4th, 2011 at 4:51 am
Hi Steve, I read the orginal thread before finding your blog post and while you give a great answer to the problem, you don’t really answer the question as it relates to Oracle SQL developer. Yes, it’s straight-forward in SQL*Plus and even more simple in Toad but comparatively speaking Oracle SQL developer forces you through hoops…. well, that’s my opinion right now because I am new to SQL Developer and getting used to it. My searches online seem to suggest that the answer to “How do I test a stored procedure that returns a ref cursor using Oracle SQL Developer?” is – Don’t. I would be very happy to be proved wrong.
October 12th, 2011 at 3:46 pm
CREATE OR REPLACE PROCEDURE SCOTT.insert_test(i_acctperiod IN VARCHAR2 := NULL,
o_cursor out sys_refcursor )
IS
BEGIN
DECLARE
v_shorttermcutoffdatestr VARCHAR(20);
l_query varchar2(1000);
BEGIN
delete from test;
commit;
SELECT TO_CHAR(add_months(E.HIREDATE,12),’MM/DD/YYYY’) into v_shorttermcutoffdatestr FROM EMP1 E WHERE
TO_CHAR(E.HIREDATE,’Mon YYYY’) = i_acctperiod;
l_query :=’ INSERT INTO TEST
select ename,empno from emp1
where hiredate variable rc2 refcursor
SQL> exec insert_test(‘Dec 1980′,:rc2);
January 23rd, 2012 at 9:23 pm
The function compiles but I get this error when i try to execute it:
identifier ‘testfunc’ must be declared.
This code is so far useless.
January 23rd, 2012 at 9:33 pm
Susan, did you create it as a function (like #1 and #2) or a procedure (like #3)? Can you post your creation and execution along with the error? Perhaps if I can see what you did it won’t be so useless.