Solved

print using dbms_output.put_line while triggers get executed

Posted on 2004-03-26
11
6,759 Views
Last Modified: 2007-12-19
I have a web application in JSP with Oracle 8i as database. I have a few triggers in Oracle which contain dbms_output.put_line to print values on the SQL*Plus Interface(used for debugging). When the user performs an action in the application and the trigger or procedure gets executed I want that the  dbms_output.put_line statement in that trigger or procedure prints on the SQL*Plus Interface.
Normally when i exceute procedure or trigger in SQL*Plus interface values are printed but not when they execute in the background as a result of operations performed by the user.
0
Comment
Question by:Nitika19
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 2

Expert Comment

by:aabbas
ID: 10687340
DBMS_OUTPUT package is used for debugging and returning values in SQL*PLUS environment only.

Do you mean, send debugging information to SQL*PLUS even if you run your JSP application?
You can use DBMS_PIPE to establish inter-session messaging to accomplish this specific requirement.

But if you just want to track/debug out of your application, writing to file is a good approach. You can create a simple debug function of yours, in which you can use UTL_FILE instead of DBMS_OUTPUT, so that every debugging line is written to a file and can be referred later.

Tom Kyte's comment on this is undoubtedly invaluable:
<<quote>>
See

http://osi.oracle.com/~tkyte/article2/index.html#Errors

and look for the text:

       "One utility I use frequently when writing"...

for my solution to this.  I use UTL_FILE and a small routine we've written
called "debug.f" (works like C's printf).  That way, I can see the output from a
session immediately by "tailing" or editing the file they produce.
<</quote>>

Hope, it will help.

Best of luck.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10687349
dbms_output.put_line is for use in SQL*Plus environment where you have to use also the command

set serveroutput on

In server environment they can not find the terminal to display the data.

This is the reason to store the data you need either in a flat file, using UTL_FILE package or to store them in an Oracle table (the best solution).
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10687358
Nitika19:

Dbms_output has to be hosted in an environment like sqlplus that has the IO capability.

if you want to write OUTPUT to somewhere you can read, you can use UTL_FILE package.

this will write to a file in your desired directory, and you can read it later.

let me know if you need a sample code for that
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 75 total points
ID: 10687543
This is a scetch to the procedure you wanted:

procedure YOUR_PROC   is

    sasha SYS.UTL_FILE.FILE_TYPE;

     sanjeev   VARCHAR2(1800);
     /* DEFINE HERE A PL/SQL CURSOR TO SELECT THE DATA FROM TABLES */
begin

   sasha := UTL_File.Fopen('D:\staff\cv','56789.txt', 'w');


   FOR .....      /*this is an explicite loop for the cursor */
    LOOP
  /* put the data from the cursor in the buffer "sanjeev"*/
  /*ensure the proper format, i.e. commas as separators */

        UTL_File.put_line(sasha,sanjeev);

     END LOOP;

     UTL_File.Fclose(sasha);
             

                 
                 
EXCEPTION                        
WHEN  UTL_File.invalid_filehandle   THEN ... UTL_File.Fclose(sasha);  
       --   invalid_filehandle - not a valid file handle
WHEN  UTL_File.write_error   THEN ... UTL_File.Fclose(sasha);
        -- OS error occurred during write
WHEN  UTL_File.invalid_operation   THEN ... UTL_File.Fclose(sasha);                    

end  YOUR_PROC ;


REMARK
Server security for PL/SQL file I/O consists of a restriction on
the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA).

Specify the accessible directories for the UTL_FILE
functions in the initialization file
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>

Starting from 9i you can use Oracle directories instead:

create or replace directory XMLDIR as 'E:\ICW_TASKS\XML\SOURCE\';
grant read on directory xmldir to my_user with grant option;
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 10687732
Here are two examples show you  use DBMS_OUTPUT.GETLINE and  DBMS_OUTPUT.GETLINES to retrieve information from   DBMS_OUTPUT buffer.

FUNCTION get_next_line RETURN VARCHAR2
IS
   return_value VARCHAR2(255);
   get_status INTEGER;
BEGIN
   DBMS_OUTPUT.GET_LINE (return_value, get_status);
   IF get_status = 0
   THEN
      RETURN return_value;
   ELSE
      RETURN NULL;
   END IF;
END;

---------------

DECLARE
  output_table DBMS_OUTPUT.CHARARR;  /* output_buf_tab */
  a_line VARCHAR2(10) := RPAD('*',10,'*');
  status INTEGER;
  max_lines CONSTANT NUMBER := 15;
