create table hdr ( a int, b varchar2(30), c varchar2(30) )Table created.
create table dtl (a int, b int, c varchar2(30), d varchar2(30))Table created.
insert into hdr values (1, 'HEADER1', 'HEADER2')1 row(s) inserted.
insert into dtl values (1, 1, 'DETAIL11', 'DETAIL12')1 row(s) inserted.
insert into dtl values (1, 2, 'DETAIL21', 'DETAIL22')1 row(s) inserted.
create table hdr ( a int, b varchar2(30), c varchar2(30) )ORA-00955: name is already used by an existing objectMore Details: https://docs.oracle.com/error-help/db/ora-00955
create table dtl (a int, b int, c varchar2(30), d varchar2(30))ORA-00955: name is already used by an existing objectMore Details: https://docs.oracle.com/error-help/db/ora-00955
insert into hdr values (1, 'HEADER1', 'HEADER2')1 row(s) inserted.
insert into dtl values (1, 1, 'DETAIL11', 'DETAIL12')1 row(s) inserted.
insert into dtl values (1, 2, 'DETAIL21', 'DETAIL22')1 row(s) inserted.
create table hdr ( a int, b varchar2(30), c varchar2(30) )ORA-00955: name is already used by an existing objectMore Details: https://docs.oracle.com/error-help/db/ora-00955
create table dtl (a int, b int, c varchar2(30), d varchar2(30))ORA-00955: name is already used by an existing objectMore Details: https://docs.oracle.com/error-help/db/ora-00955
insert into hdr values (1, 'HEADER1', 'HEADER2')1 row(s) inserted.
insert into dtl values (1, 1, 'DETAIL11', 'DETAIL12')1 row(s) inserted.
insert into dtl values (1, 2, 'DETAIL21', 'DETAIL22')1 row(s) inserted.
drop table hdrTable dropped.
drop table dtlTable dropped.
create table hdr ( a int, b varchar2(30), c varchar2(30) )Table created.
create table dtl (a int, b int, c varchar2(30), d varchar2(30))Table created.
insert into hdr values (1, 'HEADER1', 'HEADER2')1 row(s) inserted.
insert into dtl values (1, 1, 'DETAIL11', 'DETAIL12')1 row(s) inserted.
insert into dtl values (1, 2, 'DETAIL21', 'DETAIL22')1 row(s) inserted.
select * from hdr| A | B | C | 1 | HEADER1 | HEADER2 |
|---|
select * from dtl| A | B | C | D | 1 | 1 | DETAIL11 | DETAIL12 | 1 | 2 | DETAIL21 | DETAIL22 |
|---|
select rtrim(xmlcast(xmlagg(xmlelement(E, ' -#HDR#- ' || hdr.a || ' - ' || hdr.b || ' - ' || hdr.c || ' - ' || chr(13) || ' -#Line#- ' || dtl.a || ' - ' || dtl.b || ' - ' || dtl.c || ' - ' || dtl.d, ' ; ' ||chr(13))
.Extract('//text()') order by hdr.a, dtl.b) as CLOB, ',') AS "A0"
from hdr hdr, dtl dtl
where 1 = 1
and hdr.a = dtl.a
and hdr.a = 1ORA-00907: missing right parenthesisMore Details: https://docs.oracle.com/error-help/db/ora-00907
select rtrim(xmlcast(xmlagg(xmlelement(E, ' -#HDR#- ' || hdr.a || ' - ' || hdr.b || ' - ' || hdr.c || ' - ' ||
chr(13) ||
' -#Line#- ' || dtl.a || ' - ' || dtl.b || ' - ' || dtl.c || ' - ' || dtl.d, ' ; ' ||chr(13))
.Extract('//text()') order by hdr.a, dtl.b) as CLOB), ',') AS "A0"
from hdr hdr, dtl dtl
where 1 = 1
and hdr.a = dtl.a
and hdr.a = 1| A0 | -#HDR#- 1 - HEADER1 - HEADER2 - -#Line#- 1 - 1 - DETAIL11 - DETAIL12 ; -#HDR#- 1 - HEADER1 - HEADER2 - -#Line#- 1 - 2 - DETAIL21 - DETAIL22 ; |
|---|