Return error and stop execution of procedure when error occurs

I'm afraid we'll need more info.

For starters:  How are you loading the staging table?

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.

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?

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;

Select allOpen in new window

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;

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

Select allOpen in new window

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

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.
                                               

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;

Select allOpen in new window

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.

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

Select allOpen in new window

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.

Thank you works perfects