writing sql error to a log file

flowers_n · #1 (**permalink**) 01-03-08, 04:55

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.

stolze · #2 (**permalink**) 01-03-08, 05:37

You could just redirect the output of your SQL CLP?

flowers_n · #3 (**permalink**) 01-03-08, 06:37

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.

stolze · #4 (**permalink**) 01-03-08, 08:08

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech