Re: Creating and calling stored procedure

"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message news:f0oi10$tko$1_at_klatschtante.init7.net...
> On 2007-04-25, Odd Bjørn Andersen <obande_at_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 Received on Thu Apr 26 2007 - 02:38:11 CDT