Re: Want to Create Custom Cursor in Stored Procedure?

In article <39462B10.89877E23_at_gate.net>,   "Keith L. Norris" <kln_at_gate.net> wrote:
> Hello Knowledge Source,
>
> Can I create a customized cursor is a stored procedure on an Oracle
> backend based on parameters passed to the stored procedure? For
> example, as far as I know, an explicit cursor is declared like:
>
> Declare Cursor as SELECT Field1, Field2, Field3 FROM table1 WHERE
 Field1
>
> = variable;
>
> Is there a way to say something like:
>
> DECLARE Cursor as StringVariable;
>
> If this could work as I'm hoping, StringVariable could contain my own
> customized select statement. This way I could control the conditions.
> For example, I may want to declare the cursor as:
>
> Declare Cursor as SELECT Field1, Field2, Field3 FROM table1 WHERE
 Field1
>
> = variable;
>
> during one call to the stored procedure and define the cursor to be:
>
> Declare Cursor as SELECT Field1, Field2, Field3 FROM table1 WHERE
 Field2
>
> = variable;
>
> in the next call to the stored procedure.
>
> I am using MS Access97 as the front-end. Is there a way to pass the
> string that I want evaluated in the cursor of the stored procedure in
 to
>
> the stored procedure and then have the stored procedure declare a
 cursor
>
> having the value of the variable passed in? For example, could I
> declare a cursor in the stored procedure as:
>
> DECLARE Cursor as StringVariablePassedInToStoredProcedure;
>
> ??
>
> I am new to PL/SQL and stored procedures.
>
> Thank you very much!!!
> Keith
>
>

In Oracle8i, release 8.1 and up, you can dynamically open a ref cursor (for a description and examples of these see: http://osi.oracle.com/~tkyte/ResultSets/index.html )

There you can basically:

declare

   p_cursor types.refcursor;
begin

   open p_cursor for 'some string here'; end;

In 8.0 and before, you must use DBMS_SQL to do dynamic sql (but you will not be returning these as result sets to the client, dbms_sql cursors "live" on the server only)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.