Hello,
I would say I rather new to DOA and Oracle so appologies if I make trivial error. Also sorry for long post.
I get error when fetching result dataset from packaged stored procedure.
I use package wizard and it generated unit with procedure I require:
------
// P_TEST.S_TESTAS
procedure TPTest.STestas(const InAaa: string; out Rez: Double;
out RsTestas: TOracleQuery);
begin
RsTestas := TOracleQuery.Create(nil);
RsTestas.Session := Session;
try
GetQuery;
OCPQuery.DeclareVariable('IN_AAA', otString);
OCPQuery.SetVariable('IN_AAA', InAaa);
OCPQuery.DeclareVariable('REZ', otFloat);
OCPQuery.DeclareVariable('RS_TESTAS', otCursor);
OCPQuery.SetComplexVariable('RS_TESTAS', RsTestas);
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' "P_TEST"."S_TESTAS"(');
OCPQuery.SQL.Add(' IN_AAA => :IN_AAA,');
OCPQuery.SQL.Add(' REZ => :REZ,');
OCPQuery.SQL.Add(' RS_TESTAS => :RS_TESTAS);');
OCPQuery.SQL.Add('end;');
OCPQuery.Execute;
Rez := ConvertVariant(OCPQuery.GetVariable('REZ'));
except
RsTestas.Free;
raise;
end;
end;
----------
I try to retrieve cursor from delphi 6.0 procedure:
------
procedure TForm1.Button2Click(Sender: TObject);
var i:double;
begin
ptest1 := Tptest.Create(nil);
ptest1.Session := oraclesession1;
ptest1.STestas('a',i,oraclequery1);
memo1.Lines.add('sql:'+oraclequery1.SQL.Text+';');
oraclequery1.Execute;
while not oraclequery1.Eof do begin
memo1.Lines.add(floattostr(i));
oraclequery1.Next; end;
//oraclequery1.Free;
ptest1.Free;
end;
-----------
Everything seems as if taken from DOA help - it is
Everything works quite ok up to a point where I try oraclequery1.execute. I even get a correct non-cursor return value from calling STESTAS. However oraclequery1.execute produces "ORA-01722 Invalid number".
Oraclequery1 and oraclesession1 are components which i added to project from IDE
As this is really "book stuff" I can only guess that either DOA or oracle procedure itself has error. Procedure basically searches all records which have substring occurance in them and defined as follows:
PROCEDURE S_TESTAS
(in_AAA IN TESTAS.AAA%TYPE,
REZ OUT NUMBER,
RS_TESTAS OUT cr);
What could be wrong?
I would say I rather new to DOA and Oracle so appologies if I make trivial error. Also sorry for long post.
I get error when fetching result dataset from packaged stored procedure.
I use package wizard and it generated unit with procedure I require:
------
// P_TEST.S_TESTAS
procedure TPTest.STestas(const InAaa: string; out Rez: Double;
out RsTestas: TOracleQuery);
begin
RsTestas := TOracleQuery.Create(nil);
RsTestas.Session := Session;
try
GetQuery;
OCPQuery.DeclareVariable('IN_AAA', otString);
OCPQuery.SetVariable('IN_AAA', InAaa);
OCPQuery.DeclareVariable('REZ', otFloat);
OCPQuery.DeclareVariable('RS_TESTAS', otCursor);
OCPQuery.SetComplexVariable('RS_TESTAS', RsTestas);
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' "P_TEST"."S_TESTAS"(');
OCPQuery.SQL.Add(' IN_AAA => :IN_AAA,');
OCPQuery.SQL.Add(' REZ => :REZ,');
OCPQuery.SQL.Add(' RS_TESTAS => :RS_TESTAS);');
OCPQuery.SQL.Add('end;');
OCPQuery.Execute;
Rez := ConvertVariant(OCPQuery.GetVariable('REZ'));
except
RsTestas.Free;
raise;
end;
end;
----------
I try to retrieve cursor from delphi 6.0 procedure:
------
procedure TForm1.Button2Click(Sender: TObject);
var i:double;
begin
ptest1 := Tptest.Create(nil);
ptest1.Session := oraclesession1;
ptest1.STestas('a',i,oraclequery1);
memo1.Lines.add('sql:'+oraclequery1.SQL.Text+';');
oraclequery1.Execute;
while not oraclequery1.Eof do begin
memo1.Lines.add(floattostr(i));
oraclequery1.Next; end;
//oraclequery1.Free;
ptest1.Free;
end;
-----------
Everything seems as if taken from DOA help - it is
Everything works quite ok up to a point where I try oraclequery1.execute. I even get a correct non-cursor return value from calling STESTAS. However oraclequery1.execute produces "ORA-01722 Invalid number".
Oraclequery1 and oraclesession1 are components which i added to project from IDE
As this is really "book stuff" I can only guess that either DOA or oracle procedure itself has error. Procedure basically searches all records which have substring occurance in them and defined as follows:
PROCEDURE S_TESTAS
(in_AAA IN TESTAS.AAA%TYPE,
REZ OUT NUMBER,
RS_TESTAS OUT cr);
What could be wrong?
