Re: HELP!Oracle Store Procedure

Yes, you must use a cursor. A 'stand-alone' (outside of a cursor) select statement can only be used when it is designed to return no more than 1 row. Obviously your select statement is intended to return multiple rows. A cursor allows you to loop through the result set retrieved by your select one row at a time, work with the data, then close and discard the results when you've looped through all of the rows. This is a fundamental concept in PL/SQL.

You also have to have variables to which you assign your retrieved results before you can work with them in any way. Also a fundamental concept. The compiler is giving you that error because it's treating your table name as a variable which has not been declared because you're not selecting 'into' something prior to your 'from' clause

You have some reading to do. The best source is undoubtedly the O'Reilly book 'Oracle PL/SQL Programming' by Steven Feuerstein. Buy it and read, read, read before you try anything else... it will be far less frustrating for you and is well worth the time investment.

Good luck,
-Brendan

In article <6jc97h$sae$1_at_nnrp1.dejanews.com>,   cosmogong_at_my-dejanews.com wrote:
>
> I am quite new to oracle store procedure. I logged in using my login name
> and password and try to create a simple procedure:
> CREATE OR REPLACE PROCEDURE test_proc AS
> BEGIN
> SELECT * FROM ORADBA.table_name;
> END;
>
> and oracle complained that ORADBA.table_name must be declared first.
>
> I don't know why it is so and I also wonder if this will return me a
> recordset. I read something about cursors. Do I have to use a cursor?
>
> Many thanks.
>
> Cosmo
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed May 13 1998 - 15:49:29 CDT