This content has been marked as final. Show 4 replies
Shoaib wrote:Dynamic SQL is tricky to work with. You have to think at two different levels while writing it - what is the actual code doing and what will the code that is being generated be doing when it runs? This is hard.
I am creating a procedure , which will write to excel with some columns.
Now I want the column generation to be Dynamic during runtime.So for that, I Get all the columns list from the loop and that column I will use as a Heading in Excel.
But I want the Value of Stk_1 , Stk_2 , Stk_3 instead of Columns Names of ABC Table.
The only time that dynamic SQL makes sense is if the SQL must vary between runs, and even then hard-coding different SQL for only a few differernt circumstances is usually easier.
If you are getting column names in the output you are probably quoting the identifiers or else the package you are using is doing something unexpected. I would simplify to use a reference cursor, something like (untested)
To save the output you can use SPOOL as suggested or UTL_FILE to save the output on the server after loading the results into a record or variables.
--define ref cursor for sql*plus. opened ref cursor should be inherited by sql*plus session when script ends when we assign it session_refcursor refcursor; declare cursor c sys_refcursor; v varchar2(32767); begin v := 'select dummy from dual'; open c for v; :session_refcurosr := c; end; / print :session_refcursor;