Re: PL/SQL Stored Procedure Question

Hold your horses there sonshine !

The error in your case is that you are not selecting the information into anything. It is not like SQL plus where you just run your SQL statement and the result set is scrolled up on the screen.

You have a couple of options here.

1. Use a cursor to retrieve your data (this is my recomendation)
2. use a select into clause is the procedure.

example of 1

procedure test is ( i_input in varchar2) is c1row mytable%rowtype;
cursor c1 is

   select * from mytable
   where mytable.column = i_input;
begin

   open c1;
   fetch c1 into c1_row;
   close c1;
end;

Example 2

You should check in your SQL manual or online help on how to use a select into clause.

I don't like this method as much because if the parameter is too small to fit the selected
data into you can cause an exception to be raised.

procedure test is ( i_input in varchar2) is v_temp_variable varchar2(100);
begin

   select mytable.column1 into v_temp_variable

     from mytable
     where mytable.column = i_input;

Then you can do what ever you like with the data retrieved into c1row. If you want it in a file you could use the utl_file package to write the data to a file on the
file system. You may want to put it into another table ? I don't know. But it can be done.

Hope this is helpful ...

Stan Zieg wrote:

> I would expect this to be a simple question, but have been unable to work
> through it myself....
>
> I'd like to create a parameterized stored procedure that uses input
> parameters to set up conditions for a query, and returns a recordset. I
> would expect that the implementation would be something like:
>
> (Using SQL PLUS as front end)
>
> SQL> CREATE OR REPLACE PROCEDURE myTEST(p_InValue IN VARCHAR2) AS
> 2 BEGIN
> 3 SELECT * FROM myTable WHERE MyTable.Value = p_InValue;
> 4 END myTEST;
> 5 /
>
> Then, by executing with a command like:
>
> EXECUTE MyTest('123456')
>
> The result would be the recordset that 'would' have resulted from the
> following query:
>
> SELECT * FROM myTable WHERE MyTable.Value = '123456';
>
> When I try to create the procedure I get the error message:
>
> Warning: Procedure created with compilation errors.
>
> Upon examination of error message:
>
> SQL> show errors
> Errors for PROCEDURE myTEST:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 3/1 PLS-00428: an INTO clause is expected in this SELECT statement
> 3/1 PL/SQL: SQL Statement ignored
> SQL>
>
> I would expect this capability to be a standard part of Oracle, since this
> is
> accomplished easily in SQL Server using stored procedures. It would appear
> from the error description that the result set must be moved into a table
> before it is retrieved. This is pretty inefficient, since the results of
> the query are transitory, and I only wish to send the result back to the
> requestor, not to save the result. The actual query I need to run is
> pretty complex and involves several subqueries. I have been unable to
> define a view that I select against with WHERE conditions, because the
> conditions cannot be
> passed to the subquery and the subquery size becomes huge without the
> limiting conditions in place.
>
> Can anyone help?
Received on Fri May 15 1998 - 02:38:40 CEST