Re: Creating and calling stored procedure


"Rene Nyffenegger" <rene.nyffenegger@xxxxxx> wrote in message
news:f0oi10$tko$1@xxxxxxxxxxxxxxxxxxxxxxxxx
On 2007-04-25, Odd Bjørn Andersen <obande@xxxxxxxxx> 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


.

Relevant Pages

  • Re: Creating and calling stored procedure
    ... an answer to in the documentation. ... I want to write a simple stored procedure in PL/SQL with one input ... how do I specify them in the call (execute) ...
    (comp.databases.oracle.misc)
  • Re: Wait for background processes to complete
    ... To be able to execute commands in the background and wait for their ... The documentation I am referring to is http://perldoc.perl.org/. ... You can run a command in the background with: ... There is a general problem with perl documentation: ...
    (comp.lang.perl.misc)
  • Re: Wait for background processes to complete
    ... To be able to execute commands in the background and wait for their ... The documentation I am referring to is http://perldoc.perl.org/. ... You can run a command in the background with: ... There is no further reference to a "double fork" (except in the ...
    (comp.lang.perl.misc)
  • Re: Why forth is not popular
    ... CHForth and all other Forths ... distributed by the Dutch FIG have truly excellent documentation and tutorials. ... > error, you execute your marker, make changes to your definition and run ... but Perl doesn't claim to be a compiler. ...
    (comp.lang.forth)
  • Re: Questions about Coding Practices
    ... the trinary conditional operator compiles the entire statement before it actually execute them. ... Use extensive external documentation for API documentation. ... What I _would_ recommend eschewing, and perhaps this is what the book means also, are comments _within_ the method body. ...
    (microsoft.public.dotnet.languages.csharp)