Skip to Main Content
  • Questions

  • Invalid identifier while merge using dynamic sql - static merge statement suceeds

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joyan.

Asked: October 19, 2016 - 8:42 pm UTC

Last updated: November 04, 2016 - 3:51 am UTC

Version: 11.0

Viewed 1000+ times

You Asked

Hi,

I am facing a weird problem in which a merge statement using dynamic sql is failing with the below error while the static merge statement is suceeding.

Error:
ORA-00904: : invalid identifier
ORA-06512: at line 60

Below is the logic I am trying to accomplish. The inner query in the USING clause is much more complex than shown here. I have provided this simplified example to convey my point.

Code Snippet:
create table src_tab (seq_id number, delim_col varchar2(200));

create table dest_tab (seq_id number, date_col date, num_col1 number, num_col2 number, num_col3 number);

insert into src_tab values (1, '10-OCT-16~247071961109~247071936109~10024707193122');

insert into dest_tab values (2, '10-OCT-16', 247071961109, 247071936109, 10024707193122);

This is working:
merge into dest_tab dest
using
(select seq_id,
REGEXP_SUBSTR(a.delim_col, '[^~]+',1,1) AS date_col,
REGEXP_SUBSTR(a.delim_col, '[^~]+',1,2) AS num_col1,
REGEXP_SUBSTR(a.delim_col, '[^~]+',1,3) AS num_col2,
REGEXP_SUBSTR(a.delim_col, '[^~]+',1,4) AS num_col3
from src_tab a where seq_id = 1
) src_tab
on (dest.date_col = src.date_col
and dest.num_col1 = src.num_col1
and dest.num_col2 = src.num_col2
and dest.num_col3 = src.num_col3)
when matched then update set dest.seq_id = src.seq_id
where dest.date_col = src.date_col
and dest.num_col1 = src.num_col1
and dest.num_col2 = src.num_col2
and dest.num_col3 = src.num_col3;

This is not working:
declare
l_seq_id number := 1;
l_sql clob;
begin
l_sql := ' merge into dest_tab dest
using
(select seq_id,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,1) AS date_col,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,2) AS num_col1,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,3) AS num_col2,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,4) AS num_col3
from src_tab a where seq_id = :1
) src_tab
on (dest.date_col = src.date_col
and dest.num_col1 = src.num_col1
and dest.num_col2 = src.num_col2
and dest.num_col3 = src.num_col3)
when matched then update set dest.seq_id = src.seq_id
where dest.date_col = src.date_col
and dest.num_col1 = src.num_col1
and dest.num_col2 = src.num_col2
and dest.num_col3 = src.num_col3';

execute immediate l_sql using l_seq_id;

for x in (select plan_table_output from table (dbms_xplan.display_cursor))
loop
dbms_output.put_line ( x.PLAN_TABLE_OUTPUT );
end loop;

end;

Few important points
In the explain plan I see few things.

"DEST"."DATE_COL"=INTERNAL_FUNCTION("from$subquery$_018"."DATE_COL") AND
"DEST"."NUM_COL1"=TO_NUMBER("from$subquery$_018"."NUM_COL1") AND
"DEST"."NUM_COL2"=TO_NUMBER("from$subquery$_018"."NUM_COL2") AND
"DEST"."NUM_COL3"=TO_NUMBER("from$subquery$_018"."NUM_COL3") AND

and Connor said...

I beg to differ with your assessment :-)


SQL> drop table src_tab purge;

Table dropped.

SQL> drop table dest_tab purge;

Table dropped.

SQL> create table src_tab (seq_id number, delim_col varchar2(200));

Table created.

SQL> create table dest_tab (seq_id number, date_col date, num_col1 number, num_col2 number, num_col3 number);

Table created.

SQL> insert into src_tab values (1, '10-OCT-16~247071961109~247071936109~10024707193122');

1 row created.

SQL> insert into dest_tab values (2, '10-OCT-16', 247071961109, 247071936109, 10024707193122);

1 row created.

SQL>
SQL>
SQL> merge into dest_tab dest
  2  using
  3  (select seq_id,
  4  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,1) AS date_col,
  5  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,2) AS num_col1,
  6  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,3) AS num_col2,
  7  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,4) AS num_col3
  8  from src_tab a where seq_id = 1
  9  ) src_tab
 10  on (dest.date_col = src.date_col
 11  and dest.num_col1 = src.num_col1
 12  and dest.num_col2 = src.num_col2
 13  and dest.num_col3 = src.num_col3)
 14  when matched then update set dest.seq_id = src.seq_id
 15  where dest.date_col = src.date_col
 16  and dest.num_col1 = src.num_col1
 17  and dest.num_col2 = src.num_col2
 18  and dest.num_col3 = src.num_col3;
and dest.num_col3 = src.num_col3)
                    *
ERROR at line 13:
ORA-00904: "SRC"."NUM_COL3": invalid identifier


