recieving record as result of a function in package by Delphi2006

[ChristofK]

This is the Record:

TYPE t_ctldata01 IS RECORD (
ctlstr VARCHAR2 (200),
artno01 trace_su.t_sn01.artno01%TYPE,
orderno trace_su.t_sn01.orderno%TYPE
);

This is the function:

FUNCTION getctldata01 (p_panelno IN trace_su.t_sn01.panelno%TYPE,
p_status01 IN trace_su.t_status.status%TYPE,
p_status02 IN trace_su.t_status.status%TYPE,
p_status03 IN trace_su.t_status.status%TYPE,
p_status04 IN trace_su.t_status.status%TYPE)
RETURN t_ctldata01;

The following call in PL/SQL works fine:

DECLARE
RetVal trace_su.pkg_trace_t_ctl_l3.t_ctldata01;
P_PANELNO NUMBER;
P_STATUS01 CHAR(1);
P_STATUS02 CHAR(1);
P_STATUS03 CHAR(1);
P_STATUS04 CHAR(1);

BEGIN
P_PANELNO := 449836;
P_STATUS01 := 'g';
P_STATUS02 := 'o';
P_STATUS03 := NULL;
P_STATUS04 := NULL;

RetVal := TRACE_SU.PKG_TRACE_T_CTL_L3.GETCTLDATA01 ( P_PANELNO, P_STATUS01, P_STATUS02, P_STATUS03, P_STATUS04 );
DBMS_OUTPUT.put_line( retval.ctlstr);
DBMS_OUTPUT.put_line( retval.artno01);
DBMS_OUTPUT.put_line( retval.orderno);
COMMIT;
END;

OUTPUT is:

10101101
702058602N1
MA07027V0

Using DOA V 4.0.7.1, I build a package-dcu with package-wizzard, but calling the function, I get errors:
ORA-06550: line 2, colon 19:
PLS-00201: Identifier 'PKG_TRACE_T_CTL_L3.T_CTLDATA01' must be declared
.... the rest was ignored

(I use the package created by the wizzard, so the produced code should have errors)

but the identifier is declared, see following code:
Here is the package Delphi code:

******************************************
unit Pkg_Trace_T_Ctl_L3;

// Oracle Package Wizard 1.0.3
// File Pkg_Trace_T_Ctl_L3.pas generated by 000100005436 on 27.02.2009 11:13:25
// This unit contains interface objects for oracle package TRACE_SU.PKG_TRACE_T_CTL_L3
// WARNING: Modifications made to this file will be lost after regeneration!

interface

uses Classes, SysUtils, Oracle;

type
Pkg_Trace_T_Ctl_L3_Pkg_Trace_T_Ctl_L3_T_Ctlrec = class(TPLSQLRecord)
public
Sysid: string;
Panelno: Double;
Datetime: TDateTime;
Status: string;
procedure Assign(Source: TPLSQLRecord); override;
end;

Pkg_Trace_T_Ctl_L3_T_Ctldata01 = class(TPLSQLRecord)
public
Ctlstr: string;
Artno01: string;
Orderno: string;
procedure Assign(Source: TPLSQLRecord); override;
end;

TPkg_Trace_T_Ctl_L3 = class(TOracleCustomPackage)
public
function Getctldata01(P_Panelno: Double; const P_Status01: string;
const P_Status02: string; const P_Status03: string;
const P_Status04: string): Pkg_Trace_T_Ctl_L3_T_Ctldata01;
published
property Name;
property Session;
property Cursor;
property PackageSpecification;
end;

var
DefaultPLSQLTableSize: Integer = 100; // Default size of a PL/SQL Table

procedure Register;

implementation

procedure Pkg_Trace_T_Ctl_L3_Pkg_Trace_T_Ctl_L3_T_Ctlrec.Assign(Source: TPLSQLRecord);
begin
inherited;
with Source as Pkg_Trace_T_Ctl_L3_Pkg_Trace_T_Ctl_L3_T_Ctlrec do
begin
Self.Sysid := Sysid;
Self.Panelno := Panelno;
Self.Datetime := Datetime;
Self.Status := Status;
end;
end;

