Solved

Return error and stop execution of procedure when error occurs

Posted on 2011-03-11
15
350 Views
Last Modified: 2012-05-11
Hello,

I'm currenly building a staging area for my inserts and I need a message returned when a failed insert occurs. This is what I'd like to have,

When inserting into TABLE and an error occurs stop the process and return message and data to reference error.

Thank you
0
Comment
Question by:lulubell-b
  • 8
  • 7
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35109472
I'm afraid we'll need more info.

For starters:  How are you loading the staging table?
0
 

Author Comment

by:lulubell-b
ID: 35109685
I'm loading the stage tables using a simple inserts imbedded within a procedure.

I need a code of 1 or 0 to be returned along with the data being error out. Once its error'd please return information to find the record(s) that error'd out.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35109777
Still need more information...

If you are using a procedure, you can trap just about anything you want in the exception handler.

Procedures do not 'return' values.  They have OUT parameters that you can set.

Getting the 'bad' row depends on the code you wrote to load the data.  How are you loading the data?
0
 

Author Comment

by:lulubell-b
ID: 35110018
THis is how Im loading the data
create or replace
PROCEDURE                                                    TEST_PUSH_STG_00
/* NAME: TEST_PUSH_STG_00
   DESCRIPTION: 
*/ 
     (
      PARAM_0 IN NUMBER, -- PERSON THAT CREATED DUMMY RECORD
      PARAM_SYSFLAG IN NUMBER, --NUMBER --THE SYSTEM USAGE FLAG FOR AMC / SDDC/ ETC
      PARAM_1 IN NUMBER
      )
AS
MYPUBVAL NUMBER;
FAILCHK NUMBER;
ORG_USG NUMBER;
BEGIN

INSERT
INTO CDB.TEMP_STG_LM
    (PRJ_ID,
    IELM_ID,
    CNQ_PART_ID,
    PB_ID,
    A_ID,
    REC_CHNG_CD)
SELECT 
PRJ_ID,
    IELM_ID,
    CNQ_PART_ID,
    PB_ID,
    A_ID,
    REC_CHNG_CD
FROM CDB.PRJF_ELM AIE
WHERE AIE.PB_ID = PARAM_1
AND AIE.ORGASC_ID = PARAM_SYSFLAG;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;


END TEST_PUSH_STG_00;

Open in new window

0
 

Author Comment

by:lulubell-b
ID: 35110526
Is this correct?

P_RECORDSET OUT SYS_REFCURSOR

BEGIN


 EXCEPTION WHEN OTHERS THEN
            SQL_CD := SQLCODE;
            myErrm := SQLERRM;
            -- how do I return the subset of data for the err'd records
           
end;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35110664
You are on the right track.

FYI:  The code you originally posted has a PARAM_0 parameter but it isn't used in the code.

To get the record that failed, you will need to change the code a little.  I don't know how to get the failing record with an "insert into select" statement


Check out the small sample below.  It should give you the basics.
drop table tab1 purge;
create table tab1(id number, col1 char(1));

drop table tab2 purge;
create table tab2(col1 char(1) primary key);

insert into tab1 values(1,'a');
insert into tab1 values(2,'b');
insert into tab1 values(3,'a');

create or replace procedure myProc(inparam in char, outErrCode out varchar2, outErrId out number)
is
begin

	for i in (select id,col1 from tab1 where col1 = inparam) loop
		outErrId := i.id;
		insert into tab2 values(i.col1);
	end loop;

	exception
		when others then
			outErrCode := substr(SQLERRM,1,255);
end;
/

show errors


--set up sqlplus variables to test the procedure
var code varchar2(255)
var id number

exec myProc('a',:code,:id);

print code
print id

Open in new window

0
 

Author Comment

by:lulubell-b
ID: 35110990
I need to return a  SYS_REFCURSOR  as an OUT parameter

WHen an error is fired will it stop the process or will it still continue to run thru the rest of the procedure
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 35111015
The question asked is how to return a error code.  This is quickly evolving into an entirely new question.

>>I need to return a  SYS_REFCURSOR  as an OUT parameter

What ref cursor?  You are loading a staging table with the inserts.  What do you want returned?

