Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL PROCEDURES FOR CURSORS WITH VARIABLE ARGUMENTS IN SELECT STATEMENT

Re: PL/SQL PROCEDURES FOR CURSORS WITH VARIABLE ARGUMENTS IN SELECT STATEMENT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Feb 1999 14:24:04 GMT
Message-ID: <36c0506d.4172479@192.86.155.100>


A copy of this was sent to "Povodi Ohre a.s." <OHRE_at_UNL.PVTNET.CZ> (if that email address didn't require changing) On Fri, 5 Feb 1999 07:46:25 +0100, you wrote:

>Dear Friends,
>I am beginner in PL/SQL programming. I need to write a
>procedure with cursor varible which can use select with
>variable parameters.
>
>Example:
>
>PROCEDURE A
>(TABLE IN VARCHAR2,
> TIME IN DATE,
> ...)
> CURSOR c1 IS
> SELECT value FROM <table parameter> WHERE TIME1=<time parameter>;
> BEGIN
> ...
> END;
>
>How can I do it?
>

with dynamic sql...

it might look like:

SQL> create or replace procedure dynquery( p_tname in varchar2, p_time in date)   2 is

  3          l_theCursor             integer default dbms_sql.open_cursor;
  4          l_columnValue   varchar2(2000);
  5          l_status                integer;
  6          l_query                 varchar2(1000) default 'select ename from '
|| p_tname ||
  7
' where hiredate = :x';
  8 begin
  9
  9      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 10          dbms_sql.bind_variable( l_theCursor, ':x', p_time );
 11      dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
 12  
 12      l_status := dbms_sql.execute(l_theCursor);
 13  
 13          loop
 14          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 15          dbms_sql.column_value( l_theCursor, 1, l_columnValue );
 16                  dbms_output.put_line(  l_columnValue );
 17          end loop;
 18      dbms_sql.close_cursor(l_theCursor);
 19
 19 end ;
 20 /

Procedure created.

SQL>
SQL> exec dynquery( 'emp', '23-jan-82' ); MILLER PL/SQL procedure successfully completed.

>Thank you very much in advance
>
> Robert
>
 

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 Sat Feb 06 1999 - 08:24:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US