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 Go to next message
saipradyumn
Messages: 182
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 Go to previous messageGo to next message
_jum
Messages: 488
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 Go to previous messageGo to next message
saipradyumn
Messages: 182
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 #607966 is a reply to message #607941] Thu, 13 February 2014 23:50 Go to previous messageGo to next message
saipradyumn
Messages: 182
Registered: October 2011
Location: Hyderabad
Senior Member


Hi Michel


Could you please let me know logic for bulk insertion using simple query which is having multi dimensional result set .


Thanks
Sai Pradyumn
Re: Accessing the multi dimensional collection in SQL Query [message #607990 is a reply to message #607966] Fri, 14 February 2014 01:28 Go to previous messageGo to next message
_jum
Messages: 488
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 Go to previous message
saipradyumn
Messages: 182
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

Previous Topic: check ordernum present in that column-PLS-00103: Encountered the symbol "SP_LOAD_ORDER" when expecti
Next Topic: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement
Goto Forum:
  


Current Time: Wed Jul 30 08:35:45 CDT 2014

Total time taken to generate the page: 0.15339 seconds