How can I use construction select * bulk collect into ... with DOA ???

[shap]

There is a construction select * bulk collect into ... in Oracle PL/SQL
and I can use it with DOA like this...

Component TOracleQuery

OracleQuery.SQL

begin
select deptno, dname, loc bulk collect
into :Nom, :NameA1,  :NameA2
from dept3
WHERE RowNum <= 10 ORDER BY DeptNo;
end;

Variables

:NOM Integer PL/SQL Table  Table Size 25
:NameA1 String PL/SQL Table  Table Size 25 String Size 40
:NameA2 String PL/SQL Table  Table Size 25 String Size 40

var
k : integer;
DeptNoTable : Array of variant;
DeptNameTable : Array of variant;
DeptLocTable : Array of variant;

begin
  with OracleQuery do
  begin
    Execute;
    DeptNoTable:= GetVariable('Nom');
    DeptNameTable:= GetVariable('NameA1');
    DeptLocTable:= GetVariable('NameA2');
  end;

and then I can put results in Memo,
for example....

for k:=VarArrayLowBound(DeptNoTable,1) to VarArrayHighBound(DeptNoTable,1) do
begin
Memo.Lines.Add(IntToStr(DeptNoTable[k])+' '+DeptNameTable[k]+' '+DeptLocTable[k]);
end;
end;

But, if I want use one complex variable (record or table of record) instead of three simple as above,
I meet with failure, forexample ...

previously I have created a type

create or replace type dept2_ot as object (
deptno NUMBER(2),
dname  VARCHAR2(14),
loc    VARCHAR2(13))

and then previously I have create another type

create or replace type dept_nt is table of dept_ot

What OracleQuery.SQL have I to write using construction select * bulk collect into...

and what Variable have I to define and connect with created type :NameB with dept2_ot ???

Something like ....

begin
select * bulk collect
into :NameB
from dept
WHERE RowNum <= 10 ORDER BY DeptNo;
end;

Variable :NameB IN/OUT DataType Object or PL/SQL string

I try to use Package Wizard, but with out success.

Or may be create type like

type
dept_t = record
deptno: integer;
dname : string;
loc : string;
end;

then define array as

dept_arr: array[1..20] of dept_t;

but how to connect it with Variable :NameB in OracleQuery.SQL ???

 

[Marco Kalter]

You cannot pass record types or PL/SQL Tables of record types as bind variables. You can only use scalar data types or PL/SQL Tables of a scalar data type.

To pass multiple records of a compound data type, you can onlt use collection types like varray or nested table, which are represented by the TOracleObject in Direct Oracle Access.

 

[shap]

Give me links to example, please, if you know it.

 

[Marco Kalter]

The help file includes several examples.

 

[shap]

I do it so....., and it works...

procedure TfmMain.btGetAllClick(Sender: TObject);
var
i, nom, k : integer;
Str1, Str2 : string;
O_DEPT: TOracleObject;
begin
  O_DEPT := TOracleObject.Create(OracleSession, '"DEPT_NT"', '');
  OracleQuery.SetComplexVariable('NameB', O_DEPT);
  OracleQuery.Execute;
i:= O_DEPT.ElementCount;
Memo.Lines.Add(intToStr(i));
for  k:=0 to i-1 do
begin
nom:= O_DEPT.ObjElements[k].GetAttr('deptno');
Str1:= O_DEPT.ObjElements[k].GetAttr('dname');
Str2:= O_DEPT.ObjElements[k].GetAttr('loc');
Memo.Lines.Add(IntToStr(nom)+' '+Str1+' '+Str2);
end;
end;

but there is another question, how may I get access to object components without
using names of attributes 'deptno', 'dname', 'loc'
and using their index 0,1,2
by way of O_DEPT. ...[1] ????

 

[Marco Kalter]

You can use Attrs[Index].Value, and use the AttrCount property to determine the number of attributes.

 

[shap]

Thank you, it works, except AttrCount property, as O_DEPT.AttrCount = 1, may be because of using bulk collect in select ???
Nevertheless expression "O_DEPT.ObjElements[k].Attrs[j].Value " is very handy for using in reports creating.

 

[shap]

Sorry, another question...
in expression see higher
O_DEPT := TOracleObject.Create(OracleSession, '"DEPT_NT"', '');
I want use type DEPT_NT specified in package Pack1,
how I have to change this expression
O_DEPT := TOracleObject.Create(OracleSession, '"Pack1.DEPT_NT"', '');
and
O_DEPT := TOracleObject.Create(OracleSession, '"Pack1"."DEPT_NT"', '');

O_DEPT := TOracleObject.Create(OracleSession, '"Pack1"'.'"DEPT_NT"', '');
all are wrong.......

 

[Marco Kalter]

You cannot instantiate object types defined in a package. They must be created as standalone object types.