PL/SQL Stored Procedure Question

From: Stan Zieg <stanley.zieg_at_sciatl.com>
Date: 14 May 1998 18:51:14 GMT
Message-ID: <01bd7f69$33ccdf00$389785c0_at_sciatl>

[Quoted] [Quoted] I would expect this to be a simple question, but have been unable to work [Quoted] 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';

[Quoted] 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 Thu May 14 1998 - 20:51:14 CEST