Re: Stored Procedure with NOT PL/SQL

Dear Frank

you're having a few conceptual problems ... but don't worry, we've all been there!!!

> I want to create a stored procedure for
> SELECT * from MYTABLE

sounds like possiblity of >1 row returned ...

> When I use BEGIN & END command, Oracle asked me using INTO.
> I don't want to INTO some table

fear not! the INTO clause in PL/SQL means INTO a variable however, as others pointed out, only works for 0 or 1 row returned

>but only display it on the screen.

in SQL*Plus, use:
SET SERVEROUTPUT ON in PL/SQL, after the first BEGIN, use:
DBMS_OUTPUT.enable;

RTFM on PL/SQL and Cursor FOR loops
it's the fastest way to get what you need defined and done, ex:

CURSOR my_cursor IS
SELECT * FROM my_table;

later that same module ...
FOR my_cursor_rec IN my_cursor LOOP
 DBMS_OUTPUT.put_line(my_cursor_rec.name); END LOOP; you can also:
define a SQL*Plus VARIABLE rc_something REFCURSOR

build a function returning a result set define a result set structure in your function use OPEN and RETURN(rs)

EXEC rc_something := my_function
then, simply PRINT :rc_something

this avoids using DBMS_OUTPUT package and is probably most like what you're accustomed to in SQL Server

good luck

Bob Grove
bgrove_at_shore.net
"join the struggle against multi-mediocrity"

Frank Meng wrote in message <365AC672.C13F90D1_at_usa.net>...
> 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
>
Received on Wed Nov 25 1998 - 12:53:07 CST