Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL PROCEDURES FOR CURSORS WITH VARIABLE ARGUMENTS IN SELECT STATEMENT
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 ||
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
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
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