BEGIN
   output_table (0) := 'ABC';
   output_table (12) := 'DEF';
   
   /* Output 10 lines */
   FOR linenum IN 1..10  LOOP
      DBMS_OUTPUT.PUT_LINE (a_line ||  TO_CHAR (linenum));
   END LOOP;
   /* retrieve 15 lines, status will receive the line count */
   status := max_lines;
   DBMS_OUTPUT.GET_LINES ( output_table, status);
   DBMS_OUTPUT.PUT_LINE ('lines retrieved= ' || status);
 
   FOR linenum in 0..max_lines
   LOOP
       BEGIN
          DBMS_OUTPUT.PUT_LINE
             (linenum || ':' || NVL (output_table(linenum),'<null>') );
       EXCEPTION
          WHEN OTHERS
          THEN
             DBMS_OUTPUT.PUT_LINE (linenum || ':' || sqlerrm );
       END;
    END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
       DBMS_OUTPUT.PUT_LINE ('Exception, status=' || status);
       DBMS_OUTPUT.PUT_LINE (SQLERRM );
END;
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Nitika19
ID: 10693340
Thanks everyone for replying.

Can someone plz give a simple sample code for this. I used the code provided by  schwertner but somehow i can't make it work.

0
 

Author Comment

by:Nitika19
ID: 10694247
I am doing the following but getting errors

  create or replace procedure proc_sales_update(client in NUMBER,dept in varchar2,mnth in varchar2,year in varchar2,currency_sales in varchar2,v_amnt in number)
 is
   
   output_file  utl_file.file_type;

cursor sale is select ....;

cursor exch is select ...;

cursor ..;

begin

output_file := utl_file.fopen ('C:\test','abc.txt', 'W');
utl_file.put_line (output_file, currency_sales);
utl_file.put_line (output_file, v_amnt);
utl_file.fclose(output_file);

X:='0';
open exch;
loop
fetch  exch into v_exchange_rate;
exit when exch%notfound;
end loop;
close exch;
open exch1;
loop
fetch  exch1 into v_exchange_rate;
exit when exch1%notfound;
end loop;
close exch1;

open sale;

loop

fetch sale INTO v_client,v_dept,v_month,v_year,v_currency,v_amount;
   exit when sale%notfound;
      ...
         x:='1';
    end if;
end loop;
close sale;

if x='0' then
insert into
   sales(client_id,dept_name,
   month,year,currency,amount,amount_rs)
    values(client,dept,mnth,year,currency_sales,v_amnt,v_exchange_rate*v_amnt);
end if;
end;
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10694309
When you use UTL_FILE package you should open access to the directory where the file reside. Look at my remarks. Check which version of Oracle are you using.
0
 
LVL 2

Accepted Solution

by:
n4nazim earned 100 total points
ID: 10701902
Hi,

DBMS_OUTPUT is used in SQLPLUS only for debugging purposes. If u want to debug the trigger from ur application what u can do is follows:

Create a table LOGTABLE which would keep history of actions taken

Inside yr trigger, instead of dumping o/p using DBMS_OUTPUT,insert a record in LOGTABLE using status messages, datetime ( ... whatever yr need might be )..

Later on u can always refer to this table to chek the log...

BUT DO MAKE SURE U PURGE THIS TABLE PERIODICALLY AS THIS TABLE CAN BE MASSIVE IN TERMS OF NUMBER OF RECORDS.

Rgds,
NHM
0
 
LVL 2

Assisted Solution

by:aabbas
aabbas earned 75 total points
ID: 10702322
It will be handy to have a standard interface (procedure) to print (or store output) for debugging purpose. Furthermore, Storing the debugging output in a table is quite managable than others.

For example, following procedure will be used to output debugging info.

create or replace procedure debug_out (p_msg varchar2) as
begin
  -- for SQL*Plus debugging...
  dbms_output.putline (p_msg);
  -- for storing debug info in table...
  insert into debug_info (msg_date, msg, username, terminal, ip)
  vaules (sysdate, p_msg, user, userenv ('TERMINAL'), sys_context ('USERENV', 'IPADDRESS'));
end;
/

You can now use this procedure in any of your code (procedure/function/trigger) to have debug info with you. e.g.

create or replace trigger generate_key
before insert
on mytable
for each row
declare
  mystr varchar2(50);
begin
  select prefix_col || to_char (myseq.nextval) into mystr from dual;
  debug_out (mystr);
  :new.pk_id := mystr;
exception
  when others then
    debug_out (sqlerrm);
    raise_application_error (-20999, sqlerrm);
end;
/

When the above trigger will execute, it will:
  - display (if trigger is executed in SQL*Plus environment) the debug message.
  - store debug info in debug_info table.
  - also display/store the error if the trigger causes any problem. So that the developer can refer what error was occured.

Hope, this will be understandable and will work at your side as well. :)

Best of luck.
0
 
LVL 2

Expert Comment

by:aabbas
ID: 10702341
Sorry, ... misspelled...

change IPADDRESS parameter to IP_ADDRESS in sys_context function used in INSERT above..

Regards
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now