Re: Stored proc problem

No need to be snotty about it.

When you use implicit cursors (SELECT column_list INTO variable_list FROM...), Oracle expects the query to return exactly one row. More than one, and it generates the exception TOO_MANY_ROWS. None, and you get NO_DATA_FOUND. Write an exception handler for the block containing the implicit cursor to handle any possible errors in a graceful way.

In order to handle multiple rows, use explicit cursors. Your query had no where clause so I'd expect it to return many rows (assuming the table held more than 1 row). The simplest thing you can do is: FOR rec IN (SELECT ... FROM ... WHERE...) LOOP   <process each row>
END LOOP;
where you add the statements to process the row data (held in the record variable rec in this illustration) in the loop.

Check the PL/SQL Guide for more info on cursors. --
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

Huwski wrote in article <35b73a4b.2053489_at_news.geccs.gecm.com>...
> On Thu, 23 Jul 1998 15:15:07 +0200, "John Bester"
> <johnb_at_iconnect.co.za> wrote:
>
> >I have a problem creating a stored procedure in Oracle 7.3.
> >
> >CREATE OR REPLACE PROCEDURE Test AS
> >BEGIN
> > SELECT * FROM MyTable;
> >END;
> >/
> >
> >This gives an error "MGR-00072: Warning: PROCEDURE TEST created with
> >compilation errors.", but it complies with the syntax found in the
"Oracle7
> >Server SQL Reference" (4-206). Can anyone help.
> >
> >Rgds
> >--
> >John Bester
> >johnb_at_iconnect.co.za
> >
> Really ?????? John, where do you expect the resultant data from the
> select statement to go ??
>
> What you need is......
>
> Procedure X Is
>
> v_name
>
> Begin
> Select name
> Into v_name
> From table;
> End;
>
>
> Huwski
>
> >
> >
>
>
Received on Fri Jul 24 1998 - 22:58:20 CDT