ORA-00016

[zzzato]

Hi, all. I'm using d7e and trial driver.
(I MUST check all needed features before ask my boss to buy devart drivers...)

Calling a stored procedure using DevartOracle driver I've error in subject.
I've this error on 2 different oracle servers:

a) 10g 32 bit running on winxp sp2
b) 11g 64 bit running on win2008

TIA

[zzzato]

Using "oracle direct" my test application works.
Obviously, I need to do a lot of additional stress test....

[AlexP]

Hello,

Please send the script for creating the procedure and a sample of calling it in Delphi, in order that we can try to reproduce and fix the problem.

[zzzato]

Good news.

my proc has date parameter.

*** using DIRECT CONNECTION:
if I set this date parameter with ParamByName('ADateAndTime).AsSqlTimeStamp, procedure is called succesfully and dateandtime field has correct value : perfect!

if I set this date parameter with ParamByName('ADateAndTime).AsDATETIME, procedure is called succesfully BUT field has NOT correctvalue. I set now and on database I see 3839-12-12.. IT'S NO GOOD.

*** using OCI CONNECTION:
if I set this date parameter with ParamByName('ADateAndTime).AsSqlTimeStamp, procedure is called succesfully and dateandtime field has correct value: perfect!

if I set this date parameter with ParamByName('ADateAndTime).AsDATETIME, procedure call fails and Oracle returns error ora-00016.


So:
to set DATE params I need to use AsSqlTimeStamp insthead AsDate/AsTime/AsDateTime.

I can't see some button to add attachment. I put sql code and delphi code in this post.

---------------------------
Sql:
---------------------------
CREATE TABLE MYTABLE(
RECORDID NUMBER(10) NOT NULL,
CODE VARCHAR(10) NOT NULL,
DATEANDTIME DATE NOT NULL,
COUNTER NUMBER(10) NOT NULL);

CREATE SEQUENCE myseq
START WITH 1
MAXVALUE 9999999999
MINVALUE 1
CYCLE
CACHE 20
NOORDER;



CREATE OR REPLACE PROCEDURE SP_MYTABLE_UPD(
amode NUMBER,
aRECORDID IN OUT NUMBER,
aCode varchar2,
adateandtime date,
acounter out number,
aOutr OUT VARCHAR2
)
is
pId number;


BEGIN
aOutR := NULL;


IF aMode = 0 THEN --Aggiunta



SELECT myseq.NEXTVAL into pId from dual;
INSERT INTO mytable(
recordid, code, dateandtime, counter)
VALUES (
pId, acode, adateandtime, 0);

arecordID := pId;
COMMIT;
aoutr := '1';


ELSIF amode = 1 THEN
UPDATE mytable
set COde = acode, DateAndTime = aDateAndTime
WHERE
recordID = arecordid;

aoutr := '1';

ELSIF amode = 2 THEN

delete from mytable
WHERE
recordID = arecordid;


aoutr := '1';


end if;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
aoutr := SQLERRM;
rollback;
END SP_MYTABLE_UPD;
/

--------------------------------------------
Delphi Code - unit1.dfm
--------------------------------------------
object Form1: TForm1
Left = 317
Top = 107
Width = 928
Height = 480
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object Memo1: TMemo
Left = 0
Top = 264
Width = 920
Height = 189
Align = alBottom
Lines.Strings = (
'Memo1')
TabOrder = 0
end
object rdgDriverType: TRadioGroup
Left = 8
Top = 8
Width = 177
Height = 89
Caption = 'DriverType'
Items.Strings = (
'Direct'
'OCI')
TabOrder = 1
OnClick = rdgDriverTypeClick
end
object rdgDateMode: TRadioGroup
Left = 187
Top = 9
Width = 177
Height = 89
Caption = 'Value type for date'
Items.Strings = (
'SQLTimeStamp'
'Delphi TdateTime')
TabOrder = 2
end
object GroupBox1: TGroupBox
Left = 360
Top = 8
Width = 489
Height = 97
Caption = 'GroupBox1'
TabOrder = 3
object Label1: TLabel
Left = 14
Top = 72
Width = 43
Height = 13
Caption = 'NEW ID:'
end
object edtCode: TEdit
Left = 8
Top = 32
Width = 121
Height = 21
TabOrder = 0
end
object edtDateTime: TDateTimePicker
Left = 144
Top = 32
Width = 121
Height = 21
Date = 41141.650605069440000000
Time = 41141.650605069440000000
TabOrder = 1
end
object Button1: TButton
Left = 272
Top = 32
Width = 129
Height = 25
Caption = 'INSERT'
TabOrder = 2
OnClick = Button1Click
end
object edtNewId: TEdit
Left = 64
Top = 64
Width = 121
Height = 21
TabOrder = 3
end
end
object GroupBox3: TGroupBox
Left = 360
Top = 112
Width = 489
Height = 97
Caption = 'update'
TabOrder = 4
object Label3: TLabel
Left = 6
Top = 24
Width = 11
Height = 13
Caption = 'ID'
end
object Label6: TLabel
Left = 222
Top = 16
Width = 24
Height = 13
Caption = 'code'
end
object Label7: TLabel
Left = 358
Top = 16
Width = 66
Height = 13
Caption = 'Date and time'
end
object edtNewCode: TEdit
Left = 224
Top = 32
Width = 121
Height = 21
TabOrder = 0
Text = 'edtNewCode'
end
object edtNewDate: TDateTimePicker
Left = 360
Top = 32
Width = 121
Height = 21
Date = 41141.650605069440000000
Time = 41141.650605069440000000
TabOrder = 1
end
object Button3: TButton
Left = 352
Top = 64
Width = 129
Height = 25
Caption = 'UPDATE'
TabOrder = 2
OnClick = Button3Click
end
object edtIdForUpdate: TEdit
Left = 8
Top = 40
Width = 121
Height = 21
TabOrder = 3
end
end
object OCIConnection: TSQLConnection
DriverName = 'DevartOracle'
GetDriverFunc = 'getSQLDriverORA'
LibraryName = 'dbexpoda.dll'
LoginPrompt = False
Params.Strings = (
'BlobSize=-1'
'DataBase=mergellina'
'ErrorResourceFile='
'LocaleCode=0000'
'Password=orlando'
'Oracle TransIsolation=ReadCommitted'
'User_Name=orlando')
VendorLib = 'OCI.DLL'
Left = 744
Top = 264
end
object myproc1: TSQLStoredProc
MaxBlobSize = -1
Params = <
item
DataType = ftBCD
Name = 'AMODE'
ParamType = ptInput
end
item
DataType = ftBCD
Name = 'ARECORDID'
ParamType = ptInputOutput
Size = 34
end
item
DataType = ftString
Name = 'ACODE'
ParamType = ptInput
end
item
DataType = ftTimeStamp
Name = 'ADATEANDTIME'
ParamType = ptInput
end
item
DataType = ftBCD
Name = 'ACOUNTER'
ParamType = ptOutput
Size = 34
end
item
DataType = ftString
Name = 'AOUTR'
ParamType = ptOutput
Size = 4000
end>
SQLConnection = DirectConnection
StoredProcName = 'orlando.SP_MYTABLE_UPD'
Left = 56
Top = 136
end
object DirectConnection: TSQLConnection
DriverName = 'DevartOracleDirect'
GetDriverFunc = 'getSQLDriverORADirect'
LibraryName = 'dbexpoda.dll'
LoginPrompt = False
Params.Strings = (
'BlobSize=-1'
'DataBase=mergellina:1521:mergellina'
'ErrorResourceFile='
'LocaleCode=0000'
'Password=orlando'
'Oracle TransIsolation=ReadCommitted'
'User_Name=orlando')
VendorLib = 'dbexpoda.dll'
Left = 280
Top = 128
end
object myproc2: TSQLStoredProc
MaxBlobSize = -1
Params = <
item
DataType = ftBCD
Name = 'AMODE'
ParamType = ptInput
end
item
DataType = ftBCD
Name = 'ARECORDID'
ParamType = ptInputOutput
Size = 34
end
item
DataType = ftString
Name = 'ACODE'
ParamType = ptInput
end
item
DataType = ftTimeStamp
Name = 'ADATEANDTIME'
ParamType = ptInput
end
item
DataType = ftBCD
Name = 'ACOUNTER'
ParamType = ptOutput
Size = 34
end
item
DataType = ftString
Name = 'AOUTR'
ParamType = ptOutput
Size = 4000
end>
SQLConnection = OCIConnection
StoredProcName = 'orlando.SP_MYTABLE_UPD'
Left = 192
Top = 136
end
end
--------------------------------------------
delphi code - unit1.pas
--------------------------------------------
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DBXpress, FMTBcd, DB, SqlExpr, StdCtrls, ComCtrls,
SqlTimSt, ExtCtrls;

type
TForm1 = class(TForm)
OCIConnection: TSQLConnection;
myproc1: TSQLStoredProc;
Memo1: TMemo;
DirectConnection: TSQLConnection;
rdgDriverType: TRadioGroup;
rdgDateMode: TRadioGroup;
myproc2: TSQLStoredProc;
GroupBox1: TGroupBox;
edtCode: TEdit;
edtDateTime: TDateTimePicker;
Button1: TButton;
Label1: TLabel;
edtNewId: TEdit;
GroupBox3: TGroupBox;
Label3: TLabel;
Label6: TLabel;
Label7: TLabel;
edtNewDate: TDateTimePicker;
Button3: TButton;
edtIdForUpdate: TEdit;
edtNewCode: TEdit;
procedure Button1Click(Sender: TObject);
procedure rdgDriverTypeClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Button3Click(Sender: TObject);
private
procedure ExecuteProc(Aproc: TSqlstoredProc);
procedure ExecuteProc2(Aproc: TSqlstoredProc);
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ExecuteProc(Aproc: TSqlstoredProc);
var
Idx: integer;

begin

Idx := rdgDateMode.ItemIndex;

if (idx = -1) then
begin
exit;
end;

Aproc.ParamByName('aMode').AsBCD := 0; // <----insert into
Aproc.ParamByName('acode').AsString := edtCode.Text;





if idx = 0 then
begin
Aproc.ParamByName('adateandtime').AsSQLTimeStamp := dATEtIMETOSQLTIMESTAMP(edtDateTime.DateTime);
end
else
begin
Aproc.ParamByName('adateandtime').AsDate := edtDateTime.DateTime;
end;


try
Aproc.ExecProc;

memo1.Text := 'out is:' + Aproc.ParamByName('aoutr').AsString;
edtNewId.Text := Aproc.ParamByName('arecordid').AsString;

except
on E:Exception do
begin
memo1.Text := E.ClassName + ' ' + E.Message;
end;
end;


end;


procedure TForm1.ExecuteProc2(Aproc: TSqlstoredProc);
var
Idx: integer;

begin

Idx := rdgDateMode.ItemIndex;

if (idx = -1) then
begin
exit;
end;

Aproc.ParamByName('aMode').AsBCD := 1; // <----UPDATE SET..
Aproc.ParamByName('acode').AsString := edtNewCode.Text;


if idx = 0 then
begin
Aproc.ParamByName('adateandtime').AsSQLTimeStamp := dATEtIMETOSQLTIMESTAMP(edtNewDate.DateTime);
end
else
begin
Aproc.ParamByName('adateandtime').AsDate := edtNewDate.DateTime;
end;


try
Aproc.ExecProc;

memo1.Text := 'out is:' + Aproc.ParamByName('aoutr').AsString;

except
on E:Exception do
begin
memo1.Text := E.ClassName + ' ' + E.Message;
end;
end;


end;


procedure TForm1.Button1Click(Sender: TObject);
var
Idx: integer;
begin

idx := rdgDriverType.ItemIndex;
if (idx =-1) then
exit;

case Idx of
0: ExecuteProc(myproc1);
1: ExecuteProc(myproc2);
end;

end;

procedure TForm1.rdgDriverTypeClick(Sender: TObject);
var
idX: integer;

begin

Idx := TRadioGroup(Sender).ItemIndex;

case idx of
-1: ;

0: // direct
begin
OCIConnection.Connected := False;
DirectConnection.Connected := True;
end;

1:
begin
DirectConnection.Connected := False;
OCIConnection.Connected := True;
end;
end;




end;

procedure TForm1.FormCreate(Sender: TObject);
begin
rdgDateMode.ItemIndex := 0;
rdgDriverType.ItemIndex := 0;
end;

procedure TForm1.Button3Click(Sender: TObject);
var
Idx: integer;
begin

idx := rdgDriverType.ItemIndex;
if (idx =-1) then
exit;

case Idx of
0: ExecuteProc2(myproc1);
1: ExecuteProc2(myproc2);
end;

end;

end.

[AlexP]

hello,

Thank you for the information, we've reproduced the problem.
We will try to fix it in the nearest product version.
We will notify you as soon as we have any results.

[zzzato]

Thanks!
See u.

[AlexP]

Hello,

We have already fixed the problem.
These fix will be included in the next build.
The next build will be available next week.

[zzzato]

Your team is very fast..! It's cool!

Just a simple question...
Fix will allow to use AsDateTime
I use delphi since 1997 and I have never used Param.AsSqlTimeStamp...

Best.

[AlexP]

hello,

Yes, in the new version you'll be able to use AsDateTime in TSQLStoredProc