SQL>
SQL>
SQL> declare
  2  l_seq_id number := 1;
  3  l_sql clob;
  4  begin
  5  l_sql := ' merge into dest_tab dest
  6  using
  7  (select seq_id,
  8  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,1) AS date_col,
  9  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,2) AS num_col1,
 10  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,3) AS num_col2,
 11  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,4) AS num_col3
 12  from src_tab a where seq_id = :1
 13  ) src_tab
 14  on (dest.date_col = src.date_col
 15  and dest.num_col1 = src.num_col1
 16  and dest.num_col2 = src.num_col2
 17  and dest.num_col3 = src.num_col3)
 18  when matched then update set dest.seq_id = src.seq_id
 19  where dest.date_col = src.date_col
 20  and dest.num_col1 = src.num_col1
 21  and dest.num_col2 = src.num_col2
 22  and dest.num_col3 = src.num_col3';
 23
 24  execute immediate l_sql using l_seq_id;
 25
 26  end;
 27  /
declare
*
ERROR at line 1:
ORA-00904: "SRC"."NUM_COL3": invalid identifier
ORA-06512: at line 24


Both dont work because of a syntax error. If I alter the merge (ie, correct the alias), then now they both work

SQL>
SQL>
SQL> merge into dest_tab dest
  2  using
  3  (select seq_id,
  4  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,1) AS date_col,
  5  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,2) AS num_col1,
  6  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,3) AS num_col2,
  7  REGEXP_SUBSTR(a.delim_col, '[^~]+',1,4) AS num_col3
  8  from src_tab a where seq_id = 1
  9  ) src
 10  on (dest.date_col = src.date_col
 11  and dest.num_col1 = src.num_col1
 12  and dest.num_col2 = src.num_col2
 13  and dest.num_col3 = src.num_col3)
 14  when matched then update set dest.seq_id = src.seq_id
 15  where dest.date_col = src.date_col
 16  and dest.num_col1 = src.num_col1
 17  and dest.num_col2 = src.num_col2
 18  and dest.num_col3 = src.num_col3;

1 row merged.

SQL>
SQL>
SQL> declare
  2  l_seq_id number := 1;
  3  l_sql clob;
  4  begin
  5  l_sql := ' merge into dest_tab dest
  6  using
  7  (select seq_id,
  8  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,1) AS date_col,
  9  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,2) AS num_col1,
 10  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,3) AS num_col2,
 11  REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,4) AS num_col3
 12  from src_tab a where seq_id = :1
 13  ) src
 14  on (dest.date_col = src.date_col
 15  and dest.num_col1 = src.num_col1
 16  and dest.num_col2 = src.num_col2
 17  and dest.num_col3 = src.num_col3)
 18  when matched then update set dest.seq_id = src.seq_id
 19  where dest.date_col = src.date_col
 20  and dest.num_col1 = src.num_col1
 21  and dest.num_col2 = src.num_col2
 22  and dest.num_col3 = src.num_col3';
 23
 24  execute immediate l_sql using l_seq_id;
 25
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL>
SQL>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joyan Sil, November 02, 2016 - 7:40 pm UTC

Hi,

Firstly, I apologize for giving an incorrect code snippet. This all happened because I cannot copy code from my office laptop and hence wrote it without running it even once.

So here is the problem.
Below is not the exact replica of the code but it gives an idea. This code is running in multiple environments and it is throwing Invalid identifier error in only 1 environment.

declare
l_seq_id number := 1;
l_sql clob;
l_rec rec_type := rec_type(null, null, null);
l_type := l_type();
begin

--Code to populate l_type
.
.
.
--Code to populate l_type

l_sql := ' merge into DESTADM.dest_tab dest
using
(select seq_id,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,1) AS date_col,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,2) AS num_col1,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,3) AS num_col2,
REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,4) AS num_col3
from SRCADM.src_tab a, (SELECT * FROM TABLE(:1)) b
where b.date_col = '30-OCT-2016' and b.num_col1 = 12345 and b.num_col3 = REGEXP_SUBSTR(a.delim_col, ''[^~]+'',1,4)
) src
on (dest.date_col = src.date_col
and dest.num_col1 = src.num_col1
and dest.num_col2 = src.num_col2
and dest.num_col3 = src.num_col3)
when matched then update set dest.seq_id = src.seq_id
where dest.date_col = src.date_col
and dest.num_col1 = src.num_col1
and dest.num_col2 = src.num_col2
and dest.num_col3 = src.num_col3';

execute immediate l_sql using l_type;

end;
/

Few important points:

1. DESTADM.dest_tab dest is a partitioned table, range partitioned on the date_col

2. We are passing a collection type as a bind variable

3. After generating trace in the particular environment we saw error in the following query:

PARSE ERROR#139662607386784:len.....err=904
SELECT distinct TBL$OR$IDX$PART$NUM(....) FROM (SELECT "from$_subquery$_018".......) ORDER BY 1

It appears that we are having issue while partition pruning.

4. We were able to workaround the problem by the following ways
i). By setting
alter session set "_subquery_pruning_enabled"=false

ii). By using partition name to merge to a specific partition
merge into DESTADM.dest_tab PARTITION (P_2011) dest...

I would want to know if this is a known bug in Oracle and if the root cause for this.

Thanks
Joyan
Connor McDonald
November 04, 2016 - 3:51 am UTC

If doing this:

alter session set "_subquery_pruning_enabled"=false


works around your problem, then yes, I'd say you have found a bug. Time to chat to Support.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here