我在用dbExpress调用Oracle的存储过程返回数据集时遇到了奇怪的问题,请各位高手指教。
调试过程与现象如下:
1.建立包:
Create Or Replace Package ChargePKG
As
Type ChargeCursor Is REF Cursor;
End ChargePKG;
2.当存储过程为
Create Or Replace Procedure GetCarSortChargeData(Dep VARCHAR2,
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
End GetCarSortChargeData;
时,执行“SQLStoredProc1.ExecProc;”正常
3.当存储过程为
Create Or Replace Procedure GetCarSortChargeData(Dep Number,
ClassNO Number,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
End GetCarSortChargeData;
时(即包既具有数字型输入变量,又具有字符串型输入变量),执行“SQLStoredProc1.ExecProc;”时提示“BCD overflow”错误。
4.将存储过程改为
Create Or Replace Procedure GetCarSortChargeData(Dep VARCHAR2,
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2,
CarSortChargeCursor Out ChargePKG.ChargeCursor)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
Open CarSortChargeCursor For Select * From TempGratisCarTable;
End GetCarSortChargeData;
时(即在输入变量均为字符串型变量的基础上,增加了游标型输出变量),执行“SQLStoredProc1.Open;”时提示如下错误
ORA-06550:第1行,第1列;
PLS-00306:调用'GetCarSortChargeData'时参数个数或类型错误
ORA-06550:第1行,第1列;
PLS-00306:调用'GetCarSortChargeData'时参数个数或类型错误
ORA-06550:第1行,第1列;
PL/SQL:Statement ignored.
请大侠门指教是怎么回事?是dbexpress的bug吗?
调试过程与现象如下:
1.建立包:
Create Or Replace Package ChargePKG
As
Type ChargeCursor Is REF Cursor;
End ChargePKG;
2.当存储过程为
Create Or Replace Procedure GetCarSortChargeData(Dep VARCHAR2,
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
End GetCarSortChargeData;
时,执行“SQLStoredProc1.ExecProc;”正常
3.当存储过程为
Create Or Replace Procedure GetCarSortChargeData(Dep Number,
ClassNO Number,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
End GetCarSortChargeData;
时(即包既具有数字型输入变量,又具有字符串型输入变量),执行“SQLStoredProc1.ExecProc;”时提示“BCD overflow”错误。
4.将存储过程改为
Create Or Replace Procedure GetCarSortChargeData(Dep VARCHAR2,
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2,
CarSortChargeCursor Out ChargePKG.ChargeCursor)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
Open CarSortChargeCursor For Select * From TempGratisCarTable;
End GetCarSortChargeData;
时(即在输入变量均为字符串型变量的基础上,增加了游标型输出变量),执行“SQLStoredProc1.Open;”时提示如下错误
ORA-06550:第1行,第1列;
PLS-00306:调用'GetCarSortChargeData'时参数个数或类型错误
ORA-06550:第1行,第1列;
PLS-00306:调用'GetCarSortChargeData'时参数个数或类型错误
ORA-06550:第1行,第1列;
PL/SQL:Statement ignored.
请大侠门指教是怎么回事?是dbexpress的bug吗?
“参数个数或类型错误”
參數少了?參數類型是否正確,Oracle里面的各個參數和sql相同?我也不大清楚,自己查詢下
SQLStoredProc1.Close;
SQLStoredProc1.Params.Clear;
SQLStoredProc1.PackageName:='';
SQLStoredProc1.StoredProcName:='GETCARSORTCHARGEDATA';
SQLStoredProc1.Params.CreateParam(ftString,'Dep',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'ClassNO',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'MorningShiftOnDuty',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'MorningShiftOffDuty',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'MiddleShiftOnDuty',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'MiddleShiftOffDuty',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'NightShiftOnDuty',ptInput);
SQLStoredProc1.Params.CreateParam(ftString,'NightShiftOffduty',ptInput);
SQLStoredProc1.Params.CreateParam(ftCursor,'CARSORTCHARGECURSOR',ptOutput);
SQLStoredProc1.ParamByName('Dep').Value:='1';
SQLStoredProc1.ParamByName('ClassNO').Value:='2';
SQLStoredProc1.ParamByName('MorningShiftOnDuty').Value:='2008-8-30 8:55:00';
SQLStoredProc1.ParamByName('MorningShiftOffDuty').Value:='2008-9-29 16:5:00';
SQLStoredProc1.ParamByName('MiddleShiftOnDuty').Value:='2008-8-30 15:55:00';
SQLStoredProc1.ParamByName('MiddleShiftOffDuty').Value:='2008-9-30 00:05:00';
SQLStoredProc1.ParamByName('NightShiftOnDuty').Value:='2008-8-30 23:55:00';
SQLStoredProc1.ParamByName('NightShiftOffduty').Value:='2008-9-30 9:5:00';
SQLStoredProc1.Open;
这是一定的
DbExpress升级包http://www.delphifans.com/SoftView/SoftView_55.html
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2,
CarSortChargeCursor Out ChargePKG.ChargeCursor)
注意这个定义,既然用了包里面的类型,那就必要是在包当中.
我在包ChargePKG中声明的游标ChargeCursor是公有游标,在包外的存储过程是不是应该可以引用呢?在“http://topic.csdn.net/t/20031014/12/2353495.html”这个帖子就是这么做的。但我不知道为什么JCC0128大侠能成功,我却不能成功。郁闷!
3正常
4异常:改为下面的代码后,4正常:
Create Or Replace Package ChargePKG
As
Type ChargeCursor Is REF Cursor;
Procedure GetCarSortChargeData(Dep VARCHAR2,
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2,
CarSortChargeCursor Out ChargeCursor) ;
End ChargePKG;
/
CREATE OR REPLACE PACKAGE BODY ChargePKG AS
Procedure GetCarSortChargeData(Dep VARCHAR2,
ClassNO VARCHAR2,
MorningShiftOnDuty VARCHAR2,
MorningShiftOffDuty VARCHAR2,
MiddleShiftOnDuty VARCHAR2,
MiddleShiftOffDuty VARCHAR2,
NightShiftOnDuty VARCHAR2,
NightShiftOffduty VARCHAR2,
CarSortChargeCursor Out ChargeCursor)
Is
Begin
Insert Into TempGratisCarTable(CarID,CarTpye,CriterionWeight,WholeWeight) Values(1,2,3,4);
Open CarSortChargeCursor For Select * From TempGratisCarTable;
return;
End GetCarSortChargeData;
END ChargePKG;
我试过了,还是不行,错误提示是“Access violation at address 05F91A09 in module 'dbexpora.dll',write of address 00000000”。
恐怕不能用ADOConnection+ADOStoredProc了,我要移植到Kylix下。
正解!!
用到Cursor
就应该在包里进行定义!