Home » SQL & PL/SQL » SQL & PL/SQL » Accessing the multi dimensional collection in SQL Query (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Accessing the multi dimensional collection in SQL Query [message #607917] |
Thu, 13 February 2014 08:19  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi
I had a requirement that I need to access the value which is there multi dimensional type variable.
Example
-- first dimension
CREATE OR REPLACE TYPE SNUMTABLE IS TABLE OF NUMBER;
--Second dimension
CREATE OR REPLACE TYPE R_VAR_SNUMTABLE AS OBJECT
(
NAME1 VARCHAR2(50),
ADDR VARCHAR2(50),
NUM_LIST DBO.SNUMTABLE
);
--Third dimension
CREATE OR REPLACE TYPE T_VAR_SNUMTABLE IS TABLE OF R_VAR_SNUMTABLE;
Now I need to access the very first element of number list for the given name
SQL> declare
2 num1 dbo.snumtable := dbo.snumtable(10, 12, 14, 16);
3 num2 dbo.snumtable := dbo.snumtable(20, 22, 24, 26);
4
5 rec1 r_var_snumtable := r_var_snumtable('10 series', '10 addr', num1);
6 rec2 r_var_snumtable := r_var_snumtable('20 series', '20 addr', num2);
7
8 table1 t_var_snumtable := t_var_snumtable(rec1, rec2);
9
10 res_num1 number;
11 res_var1 varchar2(20);
12
13 begin
14
15 select addr
16 into res_var1
17 from table(cast(table1 as t_var_snumtable)) temp
18 where name1 = '10 series';
19
20 /*select num_list(1)
21 into res_num1
22 from table(cast(table1 as t_var_snumtable)) temp
23 where name1 = '10 series';*/
24
25 dbms_output.put_line('results :' || res_var1);
26
27 end;
28 /
results :10 addr
PL/SQL procedure successfully completed
I am able to get the addr value for the given name. Just like that I need the very first element of the num_list .
But the commented part is not working fine .
Please let me know how can I access that element ?
Thanks
Sai Pradyumn
|
|
|
|
| Re: Accessing the multi dimensional collection in SQL Query [message #607922 is a reply to message #607917] |
Thu, 13 February 2014 08:42  |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
The return value is of type snumtable, so try:
declare
num1 snumtable := snumtable(10, 12, 14, 16);
num2 snumtable := snumtable(20, 22, 24, 26);
rec1 r_var_snumtable := r_var_snumtable('10 series', '10 addr', num1);
rec2 r_var_snumtable := r_var_snumtable('20 series', '20 addr', num2);
table1 t_var_snumtable := t_var_snumtable(rec1, rec2);
res_num1 snumtable;
res_var1 varchar2(20);
begin
select addr
into res_var1
from table(cast(table1 as t_var_snumtable)) temp
where name1 = '10 series';
select num_list
into res_num1
from table(cast(table1 as t_var_snumtable)) temp
where name1 = '10 series';
dbms_output.put_line('results :' || res_var1);
dbms_output.put_line('results :' || res_num1(2));
end;
results :10 addr
results :12
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Accessing the multi dimensional collection in SQL Query [message #607941 is a reply to message #607922] |
Thu, 13 February 2014 12:16 |
|
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Thanks Jum
But my actual requirement was need to insert into database table .
DROP TABLE TEST_TABLE
-- Create table
create table TEST_TABLE
(
NAME1 VARCHAR2(50),
ADDR VARCHAR2(50),
FIRST_NUM_LIST_VALUE NUMBER(8),
INSERTED_BY VARCHAR2(20),
INSERTED_DATE DATE
);
I don't want to persist the total num list into the database .
I just want the very first value in the Snumtable .
declare
num1 dbo.snumtable := dbo.snumtable(10, 12, 14, 16);
num2 dbo.snumtable := dbo.snumtable(20, 22, 24, 26);
rec1 r_var_snumtable := r_var_snumtable('10 series', '10 addr', num1);
rec2 r_var_snumtable := r_var_snumtable('20 series', '20 addr', num2);
table1 t_var_snumtable := t_var_snumtable(rec1, rec2);
res_num1 number;
res_var1 varchar2(20);
begin
INSERT INTO TEST_TABLE
(NAME1, ADDR, FIRST_NUM_LIST_VALUE, INSERTED_BY, INSERTED_DATE)
SELECT NAME1, ADDR, /*NUM_LIST(1)*/ 20 , USER, SYSDATE
from table(cast(table1 as t_var_snumtable)) temp;
dbms_output.put_line('EFFCTED COUNT :' || SQL%ROWCOUNT);
end;
We can implement the same by iterating over the collection .
is there any way to go for the bulk insert just like above insert ?.
|
|
|
|
|
|
| Re: Accessing the multi dimensional collection in SQL Query [message #607990 is a reply to message #607966] |
Fri, 14 February 2014 01:28 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Don't understand your requirement, but formally ok is:
declare
num1 snumtable := snumtable(10, 12, 14, 16);
num2 snumtable := snumtable(20, 22, 24, 26);
rec1 r_var_snumtable := r_var_snumtable('10 series', '10 addr', num1);
rec2 r_var_snumtable := r_var_snumtable('20 series', '20 addr', num2);
table1 t_var_snumtable := t_var_snumtable(rec1, rec2);
BEGIN
INSERT INTO TEST_TABLE
(NAME1, ADDR, FIRST_NUM_LIST_VALUE, INSERTED_BY, INSERTED_DATE)
SELECT NAME1, ADDR, t2.column_value, USER, SYSDATE
FROM table(cast(table1 as t_var_snumtable)) t1, table(t1.num_list) t2
WHERE t1.name1 = '10 series'
-- AND rownum=1
;
dbms_output.put_line('EFFCTED COUNT :' || SQL%ROWCOUNT);
END;
Without rownum you INSERT the 4 SNUMS from rec1, With rownum only "the first" (?).
|
|
|
|
| Re: Accessing the multi dimensional collection in SQL Query [message #608001 is a reply to message #607990] |
Fri, 14 February 2014 02:20 |
|
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi Jum
If there is rownum restriction ,it will effect to the total result set .
But my requirement was for each record should contain only the first vale of the numlist
After insertion output of the table should be
SQL> select * from TEST_TABLE;
NAME1 ADDR FIRST_NUM_LIST_VALUE INSERTED_BY INSERTED_DATE
-------------- --------- -------------------- -------------- -------------
10 series 10 addr 100 SAI 2/14/2014 3:1
20 series 20 addr 200 SAI 2/14/2014 3:1
SQL>
Thanks You very much for providing the alternate solution
Thanks
Sai Pradyumn
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 00:20:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
