Tested on SQLcl 23.2
SQL> connect "O'Tool"@freepdb1
Password? (**********?) ******
Connected.
SQL> alter session set plsql_warnings = "ENABLE:ALL";
Session altered.
SQL> create or replace function "f_silly" return number
2 as begin
3 return null;
4 end;
5* /
Function f_silly compiled
SQL> show errors
No errors.
Really? As I have enabled all warnings, I know that there should be at least one; so let's verify that.
SQL> select line, text
2 from user_errors
3* where type = 'FUNCTION' and name = 'f_silly';
LINE TEXT
_______ _____________________________________________________________________________________
1 PLW-05018: unit f_silly omitted optional AUTHID clause; default value DEFINER used
As expected.
Conclusion: SHOW ERRORS did not work.
Let's try something else:
SQL> show errors function "O'Tool"."f_silly"
SP2-0564: Object ""O'Tool"."f_silly"" is INVALID, it may not be described.
Above, not only SHOW ERRORS failed, but it returned a very phony error message. Of course the “f_silly” function is not invalid; whatever SHOW ERRORS did here was very likely wrong.
…
Tracing the session enables to find out what exactly SQLcl did, or did not, in the above tests.
Right after the CREATE FUNCTION statement, we see SQLcl issuing the following statements. This is as part of SQLcl automatically showing errors, without the user having to issue the SHOW ERRORS command.
PARSING IN CURSOR #140138528637184 len=64 dep=0 uid=174 oct=3 lid=174 tim=172800116161 hv=3514539816 ad='721efd68' sqlid='bgrypd38rr7t8'
select UPPER(sys_context('USERENV', 'CURRENT_SCHEMA')) from dual
END OF STMT
Why UPPER? That doesn't look right. Then:
PARSING IN CURSOR #140138528637184 len=221 dep=0 uid=174 oct=3 lid=174 tim=172800128359 hv=3304962640 ad='70ead7b0' sqlid='fcayjrg2gvfkh'
SELECT LINE, POSITION, REPLACE(TEXT, CHR(10), ' '), attribute
FROM SYS.ALL_ERRORS A
WHERE A.NAME = :NAME AND A.TYPE = :TYPE AND A.OWNER = :OWNER
ORDER BY ATTRIBUTE, LINE, POSITION -- errors first
END OF STMT
...
BINDS #140138528637184:
Bind#0
oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=96 off=0
kxsbbbfp=7f748b53ec58 bln=32 avl=07 flg=05
value="f_silly"
Bind#1
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=32
kxsbbbfp=7f748b53ec78 bln=32 avl=08 flg=01
value="FUNCTION"
Bind#2
oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=64
kxsbbbfp=7f748b53ec98 bln=32 avl=06 flg=01
value="O'TOOL"
As the account name is O'Tool, not O'TOOL, of course the above query will not return any row.
And here's what SHOW ERRORS does.
PARSING IN CURSOR #140138528637184 len=58 dep=0 uid=174 oct=3 lid=174 tim=172806449927 hv=2532680295 ad='69a3aa00' sqlid='gxhphaqbgb8m7'
select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual
END OF STMT
No conversion to uppercase this time. Then:
PARSING IN CURSOR #140138528637184 len=91 dep=0 uid=174 oct=3 lid=174 tim=172806470145 hv=3839513200 ad='72723140' sqlid='f1zqrtgkdnnmh'
select count(1) cnt from all_errors where owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
END OF STMT
And that's it: the trace file does not show any other query ran directly (dep=0) by SQLcl as part of processing the SHOW ERRORS command. (Could that be a consequence of the initial case mismatch?)
And finally, here's what SQLcl does as part of the targeted show errors function "O'Tool"."f_silly" command:
PARSING IN CURSOR #140631050612064 len=58 dep=0 uid=176 oct=3 lid=176 tim=178817503693 hv=2532680295 ad='6567c358' sqlid='gxhphaqbgb8m7'
select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual
END OF STMT
Then:
PARSING IN CURSOR #140631050612064 len=91 dep=0 uid=176 oct=3 lid=176 tim=178817505593 hv=3839513200 ad='6e7739f8' sqlid='f1zqrtgkdnnmh'
select count(1) cnt from all_errors where owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
END OF STMT
Then:
PARSING IN CURSOR #140631050612064 len=64 dep=0 uid=176 oct=3 lid=176 tim=178817508139 hv=3514539816 ad='6b76c018' sqlid='bgrypd38rr7t8'
select UPPER(sys_context('USERENV', 'CURRENT_SCHEMA')) from dual
END OF STMT
UPPER again! Then:
PARSING IN CURSOR #140631050612064 len=522 dep=0 uid=176 oct=3 lid=176 tim=178817520372 hv=1404558552 ad='6b8ff350' sqlid='5m0t5rt9vgr6s'
select object_type,owner,object_name,rank from ( select object_type,owner,object_name,0 rank from all_objects where object_name = :NAME and UPPER(owner) = nvl(:OWNER,:SCHEMA_CONTEXT) and object_type not in ( 'SYNONYM' ) ) where ((:BODY_WHERE='Y') OR (object_type not in ('PACKAGE BODY'))) AND rownum < 100 order by rank, CASE object_type WHEN 'TABLE' THEN 0 WHEN 'VIEW' THEN 1 WHEN 'INDEX' THEN 2 WHEN 'PACKAGE' THEN 3 WHEN 'PACKAGE BODY' THEN 4 ELSE 5 END
END OF STMT
...
BINDS #140631050612064:
Bind#0
oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=128 off=0
kxsbbbfp=7fe737e849e0 bln=32 avl=07 flg=05
value="f_silly"
Bind#1
oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=32
kxsbbbfp=7fe737e84a00 bln=32 avl=06 flg=01
value="O'Tool"
Bind#2
oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=64
kxsbbbfp=7fe737e84a20 bln=32 avl=06 flg=01
value="O'TOOL"
Bind#3
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=96
kxsbbbfp=7fe737e84a40 bln=32 avl=01 flg=01
value="Y"
Of course the above query will not return any row. The condition on UPPER(owner) doesn't look good, and it turns out that it is not.
And finally:
PARSING IN CURSOR #140631050612064 len=122 dep=0 uid=176 oct=47 lid=176 tim=178817612896 hv=933271490 ad='65c72070' sqlid='6d5rmdcvu15y2'
declare x varchar2(1000); s varchar2(1000); begin s:=DBMS_ASSERT.SCHEMA_NAME(:a);x:=DBMS_ASSERT.SIMPLE_SQL_NAME(:x); end;
END OF STMT
...
BINDS #140631050612064:
Bind#0
oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=32767 off=0
kxsbbbfp=7fe7379a7ac0 bln=32767 avl=08 flg=05
value=""O'Tool""
Bind#1
oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=32767 off=0
kxsbbbfp=7fe73799fa70 bln=32767 avl=09 flg=05
value=""f_silly""
If you look carefully at the above bind values, you'll notice that both are enclosed within double quotes. Unfortunately, this is not how DBMS_ASSERT.SCHEMA_NAME works:
SQL> select dbms_assert.schema_name('NONEXISTENTSCHEMA') as schema_name from dual;
Error starting at line : 1 in command -
select dbms_assert.schema_name('NONEXISTENTSCHEMA') as schema_name from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 390
44001. 0000 - "invalid schema"
*Document: Yes
*Cause: The schema for the given schema name did not exist.
*Action: Provide a valid schema name.
SQL> select dbms_assert.schema_name('O''Tool') as schema_name from dual;
SCHEMA_NAME
______________
O'Tool
SQL> select dbms_assert.schema_name('"O''Tool"') as schema_name from dual;
Error starting at line : 1 in command -
select dbms_assert.schema_name('"O''Tool"') as schema_name from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 390
44001. 0000 - "invalid schema"
*Document: Yes
*Cause: The schema for the given schema name did not exist.
*Action: Provide a valid schema name.
Room for improvement…
Best regards,