Loggin execution of Procedure [message #39365] |
Thu, 11 July 2002 11:26 |
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 |
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
|
|
|