Error Handling On Insert Statements - Converting SQL Server Syntax to Oracle

This is probably a pretty easy question, but I'm not an Oracle expert and I'm having trouble nailing down the Syntax.

I need to replicate this in Oracle, but thus far I can only seem to get it to work inside a stored procedure.


INSERT BATCH_JOB_INSTANCE ([JOB_NAME],[JOB_KEY],[JOB_INSTANCE_ID],[VERSION])
SELECT 'aa','bb',4183,1876149191
GO
IF @@error <> 0
PRINT 'There was an error inserting data into table BATCH_JOB_INSTANCE, see error message above.'
GO

INSERT BATCH_JOB_INSTANCE ([JOB_NAME],[JOB_KEY],[JOB_INSTANCE_ID],[VERSION])
SELECT 'cc',dd',332,3423
GO
IF @@error <> 0
PRINT 'There was an error inserting data into table BATCH_JOB_INSTANCE, see error message above.'
GO
Jay ReddAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Setup a base table and insert statement:

SQL> create table t1 (x1 char(10), x2 number);

Table created.

SQL> insert into t1 values ('x1','x2');
insert into t1 values ('x1','x2')
                            *
ERROR at line 1:
ORA-01722: invalid number

Open in new window


Write a PL/SQL block to trap an insert statement fail:

SQL> set serveroutput on
SQL> declare
  2     v_code NUMBER;
  3     v_errm VARCHAR2(64);
  4  begin
  5     insert into t1 values ('x1','x2');
  6  exception
  7     when others then
  8           v_code := SQLCODE;
  9           v_errm := SUBSTR(SQLERRM, 1 , 64);
 10           DBMS_OUTPUT.PUT_LINE('The error code is ' || v_code || '- ' || v_errm);
 11  END;
 12  /
The error code is -1722- ORA-01722: invalid number

PL/SQL procedure successfully completed.

SQL>

Open in new window


Part of this code taken from Oracle Documentation:
10g: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqlcode_function.htm
11g: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS849
0
 
Jay ReddAuthor Commented:
That did the trick . Thank you so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.