How can I use construction ( select * bulk collect into ...) with ODAC ???

shap · Post by **shap** » Tue 13 Mar 2007 12:25

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;

Plash · Post by **Plash** » Wed 14 Mar 2007 12:31

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.

Code: Select all

OraSQL.ParamByName('NameB').AsTable.AllocObject(OraSession.OCISvcCtx, 'DEPT_NT');

shap · Post by **shap** » Wed 14 Mar 2007 15:04

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))

Plash · Post by **Plash** » Thu 15 Mar 2007 08:52

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

shap · Post by **shap** » Thu 15 Mar 2007 11:39

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

Plash · Post by **Plash** » Thu 15 Mar 2007 14:04

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).

shap · Post by **shap** » Thu 15 Mar 2007 14:51

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 <--!!!!?????

Plash · Post by **Plash** » Fri 16 Mar 2007 10:22

This debug message is correct. You should change the SQL property of the TOraSQL component to the following:

Code: Select all

begin
select dept_ot(deptno, dname, loc) bulk collect into :NameB from dept
where RowNum<=10 order by DeptNo;
end;

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.

shap · Post by **shap** » Fri 16 Mar 2007 14:32

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;

Plash · Post by **Plash** » Mon 19 Mar 2007 08:12

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

Code: Select all

Name := OraSQL.ParamByName('NameB').AsTable.ItemAsObject[0].AttrAsString['DNAME'];

shap · Post by **shap** » Mon 19 Mar 2007 12:26

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] ....????

Plash · Post by **Plash** » Mon 19 Mar 2007 14:13

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

shap · Post by **shap** » Mon 19 Mar 2007 15:01

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.

shap · Post by **shap** » Wed 21 Mar 2007 08:07

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.......

Plash · Post by **Plash** » Wed 21 Mar 2007 09:57

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