If you want to return a ref cursor, why populate a staging table at all?

Please explain more about the process.
0
 

Author Comment

by:lulubell-b
ID: 35111070
I'm running these procedures to insert into a staging area for the application. The application handles ref cursors as the OUT Parameter when the Database has a subset of data being returned.

I was wanting to perform something like this

      exception
                                open P_RECORDSET for
            when others then
                  outErrCode := substr(SQLERRM,1,255), outErrId;

When an error occurs, the process stops, returns the error and the id of the record err'd out. These values of the error need to be returned as sys refcursor.
                                               
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35111118
What am I missing here?

Unless I'm completely misunderstanding what you need, do away with the staging table.

On a ref cursor, there is now 'error row' to return.  The cursor either opens or it doesn't.
create or replace
PROCEDURE                                                    TEST_PUSH_STG_00
/* NAME: TEST_PUSH_STG_00
   DESCRIPTION: 
*/ 
     (
      PARAM_SYSFLAG IN NUMBER, --NUMBER --THE SYSTEM USAGE FLAG FOR AMC / SDDC/ ETC
      PARAM_1 IN NUMBER,
	myOutCursor out sys_refcursor
      )
AS
MYPUBVAL NUMBER;
FAILCHK NUMBER;
ORG_USG NUMBER;
BEGIN

open myOutCursor for SELECT 
PRJ_ID,
    IELM_ID,
    CNQ_PART_ID,
    PB_ID,
    A_ID,
    REC_CHNG_CD
FROM CDB.PRJF_ELM AIE
WHERE AIE.PB_ID = PARAM_1
AND AIE.ORGASC_ID = PARAM_SYSFLAG;


END TEST_PUSH_STG_00;

Open in new window

0
 

Author Comment

by:lulubell-b
ID: 35111229
I have three things going on here, The application is bringing a set of data over from the base tables over to the project tables (these tables do not have constraints). Then the application push the data from the PRJ_tables to the STG tables (the stg tables have constraints turned on)

 Prj_tables
 STG_tables
  Base tables

I want to send an error message back to the application with use of a refcursor (ORA ID, the table that error'd and the record id) I figure I can perform this check at the insert level and would prefer to do so.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35111264
>>I want to send an error message back to the application with use of a refcursor

OK, I'll stop trying to change the design and try to answer the questions asked...

See if the below test provides an idea on what you need.  It is based on the small example above.
drop table tab1 purge;
create table tab1(id number, col1 char(1));

drop table tab2 purge;
create table tab2(col1 char(1) primary key);

insert into tab1 values(1,'a');
insert into tab1 values(2,'b');
insert into tab1 values(3,'a');

create or replace procedure myProc(inparam in char, outCursor out sys_refcursor)
is
	error_id number;
	error_code varchar2(255);
begin

	for i in (select id,col1 from tab1 where col1 = inparam) loop
		error_id := i.id;
		insert into tab2 values(i.col1);
	end loop;

	exception
		when others then
			error_code := substr(SQLERRM,1,255);
			open outCursor for select error_id as error_id, error_code as error_code from dual;
end;
/

show errors


--set up sqlplus variables to test the procedure
var myCur refcursor

exec myProc('a',:myCur);

print mycur

Open in new window

0
 

Author Comment

by:lulubell-b
ID: 35111373
great, I think this is what I need. One other question. If I have two inserts in the procedure that have two loop routines. When one errors out will the other be executed or do I have to code an IF statement within the procedure.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35111929
I typically create a 'whereAmI' variable and just keep setting it as I move through the procedure.

create or replace ...
is
   whereAmI varchr2(100);

begin
  whereAmI :='At the start';
...
do some stuff
...
  whereAmI :='Inserting from A';
  insert into ...
...
  whereAmI :='Inserting from B';
  insert into ...

exception
...
   select 'Error at: ' || whereAmI as errorLocation from dual;
0
 

Author Comment

by:lulubell-b
ID: 35234417
Thank you works perfects
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Query Syntax 6 85
null value 15 67
sql for Oracle views 8 38
Oracle -- identify blocking session 24 22
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now