Re: Creating and calling stored procedure
- From: Rene Nyffenegger <rene.nyffenegger@xxxxxx>
- Date: Wed, 25 Apr 2007 21:42:56 +0000 (UTC)
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
.
- Follow-Ups:
- Re: Creating and calling stored procedure
- From: Odd Bjørn Andersen
- Re: Creating and calling stored procedure
- Prev by Date: Re: Can I re-import objects after failing on missing tablespace?
- Next by Date: Re: Convert date into timestamp before runnin the a query
- Previous by thread: ORA-12514 - afer installing Patch
- Next by thread: Re: Creating and calling stored procedure
- Index(es):
Relevant Pages
|
Loading
