I am trying to export data from multiple tables to a custom pipe delimited file format. I am able to generate pipe delimited file from single table, but not able to achieve the required custom format.
Here is the format:
HD|034567|24052021092630|1.0
3|||||0|0|12345678909|2|SDATA|DNAME||||TBD|||M|||||||||||||||||
3|||||0|0|12345678910|1|SDATA|DNAME||||TBD|||F|||||||||||||||||
3|||||0|0|12345678911|5|SDATA|DNAME||||TBD|||M|||||||||||||||||
FT|000000003
Where first row that starts with HD is from separate table, last one is hardcoded and data in rows in the middle is from the main table. How I can generate this format .dat file using SPOOL SQL*PLUS or with any other utility that is more suitable to scenario.
Thank you
It's been 5 hours since you posted the question and no replies yet. Maybe it means that you can't do it the way you planned to.
"Trivial" solution would be to use 3 SELECT statements with a UNION ALL, e.g.
select 'HD', col1, col2, col3 from table_1
UNION ALL
select number of columns from table_2
UNION ALL
select 'FT', 000000003 from dual
but it won't work because
You could run 3 separate select statements, but there'll be empty lines in between.
Therefore, consider switching to PL/SQL and UTL_FILE package to create such a file.