4 Replies Latest reply: Jan 28, 2013 9:34 AM by riedelme RSS

    Value at Address in Pl/Sql

    Shoaib
      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.

      For Dynamic Column Generation Following Code I have Written
      {
      Declare
      Cursor Temp is
      Select stk_1,stk_2,stk_3,stk_4 From ABC;

      Cursor Total_Columns Is
      Select Code from Stock_locations;

      var_text varchar2(100);
      var_count number := 0;
      var_final_text varchar2(2000);

      Begin
      For A in Temp Loop

      For B in Total_Columns Loop
      var_count := var_count + 1;
      var_text := 'A.'||'stk_'||var_count;
      var_final_text := var_final||';'||var_text;
      End Loop;
      TEXT_IO.PUT(INFILE,VAR_FINAL_TEXT);
      End Loop;
      End,
      }
      This "var_final_text" is placing

      " A.stk_1;A.stk_2;A.stk_3 "

      But I want the Value of Stk_1 , Stk_2 , Stk_3 instead of Columns Names of ABC Table.

      Thanks

      Shoaib

      Edited by: Shoaib on 27-Jan-2013 22:26
        • 1. Re: Value at Address in Pl/Sql
          971895
          Simple you can try with spool option using sql plus.
          • 2. Re: Value at Address in Pl/Sql
            Billy~Verreynne
            Wrong forum.

            This one deals with PL/SQL as an Oracle server-side language. Not client-side Forms.

            Please close this thread (mark as answered) and post your question to the Forms Forum on OTN.
            • 3. Re: Value at Address in Pl/Sql
              Shoaib
              HI ..i do not Want to spool everytime...

              I want to create a Fix procedure for users and use it.
              • 4. Re: Value at Address in Pl/Sql
                riedelme
                Shoaib wrote:
                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.
                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.

                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)
                --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;
                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.