Solved

Return error and stop execution of procedure when error occurs

Posted on 2011-03-11
15
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 77

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 77

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 77

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
 
LVL 77

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 77

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 77

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 77

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

627 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