Re: PL/SQL Stored Procedure Question

Stan,

Given that you want to return a row from myTable, it sounds more like you're looking to write a function. Assuming we keep with the myTEST name, you will want something like :-

CREATE OR REPLACE FUNCTION myTEST(p_InValue IN myTable.Value%TYPE) RETURN myTable%ROWTYPE IS

   CURSOR myTableCursor(local_value myTable.Value%TYPE) IS    SELECT *
   FROM myTable
   WHERE value = local_value;
   myTableRow myTable%ROWTYPE;
BEGIN
   OPEN myTableCursor(p_InValue);
   FETCH myTableCursor INTO myTableRow;
   CLOSE myTableCursor;
   RETURN (myTableRow);
END myTEST;

Once compiled, and assuming you have a column called 'text' in myTable you could write in SQL*Plus

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(myTEST(1).text);

Which will result in the value for text being output, where the value column (key ?) is 1.

Hope this gives you some ideas - you might want to do some work on capturing the result of no row matching the value passed into the function.

Cheers
Marc Nicholls

Stan Zieg wrote in message <01bd7f69$33ccdf00$389785c0_at_sciatl>...
>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 Thu May 14 1998 - 21:33:36 CEST