Re: Stored Procedure with NOT PL/SQL

1. Display on screen is not an SQL language feature, but SQL*Plus interpreters one. So when You use PL/SQL ( begin ... end; clause ) this is not used, because the language doesn't know where will it be executed.
2. To display anything from PL/SQL You must use dbms_output package and set the 'serveroutput on' in Your SQL*Plus But to do this You have to have the displayed values stored in PL/SQL variables, and the clause INTO allows You to assign query results with variables
3. You have to use cursor when it is supposed that the query will return more than 1 row, because the assignment with variables is made for each row. Otherwise You'll have a TOO_MANY_ROWS exception

HTH
Robert

Frank Meng wrote:

> I want to create a stored procedure for
> SELECT * from MYTABLE
> When I use BEGIN & END command, Oracle asked me using INTO.
> I don't want to INTO some table, but only display it on the screen.
> When I don't use BEGIN & END with SQL Plus 8.0, I got the message:
> -------------------------------------
> PLS-00103: Encountered the symbol "SELECT" when expecting one of
> the following:
> begin function package pragma procedure subtype type use ...
> -------------------------------------
> Someone told me I must use a cursor to select only one record each time
> and
> use INTO.
> I can't believe it, because I can do it very easily with Microsoft SQL
> Server.
> Please tell me what is wrong to frankmeng_at_usa.net
> Thanks in advance.
> Frank

--

Robert Liziniewicz                    C.I. HTS-ComArch
Robert.Liziniewicz_at_ci.comarch.pl      ul. Ujastek 10, Krakow
                                      tel. (+48 12) 644-66-31
                                           (+48 601) 47-86-81