set define '^' set verify off prompt ...wwv_dbms_sql Rem Rem MODIFIED (MM/DD/YYYY) Rem jkallman 01/28/2008 - Created Rem mhichwa 01/30/2008 - Added procedure parse_as_user and open cursor Rem sspadafo 01/30/2008 - Make package invoker's rights Rem jkallman 01/31/2008 - Change parse_as_user arguments to take username and not id, remove open_cursor Rem jkallman 02/04/2008 - Change all names and values arrays to be of type sys.wwv_dbms_sql.vc_arr2 create or replace package sys.wwv_dbms_sql authid current_user as type vc_arr2 is table of varchar2(32767) index by binary_integer; procedure parse_as_user ( p_cursor in integer, p_query in varchar2, p_username in varchar2, p_use_roles in boolean default FALSE) ; procedure parse_as_user ( p_cursor in integer, p_statement in sys.dbms_sql.varchar2s, p_username in varchar2, p_lfflg in boolean default FALSE, p_use_roles in boolean default FALSE) ; function get_userid( p_username in varchar2 ) return int ; /* * No inputs or outputs -- just execute * a block of code, * will autobind using GET_BINDS and V() function * * p_sql can be anything except a SELECT (well, it can be * a select but nothing will happen if it is) */ procedure run_block ( p_sql in varchar2, p_user in varchar2, p_use_roles in boolean default FALSE); /* * No outputs -- executes the block of SQL * and binds using the p_names table and p_values array. * * p_sql can be anything except a SELECT (well, it can be * a select but nothing will happen if it is) */ procedure run_block ( p_sql in varchar2, p_names in sys.dbms_sql.desc_tab, p_values in sys.wwv_dbms_sql.vc_arr2, p_user in varchar2 ); /* * runs long DDL statements that are stored in the * varchar2s table. Usage: flows@DEV816> declare 2 x dbms_sql.varchar2s; 3 begin 4 x(1) := 'create table '; 5 x(2) := ' test ( x int )'; 6 sys.wwv_dbms_sql.run_ddl( x, 'SCOTT' ); 7 end; 8 / */ procedure run_ddl ( p_sql in sys.dbms_sql.varchar2s, p_user in varchar2 ); /* * No outputs - same as run_block except the bind * names are a varchar array and not a dbms_sql * data type. If state is to be preserved then * bind vars that change during execution will be * written back to the flows session state cache. */ procedure run_block2 ( p_sql in varchar2, p_names in sys.wwv_dbms_sql.vc_arr2, p_values in sys.wwv_dbms_sql.vc_arr2, p_user in varchar2, p_preserve_state in boolean default TRUE ); /* * No outputs - same as run_block except the bind * names are a varchar array and not a dbms_sql * data type. If state is to be preserved then * bind vars that change during execution will be * written back to the flows session state cache. */ procedure run_block3 ( p_sql in varchar2, p_names in sys.wwv_dbms_sql.vc_arr2, p_values in sys.wwv_dbms_sql.vc_arr2, p_types in sys.wwv_dbms_sql.vc_arr2, p_masks in sys.wwv_dbms_sql.vc_arr2, p_user in varchar2, p_preserve_state in boolean default TRUE ); /* * No outputs - same as run_block2 but this not specify an outside and * binding variables. This only binds using arrays and does not support * autobinds (bind variables directly in the statement but not in the array). * State will *not* be preserved. Thus, any assignments to items or bind variables * performed in SQL in run_block4 will not persist. For now, run_block4 * should only be used for wwv_flow_dml.insert_row and wwv_flow_dml.update_row. * */ procedure run_block4 ( p_sql in varchar2, p_names in sys.wwv_dbms_sql.vc_arr2, p_values in sys.wwv_dbms_sql.vc_arr2, p_user in varchar2 ); /* * PL/SQL syntax check * wraps begin return null; end; around p_sql and executes * using run_block */ function check_plsql ( p_sql in varchar2, p_flow_id in number, p_security_group_id in number ) return varchar2; /* * p_sql should be a query that selects A single row and A single * column and that column should be a NUMBER. * same as select_num below. */ function countem( p_sql in varchar2, p_user in varchar2 ) return varchar2; /* * valueof functions want p_sql to be a function like "foo;" or * sysdate, or user, or "5+10". They should be RHS values in an * assigment. I take p_sql and simply build a string: * * 'begin :x := ' || rtrim(P_SQL,';') || '; end;' * * and execute it. */ function valueof_vc( p_sql in varchar2, p_user in varchar2 ) return varchar2; function valueof_num( p_sql in varchar2, p_user in varchar2 ) return number; function valueof_date( p_sql in varchar2, p_user in varchar2 ) return date; /* * p_sql should be a query that selects A single row and A single * column and that column should be a NUMBER, string or date -- just * calll the right function. */ function select_vc( p_sql in varchar2, p_user in varchar2 ) return varchar2; function select_num( p_sql in varchar2, p_user in varchar2 ) return number; function select_date( p_sql in varchar2, p_user in varchar2 ) return date; /* * p_sql should be a block of code like: * * declare * l_theCursor number default dbms_sql.open_cursor; * begin * dbms_sql.parse( l_theCursor, 'select * from scott.emp', dbms_sql.native ); * return l_theCursor; * end; * * we glue: * * declare function x return number is begin * * onto the front and: * * return null; end x; begin :l_cursor := x; end; * * onto the end and execute the block -- sort of a dynamic function function. * the block must return a NUMBER and it is intended this is a dbms_sql * cursor. */ function func_returning_cursor( p_sql in varchar2, p_user in varchar2 ) return number; end wwv_dbms_sql; / show errors