Oracle SQL_Error .
SQL> INSERT INTO EMP6(EMPNO,ENAME,SAL) VALUES(1234,'MIHIR',3000);
INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(1234,'MIHIR',3000)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."JOB")
Hello expert
I have thousands of record to perform DML operation . Some constraints are declare over there.
I dont want to terminate the DML due to some constraints as above popup error message in runtime .
How can i handel this error and saved them into log table using only SQL .
Your suggestion will be appreciation .
Thanks in advance .
INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(1234,'MIHIR',3000)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."JOB")
Hello expert
I have thousands of record to perform DML operation . Some constraints are declare over there.
I dont want to terminate the DML due to some constraints as above popup error message in runtime .
How can i handel this error and saved them into log table using only SQL .
Your suggestion will be appreciation .
Thanks in advance .
WHENEVER SQLERROR CONTINUE
is the default behavior of SQL*Plus. At least it appears to be the default in the installation that I have. The command is really there to undo the effects of WHENEVER SQLERROR EXIT. It does not suppress the error message and it will not log the error row into a table.
SET TERMOUT OFF
would suppress the error messages if running through a script through SQL*Plus. If you enter the commands interactively, you would still see the error messages.
As far as I am aware, there isn't a way using a single SQL statement, that would do what you are asking to do. You could easily do it with a PL/SQL block and some error handling code, but I don't know of a way to do it with a single SQL statement. Especially if you want to log errors to a table.
is the default behavior of SQL*Plus. At least it appears to be the default in the installation that I have. The command is really there to undo the effects of WHENEVER SQLERROR EXIT. It does not suppress the error message and it will not log the error row into a table.
SET TERMOUT OFF
would suppress the error messages if running through a script through SQL*Plus. If you enter the commands interactively, you would still see the error messages.
As far as I am aware, there isn't a way using a single SQL statement, that would do what you are asking to do. You could easily do it with a PL/SQL block and some error handling code, but I don't know of a way to do it with a single SQL statement. Especially if you want to log errors to a table.
"How can I handle this error and save them into log table using only SQL?"
That may be impossible.
You need to look at the bigger picture, and give us some more information including:
1. Where do these SQL statements come from?
2. Do you have any control over how these get generated?
3. Can you use SQL*Loader or a PL\SQL procedure instead of these SQL statements?
4. Is this a one-time job, or is this intended to be an ongoing interface?
That may be impossible.
You need to look at the bigger picture, and give us some more information including:
1. Where do these SQL statements come from?
2. Do you have any control over how these get generated?
3. Can you use SQL*Loader or a PL\SQL procedure instead of these SQL statements?
4. Is this a one-time job, or is this intended to be an ongoing interface?
From older versions, the syntax allows for error handling as follows:
INSERT INTO EMP6
(EMPNO,ENAME,SAL) VALUES(1234,'MIHIR',3000)
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;
INSERT INTO EMP6
(EMPNO,ENAME,SAL) VALUES(1234,'MIHIR',3000)
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thnks a lot David and johnsone . Its such a cool feature .
Is it recommend to use this feature inside the procedure ?
Is it recommend to use this feature inside the procedure ?
What do you mean inside a procedure? Your original question states:
How can i handel this error and saved them into log table using only SQL .If you are going to use a procedure, which would negate your using only SQL comment, then I would do it differently. It would work that way, but personally, I would do it differently. You have more control when using your own error handling.
whenever sqlerror continue
You may also need this (I assume you are using SQL*Plus), not sure:
set termout off
»bp