1

My database has many kinds of clients and sometimes they use a wrong SQL string. but those clients were written in different languages such as C++, C, Java, .Net it's not possible that I learn all of them.

When a error happened ORA-00942 for example, how can I know what the SQL text is by just using Oracle or some Oracle utils if I don't know how to print the SQL text in client?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
user1021531
  • 487
  • 1
  • 7
  • 16

2 Answers2

1

AFAIK the "only" option is to trace the whole server. You have to create a special type of the trigger AFTER SERVERERROR ON DATABASE.

See: http://www.red-database-security.com/scripts/oracle_error_trigger.html

There are probably other ways of doing that, but they are too low-level. Like: OCI tracing, JDBC tracing or some alter system set events ...

Summary: a session trace will NOT contain a statement which failed parsing.

More advanced topics:

  • JDBC driver support standard logging
  • OCI drivers support "interceptor" library. You have to set some environment variable and let it point onto our own .dll library. OCI driver will load this library and will call callbacks from this lib on various events. It is described in OCI Programming reference and also AFAIK you can find some sample interceptor library on SF
  • .Net tracing is documented here
ibre5041
  • 4,903
  • 1
  • 20
  • 35
0

Yes it is very much possible. It is a new feature in SQL*Plus. Read SQL*Plus error logging – New feature release 11.1 to know more about the feature in depth.

NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.

The SPERRORLOG table looks like this:

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

For example,

SQL> set errorlogging on
SQL>
SQL> SELECT * FROM dula;
SELECT * FROM dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> SELECT USERNAME, TIMESTAMP, MESSAGE, STATEMENT FROM sperrorlog;

USERNAME   TIMESTAMP                      MESSAGE                                            STATEMENT
---------- ------------------------------ -------------------------------------------------- ------------------
LALIT      10-MAR-15 04.08.13.000000 PM   ORA-00942: table or view does not exist            SELECT * FROM dula

SQL>

So, the statement which had an error is now logged in the SPERRORLOG table.

EDIT

My database has many kinds of clients and sometimes they use a wrong SQL string. but those clients were written in different languages such as C++, C, Java, .Net

The above mentioned solution is only applicable for the scripts executed via SQL*Plus. If the queries are executed through the application client, then to find the complete information you could trace the entire session that the application is using to interact with the database.

You could have a look at this article for an example on how to generate a trace file.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    And how does this work for clients using C, C++, Java or .Net? –  Mar 10 '15 at 10:46
  • @a_horse_with_no_name, Ah! I completely missed that. SQL*Plus error logging has nothing to with application client side error logging. I should edit my answer to tell this explicitly. – Lalit Kumar B Mar 10 '15 at 10:58