I am planning to use SQLcl to extract data from an Oracle DB and write into file. We may end up with 150+ such SQL extracts.
So I am wondering if there is a way to establish the session once and then execute the SQL statements in files one by one. To be more reusable, I was thinking to have the sequential execution of the SQL files using a loop. The reason being I could see that every time it takes around 25~30 seconds to bring up the session and this will create a good amount of delay when processing 150+ extracts.
This looks possible if I can use the PL/SQL block, but I have learnt that the PL/SQL's output is facilitated by DBMS_OUTPUT.PUT_LINE() which has a limitation on line size of 32767 bytes. My extracts can exceed this line size whereas in SQLcl I don't think this limit exists.
Considering my requirements, I would really appreciate if someone can help me with a work around on this .
Thanks in advance..!