How can I get success / failure messages after executing an instruction?
How can I get messages displayed by Oracle after a query has been executed? For example, if I write
create table t;
displays this message
ORA-00906: missing left parenthesis
However, if I write
create table t(id int);
displays
table created.
I want to receive these messages like
ORA-00906: missing left parenthesis
or
Table Created
Problem Statement: I want the user to enter their request in a text area in a web browser. I am receiving and transmitting this Oracle query and want to display the same message that would be shown to him if he wrote these queries in SQL * Plus instead of the browser.
In simple terms, I want to create a page that mimics SQL * Plus.
I am using ASP.NET 4.5 in C # with ODP.NET and Oracle 10g. From my research, I somehow learned that if successful, Oracle returns some SQLCA code that is managed and mapped to the correct SQL * Plus message.
source to share
If you are using SQL*Plus
, you can catch the error messages in the table sperrorlog
. This new feature was introduced in release 11.1
. You can log an error in this table for the following types of errors -
- ORA errors
- SP2 errors
- PLS errors
I wrote a short article on this, you can look at it for more information. SQL * Plus Error Logging - new version 11.1 .
All you have to do is set errorlogging on
for your session.
This is what the table looks like sperrorlog
when described -
SQL> desc sperrorlog;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT VARCHAR2(1024)
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
Let's see an example -
SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.27.29.000000 AM
SCOTT
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
There is a problem when this function is used rollback
. I have an article about a workaround , SQL * Plus error log - workaround for ROLLBACK problem
source to share
Try the "InfoMessage" event:
http://docs.oracle.com/html/B14164_01/OracleInfoMessageEventArgsClass.htm
// C#
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
class InfoMessageSample
{
public static void WarningHandler(object src, OracleInfoMessageEventArgs args)
{
Console.WriteLine("Source object is: " + src.GetType().Name);
Console.WriteLine("InfoMessageArgs.Message is " + args.Message);
Console.WriteLine("InfoMessageArgs.Source is " + args.Source);
}
static void Main()
{
OracleConnection con = new OracleConnection("User Id=scott;" +
"Password=tiger;Data Source=oracle;");
con.Open();
OracleCommand cmd = con.CreateCommand();
//Register to the InfoMessageHandler
cmd.Connection.InfoMessage += new OracleInfoMessageEventHandler(WarningHandler);
cmd.CommandText =
"create or replace procedure SelectWithNoInto( " +
" empname in VARCHAR2) AS " +
"BEGIN " +
" select * from emp where ename = empname; " +
"END SelectWithNoInto;";
// Execute the statement that produces a warning
cmd.ExecuteNonQuery();
// Clean up
cmd.Dispose();
con.Dispose();
}
}
source to share