This discussion is archived
12 Replies Latest reply: Feb 15, 2013 2:19 AM by 991303 RSS

Write a CLOB variable to a file as log file

907777 Newbie
Currently Being Moderated
Hi, I am currently building a dynamic sql statement in a plsql procedure that stores the statement in a CLOB variable (The statement is very long) which has been declared as:

vc_sql_statement CLOB;
Now i want to write this statement in a file using the UTL FILE function.

Since the variable is more than 32K, can anyone please suggest an alternative way of how i can drop this statement in a file as log.

I am using oracle version 11g.

Many Thanks.
  • 1. Re: Write a CLOB variable to a file as log file
    €$ħ₪ Expert
    Currently Being Moderated
    here is an example to load from clob to file
    
    declare
    ....
    begin
      FOR c IN csr
      LOOP
        l_clob := DBMS_METADATA.get_ddl (c.object_type,c.object_name,c.owner);
        dbms_xslprocessor.clob2file(l_clob,'DIR',c.object_name||'fun.sql');
      end loop;
    .....
    end;
  • 2. Re: Write a CLOB variable to a file as log file
    907777 Newbie
    Currently Being Moderated
    Thank you very much for this quick relpy..

    So basically i need to put the function
    DBMS_XSLPROCESSOR.CLOB2FILE(....) 
    in a loop to insert into the file or i can directly use it like this?
    DBMS_XSLPROCESSOR.CLOB2FILE(<CLOB_DATA> , <File location >, <file name>);  
    in the middle of my plsql?

    Thank you

    Edited by: 904774 on 28-Dec-2011 00:13
  • 3. Re: Write a CLOB variable to a file as log file
    €$ħ₪ Expert
    Currently Being Moderated
    you can directly use this provided

    1.DIRECTORY must be there
    2.File should be there
  • 4. Re: Write a CLOB variable to a file as log file
    907777 Newbie
    Currently Being Moderated
    Infact at the start of the execution of the plsql block, I already open a file and start writing each steps of the execution to the log with an append mode.

    So according to my understanding, using the above procedure will append the clob data just after the previous step has been written in the log file?
  • 5. Re: Write a CLOB variable to a file as log file
    907777 Newbie
    Currently Being Moderated
    Very helpful and pointing me in the right direction. Many thanks to all

    Sorry I don't know how this come here as a post, I was just commenting your reply as helpful :)

    but my above question is still open :)

    Many Thanks

    Edited by: 904774 on 28-Dec-2011 01:53
  • 6. Re: Write a CLOB variable to a file as log file
    odie_63 Guru
    Currently Being Moderated
    So according to my understanding, using the above procedure will append the clob data just after the previous step has been written in the log file?
    No, DBMS_XSLPROCESSOR.CLOB2FILE always overwrites.

    What you have to do is :

    - build your complete CLOB first
    - write it to your file at the end using a single call to CLOB2FILE
  • 7. Re: Write a CLOB variable to a file as log file
    907777 Newbie
    Currently Being Moderated
    Before converting this variable to CLOB, i was using the VARCHAR2 as data type but then with further testing in other env, the data length for that variable only became more than 32K.

    So I decided to use a CLOB for this variable only.

    But in my plsql for logging each steps, I was using the simple UTL FILE and the data was less than 32k as each step has its its own data for the log, its not a concatenation for building the whole variable vc_sql_statement.

    The variable vc_sql_statement will be one among the many data that i will have in my log :)

    for example:
    vc_message : = 'Get parameterized value from table X at time x'
    
    <Some code to get the value X>
    
    Write vc_message to log file using simple UTL FILE
    
    vc_message : = 'Get parameterized value from table Y at time y'
    
    <Some code to get the value Y>
    
    Write vc_message to log file using simple UTL FILE
    
    
    vc_sql_statement : = <Call a procedure that return a CLOB sql statement>
    
    DBMS_XSLPROCESSOR.CLOB2FILE(vc_sql_statement,..........);
    Only for the variable vc_sql_statement I have declared it as CLOB, so you mean it will overwrite all my previous data when using DBMS_XSLPROCESSOR.CLOB2FILE.

    What I want is something like this in my log.
                        .
                      .
                      .
    Get parameterized value from table X at time x
    Get parameterized value from table Y at time y
    sql statement : <the sql in the CLOB>
                     .
                     .
                     .
                     .
                     .
    Edited by: 904774 on 28-Dec-2011 04:35

    Edited by: 904774 on 28-Dec-2011 04:37

    Edited by: 904774 on 28-Dec-2011 04:37
  • 8. Re: Write a CLOB variable to a file as log file
    odie_63 Guru
    Currently Being Moderated
    so you mean it will overwrite all my previous data when using DBMS_XSLPROCESSOR.CLOB2FILE.
    Yes.
    Well, then why not use the same CLOB variable throughout the whole procedure, and append your trace strings to it as well?

    Do you want to consult the log file while the process is still running?
  • 9. Re: Write a CLOB variable to a file as log file
    907777 Newbie
    Currently Being Moderated
    Do you want to consult the log file while the process is still running?
    No, the point here is solely to get a log file for the different steps that the plsql is executing including the sql statement in the CLOB.
    The sql statement will be useful for later debugging for data retrieved in PLSQL Developer.

    But your answer is the last thing I think that will work :)

    That is I will concatenate all the vc_message variables trace strings from top of the plsql and adding a char(10) which is line feed.
    Then I will concatenate the vc_sql_statement variable followed by the remaing vc_message till the end of the PLSQL.

    The code will look somewhat like this pseudocode:
     
    vc_message       VARCHAR2(1000 CHAR)          ;  -- Note that the vc_message trace contains relatively small strings, basically just a description of next execution step.
    cc_eol              VARCHAR2(1      CHAR):= char(10); -- line feed after each trace message
    vc_concate_msg CLOB                                   ;
    
    
    vc_message : = 'Get parameterized value from table X at time x'
     
            <Some code to get the value X>;
    
    vc_concate_msg := vc_message || cc_eol  ;
    
    vc_message : = 'Get parameterized value from table Y at time y'
     
            <Some code to get the value Y>;
    
    vc_concate_msg := vc_concate_msg || vc_message || cc_eol ;
     
    vc_sql_statement : = <Call a procedure that return a CLOB sql statement>;
    
    vc_concate_msg := vc_concate_msg || vc_sql_statement || cc_eol;
    .
    .
    .
    .
    Then at the end of the plsql, I will call the procedure CLOB2FILE and dump all in the log file
    DBMS_XSLPROCESSOR.CLOB2FILE(vc_concate_msg,..........); 
    Hope this works :)

    Edited by: 904774 on 28-Dec-2011 08:35

    Edited by: 904774 on 28-Dec-2011 08:38
  • 10. Re: Write a CLOB variable to a file as log file
    damorgan Oracle ACE Director
    Currently Being Moderated
    Three ways to do it.

    http://www.morganslibrary.org/howcani.html

    Look at #4 on the page linked above.
  • 11. Re: Write a CLOB variable to a file as log file
    907777 Newbie
    Currently Being Moderated
    Thank you loads..I will test this when i resume work in Jan.

    I will go the option of using CLOB2FILE. There is no reason why it shoudn't work :)

    These forums are really helpful.

    Keep up with the good work

    Cheers.

    Edited by: 904774 on 30-Dec-2011 05:32

    Edited by: 904774 on 30-Dec-2011 05:32
  • 12. Re: Write a CLOB variable to a file as log file
    991303 Newbie
    Currently Being Moderated
    Thnx for the pointer.
    It really helped me out.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points