// PKG_TRACE_T_CTL_L3.GETCTLDATA01
function TPkg_Trace_T_Ctl_L3.Getctldata01(P_Panelno: Double; const P_Status01: string;
const P_Status02: string; const P_Status03: string;
const P_Status04: string): Pkg_Trace_T_Ctl_L3_T_Ctldata01;
begin
Result := Pkg_Trace_T_Ctl_L3_T_Ctldata01.Create(Session);
ThreadAcquire;
try
GetQuery;
OCPQuery.DeclareVariable('record_var2', otString);
OCPQuery.DeclareVariable('record_var3', otString);
OCPQuery.DeclareVariable('record_var4', otString);
OCPQuery.DeclareVariable('P_PANELNO', otFloat);
OCPQuery.SetVariable('P_PANELNO', P_Panelno);
OCPQuery.DeclareVariable('P_STATUS01', otString);
OCPQuery.SetVariable('P_STATUS01', P_Status01);
OCPQuery.DeclareVariable('P_STATUS02', otString);
OCPQuery.SetVariable('P_STATUS02', P_Status02);
OCPQuery.DeclareVariable('P_STATUS03', otString);
OCPQuery.SetVariable('P_STATUS03', P_Status03);
OCPQuery.DeclareVariable('P_STATUS04', otString);
OCPQuery.SetVariable('P_STATUS04', P_Status04);
OCPQuery.SQL.Add('declare');
OCPQuery.SQL.Add(' function_result "PKG_TRACE_T_CTL_L3".t_ctldata01;');
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' function_result := "TRACE_SU"."PKG_TRACE_T_CTL_L3"."GETCTLDATA01"(');
OCPQuery.SQL.Add(' P_PANELNO => _PANELNO,');
OCPQuery.SQL.Add(' P_STATUS01 => _STATUS01,');
OCPQuery.SQL.Add(' P_STATUS02 => _STATUS02,');
OCPQuery.SQL.Add(' P_STATUS03 => _STATUS03,');
OCPQuery.SQL.Add(' P_STATUS04 => _STATUS04);');
OCPQuery.SQL.Add(' :record_var2 := function_result.CTLSTR;');
OCPQuery.SQL.Add(' :record_var3 := function_result.ARTNO01;');
OCPQuery.SQL.Add(' :record_var4 := function_result.ORDERNO;');
OCPQuery.SQL.Add('end;');
OCPQuery.Execute;
Result.Ctlstr := ConvertVariant(OCPQuery.GetVariable('record_var2'));
Result.Artno01 := ConvertVariant(OCPQuery.GetVariable('record_var3'));
Result.Orderno := ConvertVariant(OCPQuery.GetVariable('record_var4'));
except
ThreadRelease;
Result.Free;
raise;
end;
ThreadRelease;
end;

procedure Register;
begin
RegisterComponents('Pkg_Prod_Ctl_L3', [TPkg_Trace_T_Ctl_L3]);
end;

end.
***************************************

Has anybody any idears?

Many thanks in advance

Christof Kihm

 

[Marco Kalter]

I notice you have included the owner in your PL/SQL example:

TRACE_SU.PKG_TRACE_T_CTL_L3.GETCTLDATA01()

The function result type declaration does not include the owner though. Does it help if you add the owner or if you create a synonym for the package?

 

[ChristofK]

Hi Marco,
adding the user brings no change. I tested as well the following function, which brings the same error as described:

try
if rbDB01.Checked then OracleSession1.LogonDatabase := edtDB01.Text
else if rbDB02.Checked then OracleSession1.LogonDatabase := edtDB02.Text;
OracleSession1.LogonUsername := edtUser.Text;
OracleSession1.LogonPassword := edtPasswd.Text;
OracleSession1.Connected := True;
Query.Clear;
Query.SQL.Add('DECLARE');
Query.SQL.Add('PANELNO NUMBER;');
Query.SQL.Add('STATUS01 CHAR (1);');
Query.SQL.Add('STATUS02 CHAR (1);');
Query.SQL.Add('STATUS03 CHAR (1);');
Query.SQL.Add('STATUS04 CHAR (1);');
Query.SQL.Add('function_result pkg_trace_t_ctl_l3.t_ctldata01;');
Query.SQL.Add('BEGIN');
Query.SQL.Add('PANELNO := 449836;');
Query.SQL.Add('STATUS01 := ''g'';');
Query.SQL.Add('STATUS02 := ''o'';');
Query.SQL.Add('STATUS03 := NULL;');
Query.SQL.Add('STATUS04 := NULL;');
Query.SQL.Add('function_result :=');
Query.SQL.Add('PKG_TRACE_T_CTL_L3.GETCTLDATA01 (');
Query.SQL.Add('P_PANELNO => PANELNO,');
Query.SQL.Add('P_STATUS01 => STATUS01,');
Query.SQL.Add('P_STATUS02 => STATUS02,');
Query.SQL.Add('P_STATUS03 => STATUS03,');
Query.SQL.Add('P_STATUS04 => STATUS04);');
Query.SQL.Add('DBMS_OUTPUT.put_line (function_result.CTLSTR); ');
Query.SQL.Add('DBMS_OUTPUT.put_line (function_result.ARTNO01);');
Query.SQL.Add('DBMS_OUTPUT.put_line (function_result.ORDERNO);');
Query.SQL.Add('END;');
Query.Execute;
OracleSession1.Connected := false;
except

I just took the SQL Script which works fine and sent it by a Query to the database. Same error as before.
What goes wrong? I have no more Idears at the moment

All the best, Christof

 

[Marco Kalter]

I still don't see the schema names in your example:

...
Query.SQL.Add('function_result pkg_trace_t_ctl_l3.t_ctldata01;');
...
Query.SQL.Add('PKG_TRACE_T_CTL_L3.GETCTLDATA01 (');
...

Change this to:

...
Query.SQL.Add('function_result TRACE_SU.pkg_trace_t_ctl_l3.t_ctldata01;');
...
Query.SQL.Add('TRACE_SU.PKG_TRACE_T_CTL_L3.GETCTLDATA01 (');
...

 

[ChristofK]

Hello Marco,

thanks a lot for your hint, now it works well.

By the way:
I told the Package Wizard to prefix database objects with schema name. Except the line after "declare"

OCPQuery.SQL.Add('declare');
OCPQuery.SQL.Add(' function_result "TRACE_SU"."PKG_TRACE_T_CTL_L3".t_ctldata;');

the Wizard follows this directive. I added this part of code by hand and it works.
Should I update to the next version, is it corrected there?

Thanks for your efforts,

Christof Kihm

 

[Marco Kalter]

It's not yet fixed, but I have added this to the list.

 

[ChristofK]

Thanks for the info!

Christof Kihm