Hi Tom
I have a stored proc that dumps the content of view into a table. The stored proc simply uses "INSERT INTO - SELECT *" FROM statement to do its job.
However, on one bad day, we had some data corrupted that resulted in ORA-01427. This Stored Proc, that I am talking about, is invoked via a batch process which somehow, is really important to my client. I understand, that data corruption is very bad and must not be overlooked. But, is there any way I can skip past ORA-01427 and mark the corrupted data as "the corrupted data" in some log file?
The code:
create or replace PROCEDURE "SP_CS_LT" (v_switch_cl IN VARCHAR2, v_switch_cs IN VARCHAR2, v_msg OUT VARCHAR2)
IS
v_tbl_name varchar2(200);
Begin
IF v_switch_cl = 'Y' then
v_tbl_name := 'CS_LST';
Execute Immediate 'truncate table CS_LST';
Execute Immediate 'insert into CS_LST (select distinct * from CS_LST_VW)';
END IF;
If V_Switch_Cs = 'Y' Then
v_tbl_name := 'CS_SRCH';
Execute Immediate 'truncate table CS_SRCH';
Execute Immediate 'INSERT INTO CS_SRCH (SELECT DISTINCT * FROM CS_SRCH_VW)';
END IF;
v_msg := 'SUCCESS';
commit;
exception
when others then
v_msg := 'FAILURE: table - '|| v_tbl_name ||' '|| SQLERRM;
END SP_CS_LT;
The problem is in your view's query. Somewhere in it you have a scalar subquery that returns more than one row. If you can figure out which column of the view is throwing this exception you could exclude it from the load.
For example, t2 is a child of t. Row 1 in t has 1 row in t2. But row 2 in t matches two rows in t2:
create table t as
select rownum x from dual connect by level <= 2;
create table t2 as
select x, rownum y
from t, lateral (select * from dual connect by level <= x);
select * from t;
X
----------
1
2
select * from t2;
X Y
---------- ----------
1 1
2 2
2 3So if you try to get the matching t2 value for each row of t as a subquery in the select, you'll hit the error:
select x, ( select y from t2 where t2.x = t.x) y from t;
ORA-01427: single-row subquery returns more than one row
Figuring out why this is happening is a combination of inspecting the view for these subqueries along with some trial and error.
Once you've identified the offending column(s), you can write code that traps the exception and replaces them with null/magic value/"MISSING DATA" as appropriate:
create or replace view v as
select x, ( select y from t2 where t2.x = t.x) y from t;
create table t3 as
select * from v where 1=2;
declare
single_row_ex exception;
pragma exception_init (single_row_ex, -01427);
begin
insert into t3
select * from v;
exception
when single_row_ex then
insert into t3
select x, null y from v;
end;
/
select * from t3;
X Y
---------- ----------
1
2Of course, the better solution is to fix the view so this doesn't happen! The correct solution depends on your data and requirements. Options include:
- Taking the min/max value from the subquery:
create or replace view v as
select x, ( select min(y) from t2 where t2.x = t.x) y from t;
select * from v;
X Y
---------- ----------
1 1
2 2- Moving the subquery to be a join:
create or replace view v as
select t.x, y
from t
join t2
on t2.x = t.x;
select * from v;
X Y
---------- ----------
1 1
2 2
2 3You'll need to speak with the application users to figure what the correct approach is here.