Hello,
Sorry about the late reply. For some reason, I thought that I'd be receiving an email when the post is updated and so haven't been checking the forum.
Anyway, here is the sample code as requetsed, please note that this happens only if the parameters are of the type IN OUT NOCOPY:-
Oracle package PKGanapa.pck:
CREATE OR REPLACE PACKAGE pkGanapa
AS
PROCEDURE InsertEmployeeRow(iParticipantRow IN OUT NOCOPY Scott.Emp%ROWTYPE);
PROCEDURE InsertDepartmentRow(iPersonRow IN OUT NOCOPY Scott.Dept%ROWTYPE);
END pkGanapa;
/
CREATE OR REPLACE PACKAGE BODY pkGanapa
AS
PROCEDURE InsertEmployeeRow(iParticipantRow IN OUT NOCOPY Scott.Emp%ROWTYPE)
AS
BEGIN
NULL;
END;
PROCEDURE InsertDepartmentRow(iPersonRow IN OUT NOCOPY Scott.Dept%ROWTYPE)
AS
BEGIN
NULL;
END;
END pkGanapa;
/
Package Wrapper created using package Wizard:
unit Pkganapa;
// Oracle Package Wizard 1.0.3
// File Pkganapa.pas generated by kganapa on 10/19/2006 10:19:25 AM
// This unit contains interface objects for oracle package FSASYSTEM.PKGANAPA
// WARNING: Modifications made to this file will be lost after regeneration!
interface
uses Classes, SysUtils, Oracle;
type
PkganapaNocopy = class(TPLSQLRecord)
public
Deptno: Variant;
Dname: Variant;
Loc: Variant;
procedure Assign(Source: TPLSQLRecord); override;
end;
(*
PACKAGE pkGanapa
AS
PROCEDURE InsertEmployeeRow(iEmployeeRow IN OUT NOCOPY Scott.Emp%ROWTYPE);
PROCEDURE InsertDepartmentRow(iDepartmentRow IN OUT NOCOPY Scott.Dept%ROWTYPE);
END pkGanapa;
*)
TPkganapa = class(TOracleCustomPackage)
public
procedure Insertdepartmentrow(var AIdepartmentrow: PkganapaNocopy);
procedure Insertemployeerow(var AIemployeerow: PkganapaNocopy);
published
property Name;
property Session;
property Cursor;
end;
var
DefaultPLSQLTableSize: Integer = 100; // Default size of a PL/SQL Table
implementation
procedure PkganapaNocopy.Assign(Source: TPLSQLRecord);
begin
inherited;
with Source as PkganapaNocopy do
begin
Self.Deptno := Deptno;
Self.Dname := Dname;
Self.Loc := Loc;
end;
end;
// PKGANAPA.INSERTDEPARTMENTROW
procedure TPkganapa.Insertdepartmentrow(var AIdepartmentrow: PkganapaNocopy);
begin
GetQuery;
OCPQuery.DeclareVariable('record_var2', otInteger);
OCPQuery.SetVariable('record_var2', AIdepartmentrow.Deptno);
OCPQuery.DeclareVariable('record_var3', otString);
OCPQuery.SetVariable('record_var3', AIdepartmentrow.Dname);
OCPQuery.DeclareVariable('record_var4', otString);
OCPQuery.SetVariable('record_var4', AIdepartmentrow.Loc);
OCPQuery.SQL.Add('declare');
OCPQuery.SQL.Add(' IDEPARTMENTROW "PKGANAPA".NOCOPY;');
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' IDEPARTMENTROW.DEPTNO := :record_var2;');
OCPQuery.SQL.Add(' IDEPARTMENTROW.DNAME := :record_var3;');
OCPQuery.SQL.Add(' IDEPARTMENTROW.LOC := :record_var4;');
OCPQuery.SQL.Add(' "FSASYSTEM"."PKGANAPA"."INSERTDEPARTMENTROW"(IDEPARTMENTROW => IDEPARTMENTROW);');
OCPQuery.SQL.Add(' :record_var2 := IDEPARTMENTROW.DEPTNO;');
OCPQuery.SQL.Add(' :record_var3 := IDEPARTMENTROW.DNAME;');
OCPQuery.SQL.Add(' :record_var4 := IDEPARTMENTROW.LOC;');
OCPQuery.SQL.Add('end;');
OCPQuery.Execute;
AIdepartmentrow.Deptno := OCPQuery.GetVariable('record_var2');
AIdepartmentrow.Dname := OCPQuery.GetVariable('record_var3');
AIdepartmentrow.Loc := OCPQuery.GetVariable('record_var4');
end;
// PKGANAPA.INSERTEMPLOYEEROW
procedure TPkganapa.Insertemployeerow(var AIemployeerow: PkganapaNocopy);
begin
GetQuery;
OCPQuery.DeclareVariable('record_var2', otInteger);
OCPQuery.SetVariable('record_var2', AIemployeerow.Empno);
OCPQuery.DeclareVariable('record_var3', otString);
OCPQuery.SetVariable('record_var3', AIemployeerow.Ename);
OCPQuery.DeclareVariable('record_var4', otString);
OCPQuery.SetVariable('record_var4', AIemployeerow.Job);
OCPQuery.DeclareVariable('record_var5', otInteger);
OCPQuery.SetVariable('record_var5', AIemployeerow.Mgr);
OCPQuery.DeclareVariable('record_var6', otDate);
OCPQuery.SetVariable('record_var6', AIemployeerow.Hiredate);
OCPQuery.DeclareVariable('record_var7', otFloat);
OCPQuery.SetVariable('record_var7', AIemployeerow.Sal);
OCPQuery.DeclareVariable('record_var8', otFloat);
OCPQuery.SetVariable('record_var8', AIemployeerow.Comm);
OCPQuery.DeclareVariable('record_var9', otInteger);
OCPQuery.SetVariable('record_var9', AIemployeerow.Deptno);
OCPQuery.SQL.Add('declare');
OCPQuery.SQL.Add(' IEMPLOYEEROW "PKGANAPA".NOCOPY;');
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' IEMPLOYEEROW.EMPNO := :record_var2;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.ENAME := :record_var3;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.JOB := :record_var4;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.MGR := :record_var5;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.HIREDATE := :record_var6;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.SAL := :record_var7;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.COMM := :record_var8;');
OCPQuery.SQL.Add(' IEMPLOYEEROW.DEPTNO := :record_var9;');
OCPQuery.SQL.Add(' "FSASYSTEM"."PKGANAPA"."INSERTEMPLOYEEROW"(IEMPLOYEEROW => IEMPLOYEEROW);');
OCPQuery.SQL.Add(' :record_var2 := IEMPLOYEEROW.EMPNO;');
OCPQuery.SQL.Add(' :record_var3 := IEMPLOYEEROW.ENAME;');
OCPQuery.SQL.Add(' :record_var4 := IEMPLOYEEROW.JOB;');
OCPQuery.SQL.Add(' :record_var5 := IEMPLOYEEROW.MGR;');
OCPQuery.SQL.Add(' :record_var6 := IEMPLOYEEROW.HIREDATE;');
OCPQuery.SQL.Add(' :record_var7 := IEMPLOYEEROW.SAL;');
OCPQuery.SQL.Add(' :record_var8 := IEMPLOYEEROW.COMM;');
OCPQuery.SQL.Add(' :record_var9 := IEMPLOYEEROW.DEPTNO;');
OCPQuery.SQL.Add('end;');
OCPQuery.Execute;
AIemployeerow.Empno := OCPQuery.GetVariable('record_var2');
AIemployeerow.Ename := OCPQuery.GetVariable('record_var3');
AIemployeerow.Job := OCPQuery.GetVariable('record_var4');
AIemployeerow.Mgr := OCPQuery.GetVariable('record_var5');
AIemployeerow.Hiredate := OCPQuery.GetVariable('record_var6');
AIemployeerow.Sal := OCPQuery.GetVariable('record_var7');
AIemployeerow.Comm := OCPQuery.GetVariable('record_var8');
AIemployeerow.Deptno := OCPQuery.GetVariable('record_var9');
end;
end.
Let me know if you need any other information, sorry it turned out a huge post.
I have one more question regarding the OCPQuery that's generated by the package wizard, shouldn't there be OCPQuery.Close added automatically after the Execute and once return values are assigned? The reason I ask is it's leaving a bunch of open cursors which will go away only when the user logs off (session destroyed). I can't add code to the pacakge wrapper since the changes will be lost once it's regenerated. I'd really appreciate more information on this.
Thanks for your time,
Kavitha
