I am very new to Oracle and have a simple question, which I can't easy find
an answer to in the documentation.

I want to write a simple stored procedure in PL/SQL with one input
parameter, and which returns 1 result set.
That should be very simple, but I can't find any examples for this simple
task in the documentation. Maybe
I haven't looked in the right places :-) Is there anyone who could help me
with an example?

And I also want to call this procedure from SQL*Plus, like this: call (or
execute) schema.proc (param=1) and get something like this in return:

Id Name Address
1 AA Street 1, 12345 Town
2 BB Street 5, 12345 Town
......

Is that possible? And what is the syntax? And what if there are out
parameters, how do I specify them in the call (execute)
statement?

Regards
Odd B Andersen

"Odd Bjørn Andersen" <obande@online.no> a écrit dans le message de news: 462f446f$0$90269$14726298@news.sunsite.dk...
|I am very new to Oracle and have a simple question, which I can't easy find
| an answer to in the documentation.
|
| I want to write a simple stored procedure in PL/SQL with one input
| parameter, and which returns 1 result set.
| That should be very simple, but I can't find any examples for this simple
| task in the documentation. Maybe
| I haven't looked in the right places :-) Is there anyone who could help me
| with an example?
|
| And I also want to call this procedure from SQL*Plus, like this: call (or
| execute) schema.proc (param=1) and get something like this in return:
|
| Id Name Address
| 1 AA Street 1, 12345 Town
| 2 BB Street 5, 12345 Town
| .....
|
| Is that possible? And what is the syntax? And what if there are out
| parameters, how do I specify them in the call (execute)
| statement?
|
| Regards
| Odd B Andersen
|
|

http://download-uk.oracle.com/docs/c...5.htm#i1211948

Regards
Michel Cadot

Odd Bjørn Andersen wrote:
> I am very new to Oracle and have a simple question, which I can't easy find
> an answer to in the documentation.
>
> I want to write a simple stored procedure in PL/SQL with one input
> parameter, and which returns 1 result set.
> That should be very simple, but I can't find any examples for this simple
> task in the documentation. Maybe
> I haven't looked in the right places :-) Is there anyone who could help me
> with an example?
>
> And I also want to call this procedure from SQL*Plus, like this: call (or
> execute) schema.proc (param=1) and get something like this in return:
>
> Id Name Address
> 1 AA Street 1, 12345 Town
> 2 BB Street 5, 12345 Town
> .....
>
> Is that possible? And what is the syntax? And what if there are out
> parameters, how do I specify them in the call (execute)
> statement?
>
> Regards
> Odd B Andersen

http://www.psoug.org
Click on Morgan's Library
Click on REF CURSOR
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 2007-04-25, Odd Bjørn Andersen <obande@online.no> wrote:
> I am very new to Oracle and have a simple question, which I can't easy find
> an answer to in the documentation.
>
> I want to write a simple stored procedure in PL/SQL with one input
> parameter, and which returns 1 result set.
> That should be very simple, but I can't find any examples for this simple
> task in the documentation. Maybe
> I haven't looked in the right places :-) Is there anyone who could help me
> with an example?
>
> And I also want to call this procedure from SQL*Plus, like this: call (or
> execute) schema.proc (param=1) and get something like this in return:
>
> Id Name Address
> 1 AA Street 1, 12345 Town
> 2 BB Street 5, 12345 Town
> .....
>
> Is that possible? And what is the syntax? And what if there are out
> parameters, how do I specify them in the call (execute)
> statement?
>
> Regards
> Odd B Andersen


something like (untested):

create procedure p(param in varchar2) as
begin

for r in (select col_1, col_2 from tab where col_3 = param) loop

dbms_output.put_line(r.col_1 || ' ' || r.col_2);

end loop;

end p;
/


See also
http://www.adp-gmbh.ch/blog/2006/01/08.html
http://www.adp-gmbh.ch/blog/2006/03/24.php
http://www.adp-gmbh.ch/blog/2007/04/22.php


--
Rene Nyffenegger
http://www.adp-gmbh.ch

"Rene Nyffenegger" <rene.nyffenegger@gmx.ch> wrote in message
news:f0oi10$tko$1@klatschtante.init7.net...
> On 2007-04-25, Odd Bjørn Andersen <obande@online.no> wrote:
>> I am very new to Oracle and have a simple question, which I can't easy
>> find
>> an answer to in the documentation.
>>
>> I want to write a simple stored procedure in PL/SQL with one input
>> parameter, and which returns 1 result set.
>> That should be very simple, but I can't find any examples for this simple
>> task in the documentation. Maybe
>> I haven't looked in the right places :-) Is there anyone who could help
>> me
>> with an example?
>>
>> And I also want to call this procedure from SQL*Plus, like this: call (or
>> execute) schema.proc (param=1) and get something like this in return:
>>
>> Id Name Address
>> 1 AA Street 1, 12345 Town
>> 2 BB Street 5, 12345 Town
>> .....
>>
>> Is that possible? And what is the syntax? And what if there are out
>> parameters, how do I specify them in the call (execute)
>> statement?
>>
>> Regards
>> Odd B Andersen
>
>
> something like (untested):
>
> create procedure p(param in varchar2) as
> begin
>
> for r in (select col_1, col_2 from tab where col_3 = param) loop
>
> dbms_output.put_line(r.col_1 || ' ' || r.col_2);
>
> end loop;
>
> end p;
> /
>
>
> See also
> http://www.adp-gmbh.ch/blog/2006/01/08.html
> http://www.adp-gmbh.ch/blog/2006/03/24.php
> http://www.adp-gmbh.ch/blog/2007/04/22.php
>
>
> --
> Rene Nyffenegger
> http://www.adp-gmbh.ch

Thanks to all of you! I will look at the suggestion, and the links.
Hopefully they will help me solve this.

Regards
Odd B Andersen