Oracle WWV_DBMS_SQL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Undocumented APEX support utilities
AUTHID CURRENT_USER
Data Types type vc_arr2 is table of varchar2(32767) index by binary_integer;

c_empty_vc_arr2 vc_arr2;
Dependencies
ALL_USERS WWV_FLOW_DATA_UPLOAD WWV_FLOW_PROVISIONING
DBMS_ASSERT WWV_FLOW_DISP_PAGE_PLUGS WWV_FLOW_RENDER_QUERY
DBMS_DB_VERSION WWV_FLOW_DML WWV_FLOW_REST
DBMS_SQL WWV_FLOW_DYNAMIC_EXEC WWV_FLOW_SECURITY
DBMS_STANDARD WWV_FLOW_ERROR WWV_FLOW_SW_API
DBMS_SYS_SQL WWV_FLOW_F4000_PLUGINS WWV_FLOW_SW_PAGE_CALLS
DBMS_UTILITY WWV_FLOW_F4000_UTIL WWV_FLOW_SW_UTIL
GV_$SESSION WWV_FLOW_FILE_MGR WWV_FLOW_TEAM_FILE
HTMLDB_UTIL WWV_FLOW_GENERATE_DDL WWV_FLOW_THEME_FILES
PLITBLM WWV_FLOW_IMP_PARSER WWV_FLOW_TREE
SYS_STUB_FOR_PURITY_ANALYSIS WWV_FLOW_INSTALL_WIZARD WWV_FLOW_TREE_REGION
USER$ WWV_FLOW_INSTANCE_ADMIN WWV_FLOW_UPGRADE
V WWV_FLOW_ITEM WWV_FLOW_UTILITIES
V_$SESSION WWV_FLOW_JOB WWV_FLOW_WEB_SERVICES
WWV_FLOW WWV_FLOW_LANG WWV_FLOW_WIZARD_API
WWV_FLOWS WWV_FLOW_LIST WWV_FLOW_WS_API
WWV_FLOW_ADVISOR_DEV WWV_FLOW_LOAD_DATA WWV_FLOW_WS_ATTACHMENT
WWV_FLOW_AUTHENTICATION_NATIVE WWV_FLOW_LOAD_EXCEL_DATA WWV_FLOW_WS_EXPORT
WWV_FLOW_CALENDAR WWV_FLOW_NATIVE_ITEM WWV_FLOW_WS_GEOCODE
WWV_FLOW_COLLECTION WWV_FLOW_PLSQL_EDITOR WWV_FLOW_WS_IMPORT
WWV_FLOW_COMPANY_SCHEMAS WWV_FLOW_PLUGIN WWV_FLOW_WS_SETUP
WWV_FLOW_CUSTOM_AUTH_STD WWV_FLOW_PLUGIN_DEV WWV_FLOW_WS_WEBPAGE
WWV_FLOW_DATALOAD_XML WWV_FLOW_PLUGIN_UTIL WWV_RENDER_REPORT3
WWV_FLOW_DATA_QUICK_FLOW WWV_FLOW_PROVISION  
Documented No
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to APEX_040200
Source {ORACLE_HOME}/apex/core/wwv_dbms_sql.sql
Subprograms
CHECK_PLSQL PARSE_AS_USER SELECT_DATE
CLEAR_ERROR_BACKTRACE RUN_BLOCK SELECT_NUM
COUNTEM RUN_BLOCK2 SELECT_VC
FUNC_RETURNING_CURSOR RUN_BLOCK3 VALUEOF_DATE
GET_ERROR_BACKTRACE RUN_BLOCK4 VALUEOF_NUM
GET_USERID RUN_BLOCK5 VALUEOF_VC
KILL_SESSION RUN_DDL  
 
CHECK_PLSQL
Wraps begin return null; end; around p_sql and executes using RUN_BLOCK wwv_dbms_sql.check_plsql(
p_sql               IN VARCHAR2,
p_flow_id           IN NUMBER,
p_security_group_id IN NUMBER)
RETURN VARCHAR2;
TBD
 
COUNTEM
p_sql should be a query that selects A single row and a single column and that column should be a NUMBER

This demo does not generate an error neither does it provide a count.
wwv_dbms_sql.countem(
p_sql  IN VARCHAR2,
p_user IN VARCHAR2)
RETURN VARCHAR2;
conn / as sysdba

DECLARE
 lQuery  VARCHAR2(40) := 'SELECT MAX(num_rows) FROM dba_tables';
 lRetVal NUMBER;
BEGIN
  lRetVal := wwv_dbms_sql.countem(lQuery, 'SYS');
  dbms_output.put_line('Return Value: ' || TO_CHAR(lRetVal));
END;
/
 
GET_USERID
Returns the USER_ID corresponding with a named user wwv_dbms_sql.get_userid(p_username IN VARCHAR2 ) RETURN INT;
conn / as sysdba

SELECT username, user_id
FROM dba_users
ORDER BY 1;

SELECT wwv_dbms_sql.get_userid('XDB')
FROM dual;
 
KILL_SESSION
Package equivalent to ALTER SYSTEM KILL SESSION wwv_dbms_sql.kill_session(
p_sid     IN NUMBER,
p_serial# IN NUMBER,
p_inst_id IN NUMBER DEFAULT NULL);
SELECT inst_id, sid, serial#, username, schemaname, osuser
FROM gv$session
WHERE username = 'AQUSER';

wwv_dbms_sql.kill_session(9, 177, 2);
 
PARSE_AS_USER
Overload 1 wwv_dbms_sql.parse_as_user(
p_cursor    IN INTEGER,
p_query     IN VARCHAR2,
p_username  IN VARCHAR2,
p_use_roles IN BOOLEAN DEFAULT FALSE);
TBD
Overload 2 wwv_dbms_sql.parse_as_user(
p_cursor    IN INTEGER,
p_statement IN sys.dbms_sql.varchar2a,
p_username  IN VARCHAR2,
p_lfflg     IN BOOLEAN DEFAULT FALSE,
p_use_roles IN BOOLEAN DEFAULT FALSE);
TBD
Overload 3 wwv_dbms_sql.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);
TBD
 
RUN_BLOCK
Execute a block of code, will autobind using GET_BINDS and V() function

Overload 1
wwv_dbms_sql.run_block(
p_sql       IN VARCHAR2,
p_user      IN VARCHAR2,
p_use_roles IN BOOLEAN DEFAULT FALSE);
TBD
executes the block of SQL and binds using the p_names table and p_values array

Overload 2
wwv_dbms_sql.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 );
TBD

Related Topics
DBMS_SQL
DBMS_SYS_SQL
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved