下面是小编为大家推荐的教你在Oracle中启动脚本跟踪存储过程（共含6篇），欢迎大家分享。同时，但愿您也能像本文投稿人“出去耍下”一样，积极向本站投稿分享好文章。
一、用脚本启动并设置跟踪的示例 我们可以用脚本进行跟踪存储过程，当然要了解这些存储过程的具体语法和参数的含义，至于这些语法和参数含义请查询联机帮助，下面请看一实例：
/*******************************************/
/* Created by: SQL Profiler */
/* Date: /06/19 16:50:05 */
/*******************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'c:\test', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Writing to a table is not supported through the SP's
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter
set @intfilter = 1
exec sp_trace_setfilter @TraceID, 23, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'pubs'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
二、生成跟踪脚本的最简式
事件探查器建立跟踪, 并设置好各种选项, 完成后运行跟踪，然后生成脚本，
事件探查器--文件--导出跟踪定义的文件--选择合适的版本。这样就会生成一个跟踪的脚本, 打开生成的脚本, 修改里面的:exec @rc = sp_trace_create部分, 设置跟踪结果的保存文件(用语句跟踪的时候, 跟踪结果只能保存到文件)。然后, 在需要跟踪的时候, 运行这个脚本来启动跟踪。启动跟踪后, 跟踪自动进行, 所以你可以关闭查询分析器做其他事情去了。
三、已知的问题
1.跟踪记录不是实时写入跟踪文件的, 因此, 可能会到你停止跟踪的时候, 跟踪信息才写入跟踪文件
2.查看当前已经进行的跟踪可以用(关于结果集的解释, 请看联机帮助):
SELECT * FROM ::fn_trace_getinfo(0)
3. 停止某个跟踪, 可以在sp_trace_create 语句中设置自动停止时间, 也可以手动停止跟踪, 用下面的语句:
EXEC sp_trace_setstatus
@traceid = 1 , -- 跟踪的id
@status = 0 -- 停止, 这样以后还可能指定此项为来启用
EXEC sp_trace_setstatus
@traceid = 1 ,
@status = 2 -- 关闭, 彻底释放
Oracle数据库里存放着各种各样的数据,其中有一些数据表会随着时间的推移,越来越大，如交友聊天的日志、短信收发的日志、生产系统的日志、动态网站发布系统的日志等等。这样的信息又和时间紧密相关，有没有办法 让这些日志表能到时间自动分割成历史年月(如log08,log200309)的表呢? 请看看我用存储过程定期分割表的 方法吧。
一、问题的引出
1.初学数据库时只知道用delete来删除表里的数据。但在Oracle数据库里，大量delete记录后，并不能释放表
所占用的物理空间，这里面有一个高水位的概念，所以我们不能用delete来分割表。
2.用重命名(rename)表的方法
(1).先建一个和原来日志表(假如是log)数据结构一模一样的新表(如log_new)，建约束、索引及指定字段的默
认值;
(2).重命名表log到log_YYYYMM;
要注意的问题是OLTP系统可能会因为DML操作阻碍重命名执行成功，出现ORA-00054资源正忙的错误提示,
需要试多次才能成功。
(3).重命名表log_new到log。
这样应用程序不用修改(受影响的时间仅几秒钟)，日志表就被截断分割了。
上述步骤可以在Oracle里可以用存储过程来实现它们。
二、用存储过程来分割表
可以看到在重命名表的方法中，步骤(2)是个关键。
下面这个rename_table过程会在有锁阻碍的情况下用递归的方式重试100次。
重命名原始表到目标表的存储过程rename_table:
create or replace procedure rename_table
(source_name in varchar2,
target_name in varchar2,
times in out number)
is
query_str varchar2(4000);
source_name1 varchar2(64);
target_name1 varchar2(64);
cursor c1 is select segment_name from user_segments where segment_name=upper(source_name);
dummy c1%rowtype;
cursor c2 is select segment_name from user_segments where segment_name=upper(target_name);
dummy2 c2%rowtype;
begin
source_name1:=source_name;
target_name1:=target_name;
open c1;
fetch c1 into dummy;
-- if c1%found then
-- dbms_output.put_line(source_name1||'exist!');
-- end if;
open c2;
fetch c2 into dummy2;
-- if c2%notfound then
-- dbms_output.put_line(target_name1||'not exist!');
-- end if;
if c2%notfound and c1%found then
query_str :='alter table '||source_name1||' rename to '||target_name1;
execute immediate query_str;
dbms_output.put_line('rename success!');
end if;
close c1;
close c2;
exception
WHEN OTHERS THEN
times:=times+1;
if times<100 then
-- dbms_output.put_line('times:'||times);
rename_table(source_name1,target_name1,times);
else
dbms_output.put_line(SQLERRM);
dbms_output.put_line('error over 100 times,exit');
end if;
end;
/
截断分割log表的存储过程log_history:
create or replace procedure log_history
is
query_str varchar2(32767);
year_month varchar2(8);
times number;
begin
select to_char(sysdate-15,'YYYYMM') into year_month from dual;
times:=0;
query_str :='create table log_new pctfree 10 pctused 80
as select * from log where 1=2';
execute immediate query_str;
query_str :='alter table log_new add constraints log_'||year_month||'_pk
primary key (id) tablespace indx nologging pctfree 10';
execute immediate query_str;
query_str :='alter table log_his modify logtime default sysdate';
execute immediate query_str;
query_str :='create index log_'||year_month||'_logtime on log(logtime)
tablespace indx nologging pctfree 10';
execute immediate query_str;
rename_table('log','log'||year_month,times);
query_str :='alter table log_new rename to log';
execute immediate query_str;
end;
/
当然您工作环境的日志表可能和 我这个做例子的日志表结构上有所不同，约束条件、索引和默认值都不尽相同，
只要稍加修改就可以了。
三、用户需要有create any table系统权限(不是角色里包含的权限)
因为在执行存储过程时，由角色赋予的权限会失效, 所以执行log_history的用户一定要有DBA单独赋予的
create any table系统权限。
最后在OS里定时每月一号凌晨0:00分执行log_history，让存储过程定期分割表。
如果要分割的日志表很多，模仿log_history可以写很多类似的存储过程来分割不同项目里的日志表。
然后让OS按月，按周或者不定期地执行这些存储过程, 管理员只要查看日志就可以了。
四、其它注意事项
如果应用程序有BUG，可能对在用原始日志表产生长期不能释放的锁，执行log_history重命名会不成功。
这时DBA可以查看数据字典:
select object_id,session_id,locked_mode from v$locked_object;
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
如果有长期出现的一模一样的列（包括登录时间），可能是没有释放的锁。
我们要在执行分割日志表的存储过程前，用下面SQL语句杀掉长期没有释放非正常的锁：
alter system kill session 'sid,serial#';
五、结束语
用上面介绍的存储过程定期分割日志表有很大的灵活性。历史数据不仅查询方便，转移和备份起来也都很容易。
Unix和Windows平台的都可以使用。对服务器硬盘空间较小的中小型公司意义尤其明显。
第一：存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快，
第二：存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
第三：存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。
第四：存储过程主要是在服务器上运行，减少对客户机的压力。
第五：存储过程可以包含程序流、逻辑以及对数据库的查询，
同时可以实体封装和隐藏了数据逻辑。
第六：存储过程可以在单个存储过程中执行一系列 SQL 语句。
第七：存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
其实存储过程还可以控制权限，比如一个表不直接允许用户直接访问，但要求允许用户访问和修改其中一个或多个字段，那就可以通过一个存储过程来实现并允许该用户使用该存储过程。
还有，如果多条SQL语句执行过程中，过程环节返回了数据作为后面环节的输入数据，如果直接通过SQL语句执行，势必导致大量的数据通过网络返回到客户机，并在客户机运算；如果封装在存储过程中，则将运算放在服务器进行，不但减少了客户机的压力，同时也减少了网络流量，提高了执行的效率。
在 Oracle 9i之前，虽然有一个NEW_TIME函数可以改变DATE的时间戳部分，但是还没有专门用来存储时区信息的数据类型，在Oracle9i中，我们可以使用DBTIMEZONE伪字段查询 数据库 的时区，使用SESSIONTIMEZONE伪字段查询会话的时区。 但是，对于大多数数据库，这
在Oracle9i之前，虽然有一个NEW_TIME函数可以改变DATE的时间戳部分，但是还没有专门用来存储时区信息的数据类型。在Oracle9i中，我们可以使用DBTIMEZONE伪字段查询数据库的时区，使用SESSIONTIMEZONE伪字段查询会话的时区。
但是，对于大多数数据库，这些值都是-07:00之类的偏移值，因此对于NEW_TIME函数是没有用的。Oracle9i有关NEW_TIME的文档建议使用FROM_TZ来替代，但是这可能会产生误导。FROM_TZ只将一个时区应用到一个时间戳上；它并不能把一个时区转换成另外一个时区。
其实有一个比较好的方法（从文档中得到这个方法可能有点难）。首先，为了完成这个工作，在正确的时区内需要一个TIMESTAMP WITH ZONE数据类型。然后，如果你将关键字AT TIME ZONE应用到那个值，它就会自动地调整为新的时区和日期。
这个语句将为太平洋白天时间（即其切换到PST之前的时刻）构造一个TIMESTAMP WITH TIME ZONE然后再将其转换到GMT。AT TIME ZONE关键字也接受默认的偏移值语法：
你还可以使用伪字段来自动调整当前会话的时区：
上面的表达式返回一个当前会话的本地时间（数据类型为时区），重新调整数据库的时区，调整后的时区将与SYSTIMESTAMP的结果相等，
有了以上的这些信息，就可以构造一个比较好的NEW_TIME函数：
即使第一个参数被标记为一个timestamp with time zone，你依然可以传入一个TIMESTAMP和DATE，这样由于Oracle的自动转型操作，得到的时间将是会话在本地时区的当前时间。这个函数接受包括偏移值在内的任何可以被TIMESTAMP识别的时区，然后将接受的时区调整为正确的值。
（责任编辑 火凤凰 sunsj@51cto.com TEL：（010）68476636-8007）
原文转自：www.ltesting.net
oracle|存储过程|数据
选自CSDN search.csdn.net/Expert/topic/2280/2280860.xml?temp=2.169436E-02 论坛中JCC0128 网友的发言
【delphi+oracle报表解决方案(一)】delphi中调用oracle的存储过程(分带返回游标，不返回值两种)
关键字: delphi ,oracle存储过程，游标,返回数据集,报表
注：delphi 6+ oracle 8.1.6
一.创建包与包体
1.附：建表aaclass为下面作测试用
create table aaclass(CID VARCHAR2(50), CNAME VARCHAR2(50), pnumber NUMBER(10,0) );
INSERT INTO aaclass values('c1', 'cn1', 10 ) ;
INSERT INTO aaclass values('c2', 'cn2', 40 ) ;
INSERT INTO aaclass values('c1', 'cn3', 30 ) ;
commit;
2.建包:
CREATE OR REPLACE PACKAGE PKG_JCCTEST1
AS
type rc_class is ref cursor;
--求p1,p2的和与差，返回的多个值通过游标返回
procedure GetSubAndSum2(p1 number,p2 number ,
ResultCursor out rc_class);
--查询满足条件的数据集，返回数据集通过游标返回
procedure GetClass2(a in number,ResultCursor out rc_class ) ;
--往表中插一条记录，不返回结果集时，本人用AdoQuery调用(adodataset好象要求必须返回结果集)
procedure InsertClass( p_cid varchar2 ,p_cname varchar2 ,
p_pnumber number) ;
end PKG_JCCTEST1;
3.建包体
CREATE OR REPLACE PACKAGE BODY PKG_JCCTEST1
AS
procedure GetSubAndSum2(p1 number,p2 number ,
ResultCursor out rc_class)
IS
BEGIN
open ResultCursor for
select p1-p2 as “sum”, p1+p2 as “sub” from dual;
END ;
procedure GetClass2(a in number,ResultCursor out rc_class )
is
begin
open ResultCursor for
select aaclass.* from aaclass where pnumber >a;
end ;
procedure InsertClass( p_cid varchar2 ,p_cname varchar2 ,
p_pnumber number)
is
begin
insert into aaclass values(p_cid,p_cname,p_pnumber) ;
-- commit;
end ;
二.在delphi中利用AdoDataSet调用上述第一个存储过程
1.利用AdoConnection1连接数据库(驱动为 oracle Provider for OLE DB)，
**并在连接字符串中加入这一节: PLSQLRSet=1; 如下所示：
Provider=OraOLEDB.Oracle.1;Password=KXD;Persist Security Info=True;User ID=KXD;Data Source=TEST3;PLSQLRSet=1
2.在窗体上加AdoDataSet1 指明连接为上述AdoConnection1,下面可以放一个按钮，单击按钮就能调用第一步中创建的包过程，并返回数据集，代码如下所示：
procedure TForm1.Button1Click(Sender: TObject);
var
AResult , BResult : integer;
begin
ADODataSet1.Close ;
ADODataSet1.CommandType := cmdText ;
ADODataSet1.Parameters.Clear ;
//***利用call方法调用oracle过程时，参数必须由?来传， 即使你要传的参数为常理
//输出游标的参数不需要指定!!!!!!，本来此函数带三个参数，我们这里只需要传两个参数.
ADODataSet1.CommandText := '{call PKG_JCCTEST1.GetSubAndSum2(?,?)}' ;
//***C 顺序有关，createparam必须放在commandtext赋值语句之后.
// 创建第一个参数，对应call中的第一个?,ftinteger为类型,10为长度，45为传入的实参值
ADODataSet1.Parameters.CreateParameter('p1',ftinteger,pdinput,10,45);
//创建第二个参数，根据createparameter的顺序 自动与call中的第二个参数对应
ADODataSet1.Parameters.CreateParameter('p2',ftinteger,pdinput,10,4);
//下面调用ADODataSet1 的open方法，返回数据集(对应包过程的游标)
ADODataSet1.Open ;
//根据存储过程，数据集只有一条记录，所以不需要用while do 来遍历数据集，直接取数据了
//此处的字段名根据包过程中的返回游标 对应的字段名来取
//定义的存储过程返回游标如： open ResultCursor for
// select p1-p2 as “sum”, p1+p2 as “sub” from dual;
//把对应的字段值取出来即可
AResult := ADODataSet1.Fields.FieldByName('sub').Value ;
BResult := ADODataSet1.Fields.FieldByName('sum').Value ;
//显示结果
showmessage(inttostr(AResult)) ;
showmessage(inttostr(BResult)) ;
end;
三.在delphi中利用AdoDataSet调用上述第二个存储过程
还是利用上述的AdoDataSet1来调用第二个存储过程，无需任何改动,加第二个按钮，单击时代码如下：
procedure TForm1.Button2Click(Sender: TObject);
begin
ADODataSet1.Close ;
ADODataSet1.CommandType := cmdText ;
ADODataSet1.Parameters.Clear ;
//***利用call方法调用oracle过程时，参数必须由?来传， 即使你要传的参数为常理
//输出游标的参数不需要指定!!!!!!，本来此函数带两个参数，我们这里只需要传一个参数.
ADODataSet1.CommandText := '{call PKG_JCCTEST1.GetClass2(?)}' ;
//***C 顺序有关，createparam必须放在commandtext赋值语句之后.
// 创建第一个参数，对应call中的第一个?,ftinteger为类型,10为长度，20为传入的实参值
ADODataSet1.Parameters.CreateParameter('p1',ftinteger,pdinput,10,20);
//下面调用ADODataSet1 的open方法，返回数据集(对应包过程的游标)
ADODataSet1.Open ;
while not ADODataSet1.Eof do
begin
showmessage('CID : '+string(ADODataSet1.FieldByName('CID').Value) +
'--CNAME :' + string(ADODataSet1.FieldByName('CNAME').Value) +
'--PNUMBER :' + string(ADODataSet1.FieldByName('PNUMBER').Value)
) ;
ADODataSet1.Next ;
end ;
end;
四 利用adoquery调用第三个过程，不返回数据集的
procedure TForm1.Button3Click(Sender: TObject);
begin
AdoQuery1.Close ;
AdoQuery1.Parameters.Clear ;
AdoQuery1.SQL.Clear ;
AdoQuery1.SQL.Add('{call PKG_JCCTEST1.GetSubAndSum2(?,?)}') ;
AdoQuery1.Parameters.CreateParameter('P1',ftstring,pdinput, 50,'c11') ;
AdoQuery1.Parameters.CreateParameter('P2',ftstring,pdinput, 50,'cn11') ;
AdoQuery1.Parameters.CreateParameter('P3',ftinteger,pdinput, 50,25) ;
AdoQuery1.ExecSQL ;
end;
五 利用adoquery调用第一个过程，返回数据集的.
procedure TForm1.Button4Click(Sender: TObject);
begin
AdoQuery1.Close ;
AdoQuery1.Parameters.Clear ;
AdoQuery1.SQL.Clear ;
AdoQuery1.SQL.Add('{call PKG_JCCTEST1.GetSubAndSum2(?,?)}') ;
AdoQuery1.Parameters.CreateParameter('P1',ftinteger,pdinput, 50,25) ;
AdoQuery1.Parameters.CreateParameter('P2',ftinteger,pdinput, 50,22) ;
AdoQuery1.Open ;
Showmessage(string( AdoQuery1.FieldByName('sub').Value)+'-'+
string( AdoQuery1.FieldByName('sum').Value));
end;
六.关于三层体系的此类问题
两层的解决了，三层类似.
中间层用tadodataset 或tadoquery (+tdatasetprovider),中间层的adoconnection的连接字符串加上plsqlRset=1;
客户端用clientdataset ,大同小异，举例如下:
begin
//调用相应的过程
ClientDataSet1.Close ;
ClientDataSet1.Params.Clear ;
ClientDataSet1.CommandText := '{call PackageName.ProcedureName(?,?)}' ;
ClientDataSet1.Params.CreateParam(ftInteger , 'ParamName1', ptInput) ;
ClientDataSet1.Open ;
end ;
本人水平有限，如有不当与错误之处请指正!
存储过程|函数
在PL/SQL 开发中调试存储过程和函数的一般性方法摘要: Oracle 在PLSQL中提供的强大特性使得数据库开发人员可以在数据库端完成功能足够复杂的任务, 本文将结合Oracle提供的相关程序包(package)以及一个非常优秀的第三方开发工具来介绍在PLSQL中开发及调试存储过程的方法，当然也适用于函数，
版权声明: 本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息。
原文出处: www.aiview.com/notes/ora_using_proc.htm
作者: 张洋 Alex_doesAThotmail.com
最后更新: 2003-8-2
目录 准备工作 从一个最简单的存储过程开始 调试存储过程 在存储过程中写日志文件 捕获违例
Oracle 在PLSQL中提供的强大特性使得数据库开发人员可以在数据库端完成功能足够复杂的任务, 本文将结合Oracle提供的相关程序包(package)以及一个非常优秀的第三方开发工具来介绍在PLSQL中开发及调试存储过程的方法，当然也适用于函数。
本文所采用的软件版本和环境:
服务器: Oracle 8.1.2 for Solaris 8
PL/SQL Developer 4.5
准备工作
在开始之前, 假设您已经安装好了Oracle的数据库服务, 并已经建立数据库, 设置好监听程序, 以允许客户端进行连接; 同时您已经拥有了一台设置好本地Net服务名的开发客户机, 并已经安装好PL/SQL Developer开发工具的以上版本或者更新.
在下面的示例代码中,我们使用Oracle数据库默认提供的示例表 scott.dept 和 scott.emp. 建表的语句如下：
create table SCOTT.DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
create table SCOTT.EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
从一个最简单的存储过程开始
我们现在需要编写一个存储过程, 输入一个部门的编号, 要求取得属于这个部门的所有员工信息, 包括员工编号和姓名. 员工的信息通过一个cursor返回给应用程序.
create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR
) as
begin
open curEmp for
select empno,
ename
from scott.emp
where deptno = in_deptNo;
end usp_getEmpByDept;
上面我们定义了两个参数, 其中第二个参数需要利用cursor返回员工信息, PLSQL中提供了REF CURSOR的数据类型, 可以采用两种方式进行定义, 一种是强类型,一种是弱类型, 前者在定义时指定cursor返回的数据类型, 后者可以不指定, 由数据库根据查询语句进行动态绑定.
在使用前必须首先使用TYPE关键字进行定义, 我们把数据类型REF_CURSOR定义在自定义的程序包中: pkg_const
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
end pkg_const;
注意: 这个包需要在创建上面的存储过程之前被编译, 因为存储过程用到了包中定义的数据类型.
调试存储过程
使用PL/SQL Developer 登录数据库, 用户名scott, 密码默认为: tiger. 将包和存储过程分别编译, 然后在左侧浏览器的procedure栏目下找到新建的存储过程, 点击右键, 选择“Test”/“测试”, 在下面添好需要输入的参数值, 按快捷键F8直接运行存储过程, 执行完成之后, 可以点开返回参数旁边的按钮查看结果集.
如果存储过程内部语句较复杂, 可以按F9进入存储过程进行跟踪调试. PL/SQL Developer提供与通用开发工具类似的跟踪调试功能, 分为step、step over、step out 等多种方式， 对于变量也可进行trace或者手动赋值。
在存储过程中写日志文件
以上方法可以在开发阶段对编写和调试存储过程提供最大限度的方便，但为了在系统测试或者生产环境中确认我们的代码是否正常工作时，就需要记录log。
PLSQL提供了一个UTL_FILE包，通过定义UTL_FILE包中的FILE_TYPE类型，可以获得一个文件句柄，通过此句柄可以实现一般的文件操作功能。但默认的数据库参数是不允许使用UTL_FILE包的，需要手动进行配置，使用GUI的管理工具或者手工编辑INIT.ORA文件，找到 “utl_file_dir” 参数，如果没有，则添加一行，修改成如下：
utl_file_dir='/usr/tmp'
或者
utl_file_dir=*
第一种方式限定了在UTL_FILE包中可以存取的目录，第二种方式则不进行限定。无论哪种方式，都要保证运行数据库实例的用户，一般是oracle，拥有此目录的存取权限，否则在使用包的过程中会报出错误信息。
注意等号左右不要留空格，可能会引起解析错误，导致设置无效。
下面在上面的存储过程中加入记录log的代码：
create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR
) as
fi utl_file.file_type;
begin
if( pkg_const.DEBUG ) then
fi := utl_file.fopen( pkg_const.LOG_PATH, to_char( sysdate, 'yyyymmdd' ) || '.log', 'a' );
utl_file.put_line( fi, ' ****** calling usp_getEmpByDept begin at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.put_line( fi, ' INPUT:' );
utl_file.put_line( fi, ' in_chID =>' || in_chID );
end if;
open curEmp for
select empno,
ename
from scott.emp
where deptno = in_deptNo;
if( pkg_const.DEBUG ) then
utl_file.put_line( fi, ' RETURN:' );
utl_file.put_line( fi, ' out_curEmp: unknown' );
utl_file.put_line( fi, ' ****** usp_getEmpByDept end at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
exception
when others then
if( pkg_const.DEBUG ) then
if( utl_file.is_open( fi )) then
utl_file.put_line( fi, ' ERROR:' );
utl_file.put_line( fi, ' sqlcode = ' || sqlcode );
utl_file.put_line( fi, ' sqlerrm = ' || sqlerrm );
utl_file.put_line( fi, ' ****** usp_getEmpByDept end at ' || to_char( sysdate, 'hh24:mi:ss mm-dd-yyyy' ) || ' ******' );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
end if;
/* Raise the exception for caller. */
raise_application_error( -20001, sqlcode || '|' || sqlerrm );
end usp_getEmpByDept;
在上面的代码中，我们又引用了两个新的常量：
DEBUG
LOG_PATH
分别定义了调试开关参数和文件路径参数，对此，我们需要修改我们前面定义的程序包：
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
DEBUG constant boolean := true;
LOG_PATH constant varchar2(256) := '/usr/tmp/db';
end pkg_const;
在代码块的起始处，将输入参数的名称与值成对的记入log文件，在代码块的正常退出部分，将输出参数的名称和数值也成对的记录下来，如果程序非正常退出，则在exception 的处理部分，把错误代码及错误信息写入log文件，
一般使用这些信息就可以较迅速的找出程序运行中出现的大部分错误。
注意：如果返回参数的类型是cursor，是无法在存储过程内部将返回的结果集一条一条写入log文件的，此时应当结合在调用程序中记录的log信息，下面具体分析一下上述代码：
fopen() 函数使用给定的路径和文件名，新建文件或者打开已有的文件，这取决于最后一个参数, 当使用'a'作为参数时，如果给定的文件不存在，则以此文件名新建文件，并以写'w'方式打开，返回一个文件句柄。
上面代码以天为单位建立日志文件，并且，不同存储过程之间共享log文件，这种方式的优点是可能通过查看log文件追溯出程序的调用顺序和逻辑。实际应用中，应根据不同的需求，具体分析，可以使用更复杂的log文件生成策略。
put_line() 函数用于写入字符到文件，并在字符串的结尾加入换行符，若不想换行，使用put()函数。
new_line() 函数用于生成指定数目的空行，上面对文件的修改写在一个缓冲区内，执行fflush() 将立即将buffer中的内容写入文件，当你希望在文件还未关闭之前就需要读取已经作出的改变时，调用此函数。
is_open() 函数用于判断一个文件句柄的状态，最后用完一定记得把打开的文件关闭，调用fclose() 函数，并且应把这个语句加入exception的处理中，防止过程非正常退出时留下未关闭的文件句柄。
捕获违例
在PLSQL中，你可以通过两个内建的函数sqlcode 和sqlerrm 来找出发生了哪类错误并且获得详细的message信息，在内部违例发生时，sqlcode返回从-1至－20000之间的一个错误号，但有一个例外，仅当内部违例no_data_found 发生时，才会返回一个正数 100。当用户自定义的违例发生时，sqlcode返回＋1，除非用户使用 pragma EXCEPTION_INIT 将自定义违例绑定一个自定义的错误号。当没有任何违例抛出时，sqlcode返回0。
下面是一个简单的捕获违例的例子：
declare
i number(3);
begin
select 100/0 into i from dual;
exception
when zero_divide then
...
end;
在上面的exception 中我们使用others 关键字捕获所有未明确指定的违例，并进行记录log处理，同时我们必须在做完这些处理之后，把违例再次抛出给调用程序，调用函数：
raise_application_error()，此函数向调用程序返回一个用户自定义的错误号码和错误信息，第一个参数指定一个错误号码，由用户自行定义，但必须限定在-20000至－20999之间，避免与Oracle内部定义exception的错误号码冲突，第二个参数需要返回一个字符串，这里我们使用它返回我们上面捕获的错误号码和错误描述。
注意：通过raise_application_error()函数抛出的违例已经不是开始在程序块内部捕获的内部违例，而是由用户自己定义的。
