Re: Can't create a simple select procedure

Hi,
You need to deside what you want to do with the data you get from your SELECT before you program in a stored procedure. The example you give has no output hence it makes no sence. The syntax error you have is that PL/SQL requires into clauses on in-line declared cursors.

If you want to process the rows from a select as yours I would make something like this:

BEGIN
   FOR W1 IN (SELECT * FROM mytable) LOOP

      do_something(w1.column1, w1.column2 .....); -- (or just declare W1 as ROWTYPE of mytable and pass that)

   END LOOP;
END; It can be that simple :)

• Peter Larsen Senior Oracle Consultant

awold_at_usa.net wrote in message <74mojd$ote$1_at_nnrp1.dejanews.com>...
>Ok, I'm new to oracle but before I go out and get "Idiot" tatooed onto my
>forehead I thought I'd toss my problem out here. I can't seem to create a
>simple select procedure. All I want to do is "SELECT * FROM CMS_APP". I
can
>easily run this from SQL Plus 8.0. But when I go to created this as a
>procedure it always compiles with errors. Ok, so this is what I do:
>
>1) Start up Oracle Enterprise Manager
>
>2) In the pane showING the folder tree I navigate to
>DATABASES|MYDATABASE|SCHEMA OBJECTS|PROCEDURES|SSUSER (SSUSER is my
schema)
>
>3) Right click on SSUSER and choose Create...
>
>4) Now I get the create dialog, I give the procedure some name
>
>5) Then in the Source text box I enter
> AS
> BEGIN
> SELECT * FROM CMS_APP;
> END;
>
>6) I OK the dialog, the dialog goes away, I right click on SSUSER again but
>choose Quick Edit... to edit the procedure
>
>7) The status of the procedure is INVALID and when I click on the Show
Errors
>button I get another dialog that show the following 2 lines:
>
>PLS-00428: an INTO clause is expected in this SELECT statement
>PL/SQL: SQL Statement ignored
>
>8) Can anyone tell me why something as simple as this doesn't work! I've
>already looked up the error number PLS-00428 and that doesn't tell me
>anything. If instead I try an SQL statement such as this:
>
>INSERT INTO CMS_APP (appname,version) VALUES ('some app name','some
version')
>
>I can create the procedure with know problem. Anybody know why I should be
>experiencing this problem?
>
>Tony W.
>Renaissance Worldwide, Inc
>awold_at_rens.com
>awold_at_usa.net
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Dec 10 1998 - 15:13:33 CET