I have a very large set of data that is formed similar to this:
I want to produce report output that drops the table headers (field1, field2, field3) and instead use data row1 as headers in output. I am able to do this nicely in SQLPlus and SPOOL to CSV, but I want to do everything in PLSQL so that I can log to concurrent request output.
I cannot simply load the data into a new table using row1 as header because I cannot predict what the new header names will be (for the purpose of extraction). The steps of dropping table headers and extracting to file system need to happen together.
Looking for some suggestions. I am OK with continuing the use of SQLPlus for the extraction if there is a way to use fnd_file.put_line or some other method to write to concurrent request log. As far as I can tell fnd_file.put_line commands do not work in SQLPlus.
Before you all go off on me, I dont want to hear about how its bad design to have dynamic table headers. I have my reasons in this particular case.
Advertisement
Answer
I solved this within the SQLPlus program. Before starting to SPOOL the output I issue the PROMPT command with text that I want to be written to the request output.
The below will place ‘this is some log text’ in the request output
prompt this is some log text