This was an interesting question.
Firstly why not just look at the clear text PL/SQL? - well in this example case it was a commercial application he was asked to install into a database and the PL/SQL was wrapped and he could not see the code. He did of course ask the vendor and in the end got an answer that some other scripts that created the tables needed to be run first and problem solved.
So assuming that you do not have access to the clear text PL/SQL what can you do?
I want to create a simple example. First connect to my 23c database as SYS and create a sample user PETE1:
[oracle@localhost ~]$ sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 09:58:52 2024
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> create user pete1 identified by pete1;
User created.
SQL> grant create session to pete1;
Grant succeeded.
SQL> grant unlimited tablespace to pete1;
Grant succeeded.
SQL> grant create procedure to pete1;
Grant succeeded.
SQL>
Connect to my sample user PETE1 and test whether I can access SYS.USER$. Obviously I know the answer in advance that the error is my PL/SQL cannot access SYS.USER$ but I need to be sure there is an error first simply in SQL*Plus:
SQL> connect pete1/pete1@//192.168.56.18:1521/freepdb1
Connected.
SQL> select name from sys.user$;
select name from sys.user$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
Now create the PL/SQL and in it access SYS.USER$ - the table that is missing, inaccessible.
SQL> get test.sql
1 -- test PL/SQL for wrapped create
2 create or replace procedure test as
3 lv_password varchar2(4000);
4 begin
5 select password
6 into lv_password
7 from sys.user$
8 where name='SYS';
9* end;
10 .
Now wrap the PL/SQL:
[oracle@localhost ~]$ wrap iname=test.sql oname=test.plb
PL/SQL Wrapper: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:07:41 2024
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Processing test.sql to test.plb
[oracle@localhost ~]$
Show the code to prove it is wrapped:
[oracle@localhost ~]$ cat test.plb
create or replace procedure test wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
81 be
LlzHv6ZvxN4mhihv9rosYR6UAmEwg5nnm7+fMr2ywFwWoWLRzLh0iwbSvW0ouHTLy/4owMzn
x3TAM7h0ZSV8f3x8UKCLwMAy/tKGBnSfgVIyy8yp1YYG0tKZCOpnJfqVKW9duwovdojYH6uK
qxoVlYi57p6glXKzsT0rpBbYSNdD/MHg16ama3fKHg==
/
[oracle@localhost ~]$
Now let us try and install this code into my database:
SQL> @test.plb
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
6/11 PL/SQL: ORA-00942: table or view does not exist
SQL>
OK, so we know there is a ORA-00942 error but we do not know what table causes it so that we can fix it as the code is wrapped and looking at lines 4 and 6 is meaningless in this context
How to know what table or view does not exist?
Try the ALL_ERRORS view
SQL> set serveroutput on
SQL> @sc_print 'select * from all_errors'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from all_errors','''','''''');
Executing Query [select * from all_errors]
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
SEQUENCE : 1
LINE : 6
POSITION : 11
TEXT : PL/SQL: ORA-00942: table or view does not exist
ATTRIBUTE : ERROR
MESSAGE_NUMBER : 0
-------------------------------------------
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
SEQUENCE : 2
LINE : 4
POSITION : 2
TEXT : PL/SQL: SQL Statement ignored
ATTRIBUTE : ERROR
MESSAGE_NUMBER : 0
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
This is not useful as it doesn't tell me what table does not exist and is a repeat of the error we saw trying to compile the wrapped code in SQL*Pus.
We can try dependencies instead:
SQL> @sc_print 'select * from all_dependencies where name=''''TEST'''' and referenced_type=''''TABLE'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from all_dependencies where name=''TEST'' and referenced_type=''TABLE''','''','''''');
Executing Query [select * from all_dependencies where name='TEST' and
referenced_type='TABLE']
OWNER : PETE1
NAME : TEST
TYPE : PROCEDURE
REFERENCED_OWNER : SYS
REFERENCED_NAME : USER$
REFERENCED_TYPE : TABLE
REFERENCED_LINK_NAME :
DEPENDENCY_TYPE : HARD
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Yes, the issue is there in the dependencies view and shows that it is indeed SYS.USER$ but if there were hundreds of tables then its harder to locate the actual one. Test if we can access it as PETE1 in SQL*Plus:
SQL> desc sys.user$
ERROR:
ORA-04043: Object sys.user$ does not exist.
SQL>
Reconnect as SYS and grant ALTER SESSION to PETE1 to allow the use of trace:
C:\_audit_scripts\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 18 10:22:48 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> grant alter session to pete1;
Grant succeeded.
SQL>
Set trace and install the PLB again:
[oracle@localhost ~]$ sqlplus pete1/pete1@//192.168.56.18:1521/freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Mar 18 10:23:02 2024
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Mon Mar 18 2024 10:22:32 +00:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> @test.plb
Warning: Procedure created with compilation errors.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL>
locate the trace file:
SQL> sho parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /opt/oracle
diagnostics_control string IGNORE
SQL>
Look for the potential trace file:
[oracle@localhost oracle]$ find . -name "*.trc" -print 2>/dev/null | xargs ls -al 2>/dev/null | grep "Mar 18"
-rw-r-----. 1 oracle oinstall 2243134 Mar 18 10:30 ./diag/rdbms/free/FREE/trace/FREE_dbrm_3163.trc
-rw-r-----. 1 oracle oinstall 101670 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr0_184977.trc
-rw-r-----. 1 oracle oinstall 905271 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr1_179252.trc
-rw-r-----. 1 oracle oinstall 42302 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr2_185301.trc
-rw-r-----. 1 oracle oinstall 71822 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr3_185112.trc
-rw-r-----. 1 oracle oinstall 41974 Mar 18 10:33 ./diag/rdbms/free/FREE/trace/FREE_gcr4_185336.trc
-rw-r-----. 1 oracle oinstall 1151 Mar 18 09:57 ./diag/rdbms/free/FREE/trace/FREE_j001_183579.trc
-rw-r-----. 1 oracle oinstall 1150 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_j003_184635.trc
-rw-r-----. 1 oracle oinstall 1453 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j005_184641.trc
-rw-r-----. 1 oracle oinstall 1449 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_j006_184644.trc
-rw-r-----. 1 oracle oinstall 943553 Mar 18 09:55 ./diag/rdbms/free/FREE/trace/FREE_lmhb_3199_data.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_183247.trc
-rw-r-----. 1 oracle oinstall 72908 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m000_184934.trc
-rw-r-----. 1 oracle oinstall 35341 Mar 18 10:20 ./diag/rdbms/free/FREE/trace/FREE_m001_184937.trc
-rw-r--r--. 1 oracle oinstall 31938 Mar 18 10:09 ./diag/rdbms/free/FREE/trace/FREE_m002_184940.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m004_184027.trc
-rw-r--r--. 1 oracle oinstall 31969 Mar 18 09:56 ./diag/rdbms/free/FREE/trace/FREE_m005_180811.trc
-rw-r-----. 1 oracle oinstall 1744 Mar 18 10:22 ./diag/rdbms/free/FREE/trace/FREE_ora_185518.trc
-rw-r-----. 1 oracle oinstall 537629 Mar 18 10:24 ./diag/rdbms/free/FREE/trace/FREE_ora_185538.trc
[oracle@localhost oracle]$
Looks like the last ones are a potential match so grep now for the PL/SQL procedure:
[oracle@localhost trace]$ grep -i test *.trc
FREE_m000_178093.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1608) into (partition WRH$_AWR_TEST_1_1405253007_1608, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_178093.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_178093.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1656) into (partition WRH$_AWR_TEST_1_1405253007_1656, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m000_184934.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m000_184934.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc: SQLSTR={alter table WRH$_AWR_TEST_1 split partition WRH$_AWR_TEST_1_1405253007_MXSN at (1405253007,1632) into (partition WRH$_AWR_TEST_1_1405253007_1632, partition WRH$_AWR_TEST_1_1405253007_MXSN tablespace SYSAUX) update indexes}
FREE_m001_179164.trc:Error=12954 prepare storage for table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_m001_179164.trc:kewcpspr: Skip split due repair needed table=WRH$_AWR_TEST_1, dbid=1405253007
FREE_ora_185538.trc:create or replace procedure test wrapped
FREE_ora_185538.trc: value="procedure test wrapped
FREE_ora_185538.trc: value="TEST"
[oracle@localhost trace]$
Nothing useful is found in the trace. We can see the ORA-00942 and the insert into error$ but not the SQL that causes the error in the trace file.
There are other things we can do; the first is talk to the vendor and ask why their wrapped code fails to compile or ask the vendor about the line number in the code and what table is missing. We can use simple ideas such as dependencies to try and locate the missing table or permissions when we try and compile wrapped code where we don't have access to the clear text code.
Also remember the 9i wrap.exe and lower was the front end to a PL/SQL compiler, the 10g and higher is a simple obfuscation of the clear text code.
#oracleace #sym_42 #oracle #database #security #plsql #compile #permissions #error #942 #23c
