Home » Open Source » Programming Interfaces » Selecting/Updating/deleting nested table by passing particular index number. (Oracle 10g)
Selecting/Updating/deleting nested table by passing particular index number. [message #541361] Mon, 30 January 2012 22:09 Go to next message
yaggy
Messages: 21
Registered: January 2012
Junior Member
Hi,
I have three tables with nested table columns.
I want to customize the task of updating,deleting and selecting the nested table column by passing particular index number(like array datatype in postgresql)...
Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541362 is a reply to message #541361] Mon, 30 January 2012 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have three tables with nested table columns.
Then you have a seriously flawed design that does NOT conform to Third Normal Form

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541387 is a reply to message #541361] Tue, 31 January 2012 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is far better to NOT use nested table in Oracle.
Poor performances, complex and not relationnal SQL, hard to manage...

Regards
Michel
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Cool.

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 Go to previous messageGo to next message
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 #541620 is a reply to message #541599] Wed, 01 February 2012 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need help in fetching the data(Text data type ) from NESTED_TABLE table

Quote:
select  rownum rn,
                                    column_value tab2_element
                              from  nested_table,
                                    table(tab2)
                              where id = 2


Does this gives you no hint?

With every SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

But before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
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 Go to previous messageGo to next message
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

Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541722 is a reply to message #541707] Thu, 02 February 2012 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But the above query is not working in PHP Application


Note that "it is not working" does not give any clue about what you get.
It is most likely working but you have interpret the returned value as an object as SQL*Plus does it.

Quote:
But this is giving me wrong output....


Which should be?

Regards
Michel
Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541755 is a reply to message #541722] Thu, 02 February 2012 02:42 Go to previous messageGo to next message
yaggy
Messages: 21
Registered: January 2012
Junior Member
The variable used in PHP is 'String Array'.

I want the query to be modified such that, those values should be of string type.

Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541756 is a reply to message #541755] Thu, 02 February 2012 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post should be the result. I still don't understand what it should be. SHOW US.

Regards
Michel
Re: Selecting/Updating/deleting nested table by passing particular index number. [message #541758 is a reply to message #541756] Thu, 02 February 2012 02:56 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it is more a PHP question than a SQL one I moved this topic to "Open Database Interfaces" forum.

Maybe you can read the The Oracle+PHP Cookbook articles on OTN and especially Persisting PHP5 Objects in Oracle.

The following thread in Oracle forums might help you: Passing oracle object from php to oracle.

Regards
Michel


[Updated on: Thu, 02 February 2012 02:56]

Report message to a moderator

Previous Topic: How To Update oracle data from php, by using 'Commit' ?
Next Topic: Need help VERY BADLY
Goto Forum:
  


Current Time: Thu Mar 28 08:45:36 CDT 2024