Re: How to create stored procedure in SQLplus?

Rookie wrote:

> thx
> I have tried but it said compiled with error
>
> assume i have a table abc
>
> create or replace procedure showabc is
> begin
> select * from abc;
> end;
> /
>
> what is the problem ?
>
> thanks again
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com>
> ???????:3EB13413.7009_at_yahoo.com...
> > Rookie wrote:
> > >
> > > Hi could some one show me how to create stored procedure in SQLplus,
> thx?
> > > either with param or without param. thanks in advance
> >
> > SQL> create or replace procedure do_nothing is
> > 2 begin
> > 3 null;
> > 4 end;
> > 5 /
> >
> > SQL> create or replace procedure do_nothing(p_param in number) is
> > 2 x number;
> > 3 begin
> > 4 x := p_param;
> > 5 end;
> > 6 /
> >
> > hth
> > connor
> >
> > --
> > =========================
> > Connor McDonald
> > http://www.oracledba.co.uk
> >
> > "Some days you're the pigeon, some days you're the statue"

The problem is that a stored procedure executes inside the database ... not in SQL*PLUS so you are no longer allowed to SELECT * FROM ...

Instead do this:

CREATE OR REPLACE PROCEDURE showabc IS

x_rec abc%ROWTYPE;

BEGIN
   SELECT *
   INTO x_rec
   FROM abc;
END showabc;
/

Usually you would not SELECT * but rather select specific fields:

CREATE OR REPLACE PROCEDURE showabc IS

fld1   abc.field1%TYPE;
fld2   abc.field2%TYPE;
fld3   abc.field3%TYPE;

BEGIN
   SELECT field1, field2, field3
   INTO fld1, fld2, fld3
   FROM abc;
END showabc;
/

To view the results either include an insert statement into a test table or use DBMS_OUTPUT which requires that you execute SQL> SET SERVEROUTPUT ON in your session before executing the procedure.

CREATE OR REPLACE PROCEDURE showabc IS

fld1   abc.field1%TYPE;
fld2   abc.field2%TYPE;
fld3   abc.field3%TYPE;

BEGIN
   SELECT field1, field2, field3
   INTO fld1, fld2, fld3
   FROM abc;
   DBMS_OUTPUT.PUT_LINE(fld1);
END showabc;
/

SQL> SET SERVEROUTPUT ON
SQL> exec showabc;

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)