Devart Forums

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

Component TORASQL,

ORASQL.sql.text

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

Parameters
:Nom Integer IN/OUT PL/SQL Table Length=10
:NameA1 String IN/OUT Size=20 PL/SQL Table Length=10
:NameA2 String IN/OUT Size=20 PL/SQL Table Length=10

and it's all right and after ORASQL.Execute;
I can get result by example in

ORASQL.ParamByName('Nom').ItemAsInteger;
ORASQL.ParamByName('NameA1').ItemAsString;
ORASQL.ParamByName('NameA2').ItemAsString;

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

previously I have create 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 ORASQL.sql.text have I to write using construction (select * bulk collect into...)

and what Parametr have I to define???

Something like ....

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

Parameter :NameB IN/OUT DataType Object IN/OUT

or something like ....

declare
dept_o_tab dept_nt:=dept_nt();
begin
select dept_ot (deptno, dname, loc)
bulk collect into dept_o_tab from dept ;
end;

or may be bu means of procedure

create or replace procedure deptout(dept_tab IN out dept_nt) is
begin
select dept_ot(deptno, dname, loc)
bulk collect into dept_tab from dept;
end;

and ORASQL.sql.text like...

begin
deptout(:NameB);
end;

You should set the DataType property of the NameB parameter to ftTable. You should also call the AllocObject method of the TOraNestTable object contained in the parameter before calling the Execute method of the TOraSQL component.

Sorry, but something is impossible to understand ....

ORASQL.sql.text

begin
select * bulk collect into :NameB from dept
where RowNum

May be criminal is in ????
but I don't khow how to set it
into Params option Size is inaccessible....

and as result - Error

Project PLSQLTable.exe raised exception class Exception with message 'Data type is not supported.'.
Process stopped. Use .........

__________________

where dept_nt ....

create or replace type dept_nt is table of dept_ot

where dept_ot ...

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

You should set the Table property of the NameB parameter to False ('PL/SQL Table' check bok in design-time parameters editor).

More details, please...
ORASQL
Params
Parameter Name - NameB
Data Type - Table
Param Type IN/OUT
There are no available check boxes...
'PL/SQL Table' check bok in design-time parameters editor also inaccessible....
May be I have old version of ODAC??? ver. 4.10

This error may be shown if you set the Net option of TOraSession component to True. Object and Nested table types are supported only for OCI mode (Net option is set to False).

No, Net option is set to False, Net check box is empty and is inaccessible.
It use OCI mode. Somting wrong is clear in Debug message
begin
select * bulk collect into :NameB from dept
where RowNum <--!!!!?????

This debug message is correct. You should change the SQL property of the TOraSQL component to the following: We could not reproduce the problem with exception 'Data type is not supported'. Please send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create server objects.
Also supply us the following information
- exact version of Delphi;
- exact version of ODAC (see Oracle | About ODAC in Delphi menu);
- exact version of Oracle server and client. You can see it in the Info sheet of TOraSession Editor.

Sorry I mix OCI and Server versions
Exaption with message 'Data type is not supported'
arise if
OCI: Version 8.1.7.0.0
Oracle: Oracle Database 10g Enterprise Edition 10.2.0.1.0
and if
OCI: Version 8.1.7.0.0
Oracle: Oracle8i Enterprise Edition 8.1.7.4.1
then Exaption does not arise and

OraSQL.Execute end successfully
and then another question....
How after
OraSQL.ParamByName('NameB').AsObject.AllocObject(OraSession.OCISvcCtx, 'DEPT_NT');
OraSQL.Execute;
I can get access to elements of NameB ???

OraSQL. ?????

______________________
Scripts to create server objects

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

create or replace type dept_nt is table of dept_ot;

You can use the ItemAsObject property of TOraNestTable and the AttrAs... property of TOraObject.

Thank you very much, it works
>Name := OraSQL.ParamByName('NameB').AsTable.ItemAsObject[0].AttrAsString['DNAME'];

but may be exists variant without object commponent name - 'deptno', 'dname', 'loc'
and with numeric indication
as something[0] , something[1] ....????

ODAC supports access to attributes of the Object only by attribute name.

Thank you, I see....
and it is to be regretted that
Net option of TOraSession component may not be set to True.
Thank you very much.

Sorry, another question...
in expression see higher
OraSQL.ParamByName('NameB').AsTable.AllocObject(OraSession.OCISvcCtx, 'DEPT_NT');
I want use type DEPT_NT specified in package Pack1,
how I have to change this expression
OraSQL.ParamByName('NameB').AsTable.AllocObject(OraSession.OCISvcCtx, 'Pack1.DEPT_NT');
and
OraSQL.ParamByName('NameB').AsTable.AllocObject(OraSession.OCISvcCtx, 'Pack1'.'DEPT_NT');
both are wrong.......

ODAC supports only nested table types that are created in the database using the CREATE TYPE statement.