Re: Creating and calling stored procedure

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