Tuesday, January 20, 2015

A Difference Between SQL*Plus and SQL Developer

A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911

SQL> @test1
SQL> set echo on
SQL> select 'Comment->' from dual; /*Andrew was here*/
  2  select 'More SQL' from dual;
select 'Comment->' from dual; /*Andrew was here*/
                            *
ERROR at line 1:
ORA-00911: invalid character
 
SQL>

To get the code to work, you need to include the comment before the semi-colon:

SQL> @test2
SQL> set echo on
SQL> select 'Comment->' from dual /*Andrew was here*/;
 
'COMMENT-
---------
Comment->
 
SQL> select 'More SQL' from dual;
 
'MORESQL
--------
More SQL
 
SQL>

However, if you try this in SQL Developer, both options work (as usual, click on the images to enlarge them and bring them into focus):

 
 

Thursday, December 18, 2014

SUCCESS and FAILURE Columns in DBA_STMT_AUDIT_OPTS

I tested this in Oracle 10.2. DBA_STMT_AUDIT_OPTS records auditing options which are currently in effect. If no auditing has been requested, it will be empty:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>
 
You can audit successful connections as follows:
 
SQL> audit create session by system whenever successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will then be recorded in DBA_STMT_AUDIT_OPTS. The SUCCESS column will be set to BY ACCESS to show that only successful connections are being audited:   
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE
---------- --------------- ---------- ----------
SYSTEM     CREATE SESSION  BY ACCESS  NOT SET
 
SQL>
 
You can stop the auditing like this:
 
SQL> noaudit create session by system whenever successful
  2  /
 
Noaudit succeeded.
 
SQL>
 
…and the entry will disappear from DBA_STMT_AUDIT_OPTS:
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>
 
Here is how you audit unsuccessful connections. Note that in the context of the AUDIT and NOAUDIT commands, CREATE SESSION and CONNECT have the same meaning:
 
SQL> audit connect by system whenever not successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will also be recorded in DBA_STMT_AUDIT_OPTS but the FAILURE column will be set to BY ACCESS to show that only failed connections are being audited:   
 
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE
---------- --------------- ---------- ----------
SYSTEM     CREATE SESSION  NOT SET    BY ACCESS
 
SQL>
 
As before, stopping the auditing removes the entry from DBA_STMT_AUDIT_OPTS:
 
SQL> noaudit connect by system whenever not successful
  2  /
 
Noaudit succeeded.
 
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
 
no rows selected
 
SQL>