Home » SQL & PL/SQL » SQL & PL/SQL » Loggin execution of Procedure
Loggin execution of Procedure [message #39365] Thu, 11 July 2002 11:26 Go to next message
Vinicius Carvalho
Messages: 1
Registered: July 2002
Junior Member
Hi there, I'm new to PL/SQL and I have a few doubts.
I want to log the execution of a procedure, it looks like this

DECLARE
c1 is
select ....;
BEGIN
for c_reg in c1 loop
delete from XXXX where id = c_reg.id;
end loop;

ok, Now my doubts
I want to create a spoolfile that could change every day, I tried:

select 'desativa_servico_workgroup' || TO_CHAR(sysdate, 'DDMMYYYY_HH24MISS') || '.lst' into v_path FROM DUAL;

but how do I assign my v_path variable to the spool command?

I want to place a header

user id
=============================
c_reg.user c_reg.id

in the spool file, how do I do it?

Thanks
Re: Loggin execution of Procedure [message #39388 is a reply to message #39365] Fri, 12 July 2002 15:34 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In sqlplus you can use the spool command. If your pl/sql is a stored procedure/function/package, you need to use utl_file.
set verify off
COLUMN a new_val thisuser noprint
COLUMN b new_val thisdb noprint
COLUMN c new_val todaysdate noprint
select USER a,
	nvl(substr(global_name, 0, instr(global_name, '.')-1), global_name) b,
	TO_CHAR(SYSDATE, 'YYYYmmdd_HH24MI') c
from global_name;
column fname new_value fname noprint
SELECT 'my_log_&thisuser@&thisdb._&todaysdate..log' fname FROM dual;
spool &fname
set serveroutput on
begin
 dbms_output.put_line('my col heading');
 dbms_output.put_line('===============');
 for i in (select emp_name from emp) loop
   dbms_output.put_line(i.emp_name);
 end loop;
end;
prompt Your output file is &fname

Using utl_file --> http://www.orafaq.net/faqplsql.htm#UTLFILE
Previous Topic: Clocking Execution timing in MILLISECONDS
Next Topic: help for sql
Goto Forum:
  


Current Time: Fri Mar 29 02:15:44 CDT 2024