Home » SQL & PL/SQL » SQL & PL/SQL » Interesting problem with query (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
| Interesting problem with query [message #646651] |
Wed, 06 January 2016 06:48  |
 |
javon13
Messages: 17
Registered: October 2015
|
Junior Member |
|
|
Hello,
I am facing interesting problem with quite complex sql query insert .. select together with log errors.
I am doing something like this:
insert into table_1 (
col1,
col2,
col3,
col4,
col5)
select
orig_table1.orig_col1 col1,
case
when orig_table2.orig_val1 like 'AA.%'
then
NULL
when orig_table2.orig_val2 like 'BB.%'
then
NULL
end
col2,
case
when orig_table2.orig_val3 like 'CC.%'
then
NULL
when orig_table2.orig_val4 like 'DD.%'
then
'SOME_VALUE'
end
col3,
NULL col4,
orig_table2.orig_col2 col5
from
orig_table1
inner join orig_table2
on orig_table1.id = orig_table2.id
where
some other conditions
log errors
reject limit unlimited
;
The original query is much longer and more complex but I chose most of the operations which are present in original query.
I am getting this error:
SQL Error: ORA-38908: internal error occurred during DML Error Logging
ORA-24328 illegal attribute value
ORA-12899: value too large for column string (actual: string, maximum: string)
38908. 00000 - "internal error occurred during DML Error Logging"
*Cause: An unexpected error occurred while executing recursive SQL
to insert a row into the DML Error Logging table.
*Action: Report this error to Oracle Support.
Analyzing it step by step I found potential problem. It is the case condition where NULL is being returned in every condition (I know, it looks strange but it is prepared for the future development). Once I put to_char(NULL) at least around one NULL in when condition it started to work as expected - data are inserted into table and bad rows are inserted into error log table.
BUT!! So I wanted to solve all NULL appearances by putting to_char, to_date, to_number around it and my query started produce the same errors again.
I am little bit confused. Do anyone of you have clue, what is going on?
Thanks a lot in advance.
[Updated on: Wed, 06 January 2016 07:03] Report message to a moderator |
|
|
|
| Re: Interesting problem with query [message #646653 is a reply to message #646651] |
Wed, 06 January 2016 07:34  |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
CASE statements can return multiple datatypes. Which one is used for a given case is determined by the datatype of the first THEN value.
Null can also have multiple datatypes so it doesn't know which to use.
This:
case
when orig_table2.orig_val1 like 'AA.%'
then
NULL
when orig_table2.orig_val2 like 'BB.%'
then
NULL
end
col2,
Is equivalent to this:
Past that you need to show us the actual query, or a simplified version that still throws the same error
|
|
|
|
| Re: Interesting problem with query [message #646655 is a reply to message #646653] |
Wed, 06 January 2016 08:22 |
|
javon13
Messages: 17
Registered: October 2015
|
Junior Member |
|
|
OK, thanks. In the meantime, I prepared mockup version and checked that behavior is the same like it is in production:
create table TABLE_1 (
id number(10) PRIMARY KEY,
col1 varchar2(8),
col2 varchar2(8),
col3 varchar2(8),
col4 varchar2(8),
col5 varchar2(8)
);
exec DBMS_ERRLOG.CREATE_ERROR_LOG (dml_table_name => 'TABLE_1');
create table ORIG_TABLE_1 (
id number(10) PRIMARY KEY,
orig_col1 varchar2(10),
orig_col2 varchar2(10),
orig_col3 varchar2(10),
orig_col4 varchar2(10),
orig_col5 varchar2(10)
);
create table ORIG_TABLE_2 (
id number(10) PRIMARY KEY,
orig_col1 varchar2(10),
orig_col2 varchar2(10),
orig_val1 varchar2(10),
orig_val2 varchar2(10),
orig_val3 varchar2(10),
orig_val4 varchar2(10),
orig_val5 varchar2(10)
);
insert all
into ORIG_TABLE_1
values (1,'John','Smith','34 Tree Rd','Pine','CA')
into ORIG_TABLE_1
values (2,'James','Jones','1 Atl Blvd','Ocean','NC')
into ORIG_TABLE_1
values (3,'Paul','Taylor','10 Dog St','Kingwood','NJ')
into ORIG_TABLE_1
values (4,'Patrick','Brown','45 Forrest','City','MT')
into ORIG_TABLE_2
values (1, 'Tall', 'Grey', 'AA.123', 'BD.123', 'CD.123', 'DB.123', 'EE.123')
into ORIG_TABLE_2
values (2, 'Thin', 'Green', 'AB.123', 'BA.123', 'CA.123', 'DC.123', 'EA.123')
into ORIG_TABLE_2
values (3, 'Small', 'Blue', 'AC.123', 'BB.123', 'CB.123', 'DD.123', 'EB.123')
into ORIG_TABLE_2
values (4, 'Big', 'Yellow', 'AD.123', 'BC.123', 'CC.123', 'DA.123', 'EC.123')
select * from dual;
Now insert .. select statements:
This one returns errors:
insert into TABLE_1 (
id,
col1,
col2,
col3,
col4,
col5
)
select
1 id,
ORIG_TABLE_1.orig_col1 col1,
case
when ORIG_TABLE_2.orig_val1 like 'AA.%'
then
NULL
when ORIG_TABLE_2.orig_val2 like 'BB.%'
then
NULL
end
col2,
case
when ORIG_TABLE_2.orig_val3 like 'CC.%'
then
NULL
when ORIG_TABLE_2.orig_val4 like 'DD.%'
then
'SOME VALUE'
end
col3,
NULL col4,
ORIG_TABLE_1.orig_col3
from ORIG_TABLE_1
inner join ORIG_TABLE_2
on ORIG_TABLE_1.id = ORIG_TABLE_2.id
where ORIG_TABLE_1.id<10
and ORIG_TABLE_2.id>0
log errors
reject limit unlimited
;
This one returns errors as well (NULL is retyped everywhere):
insert into TABLE_1 (
id,
col1,
col2,
col3,
col4,
col5
)
select
1 id,
ORIG_TABLE_1.orig_col1 col1,
case
when ORIG_TABLE_2.orig_val1 like 'AA.%'
then
to_char(NULL)
when ORIG_TABLE_2.orig_val2 like 'BB.%'
then
to_char(NULL)
end
col2,
case
when ORIG_TABLE_2.orig_val3 like 'CC.%'
then
to_char(NULL)
when ORIG_TABLE_2.orig_val4 like 'DD.%'
then
'SOME VALUE'
end
col3,
to_char(NULL) col4,
ORIG_TABLE_1.orig_col3
from ORIG_TABLE_1
inner join ORIG_TABLE_2
on ORIG_TABLE_1.id = ORIG_TABLE_2.id
where ORIG_TABLE_1.id<10
and ORIG_TABLE_2.id>0
log errors
reject limit unlimited
;
And finally this one is ok. It inserts no data into table_1 because of the length but inserts data into error table what is desired:
insert into TABLE_1 (
id,
col1,
col2,
col3,
col4,
col5
)
select
1 id,
ORIG_TABLE_1.orig_col1 col1,
case
when ORIG_TABLE_2.orig_val1 like 'AA.%'
then
NULL
when ORIG_TABLE_2.orig_val2 like 'BB.%'
then
to_char(NULL)
end
col2,
case
when ORIG_TABLE_2.orig_val3 like 'CC.%'
then
NULL
when ORIG_TABLE_2.orig_val4 like 'DD.%'
then
'SOME VALUE'
end
col3,
NULL col4,
ORIG_TABLE_1.orig_col3
from ORIG_TABLE_1
inner join ORIG_TABLE_2
on ORIG_TABLE_1.id = ORIG_TABLE_2.id
where ORIG_TABLE_1.id<10
and ORIG_TABLE_2.id>0
log errors
reject limit unlimited
;
I understand that NULL is special so it is good to use cast, to_char etc. but I don't really understand why it return ORA-38908 when all NULLs are cured using to_char(NULL).
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Aug 31 04:40:44 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
