Re: Ref Cursor question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 07 Feb 1999 14:13:22 GMT
Message-ID: <36cf9d8e.16941340@192.86.155.100>

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Sun, 7 Feb 1999 09:23:42 -0000, you wrote:

>
>Your problem with the SQL that works from SQL*Plus but not
>in PL/SQL is the in-line view. Your version of Oracle is running
>at a level where PL/SQL is not yet capable of handling in-line views.
>
>Your requirement for
> >CREATE OR REPLACE PROCEDURE RETURN_RESULT_SET
> > (oCursor IN OUT MyPkg.CursorType,
> > AColumn IN VARCHAR,
> > AValue IN VARCHAR) as
> >BEGIN
> > open oCursor for
> > select * from ATable where AColumn like AValue||%
> >END;
> >
>
>has to be handled using the dbms_sql package.
>
>
>

In Oracle8i, this will be much easier by the way. It will look like this:

SQL> variable x refcursor

SQL> create or replace procedure return_result_set   2 ( oCursor in out myPkg.CursorType,   3 atable in varchar2,
  4 acolumn in varchar2,
  5 avalue in varchar2 )
  6 is
  7 begin

  8      open oCursor for
  9         'select empno, ename, job  from ' || aTable ||
 10          ' where ' || acolumn || ' like :avalue' using avalue;

11 end;
12 /
Procedure created.

SQL> exec return_result_set( :x, 'emp', 'ename', '%B%' ); PL/SQL procedure successfully completed.

SQL> print x

EMPNO ENAME JOB
---------- ---------- ---------

7698 BLAKE MANAGER SQL> exec return_result_set( :x, 'emp', 'job', '%C%' ); PL/SQL procedure successfully completed.

SQL> print x

EMPNO ENAME JOB
---------- ---------- ---------

      7369 SMITH      CLERK
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7934 MILLER     CLERK

dynamic sql in pl/sql is vastly improved -- much much easier to do.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Feb 07 1999 - 08:13:22 CST