I think this is the section then:
-- determine if dbms_java and dbms_debug are installed
qry_str := 'select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,
sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg
from sys.all_objects
where owner = ''SYS''
and object_type = ''PACKAGE''
and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
qry_str := check_for_dba(qry_str);
execute immediate qry_str into has_dbms_java, has_dbms_debug;
Full log:
----------------------------------
-- Timestamp: 14:26:14.489
Connect: USER@DATABASE1
----------------------------------
-- Session: USER@DATABASE1
-- Timestamp: 14:26:14.902
SELECT version, product, sysdate, null FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE '%ORACLE%';
----------------------------------
-- Session: USER@DATABASE1
-- Timestamp: 14:26:14.909
Declare
Toad_Security_Exists number;
debug_connect_session number;
alter_any_table number;
alter_session number;
alter_system number;
alter_database number;
alter_tablespace number;
alter_user number;
create_table number;
create_role number;
create_trigger number;
create_sequence number;
administer_database_trigger number;
administer_resource_manager number;
create_any_index number;
create_any_sequence number;
create_any_table number;
create_any_trigger number;
debug_any_procedure number;
drop_any_sequence number;
execute_any_procedure number;
select_any_table number;
delete_any_table number;
insert_any_table number;
grant_any_object_priv number;
grant_any_role number;
grant_any_privilege number;
select_any_dictionary number;
update_any_table number;
create_public_syn number;
has_dba_synonyms number;
has_dba_objects number;
select_catalog_role number;
db_charset nls_database_parameters.value%type;
utplsql_ver VARCHAR2 (60);
utplsql_own sys.all_objects.owner%TYPE;
e_inherit_privs EXCEPTION;
PRAGMA EXCEPTION_INIT(e_inherit_privs, -6598);
compatible_version varchar2(60);
cant_access_vparameter integer;
sid number;
qry_str varchar2(4000);
test varchar2(60);
has_dbms_java number;
has_dbms_debug number;
local_trans_id varchar2(60);
qu_version_owner user_objects.object_name%type;
CTRepositoryVersionMajor number;
CTRepositoryVersionMinor number;
CTRepositoryVersionRevision number;
CTRepositoryVersionBuild number;
Is_RAC number;
instance_id number;
instance_name varchar2(100);
server_name varchar2(100);
OS_USERNAME varchar2(60);
radix varchar2(1);
dbid varchar2(30);
dbname varchar2(9);
authentication_type varchar2(30);
IsExadata number;
AmazonRDSPackage user_objects.object_name%type;
OraDBType number;
ContainerID number;
ContainerName varchar2(128);
MaxStringSize varchar2(4000);
AppRoot varchar2(3);
AppPDB varchar2(3);
max_ident_name_len number;
ShardLink user_db_links.db_link%type;
ShardID NUMBER;
ShardDirector varchar2(256);
--
-- swap out ALL_ views for DBA_ views if possible
function check_for_dba(in_str in varchar2) return varchar2 is
result varchar2(4000);
begin
result := in_str;
if has_dba_objects > 0 then
result := REPLACE (result, 'all_objects', 'dba_objects');
end if;
if has_dba_synonyms > 0 then
result := REPLACE (result, 'all_synonyms', 'dba_synonyms');
end if;
return result;
end;
Begin
-- Put Toad data into v$session columns
Begin
SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, :IN_ID);
Exception
When others then null; -- we can live without this.
End;
--
-- Retrieve specific system privileges
select
sum(decode(Privilege, 'ADMINISTER DATABASE TRIGGER', 1, 0)) administer_database_trigger,
sum(decode(Privilege, 'ADMINISTER RESOURCE MANAGER', 1, 0)) administer_resource_manager,
sum(decode(Privilege, 'ALTER ANY TABLE', 1, 0)) alter_any_table,
sum(decode(Privilege, 'ALTER DATABASE', 1, 0)) alter_database,
sum(decode(Privilege, 'ALTER TABLESPACE', 1, 0)) alter_tablespace,
sum(decode(Privilege, 'ALTER SESSION', 1, 0)) alter_session,
sum(decode(Privilege, 'ALTER SYSTEM', 1, 0)) alter_system,
sum(decode(Privilege, 'ALTER USER', 1, 0)) alter_user,
sum(decode(Privilege, 'CREATE ANY INDEX', 1, 0)) create_any_ind,
sum(decode(Privilege, 'CREATE ANY SEQUENCE', 1, 0)) create_any_seq,
sum(decode(Privilege, 'CREATE ANY TABLE', 1, 0)) create_any_table,
sum(decode(Privilege, 'CREATE ANY TRIGGER', 1, 0)) create_any_trig,
sum(decode(Privilege, 'CREATE PUBLIC SYNONYM', 1, 0)) create_public_syn,
sum(decode(Privilege, 'CREATE ROLE', 1, 0)) create_role,
sum(decode(Privilege, 'CREATE SEQUENCE', 1, 0)) create_sequence,
sum(decode(Privilege, 'CREATE TABLE', 1, 0)) create_table,
sum(decode(Privilege, 'CREATE TRIGGER', 1, 0)) create_trigger,
sum(decode(Privilege, 'DEBUG ANY PROCEDURE', 1, 0)) debug_any_proc,
sum(decode(Privilege, 'DEBUG CONNECT SESSION', 1, 0)) debug_connect_session,
sum(decode(Privilege, 'DROP ANY SEQUENCE', 1, 0)) drop_any_seq,
sum(decode(Privilege, 'EXECUTE ANY PROCEDURE', 1, 0)) execute_any_procedure,
sum(decode(Privilege, 'GRANT ANY OBJECT PRIVILEGE', 1, 0)) grant_any_object_priv,
sum(decode(Privilege, 'GRANT ANY ROLE', 1, 0)) grant_any_role,
sum(decode(Privilege, 'GRANT ANY PRIVILEGE', 1, 0)) grant_any_privilege,
sum(decode(Privilege, 'SELECT ANY DICTIONARY', 1, 0)) select_any_dictionary,
sum(decode(Privilege, 'SELECT ANY TABLE', 1, 0)) select_any_table,
sum(decode(Privilege, 'DELETE ANY TABLE', 1, 0)) delete_any_table,
sum(decode(Privilege, 'INSERT ANY TABLE', 1, 0)) insert_table,
sum(decode(Privilege, 'UPDATE ANY TABLE', 1, 0)) update_any_table
into administer_database_trigger, administer_resource_manager, alter_any_table, alter_database, alter_tablespace,
alter_session, alter_system, alter_user, create_any_index, create_any_sequence, create_any_table,
create_any_trigger, create_public_syn, create_role, create_sequence, create_table, create_trigger,
debug_any_procedure, debug_connect_session, drop_any_sequence, execute_any_procedure, grant_any_object_priv,
grant_any_role, grant_any_privilege, select_any_dictionary, select_any_table, delete_any_table,
insert_any_table, update_any_table
from sys.session_privs
where privilege in ('DEBUG CONNECT SESSION',
'ALTER ANY TABLE',
'ALTER SESSION',
'ALTER SYSTEM',
'ALTER DATABASE',
'ALTER TABLESPACE',
'ALTER USER',
'ADMINISTER DATABASE TRIGGER',
'ADMINISTER RESOURCE MANAGER',
'CREATE TABLE',
'CREATE ROLE',
'CREATE TRIGGER',
'CREATE SEQUENCE',
'EXECUTE ANY PROCEDURE',
'SELECT ANY TABLE',
'DELETE ANY TABLE',
'INSERT ANY TABLE',
'UPDATE ANY TABLE',
'GRANT ANY OBJECT PRIVILEGE',
'GRANT ANY ROLE',
'GRANT ANY PRIVILEGE',
'SELECT ANY DICTIONARY',
'CREATE ANY INDEX',
'CREATE ANY SEQUENCE',
'CREATE ANY TABLE',
'CREATE ANY TRIGGER',
'DEBUG ANY PROCEDURE',
'DROP ANY SEQUENCE',
'CREATE PUBLIC SYNONYM');
--
-- Check for SELECT_CATALOG_ROLE. It must be a default role to appear in the result of this query.
Select sum(decode(role, 'SELECT_CATALOG_ROLE', 1, 0)) select_catalog_role
into select_catalog_role
from sys.session_roles
where role = 'SELECT_CATALOG_ROLE';
--
-- determine database character set
select value into db_charset
from sys.nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
--
-- check for other ways dba_objects and dba_synonyms could be granted
if (select_any_dictionary = 1) or (select_catalog_role = 1) then
has_dba_synonyms := 1;
has_dba_objects := 1;
else
--
-- just try to select from dba_synonyms and dba_objects as a privilege check
begin
qry_str := 'select 1 from DBA_SYNONYMS where rownum = 1';
execute immediate qry_str into has_dba_synonyms;
exception
when others then
has_dba_synonyms := 0;
end;
begin
qry_str := 'select 1 from DBA_OBJECTS where rownum = 1';
execute immediate qry_str into has_dba_objects;
exception
when others then
has_dba_objects := 0;
end;
end if;
--
-- Is Toad Security in effect?
begin
qry_str := 'Select 1
from toad.toad_restrictions
where (user_name = USER or user_name in ( select ROLE from sys.session_roles))
and rownum = 1';
execute immediate qry_str into Toad_Security_Exists;
exception
when others then
Toad_Security_Exists := 0;
end;
--
-- retrieve OS Username
begin
qry_str := 'select SYS_CONTEXT(''USERENV'',''OS_USER'') from dual';
execute immediate qry_str into OS_USERNAME;
exception
when others then
OS_USERNAME := null;
end;
--
-- retrieve SID if possible
begin
qry_str := 'select SYS_CONTEXT(''USERENV'',''SID'') from dual';
execute immediate qry_str into sid;
exception
when others then
sid := -1;
end;
--
-- get compatible version
begin
cant_access_vparameter := 0;
qry_str := 'select value from v$parameter where name = ''compatible''';
execute immediate qry_str into compatible_version;
exception
when others then
compatible_version := null;
cant_access_vparameter := 1;
end;
--
-- check for privileges on sys.dbms_transaction
begin
qry_str := 'select sys.dbms_transaction.local_transaction_id from dual';
execute immediate qry_str into local_trans_id;
exception
when others then
local_trans_id := '<error>';
end;
--
-- determine if dbms_java and dbms_debug are installed
qry_str := 'select sum(decode(object_name, ''DBMS_JAVA'', 1, 0)) jv,
sum(decode(object_name, ''DBMS_DEBUG'', 1, 0)) dbg
from sys.all_objects
where owner = ''SYS''
and object_type = ''PACKAGE''
and object_name in (''DBMS_JAVA'', ''DBMS_DEBUG'')';
qry_str := check_for_dba(qry_str);
execute immediate qry_str into has_dbms_java, has_dbms_debug;
--
-- determine if code tester repository is installed
begin
Select user into qu_version_owner
FROM sys.user_objects
WHERE object_name = 'QU_VERSION'
AND object_type in ('PACKAGE', 'SYNONYM')
AND rownum = 1;
exception
when no_data_found then
qu_version_owner := null;
end;
-- if not found then look in other schemas
if qu_version_owner is null then
begin
qry_str := 'select owner
from sys.all_objects
where object_name = ''QU_VERSION''
and object_type in (''PACKAGE'',''SYNONYM'')
and rownum = 1';
qry_str := check_for_dba(qry_str);
execute immediate qry_str into qu_version_owner;
exception
when others then
qu_version_owner := null;
end;
end if;
--
-- if it is installed, determine code tester version
CTRepositoryVersionMajor := -1;
CTRepositoryVersionMinor := -1;
CTRepositoryVersionRevision := -1;
CTRepositoryVersionBuild := -1;
if qu_version_owner is not null then
begin
qry_str := 'select QU_VERSION.major_number,
QU_VERSION.minor_number,
QU_VERSION.revision_number,
QU_VERSION.build_number
from dual';
execute immediate qry_str into CTRepositoryVersionMajor, CTRepositoryVersionMinor, CTRepositoryVersionRevision, CTRepositoryVersionBuild;
exception
when others then
null;
end;
end if;
--
-- determine if this is a RAC instance
qry_str := 'begin if dbms_utility.is_cluster_database';
qry_str := qry_str || ' then :isRAC := 1; else :isRAC := 0; end if; end;';
begin
execute immediate qry_str using out is_RAC;
exception
when others then
is_RAC := 0;
end;
--
-- determine instance id
begin
execute immediate 'select dbms_utility.current_instance from dual' into instance_id;
exception
when others then
instance_id := 1;
end;
--
-- determine instance name and server name if possible
qry_str := 'select sys_context(''USERENV'',''INSTANCE_NAME''),
sys_context(''USERENV'',''SERVER_HOST'')
from dual';
begin
execute immediate qry_str into instance_name, server_name;
exception
-- if we don't have privs on v$instance and not rac, then try to get db name
when others then
if is_RAC = 0 then
begin
qry_str := 'select sys_context(''USERENV'',''DB_NAME'') from dual';
execute immediate qry_str into instance_name;
exception
when others then
instance_name := null;
end;
end if;
end;
--
-- get timestamp radix
begin
qry_str := 'select to_char(systimestamp, ''X'' ) from dual';
execute immediate qry_str into radix;
exception
when others then
radix := '.';
end;
--
-- determine DBID, DBName if possible
begin
qry_str := 'select dbid, name from v$database';
execute immediate qry_str into dbid, dbname;
exception
when others then
null;
end;
--
-- get authentication type
begin
qry_str := 'Select SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') from dual';
execute immediate qry_str into authentication_type;
exception
when others then
authentication_type := null;
end;
--
-- Is this an Amazon RDS Instance?
begin
qry_str := 'select object_name
from sys.all_procedures
where owner = ''RDSADMIN''
and object_name = ''RDSADMIN_UTIL''
and object_type = ''PACKAGE''
and subprogram_id = 0';
execute immediate qry_str into AmazonRDSPackage;
exception
when others then
AmazonRDSPackage := null;
end;
--
-- Is this an Exadata Instance?
begin
qry_str := 'select 1 from v$cell where rownum = 1';
execute immediate qry_str into IsExadata;
exception
when others then
IsExaData := 0;
end;
--
-- get max string size
begin
qry_str := 'select value from v$parameter where name = ''max_string_size''';
execute immediate qry_str into MaxStringSize;
exception
when others then
MaxStringSize := null;
end;
--
-- What kind of database is this? (pluggable, container, normal)
begin
qry_str := 'select TO_NUMBER(sys_context(''USERENV'',''CON_ID'')),
sys_context(''USERENV'',''CON_NAME''),
Case when sys_context(''USERENV'',''CON_NAME'') = ''CDB$ROOT'' then 1 -- container,
when sys_context(''USERENV'',''CDB_NAME'') is null then 0 -- normal,
else 2 -- pluggable
end db_type
from dual';
execute immediate qry_str into ContainerID, ContainerName, OraDBType;
if OraDBType=0 then
ContainerName := null;
end if;
exception
when others then
ContainerID := -1;
ContainerName := null;
OraDBType := 0;
end;
if OraDBType=2 then
--
-- If this is a pluggable db, then check for application container or application pluggable.
-- If user does not have the SELECT privilege on V$CONTAINERS, then we could be connected to
-- and application PDB (or root) and not know it.
begin
qry_str := 'select application_root as app_root,
application_pdb as app_pdb
from v$containers
where name = sys_context(''USERENV'', ''CON_NAME'')';
execute immediate qry_str into AppRoot, AppPDB;
if AppRoot = 'YES' then
OraDBType := 3;
elsif AppPDB = 'YES' then
OraDBType := 4;
end if;
exception
when others then
null;
end;
end if;
begin
execute immediate 'declare nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn NUMBER; begin null; end;';
max_ident_name_len := 128;
exception
when others then
max_ident_name_len := 30;
end;
--
-- Get Shard Link
begin
qry_str := 'select db_link from all_db_links where shard_internal = ''YES'' and db_link like ''ORA%@ORA_MULTI_TARGET''';
execute immediate qry_str into ShardLink;
exception
when others then
ShardLink := null;
end;
--
-- Get Shard ID
begin
qry_str := 'select ORA_SHARD_ID from dual';
execute immediate qry_str into ShardID;
exception
when others then
ShardID := null;
end;
--
-- Get Shard Director
begin
qry_str := 'SELECT HOSTNAME FROM GSMADMIN_INTERNAL.GSM';
execute immediate qry_str into ShardDirector;
exception
when others then
ShardDirector := null;
end;
--
-- determine if utPLSQL is installed
utplsql_ver := NULL;
BEGIN
-- Fetch version using the public API
EXECUTE IMMEDIATE 'SELECT SUBSTR (ut.version (), 1, 60) FROM DUAL'
INTO utplsql_ver;
EXCEPTION
-- Attempt direct access on ORA-06598
WHEN e_inherit_privs THEN
BEGIN
qry_str := 'SELECT do.owner
FROM sys.all_objects do
JOIN sys.all_synonyms ds
ON (do.owner = ds.table_owner)
AND (do.object_name = ds.table_name)
WHERE ds.owner = ''PUBLIC''
AND ds.table_name = ''UT''
AND ds.synonym_name = ''UT''
AND do.object_type = ''PACKAGE''';
qry_str := check_for_dba(qry_str);
EXECUTE IMMEDIATE qry_str INTO utplsql_own;
qry_str := 'begin :utplsql_ver := ' || utplsql_own || '.ut_utils.gc_version; end;';
EXECUTE IMMEDIATE qry_str USING OUT utplsql_ver;
EXCEPTION
WHEN OTHERS THEN
utplsql_ver := SQLCODE;
END;
WHEN OTHERS THEN
utplsql_ver := SQLCODE;
END;
--
-- Done with SQL. Assign results to output variables
:OUT_DEBUG_CONNECT_SESSION := debug_connect_session;
:OUT_ALTER_ANY_TABLE := alter_any_table;
:OUT_ALTER_SESSION := alter_session;
:OUT_ALTER_SYSTEM := alter_system;
:OUT_ALTER_DATABASE := alter_database;
:OUT_ALTER_TABLESPACE := alter_tablespace;
:OUT_ALTER_USER := alter_user;
:OUT_CREATE_TABLE := create_table;
:OUT_CREATE_ROLE := create_role;
:OUT_CREATE_TRIGGER := create_trigger;
:OUT_CREATE_SEQUENCE := create_sequence;
:OUT_ADMIN_DB_TRIGGER := administer_database_trigger;
:OUT_ADMIN_RS_MAN := administer_resource_manager;
:OUT_EXECUTE_ANY_PROCEDURE := execute_any_procedure;
:OUT_CREATE_ANY_INDEX := create_any_index;
:OUT_CREATE_ANY_SEQUENCE := create_any_sequence;
:OUT_CREATE_ANY_TABLE := create_any_table;
:OUT_CREATE_ANY_TRIGGER := create_any_trigger;
:OUT_DROP_ANY_SEQUENCE := drop_any_sequence;
:OUT_DEBUG_ANY_PROCEDURE := debug_any_procedure;
:OUT_SELECT_ANY_TABLE := select_any_table;
:OUT_DELETE_ANY_TABLE := delete_any_table;
:OUT_INSERT_ANY_TABLE := insert_any_table;
:OUT_UPDATE_ANY_TABLE := update_any_table;
:OUT_GRANT_ANY_OBJECT_PRIV := grant_any_object_priv;
:OUT_GRANT_ANY_ROLE := grant_any_role;
:OUT_GRANT_ANY_PRIVILEGE := grant_any_privilege;
:OUT_SELECT_ANY_DICTIONARY := select_any_dictionary;
:OUT_CREATE_PUBLIC_SYN := create_public_syn;
:OUT_SELECT_CATALOG_ROLE := select_catalog_role;
:OUT_DB_CHARSET := db_charset;
:OUT_USER := user;
:OUT_OSUSERNAME := OS_USERNAME;
:OUT_COMPATIBLE_VERSION := compatible_version;
:OUT_CANT_ACCESS_PARAMETER := cant_access_vparameter;
:OUT_SID := sid;
:OUT_TOAD_SEC_EXISTS := Toad_Security_Exists;
:OUT_UTPLSQL_VER := utplsql_ver;
:OUT_HAS_DBMS_JAVA := has_dbms_java;
:OUT_HAS_DBMS_DEBUG := has_dbms_debug;
:OUT_LOCAL_TRANS_ID := local_trans_id;
:OUT_QU_VERSION_OWNER := qu_version_owner;
:OUT_QU_VERSION_MAJOR := CTRepositoryVersionMajor;
:OUT_QU_VERSION_MINOR := CTRepositoryVersionMinor;
:OUT_QU_VERSION_REVISION := CTRepositoryVersionRevision;
:OUT_QU_VERSION_BUILD := CTRepositoryVersionBuild;
:OUT_IS_RAC := Is_RAC;
:OUT_INSTANCE_ID := instance_id;
:OUT_INSTANCE_NAME := instance_name;
:OUT_SERVER_NAME := server_name;
:OUT_DBID := dbid;
:OUT_DBName := dbname;
:OUT_RADIX := radix;
:OUT_AUTHENTICATION_TYPE := authentication_type;
:OUT_IS_EXADATA := IsExadata;
:OUT_IS_AMAZON_RDS := AmazonRDSPackage;
:OUT_DB_TYPE := OraDBType;
:OUT_CONTAINER_ID := ContainerID;
:OUT_CONTAINER_NAME := ContainerName;
:OUT_MAX_STRING_SIZE := MaxStringSize;
:OUT_MAX_IDENT_NAME_LEN := max_ident_name_len;
:OUT_SHARD_LINK := ShardLink;
:OUT_SHARD_ID := ShardID;
:OUT_SHARD_DIRECTOR := ShardDirector;
end;
-- ORA-00942: table or view does not exist
ORA-06512: at line 259
:IN_MDL(VARCHAR[17],IN)='TOAD 16.0.90.1509'
:IN_ID(VARCHAR[28],IN)='63648016,286781904,605745280'
:OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=<NULL>
:OUT_ALTER_ANY_TABLE(INTEGER,OUT)=<NULL>
:OUT_ALTER_SESSION(INTEGER,OUT)=<NULL>
:OUT_ALTER_SYSTEM(INTEGER,OUT)=<NULL>
:OUT_ALTER_DATABASE(INTEGER,OUT)=<NULL>
:OUT_ALTER_TABLESPACE(INTEGER,OUT)=<NULL>
:OUT_ALTER_USER(INTEGER,OUT)=<NULL>
:OUT_CREATE_TABLE(INTEGER,OUT)=<NULL>
:OUT_CREATE_ROLE(INTEGER,OUT)=<NULL>
:OUT_CREATE_TRIGGER(INTEGER,OUT)=<NULL>
:OUT_CREATE_SEQUENCE(INTEGER,OUT)=<NULL>
:OUT_ADMIN_DB_TRIGGER(INTEGER,OUT)=<NULL>
:OUT_ADMIN_RS_MAN(INTEGER,OUT)=<NULL>
:OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=<NULL>
:OUT_CREATE_ANY_INDEX(INTEGER,OUT)=<NULL>
:OUT_CREATE_ANY_SEQUENCE(INTEGER,OUT)=<NULL>
:OUT_CREATE_ANY_TABLE(INTEGER,OUT)=<NULL>
:OUT_CREATE_ANY_TRIGGER(INTEGER,OUT)=<NULL>
:OUT_DROP_ANY_SEQUENCE(INTEGER,OUT)=<NULL>
:OUT_DEBUG_ANY_PROCEDURE(INTEGER,OUT)=<NULL>
:OUT_SELECT_ANY_TABLE(INTEGER,OUT)=<NULL>
:OUT_DELETE_ANY_TABLE(INTEGER,OUT)=<NULL>
:OUT_INSERT_ANY_TABLE(INTEGER,OUT)=<NULL>
:OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=<NULL>
:OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=<NULL>
:OUT_GRANT_ANY_ROLE(INTEGER,OUT)=<NULL>
:OUT_GRANT_ANY_PRIVILEGE(INTEGER,OUT)=<NULL>
:OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=<NULL>
:OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=<NULL>
:OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)=<NULL>
:OUT_DB_CHARSET(VARCHAR[0],OUT)=<NULL>
:OUT_USER(VARCHAR[0],OUT)=<NULL>
:OUT_OSUSERNAME(VARCHAR[0],OUT)=<NULL>
:OUT_COMPATIBLE_VERSION(VARCHAR[0],OUT)=<NULL>
:OUT_CANT_ACCESS_PARAMETER(INTEGER,OUT)=<NULL>
:OUT_SID(VARCHAR[0],OUT)=<NULL>
:OUT_TOAD_SEC_EXISTS(INTEGER,OUT)=<NULL>
:OUT_UTPLSQL_VER(VARCHAR[0],OUT)=<NULL>
:OUT_HAS_DBMS_JAVA(INTEGER,OUT)=<NULL>
:OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=<NULL>
:OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)=<NULL>
:OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)=<NULL>
:OUT_QU_VERSION_MAJOR(INTEGER,OUT)=<NULL>
:OUT_QU_VERSION_MINOR(INTEGER,OUT)=<NULL>
:OUT_QU_VERSION_REVISION(INTEGER,OUT)=<NULL>
:OUT_QU_VERSION_BUILD(INTEGER,OUT)=<NULL>
:OUT_IS_RAC(INTEGER,OUT)=<NULL>
:OUT_INSTANCE_ID(INTEGER,OUT)=<NULL>
:OUT_INSTANCE_NAME(VARCHAR[0],OUT)=<NULL>
:OUT_SERVER_NAME(VARCHAR[0],OUT)=<NULL>
:OUT_DBID(VARCHAR[0],OUT)=<NULL>
:OUT_DBName(VARCHAR[0],OUT)=<NULL>
:OUT_RADIX(VARCHAR[0],OUT)=<NULL>
:OUT_AUTHENTICATION_TYPE(VARCHAR[0],OUT)=<NULL>
:OUT_IS_EXADATA(INTEGER,OUT)=<NULL>
:OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)=<NULL>
:OUT_DB_TYPE(INTEGER,OUT)=<NULL>
:OUT_CONTAINER_ID(INTEGER,OUT)=<NULL>
:OUT_CONTAINER_NAME(VARCHAR[0],OUT)=<NULL>
:OUT_MAX_STRING_SIZE(VARCHAR[0],OUT)=<NULL>
:OUT_MAX_IDENT_NAME_LEN(INTEGER,OUT)=<NULL>
:OUT_SHARD_LINK(VARCHAR[0],OUT)=<NULL>
:OUT_SHARD_ID(INTEGER,OUT)=<NULL>
:OUT_SHARD_DIRECTOR(VARCHAR[0],OUT)=<NULL>
----------------------------------
-- Session: USER@DATABASE1
-- Timestamp: 14:26:23.980
DECLARE
TYPE tc_syn_type IS VARRAY (4) OF VARCHAR2 (30);
syns CONSTANT tc_syn_type := tc_syn_type ('QUEST_TEAM_CODING', 'DELL_COM_TEAM_CODING', 'QUEST_COM_TEAM_CODING', 'SQLNAV4_VCS_CONFIG');
local_result INTEGER;
local_temp INTEGER;
BEGIN
local_result := 0;
-- Look for Team Coding synonym
FOR i IN syns.FIRST .. syns.LAST
LOOP
BEGIN
BEGIN
-- Query by the synonym name to see if the underlying table exists
EXECUTE IMMEDIATE 'SELECT 1 FROM ' || syns(i)
INTO local_temp;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
-- If successful, set the output value
local_result := 1;
EXIT;
EXCEPTION
WHEN OTHERS THEN
NULL; -- we still want to check other values.
END;
END LOOP;
-- Populate out var
:out_res := local_result;
END;
:out_res(INTEGER,OUT)=0
----------------------------------
-- Session: USER@DATABASE1
-- Timestamp: 14:26:24.871
select banner from v$version;
----------------------------------
-- Session: USER@DATABASE1
-- Timestamp: 14:26:26.147
DECLARE
TYPE table_names IS VARRAY (3) OF VARCHAR2 (30);
currval_not_defined EXCEPTION;
PRAGMA EXCEPTION_INIT (currval_not_defined, -8002);
sys_prof_valid NUMBER;
toad_prof_valid NUMBER;
rst NUMBER;
sys_pack VARCHAR2 (17);
prof_seq VARCHAR2 (24);
tables table_names;
dbms_prof_tables CONSTANT table_names
:= table_names ('plsql_profiler_data',
'plsql_profiler_runs',
'plsql_profiler_units') ;
dbms_hprof_tables CONSTANT table_names
:= table_names ('dbmshp_runs',
'dbmshp_function_info',
'dbmshp_parent_child_info') ;
/*---------------------------------------------------------------------------
-- Verify that the specified package exists
---------------------------------------------------------------------------*/
FUNCTION CheckPackage (ObjectName IN VARCHAR2)
RETURN NUMBER
AS
valid NUMBER;
err_msg VARCHAR2 (2000);
BEGIN
valid := 1;
BEGIN
EXECUTE IMMEDIATE 'BEGIN ' || ObjectName || '."19014109005938850971308441679"; END;';
EXCEPTION
WHEN OTHERS
THEN
err_msg := SQLERRM;
-- If error is for missing package member then we are OK
IF INSTR (err_msg, 'PLS-00302') > 0
THEN
valid := 1;
ELSE
valid := 0;
END IF;
END;
RETURN valid;
END;
BEGIN
IF :check_hierarchical = 0
THEN
tables := dbms_prof_tables;
sys_pack := 'sys.dbms_profiler';
prof_seq := 'plsql_profiler_runnumber';
ELSE
tables := dbms_hprof_tables;
sys_pack := 'sys.dbms_hprof';
prof_seq := 'dbmshp_runnumber';
END IF;
-- Check for the SYS.DBMS_PROFILER package
sys_prof_valid := CheckPackage (sys_pack);
-- If it exists, check for the other profiler objects
IF sys_prof_valid = 1
THEN
toad_prof_valid := 1;
BEGIN
-- Check for all tables
FOR i IN tables.FIRST .. tables.LAST
LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT 1 FROM ' || tables (i) || ' WHERE 0 = 1'
INTO rst;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
toad_prof_valid := 1;
WHEN OTHERS
THEN
RAISE;
END;
END LOOP;
-- Check for the sequence
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || prof_seq || '.currval FROM DUAL';
EXCEPTION
WHEN currval_not_defined
THEN
toad_prof_valid := 1;
WHEN OTHERS
THEN
RAISE;
END;
-- Check for the TOAD_PROFILER package
IF :check_hierarchical <> 1
THEN
toad_prof_valid := CheckPackage ('toad_profiler');
END IF;
EXCEPTION
WHEN OTHERS
THEN
toad_prof_valid := 0;
END;
ELSE
toad_prof_valid := 0;
END IF;
:out_sys_prof_valid := sys_prof_valid;
:out_toad_prof_valid := toad_prof_valid;
END;
:check_hierarchical(INTEGER,IN)=0
:out_sys_prof_valid(INTEGER,OUT)=<NULL>
:out_toad_prof_valid(INTEGER,OUT)=<NULL>
----------------------------------
-- Session: USER@DATABASE1
-- Timestamp: 14:26:27.274
select object_name nam, Decode(object_type, 'TABLE', 1, 'VIEW', 2, 3) typ
from sys.user_objects
where object_type in ('TABLE','PROCEDURE', 'PACKAGE', 'FUNCTION')
order by nam asc;
That sql runs with no error:
JV DBG
0 1
