Procedure for logging in PL/SQL
549859Dec 12 2006 — edited Dec 13 2006I am looking for a way to log sqlplus output from within a pl/sql procedure. When run through SQL*PLUS directly I can use SPOOL. Is there any way to do the same from within PL/SQL? Following is an example of what I was previously executing as a .sql script via command line SQLPLUS logon/password @instance @sqlscript.sql. Now I want to create a stored procedure to do the same...
Thanks,
Susan
spool dws_office.log
SET HEADING ON
SET ECHO ON
SET VERIFY ON
SET FEEDBACK ON
select TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS AM') from DUAL;
TRUNCATE TABLE OFFICE;
INSERT INTO office
(SELECT office_id,
region_id,
office_name,
office_type,
primary_phone,
date_active,
'',
'',
'',
agency,
default_email,
zip,
city,
agency_office_code,
county,
fax,
office_description,
office_JSC_id,
secondary_phone,
zip_plus_four,
state,
address1,
address2,
'',
'',
'',
maintenance_user,
maintenance_date,
date_inactive
FROM office_data);
UPDATE office o
SET o.OfficeGroupID =
(SELECT og.OfficeGroupID
FROM OfficeGroup og
WHERE o.officeid = og.officeid),
o.OfficeGroupName =
(SELECT og.OfficeGroupName
FROM OfficeGroup og
WHERE o.officeid = og.officeid),
o.regionname =
(SELECT r.region_name
FROM regions r
WHERE o.regionid = r.region_id),
o.georegionid =
(SELECT g.georegionid
FROM georegion g
WHERE o.regionid = g.regionid),
o.georegionname =
(SELECT g.georegionname
FROM georegion g
WHERE o.regionid = g.regionid),
o.arearollupname =
(SELECT e.arearollupname
FROM esdrolluparea e
WHERE o.officeid = e.officeid);
commit;
select TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS AM') from DUAL;
BEGIN
dbms_stats.gather_table_stats(ownname=> 'DWSCHEMA', tabname=> 'office', partname=> NULL);
END;
/
spool off
exit