Re: Stored procedure with NOT PL/SQL

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

  You can write a select * from ... while in SQLPlus. And it does echo to the screen.

Once you move to stored procedures, you lose this simple interface. You have to write
in the support for doing this. Remember, stored procedures are executed on the server
and the result is returned to the calling program.

You are likely to be looking at dbms_output or the utl_file library of functions.

As to needing an into, there are some work arounds. For example

declare

    cursor myCur is select * from foo;

begin

    for myRow in myCur
    loop

            ... other code ...

    end loop;

end;

Within this (annonymous) procedure, each field of the table foo are automatically
created and assigned to a variable myRow. This variable is a %ROWTYPE variable
which has all the fields of the cursor.

To output the fields, you still must explicitly output them.

In direct response to the question, why have a stored procedure to display a
simple query of a table? You may want to create a sql file with just the select statement
in it. From SQLPlus, you can run the file

_at_foo.sql

or

run foo.sql

or

get foo.sql
/

All of the above will work...... assuming the statement is in the foo.sql file
and this is on the path.

Generally speaking stored procedures are not the way to go. I have been writing code for a long time. I have many procedures which I execute locally.
I use stored procedures for triggers, supporting selects ( functions to manipulate data),
and for data integrity. More often than not, I would use my own procedures which
are stored as text files.

Mike Krolewski Received on Sat Nov 28 1998 - 02:35:37 CET