Home » Open Source » Programming Interfaces » Selecting/Updating/deleting nested table by passing particular index number. (Oracle 10g)
|
|
|
|
|
|
| Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541457 is a reply to message #541361] |
Tue, 31 January 2012 08:11   |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
As Michel said, it is far better to NOT use nested tables. But if you must or for learning purposes:
Quote:Select TAB1(4) from NESTED_TABLE where id=1; output=33
with t as (
select rownum rn,
column_value tab1_element
from nested_table,
table(tab1)
where id = 1
)
select tab1_element
from t
where rn = 4
/
TAB1_ELEMENT
--------------
33
SQL>
Quote:Delete TAB2(3) from NESTED_TABLE where id=2;
SQL> select tab2 from nested_table where id = 2
2 /
TAB2
--------------------------------------------------------------------------------
TEXT('234', '241', '4124', '1')
update nested_table
set tab2 = (
with t1 as (
select rownum rn,
column_value tab2_element
from nested_table,
table(tab2)
where id = 2
)
select cast(multiset(select tab2_element from t1 where rn != 3) as text)
from dual
)
where id = 2
/
1 row updated.
SQL> select tab2 from nested_table where id = 2
2 /
TAB2
--------------------------------------------------------------------------------
TEXT('234', '241', '1')
SQL>
Quote:Update NESTED_TABLE set tab3(4)='new_value' where tab3(4)=43 and id=4;
And this will be your homework  .
SY.
|
|
|
|
| Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541599 is a reply to message #541457] |
Wed, 01 February 2012 05:13 |
 |
yaggy
Messages: 21
Registered: January 2012
|
Junior Member |
|
|
Thanks for your reply...
I need help in fetching the data(Text data type ) from NESTED_TABLE table.
Our PHP application is not able to retrieve values from TAB1,TAB2,TAB3 column from the below query..
> Select ID,NAME,TAB1,TAB2,TAB3 from NESTED_TABLE ;
ID NAME TAB1 TAB2 TAB3
1 neha SCOTT.text(30,31,32,33) SCOTT.text(1,2,3,4) SCOTT.text(130,318,65,336)
2 sapna SCOTT.text(34,35,36,37) SCOTT.text(234,241,4124,1) SCOTT.text(330,310,56,325)
3 ravi SCOTT.text(44,40,39,38) SCOTT.text(430,231,214,23) SCOTT.text(430,318,3,325)
4 mushkaan SCOTT.text(43,22,12,34) SCOTT.text(530,631,732,93) SCOTT.text(350,371,2,43)
Is there any other way to write a query so that PHP code can easily retrieve the values from the above query..
Thanks in advance
Yaggy
|
|
|
|
|
|
| Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541707 is a reply to message #541620] |
Wed, 01 February 2012 23:38 |
|
yaggy
Messages: 21
Registered: January 2012
|
Junior Member |
|
|
|
I am on Oracle 10.2.0.1.0. The goal is to take a collection of a multiple nested table columns in a single row...
below is my test case...
Quote:SQL> create or replace TYPE "TEXT" IS TABLE OF clob;
Type created.
Quote:
SQL> CREATE TABLE NESTED_TABLE
2 (ID NUMBER(38,0),
3 NAME VARCHAR2(100 BYTE),
4 TAB1 text,
5 TAB2 text,
6 TAB3 text )
7 NESTED TABLE "TAB1" STORE AS "TAB1"
8 NESTED TABLE "TAB2" STORE AS "TAB2"
9 NESTED TABLE "TAB3" STORE AS "TAB3"
10 /
Table created.
Quote:
SQL> INSERT INTO NESTED_TABLE VALUES(1,'NEHA',TEXT(30,31,32,33),TEXT(1,2,3,4,5,6
),TEXT(10,20,30,40,50,60,70,80));
1 row created.
SQL> INSERT INTO NESTED_TABLE VALUES(2,'SNEHA',TEXT(130,131,132,133),TEXT(12,22,
33),TEXT(130,230,330,430,530,630,730));
1 row created.
Quote:
SQL> SELECT ID,NAME,TAB1,TAB2,TAB3 FROM NESTED_TABLE WHERE ID=1;
ID NAME TAB1 TAB2 TAB3
1 NEHA SCOTT.TEXT(30,31,32,33) SCOTT.TEXT(1,2,3,4,5,6) SCOTT.TEXT(10,20,30,40,50,60,70,80)
But the above query is not working in PHP Application
I tried the following query also
Quote:
SQL> select rownum rn,t1.column_value tab1_element, t2.column_value tab1_element,t3.column_value tab1_element from nested
_table n1,table(tab1) t1,table(tab2) t2,table(tab3) t3 where id = 1;
But this is giving me wrong output....
How to write a query to select data from multiple Nested table columns present in a table
and also I want to select Multiple rows of nested table into a single line
so that my PHP Application can read those rows...
like
Quote:
ID NAME TAB1 TAB2 TAB3
1 NEHA 30,31,32,33 1,2,3,4,5,6 10,20,30,40,50,60,70,80
Or is there any other way to fetch correct data from multiple nested columns....
Thanks in advance
Yaggy
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 19 23:44:35 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
