Home » SQL & PL/SQL » SQL & PL/SQL » Selecting from a stored proc (Oracle 10)
Selecting from a stored proc [message #312854] Wed, 09 April 2008 23:07 Go to next message
witchfindergeneral
Messages: 2
Registered: April 2008
Junior Member
Hi all,

how do you write a simple stored procedure to return a result set? e.g.

create or replace tst as
begin
select * from ...

end;



Can I then simply issue a select * from tst?

Re: Selecting from a stored proc [message #312856 is a reply to message #312854] Wed, 09 April 2008 23:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can I then simply issue a select * from tst?
Yes, but it won't work.
Too bad you won't RTFM before trying to code.
The PL/SQL Reference Manual can be found at http://tahiti.oracle.com
Re: Selecting from a stored proc [message #312861 is a reply to message #312854] Wed, 09 April 2008 23:47 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> Yes, but it won't work.
You wanted to say, that the procedure will be compiled with errors (and so unusable) instead Wink

Yes there are two ways to achieve this:
- return a collection of rows - see PL/SQL User's Guide and Reference in documentation
- return a cursor - recommended; see the same documentation book or following topics:
http://www.orafaq.com/forum/t/2415/2/
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471758621321
You may find many more using Search capabilities. But start with documentation first...
Re: Selecting from a stored proc [message #312872 is a reply to message #312854] Thu, 10 April 2008 00:32 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
You need to declare cursor to select all the columns, so better try in a simple way as shown below

create or replace tst as
a varchar2(50)
begin
select ename into a from emp where empno = 7788;
dbms_output.put_line('The employee name is '||ename);
end;

Re: Selecting from a stored proc [message #312876 is a reply to message #312872] Thu, 10 April 2008 00:43 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ap_karthi wrote on Thu, 10 April 2008 07:32
You need to declare cursor to select all the columns, so better try in a simple way as shown below

create or replace tst as
a varchar2(50)
begin
select ename into a from emp where empno = 7788;
dbms_output.put_line('The employee name is '||ename);
end;



Except that this does in no way comply to the requirements.
Re: Selecting from a stored proc [message #312878 is a reply to message #312872] Thu, 10 April 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ It is not fornatted
2/ It does not answer the question

Better think before replying.

Regards
Michel
Re: Selecting from a stored proc [message #312953 is a reply to message #312878] Thu, 10 April 2008 03:41 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Thu, 10 April 2008 06:44
1/ It is not fornatted
2/ It does not answer the question

Better think before replying.



It is also syntactically incorrect, and would not compile at all, never mind compile with errors.
Re: Selecting from a stored proc [message #312984 is a reply to message #312854] Thu, 10 April 2008 04:47 Go to previous messageGo to next message
mohdbfaq
Messages: 5
Registered: April 2008
Junior Member
One option is to user pipelined functions. The other option is to return Oracle collection object.

Mohan

[Updated on: Thu, 10 April 2008 05:22] by Moderator

Report message to a moderator

Re: Selecting from a stored proc [message #313156 is a reply to message #312984] Thu, 10 April 2008 22:37 Go to previous messageGo to next message
witchfindergeneral
Messages: 2
Registered: April 2008
Junior Member
Thanks for the replies. Im not asking for a complete solution, just some advice.

I take it you can't issue this :


create or replace procedure test is
begin
select * from mytable;
end;


Cool, thats all I needed to know. It's for implementation within a crystal report btw.
Re: Selecting from a stored proc [message #313184 is a reply to message #313156] Fri, 11 April 2008 00:33 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Also search for ref cursor in PL/SQL User's Guide and Reference.

Regards
Michel
Previous Topic: Simulating If Then Else in Decode
Next Topic: filtering columns with NULL values
Goto Forum:
  


Current Time: Thu Feb 06 14:37:11 CST 2025