Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-21
2
Medium Priority
?
595 Views
Last Modified: 2014-01-22
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
0
Comment
Question by:Jay Redd
2 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 39799035
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
 

Author Closing Comment

by:Jay Redd
ID: 39799906
That did the trick . Thank you so much.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question