For i in 1..cname_in.count loop dbms_sql.bind_variable(v_cursor, ':bv' || i, value_in(i)); end loop;
for i in 1 .. 3 loop dbms_sql.define_column(v_cursor, i, v_colvalue, 4000); end loop;
v_status := dbms_sql.execute(v_cursor);
while (dbms_sql.fetch_rows(v_cursor) > 0) loop for i in 1 .. 3 loop dbms_sql.column_value(v_cursor, i, v_colvalue); dbms_output.put(v_colvalue || '--------'); end loop; dbms_output.new_line; end loop; end;
17:53:02 sys@ORCL>grant create any context to lab;
授权成功。
已用时间: 00: 00: 00.01 17:53:38 sys@ORCL>conn lab/lab; 已连接。 17:53:49 lab@ORCL>create or replace context bv_context using dyn_demo;
上下文已创建。
已用时间: 00: 00: 00.06
create or replace package dyn_demo is procedure do_query2(cname_in dbms_sql.varchar2_table, operator_in dbms_sql.Varchar2_Table, value_in dbms_sql.Varchar2_Table); end dyn_demo;
create or replace package body dyn_demo is procedure do_query2(cname_in dbms_sql.varchar2_table, operator_in dbms_sql.Varchar2_Table, value_in dbms_sql.Varchar2_Table) is type rc is ref cursor; v_cursor rc;
For i in 1 .. cname_in.count loop v_query := v_query || v_sep || cname_in(i) || ' ' || operator_in(i) || ' ' || 'sys_context(''BV_CONTEXT'',''' || cname_in(i) || ''')'; dbms_session.set_context('bv_context',cname_in(i),value_in(i)); v_sep := ' and '; end loop;
open v_cursor for v_query; loop fetch v_cursor into v_empno, v_ename, v_sal; exit when v_cursor%notfound; dbms_output.put_line(v_empno || '--------' || v_ename || '--------' || v_sal || '--------'); end loop; close v_cursor; end do_query2;