Home » SQL & PL/SQL » SQL & PL/SQL » Returning Recordset from PL/SQL
Returning Recordset from PL/SQL [message #196205] Wed, 04 October 2006 08:54 Go to next message
Fellow
Messages: 6
Registered: October 2006
Location: NYC
Junior Member
I am an experienced database programmer but an Oracle n00b. I found the discussion in this thread helpful:

http://www.orafaq.com/forum/t/70199/93680/

I can run a script like this:

DECLARE
year1 NUMBER :=0;
year2 number :=0;
BEGIN
select atvfisc_code into year1 from atvfisc
where atvfisc_code = 2007;
select atvfisc_code into year2 from atvfisc
where atvfisc_code = year1;
dbms_output.put_line('The variable year1 = ');
dbms_output.put_line(year1);
dbms_output.put_line('The variable year2 = ');
dbms_output.put_line(year2);
END;

Where table atvfisc looks like:

atvfisc.atvfisc_code (one column only in table)
-----------------
2007
2006
2005
2004
2003

and I get the output expected:
The variable year1 =
2007
The variable year2 =
2007
PL/SQL procedure successfully completed.

OK, here's the question: how do I return entire recordsets using a similar procedure? I would want to run something like this:

DECLARE
year1 NUMBER :=0;
BEGIN
--Made this query redundantly simple just for example.
select atvfisc_code into year1 from atvfisc
where atvfisc_code = 2007;

--Note: no "INTO" here. I want the records. Of course, this
--doesn't work.

select atvfisc_code from atvfisc
where atvfisc_code <> year1;
END;

and have it output:
2006
2005
2004
2003

Is there an easy way to do this?

Thanks for any help.

--Tom

Just to make things more complicated our DBA's won't allow mere users to create views, procedures, packages, or anything useful. Apparently, if they allow this, plagues of locusts will infest the servers Mad.

[Updated on: Wed, 04 October 2006 08:58]

Report message to a moderator

Re: Returning Recordset from PL/SQL [message #196211 is a reply to message #196205] Wed, 04 October 2006 09:14 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
There are three options:

a) write a function that returns a cursor
b) write a function that returns a TABLE type
c) write a PIPELINE function (which is basically the same as b) except that it doesn't return it all in one go).

There are details of these in the PL/SQL manual:

http://download-uk.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/06_ora.htm#1720
Re: Returning Recordset from PL/SQL [message #196216 is a reply to message #196211] Wed, 04 October 2006 09:35 Go to previous messageGo to next message
Fellow
Messages: 6
Registered: October 2006
Location: NYC
Junior Member
Smile Thanks for the quick reply.

I think I need to define a function that returns a TABLE type. I read the documentation in the link but couldn't apply it directly to my needs.

I'm guessing I need to
1) declare a function that returns a TABLE type: what's the syntax for this?
2) Create the body of the function.
3) Use some sort of "RETURN" call to tell which records to return.
4) Call the function.
Would it look something like this?

function Get_Years() Return <some sort of Table type>

function Get_Years() Return <some sort of Table type> is
begin
<some sort of return command> select * from atvfisc;
end;

begin
Get_Years();
end;

[Updated on: Wed, 04 October 2006 09:43]

Report message to a moderator

Re: Returning Recordset from PL/SQL [message #196221 is a reply to message #196205] Wed, 04 October 2006 09:47 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Here's a simplish example:

DROP TYPE TMP_TAB
/

DROP TYPE TMP_REC
/

create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER)
/

CREATE OR REPLACE TYPE TMP_TAB AS TABLE OF TMP_REC
/

CREATE OR REPLACE FUNCTION GET_TAB RETURN TMP_TAB is
  v_tmp_tab   tmp_tab:= 	tmp_tab();
BEGIN
   v_tmp_tab.delete;
   v_tmp_tab.extend;
   v_tmp_tab(1) := TMP_rec(1);
   return v_tmp_tab;
END;
/

select * from table(get_tab)

[Updated on: Wed, 04 October 2006 09:49]

Report message to a moderator

Re: Returning Recordset from PL/SQL [message #196224 is a reply to message #196221] Wed, 04 October 2006 09:56 Go to previous messageGo to next message
Fellow
Messages: 6
Registered: October 2006
Location: NYC
Junior Member
Thanks again for the quick reply.

I think the answer is our DBA's won't allow me to do what I want to do. I don't even have privileges to create types. <sigh>

I guess creating a type might cause the servers to bust into flames or something.

<shrug> guess I'm outta luck.
Re: Returning Recordset from PL/SQL [message #196227 is a reply to message #196205] Wed, 04 October 2006 10:02 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Can you even create Stored Procs ? If not, what is the point in you working there as a developer ? I've had these sort of battles in the past with control freak DBAs who think the only person allowed to change anything on the database should be them. Just keep on bombarding them with requests to create or amend database objects until they finally realize that way of working is utterly impractical.
Re: Returning Recordset from PL/SQL [message #196228 is a reply to message #196227] Wed, 04 October 2006 10:11 Go to previous messageGo to next message
Fellow
Messages: 6
Registered: October 2006
Location: NYC
Junior Member
Finally. Someone who understands! Smile

Nope, can't even do stored procs. That would probably result in the end of civilization as we know it.

Yes, it is a bizarre situation. Lots of office politics and protecting territory here. I was hired by my department to do the job that our MIS department can't or won't do. So, naturally, MIS has no incentive to make things easy for me. My only tools are Microsoft Access and pass-through queries. And boy I've written some doozies, huge, long, highly impressive passthroughs. But there is a limit to what you can do in a single query. So, I thought maybe I could pass through a script. But that's no good if it won't return a recordset. Mad

Re: Returning Recordset from PL/SQL [message #196233 is a reply to message #196228] Wed, 04 October 2006 10:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I was once in a likewise scenario. I complained to my manager but to no avail; he couldn't get things changed on the other side either. I told him what I was about to do:
I sent a stored procedure to the DBA that was AWFULLY full of errors (typos and plain errors).
Naturally, DBA sent it back. Within a minute I resent a changed version (I knew which error would pop up, so had a modified version with only 1 error corrected at hand).

Reason I told my manager was that DBA told HIS manager after iteration 19 that I was no good. His manager contacted mine, and after iteration 20 I was given the desired privileges.

Cool
Re: Returning Recordset from PL/SQL [message #196236 is a reply to message #196233] Wed, 04 October 2006 10:28 Go to previous message
Fellow
Messages: 6
Registered: October 2006
Location: NYC
Junior Member
Thumbs Up Thanks, Frank. That's the kind of thing I like to hear. I hope to follow your example and wear them down eventually. Until then I will have to make do with the mega passthroughs.
Previous Topic: Cost based and Rule Based Optimization
Next Topic: Retain Multiple Select Values When User Returns to Form
Goto Forum:
  


Current Time: Sun Dec 11 04:14:38 CST 2016

Total time taken to generate the page: 0.04609 seconds