Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: writing sql error to a log file

    I have one procedure which has output parameter error_msg.

    i want to pass this error_msg to a log file in unix shell scripting.

    how will i do.

    thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could just redirect the output of your SQL CLP?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2008
    Posts
    2
    How to redirect it that i don't know.


    STORAGE_TYPE=$1

    echo "########### SQL SESSION STARTED ###########"

    sqlplus tcupro/tcupro_dev@BDD1OPTN << THEEND


    Code:
    SET SERVEROUTPUT ON
    DECLARE
            V_STORAGE_TYPE   varchar2(3); 
             V_ERR_MSG  varchar2(255) ;    
    BEGIN
    
      V_STORAGE_TYPE :=  '$STORAGE_TYPE';
      V_ERR_MSG := 'SUCCESS';
    dbms_output.put_line(V_STORAGE_TYPE);
      TCUPRO.PURGE_TABLES_TEST(V_STORAGE_TYPE, V_ERR_MSG);
    dbms_output.put_line(V_ERR_MSG);
    dbms_output.put_line(V_STORAGE_TYPE);
    END;
    
    
    /
    EXIT;
    THEEND
    V_ERR_MSG IS TO BE PASSED TO A LOG FILE. HOW CAN I PASS.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Oracle has some functions like PUT_LINE that you can use to write to a file on the server. http://www.psoug.org/reference/dbms_output.html If you want to write things to a file on the client, you need some sort of application logic. For example, you can capture the output stream, extract the message with tools like SED, AWK, Perl or whatever you prefer, and then do with the message whatever you like, e.g. writing it to another file. Since I don't know the exact output produced by your script (I don't use Oracle often), others could help you there.

    p.s: Personally, I don't think it is a good idea to write something to a file residing on the database server. Most systems beyond the scale of toy systems use a multi-tiered architecture and your application doesn't run on the DBMS server.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •