Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rachna.

Asked: March 02, 2003 - 4:51 pm UTC

Last updated: October 25, 2010 - 5:42 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi ,

I want to use nested tables for the following scenario .

subProd_id is defined on the basis of certain parameter values.


create type paramobject as object
(param_description varchar2(10)
param_val varchar2(10)) ;

create type paramlist as table of paramobject ;

create table param_matrix(
subprod_id varchar2(10) ,
col_name varchar2(10) , --- The PArameter Name
col_values paramlist --- Possible values of the parameter for the subprod_id
)
nested table col_values store as col_values_tab ;

insert into param_matrix
('SUBPRODUCT1' , 'BOOKKEEP_CODE',paramlist(paramobj('Value F','F'),
paramobj('Value G','G'),
paramobj('Value H','H')) ;

insert into param_matrix
('SUBPRODUCT1' , 'LOC_STATUS',paramlist(paramobj('Value I','I'),
paramobj('Value J','J')) ;

insert into param_matrix
('SUBPRODUCT2' , 'BOOKKEEP_CODE',paramlist(paramobj('Value I','I'),
paramobj('Value J','J'),
paramobj('Value K','K'));

Actually each subproduct is unique combination of different parameters ,
defined on the basis of their values.

Is it possible to find the subproductId based on the possible parameters.
I have input of the possible values of parameters based on that I want to find the
sub productid .
I am not able to derive the subprod_id using the combination of parameters,
e.g. if Bookkepp_code = F and Loc_status = I then subprod_id is SUbproduct1
if Bookkepp_code = I then subprod_id is SUbproduct2

I hope I made myself clear with the example .
Is it possible to do , as I have dynamic parameters to define each subproductId .
Is there any other better approach for the same.

We have not only two parameters to define a subproduct_id and have many other parameters to define a subprod_id and wanted to use this feature to extract the subprod_id.


Thanks in advance .

Rachna

and Tom said...

this is not the best way to store this information if those are common questions -- you might give a little more time to the physical design.

Me -- I'll never use a nested table in a CREATE TABLE statement. You spend all of your time UN-NESTING them to make them useful again!



ops$tkyte@ORA920> select subprod_id, col_name, b.*
2 from param_matrix a, TABLE(a.col_values) b
3 /

SUBPROD_ID COL_NAME PARAM_DESC PARAM_VAL
-------------------- -------------------- ---------- ----------
SUBPRODUCT1 BOOKKEEP_CODE Value F F
SUBPRODUCT1 BOOKKEEP_CODE Value G G
SUBPRODUCT1 BOOKKEEP_CODE Value H H
SUBPRODUCT1 LOC_STATUS Value I I
SUBPRODUCT1 LOC_STATUS Value J J
SUBPRODUCT2 BOOKKEEP_CODE Value I I
SUBPRODUCT2 BOOKKEEP_CODE Value J J
SUBPRODUCT2 BOOKKEEP_CODE Value K K

8 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select subprod_id
2 from param_matrix a, TABLE(a.col_values) b
3 where (col_name = 'BOOKKEEP_CODE' and param_val = 'F')
4 or (col_name = 'LOC_STATUS' and param_val = 'I')
5 group by subprod_id
6 having count(distinct col_name) = 2
7 /

SUBPROD_ID
--------------------
SUBPRODUCT1

ops$tkyte@ORA920>
ops$tkyte@ORA920> select subprod_id
2 from param_matrix a, TABLE(a.col_values) b
3 where (col_name = 'BOOKKEEP_CODE' and param_val = 'I')
4 /

SUBPROD_ID
--------------------
SUBPRODUCT2



Rating

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rachna, March 03, 2003 - 4:19 am UTC

Hi ,
Thanks a lot for the reply.
I think I am not able to make myself very clear .
Actually if I put OR for finding subproduct1 then it will not be correct as the combination of OR can also give me another subproduct ID if say another subprod_id is defined using loc_status only and with this value. We have hundereds of subproduct ID with different combinations of parameter and their values (and there can be different parameters used to define the subproduct Ids).
What I think then in that case may be we should not store the same as Nested tables. What is the better way of storing the combination in this case.
Transaction Table
-----------------
tran# bookeep_code loc_status param_3 param_4 param_5 Subprod_ID
1 F 'loc1' 'P3' 'P4' 'P5' (????? )To be assigned from
matrix table which actually based on the parameter values .

2 NULL 'loc2' 'P3.1' 'P4' 'P5'

and so on. As of now we have approx. 25 parameters and based on different combination user wants to define subproductId which we want to store in parameter matrix to save the actual possible parameter combination .
If you need some more information , pls let me know.
Our requirement at the end is to Assign the Subproduct Ids to the transactions based on their definition in the parameter matrix.

Thanks once again



Tom Kyte
March 03, 2003 - 6:52 am UTC

3 where (col_name = 'BOOKKEEP_CODE' and param_val = 'F')
4 or (col_name = 'LOC_STATUS' and param_val = 'I')
5 group by subprod_id
6 having count(distinct col_name) = 2
7 /

is correct. It will not give you the wrong answer. It gives ONLY subproduct_ids that have two distinct col_names such that the col names are in bookkep_code and loc_status with the corresponding param vals.

OR works perfectly here. the having makes sure two distinct col_names are returned and the predicate limits us to just bookkeep_cod and loc_status.




Nested table useful or not ?

Kiran Shah, March 03, 2003 - 4:54 am UTC

Dear Tom,
Following is the statement from your answer :
"Me -- I'll never use a nested table in a CREATE TABLE statement. You spend all
of your time UN-NESTING them to make them useful again!"

Can you pl. give more light on this ?
My specific queries are :
1.) Why are you not in favour of nested tables ? (By the way, we have extensively used this facility in our design).

2.) If this is not to be used, then how can we store multiple information for particular column in a single row?

Thanks...


Tom Kyte
March 03, 2003 - 6:55 am UTC

1) they are parent child tables in disguise but ones that add:

a 16 byte raw with a unique constraint on the parent table. Most likely you ALREADY HAVE a primary key on the parent table and most likely it is smaller.

a 16 byte raw that you need to index on the child (not auto-indexed, you need to know to do it). This is the foreign key and is hidden from you.

The inability to put many types of constraints on the nested table..


They are simple parent/child tables - except you lose the ability to access the child table directly.



2) you are NOT storing anything in a "single row". Physically they are a parent child table pair, nothing more, nothing less


If you have my book "Expert one on one Oracle" - I write about them in there, describe their implementation and talk about when I would use them.

Nesting

Paul, March 03, 2003 - 5:50 am UTC

Since the nested table data for each row is not physically stored with the row there is a performance loss with using nesting. This indicates that using a nested table or simply using separate table should not differ much - from a performance viewpoint.

During the logical design - specifically RDA(Relational Data Analysis) - attributes(colummns) that can contain more than one value for one value of the primary key are considered "repeating data" and are thus moved to a separate table. Consider the example of a Person table that includes the column Alias. If a person could have more than one alias we would create a new table (Alias) containing an AliasID, Alias, and a foreign key column pointing to the Person table.

Nested table useful or not ?

Tony Andrews, March 03, 2003 - 6:23 am UTC

> By the way, we have extensively used this facility in our design).

It's unfortunate that Oracle supports concepts like nested tables in the database (though I realise it has to, for commercial reasons). Just because they are there, people think they ought to use them - in fact, the less database expertise people have, they more they seem compelled to tie themselves in knots with over-use of OR features. Experienced database designers are reluctant to use them, not just because they find them difficult to use (which they do), but because they understand the benefits of a well-normalised relational design, and the drawbacks of the hierarchic nested model. By all means use Object Views if that helps your application, but store the data in the most flexible way possible - relational tables!


but in Oracle 9i Release 2

Jan, April 24, 2003 - 3:32 am UTC

In 9i, there is an option to have 'Primary-key Based Object Identifiers' instead of 16 byte RAW.
See: 'Oracle9i Application Developer's Guide - Object-Relational Features, Release 2 (9.2)', search for 'Primary-key Based Object Identifiers'.

I have 2 questions:

1) If we use this version of Oracle and an extra coding for un-nesting is not a problem, is there any advantages to use Objects, Collections and Nested Tables in data storage? Is the *Object Oriented* PL/SQL (methods, heritance etc..) more powerful then *Relational* PL/SQL?

2) If I need to select a value based on a filtering in a Nested table, is the Parent-Child Relational way (join of 2 tables with all the necessary indexes) more efficient?

Thanks, Jan

Tom Kyte
April 24, 2003 - 7:57 am UTC

1) i use object relational extensions as a tool to make PLSQL more powerful. I have yet to use these features to store data -- although as a method to store an XML document in a "structured" format, they might have some appeal

2) not any more efficient then if you did it yourself. It is after all just a server built "parent child table" with primary keys, foreign keys and all.

Nested Table in Form

A reader, May 29, 2003 - 6:22 pm UTC

Hi, Tom,

I know the nested table can not be used in version 8, probably not in form 6i. But what is the work around if we need to stick with nested table(not create saperate master detail tables instead) in form 6i? Are there any improvement for this in forms 9i?

Thanks


Tom Kyte
May 29, 2003 - 7:07 pm UTC

nested tables were introduced in version 8.0. they can be used.


I don't use forms, haven't done a form since 1995 when this html/http thing caught on. sorry... You can try otn.oracle.com -> discussion forums.

Unfortunate wording in Concepts Guide

Tony Andrews, August 05, 2003 - 6:59 am UTC

Reading the Concepts Guide section on Object Views, I was struck by the implications of the following text:

[Quote]
Object views allow the use of relational data in object-oriented applications. They let users:

o Try object-oriented programming techniques without converting existing tables
o Convert data gradually and transparently from relational tables to object-relational tables
o Use legacy RDBMS data with existing object-oriented applications
[Unquote]

The implication seems to be that relational tables are a "legacy" that should eventually be converted to object-relational tables. This will only encourage all the novice Oracle designers out there to use nested tables and varrays in their database designs as a matter of course, believing it to be an "improvement" on doing it the relational way.
A touch of your more realistic input wouldn't have gone amiss here!


Tom Kyte
August 05, 2003 - 7:30 am UTC

legacy has a bad rap. sort of like "hacking" which used to be good but now is considered "bad".


think about it -- the second after you have the ribbon cutting on a new system, you have what? A legacy system. It could be using the coolest, newest, hotest techniques on the planet but that system -- now that it is in place -- is legacy.




Problem in nested table with connect by

Padmanabhan Natarajan, July 05, 2005 - 8:50 pm UTC

I have the following code here which uses nested tables, connect by, bulk operations and database links.

I get "ORA-22950: cannot ORDER objects without MAP or ORDER method" whenever I try to run this from a test jsp.

The following are the definitions of the types that exist in database

create type QuoteEntry IS OBJECT (
order_number number,
header_id number,
source_header_id number,
original_system_reference varchar2(50),
ship_to_site_use_id number,
original_system_source_code varchar2(30)
);

create type QuoteTable IS TABLE OF QuoteEntry

create type QuoteNumberTable IS TABLE OF Varchar2(50);

Since I cannot have queries in 'start with' clause when using a remote database connection I am pulling the data required and then manipulating them. Using inline comments I have pointed out below the database link synonms.

function fnTestConnectBy RETURN SYS_REFCURSOR
is

quoteData QuoteTable;
quoteNumbers QuoteNumberTable;
a varchar2(50);
cTest SYS_REFCURSOR ;
begin
quoteData := QuoteTable();
quoteNumbers := QuoteNumberTable();

select deals.quote_number
bulk collect into quoteNumbers
from emcint_sv_recent_shipments deals, --database link
(
SELECT QOT_HDR.QUOTE_STATUS_ID, QOT_HDR.QUOTE_NUMBER, QOT_HDR.QUOTE_NAME, QUOTE_HEADER_ID, QOT_ACC.RESOURCE_ID, QOT_HDR.PARTY_ID
FROM ASO_QUOTE_HEADERS_ALL QOT_HDR
, ASO_QUOTE_ACCESSES QOT_ACC
WHERE QOT_ACC.QUOTE_NUMBER = QOT_HDR.QUOTE_NUMBER
AND QOT_HDR.QUOTE_STATUS_ID = 29 -- nQuoteSubmittedStatus -- 29
AND QOT_ACC.RESOURCE_ID = 100080279
UNION
SELECT QOT_HDR.QUOTE_STATUS_ID, QOT_HDR.QUOTE_NUMBER, QOT_HDR.QUOTE_NAME,QOT_CRD.QUOTE_HEADER_ID,
QOT_CRD.RESOURCE_ID, QOT_HDR.PARTY_ID
FROM ASO_SALES_CREDITS QOT_CRD
,ASO_QUOTE_HEADERS_ALL QOT_HDR
WHERE QOT_CRD.QUOTE_HEADER_ID = QOT_HDR.QUOTE_HEADER_ID
AND QOT_HDR.QUOTE_STATUS_ID = 29 -- nQuoteSubmittedStatus -- 29
AND QOT_CRD.RESOURCE_ID = 100080279
) QOT
WHERE QOT.RESOURCE_ID = 100080279
AND deals.QUOTE_NUMBER = TO_CHAR(QOT.QUOTE_NUMBER)
AND deals.deal_number = 'CPQO47808';

select QuoteEntry(order_number, header_id, source_header_id, original_system_reference, ship_to_site_use_id, original_system_source_code)
bulk collect into quoteData
from emcint_sv_so_headers_all --database link
where attribute1 like 'CPQO40631'
and order_category = 'R'
and original_system_source_code in ( 2, 1043, 1123, 1124 )
and ship_to_site_use_id = 99673;

open cTest for
select * from TABLE(CAST(quoteData as QuoteTable)) tab
start with tab.original_system_reference in (Select * from TABLE(CAST(quoteNumbers as QuoteNumberTable))
)
connect by tab.source_header_id = prior tab.header_id;

return cTest;

end fnTestConnectBy;

Tom Kyte
July 06, 2005 - 7:35 am UTC

it is trying to sort your data and it cannot because it doesn't understand how your data sorts.

but why collections at all?


with X as (query1), Y as (query2)
select *
from Y
start with .....;





Thanks for the lightning quick response...but...

Padmanabhan Natarajan, July 06, 2005 - 10:21 am UTC

That was exactly what I did before jumping into collections...my original query was

select order_number, header_id, source_header_id, original_system_reference, ship_to_site_use_id, original_system_source_code
from emcint_sv_so_headers_all
where attribute1 like 'CPQO40631'
and order_category = 'R'
and original_system_source_code in ( 2, 1043, 1123, 1124 )
and ship_to_site_use_id = 99673
start with original_system_reference in (
select deals.quote_number
from emcint_sv_recent_shipments deals,
(
SELECT QOT_HDR.QUOTE_STATUS_ID, QOT_HDR.QUOTE_NUMBER, QOT_HDR.QUOTE_NAME, QUOTE_HEADER_ID, QOT_ACC.RESOURCE_ID, QOT_HDR.PARTY_ID
FROM ASO_QUOTE_HEADERS_ALL QOT_HDR
, ASO_QUOTE_ACCESSES QOT_ACC
WHERE QOT_ACC.QUOTE_NUMBER = QOT_HDR.QUOTE_NUMBER
AND QOT_HDR.QUOTE_STATUS_ID = 29 -- nQuoteSubmittedStatus -- 29
AND QOT_ACC.RESOURCE_ID = 100080279
UNION
SELECT QOT_HDR.QUOTE_STATUS_ID, QOT_HDR.QUOTE_NUMBER, QOT_HDR.QUOTE_NAME,QOT_CRD.QUOTE_HEADER_ID,
QOT_CRD.RESOURCE_ID, QOT_HDR.PARTY_ID
FROM ASO_SALES_CREDITS QOT_CRD
, ASO_QUOTE_HEADERS_ALL QOT_HDR
WHERE QOT_CRD.QUOTE_HEADER_ID = QOT_HDR.QUOTE_HEADER_ID
AND QOT_HDR.QUOTE_STATUS_ID = 29 -- nQuoteSubmittedStatus -- 29
AND QOT_CRD.RESOURCE_ID = 100080279
) QOT
WHERE QOT.RESOURCE_ID = 100080279
AND deals.QUOTE_NUMBER = TO_CHAR(QOT.QUOTE_NUMBER)
AND deals.deal_number = 'CPQO47808' )
connect by source_header_id = prior header_id

But this gave "ORA-02016: cannot use a subquery in a START WITH on a remote database" error.

I switched to collections to solve the problem, but this doesnt seem to pull the trick. Maybe I should consider global temp tables. Any thoughts on this would be really helpful.

Regards,
Padmanabhan Natarajan


Tom Kyte
July 06, 2005 - 10:50 am UTC

you can use a global temporary table or implement the MAP or ORDER method for your type in order to allow it to "sort"

Thanks again for being super quick...

Padmanabhan Natarajan, July 06, 2005 - 10:56 am UTC

Hi,

I implemented MAP method on my object but the question I am having is suppose I give in the vanilla query to connect by clause, what is that it is going to sort it by. I figured out that logically it should be rownum. But the problem is I cannot reference rownum outside of select clause. Any thoughts on this is highly appreciated.

Regards,
Padmanabhan Natarajan

Tom Kyte
July 06, 2005 - 12:25 pm UTC

it is sorting likely to do the in list, do a field by field compare in the order the fields are in the object type.

Thanks a lot again....

Padmanabhan Natarajan, July 06, 2005 - 2:21 pm UTC

Hi,

I am really not sure if the "sorting" error is because of the in list. After all, the in list is a set of varchars and there are no objects in it.

In case I implement an MAP/ORDER method as you say, should be like

if(self.col1 < col1)
return -1;
else if(self.col1 > col1)
return 1;
else if(self.col1 == col1)
{
if(self.col2 < col2)
return -1;
else if(self.col2 > col2)
return 1;
else if(...)
{
//and so on till the last equality in the last col
}

}

I hope the above pseudocode is understandable. If in case it is not I can always make it an actual PL/SQL implementation.

Please let me know if the above method needs to be implemented in the ORDER method of the object. I am not sure how I can use MAP, though it is analogous to a hashmap, I am not sure how I can generate a key based on my dataset.

Regards,
Padmanabhan Natarajan

Tom Kyte
July 06, 2005 - 2:34 pm UTC

look at your plan -- you sort/distinct inlist values, then join to outer query. I would suspect you'll see a sort merge join there. both things are sorted.

yes, that is what I meant.

you might just use a global temporary table here, if these sets are of any size.

Thanks a lot for the help...

Padmanabhan Natarajan, July 06, 2005 - 9:31 pm UTC

Hi,

I tried again with the following code but start with...connect by just doesnt seem to give in for nested tables in the way I have used them. I am pasting the new code here for your review.

create or replace type QuoteEntry IS OBJECT (
order_number number,
header_id number,
source_header_id number,
original_system_reference varchar2(50),
ship_to_site_use_id number,
original_system_source_code varchar2(30),
ORDER MEMBER FUNCTION match(q QuoteEntry) RETURN INTEGER
);

CREATE OR REPLACE TYPE BODY QuoteEntry AS
ORDER MEMBER FUNCTION match(q QuoteEntry) RETURN INTEGER IS
BEGIN
IF nvl(order_number, -1) > q.order_number THEN
RETURN 1;
ELSIF order_number < q.order_number THEN
RETURN -1;
ELSIF header_id > q.header_id THEN
RETURN 1;
ELSIF header_id < q.header_id THEN
RETURN -1;
ELSIF source_header_id > q.source_header_id THEN
RETURN 1;
ELSIF source_header_id < q.source_header_id THEN
RETURN -1;
ELSIF original_system_reference > q.original_system_reference THEN
RETURN 1;
ELSIF original_system_reference < q.original_system_reference THEN
RETURN -1;
ELSIF ship_to_site_use_id > q.ship_to_site_use_id THEN
RETURN 1;
ELSIF ship_to_site_use_id < q.ship_to_site_use_id THEN
RETURN -1;
ELSIF original_system_source_code > q.original_system_source_code THEN
RETURN 1;
ELSIF original_system_source_code < q.original_system_source_code THEN
RETURN -1;
ELSE
RETURN 0;
END IF;
END match;
END;

create type QuoteTable IS TABLE OF QuoteEntry;

create type QuoteNumberTable IS TABLE OF Varchar2(50);

function fnTestConnectBy RETURN SYS_REFCURSOR
is

quoteData QuoteTable;
quoteNumbers QuoteNumberTable;
a varchar2(50);
cTest SYS_REFCURSOR ;
begin
quoteData := QuoteTable();
quoteNumbers := QuoteNumberTable();

select deals.quote_number
bulk collect into quoteNumbers
from emcint_sv_recent_shipments deals,
(
SELECT QOT_HDR.QUOTE_STATUS_ID, QOT_HDR.QUOTE_NUMBER, QOT_HDR.QUOTE_NAME, QUOTE_HEADER_ID, QOT_ACC.RESOURCE_ID, QOT_HDR.PARTY_ID
FROM ASO_QUOTE_HEADERS_ALL QOT_HDR
, ASO_QUOTE_ACCESSES QOT_ACC
WHERE QOT_ACC.QUOTE_NUMBER = QOT_HDR.QUOTE_NUMBER
AND QOT_HDR.QUOTE_STATUS_ID = 29
AND QOT_ACC.RESOURCE_ID = 100080279
UNION
SELECT QOT_HDR.QUOTE_STATUS_ID, QOT_HDR.QUOTE_NUMBER, QOT_HDR.QUOTE_NAME,QOT_CRD.QUOTE_HEADER_ID,
QOT_CRD.RESOURCE_ID, QOT_HDR.PARTY_ID
FROM ASO_SALES_CREDITS QOT_CRD
,ASO_QUOTE_HEADERS_ALL QOT_HDR
WHERE QOT_CRD.QUOTE_HEADER_ID = QOT_HDR.QUOTE_HEADER_ID
AND QOT_HDR.QUOTE_STATUS_ID = 29
AND QOT_CRD.RESOURCE_ID = 100080279
) QOT
WHERE QOT.RESOURCE_ID = 100080279
AND deals.QUOTE_NUMBER = TO_CHAR(QOT.QUOTE_NUMBER)
AND deals.deal_number = 'CPQO47808';

select QuoteEntry(order_number, header_id, source_header_id, original_system_reference, ship_to_site_use_id, original_system_source_code)
bulk collect into quoteData
from emcint_sv_so_headers_all
where attribute1 like 'CPQO47808'
and order_category = 'R'
and original_system_source_code in ( 2, 1043, 1123, 1124 );
and ship_to_site_use_id = 99673;

open cTest for
select * from TABLE(CAST(quoteData as QuoteTable))
start with original_system_reference in (Select * from TABLE(CAST(quoteNumbers as QuoteNumberTable)))
connect by header_id = prior source_header_id;

return cTest;

end fnTestConnectBy;

This doesnt throw any error but I dont get the data I am expecting. I have a proper test case against which I am validating.

The problem doesnt seem to be in sorting because I removed "start with" and replaced it with a "where" clause and I got all the data I expected. I even experimented with joins and found that it worked. Only "connect by" doesn't seem to work.

One another question is, assuming that there is a sort merge join going on, why would I need to implement ORDER function for all the fields in object. I would like to implement only for the column which is matched for in the clause to determine my sort order. This is my understanding of sort-merge join, please correct me if I am wrong.

Thanks a lot again for being helpful and prompt all along.

Thanks & Regards,
Padmanabhan Natarajan

Tom Kyte
July 07, 2005 - 9:00 am UTC

go with global temporary tables and see what you see.

Thanks a million

Padmanabhan Natarajan, July 11, 2005 - 10:17 am UTC

Hi Tom,

I got the whole thing working with temporary tables.

Thanks a lot for taking the time to help me out and of course those outstandingly quick responses.

Regards,
Padmanabhan Natarajan

Nested Tables in 10G

A reader, September 08, 2006 - 9:43 am UTC

Tom, How about Nested Tables in 10G? Our team had a discussion about Nested table usage yesterday and someone pointed out that, in 10G Nested tables performance is much better [improvised] and are easy to select from. Is that true?
Is anything changed as far as Nested Tables are concerned in 10G?

Thanks,

Tom Kyte
September 09, 2006 - 11:50 am UTC

they are the same.

If they "pointed this out", how did they "demonstrate this"

I assume you are talking about them as a persistent on disk storage thing (in plsql, as a datatype - they are great, as a way to store data on disk, nah, not worth it)

Thanks for your response

A reader, September 11, 2006 - 11:15 am UTC

They did not demonstrate anything. Looks like they read it in some article and were thinking to take that route.

Thanks,

Using PL/SQL type in SELECT clause

Kishore Negi, August 03, 2007 - 7:56 am UTC

Hello Sir,
The code below works for case 1 but doesn't work for case 2. The only difference being in the two case is in the commneted line. If the value of r is available in the outer query as in Case 1, why is it not available for Case2. Could you please let me know exactly why does Case2 fails. What is the input that it doesn't find ?

Case1
=====
DECLARE
TYPE nested_table IS TABLE OF NUMBER(2);
l_nested_table nested_table := nested_table();
ref_cur SYS_REFCURSOR;
l_output NUMBER;
BEGIN
l_nested_table.EXTEND(2);
l_nested_table(1) := 10;
l_nested_table(2) := 20;
OPEN ref_cur FOR
SELECT r -- This is different for case 1
FROM (SELECT rownum r FROM dual CONNECT BY LEVEL <=2);
LOOP
FETCH ref_cur INTO l_output;
EXIT WHEN ref_cur%NOTFOUND;
dbms_output.put_line(l_output);
END LOOP;
END;

Case2
=====
DECLARE
TYPE nested_table IS TABLE OF NUMBER(2);
l_nested_table nested_table := nested_table();
ref_cur SYS_REFCURSOR;
l_output NUMBER;
BEGIN
l_nested_table.EXTEND(2);
l_nested_table(1) := 10;
l_nested_table(2) := 20;
OPEN ref_cur FOR
SELECT l_nested_table(r) -- This is different for case 2
FROM (SELECT rownum r FROM dual CONNECT BY LEVEL <=2);
LOOP
FETCH ref_cur INTO l_output;
EXIT WHEN ref_cur%NOTFOUND;
dbms_output.put_line(l_output);
END LOOP;
END;

Regards
Kishore
Tom Kyte
August 05, 2007 - 1:34 pm UTC

what are you trying to do. You do not select the i'th element from a collection in sql like that.

select * from table( collection );


that works (if the collection is a SQL type that is)

Kishore Negi, August 06, 2007 - 7:09 am UTC

Could you please let me know why does this statement work fine then? All i was expecting in the above case was that the index is changing with each row. If it can work for hardcoded 1, the why not for the above case.


DECLARE
TYPE nested_table IS TABLE OF NUMBER(2);
l_nested_table nested_table := nested_table();
ref_cur SYS_REFCURSOR;
l_output NUMBER;
BEGIN
l_nested_table.EXTEND(2);
l_nested_table(1) := 10;
l_nested_table(2) := 20;
OPEN ref_cur FOR
SELECT l_nested_table(1) -- Change done here
FROM (SELECT rownum r FROM dual CONNECT BY LEVEL <=2);
LOOP
FETCH ref_cur INTO l_output;
EXIT WHEN ref_cur%NOTFOUND;
dbms_output.put_line(l_output);
END LOOP;
END;

Regards
Kishore
Tom Kyte
August 06, 2007 - 12:00 pm UTC

because

select l_nested_table(1) from dual

is just like

select :bv from dual using l_nested_table(1);

you just bound in a simple scalar - that is all, you subscripted the array to get the first element and bound in that scalar value and executed a query.

ops$tkyte%ORA9IR2> create type myType as table of number
  2  /

Type created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
  2          l_data myType := myType(42,55);
  3  begin
  4          for x in (select * from TABLE( cast(l_data as myType) ))
  5          loop
  6                  dbms_output.put_line( x.column_value );
  7          end loop;
  8  end;
  9  /
42
55

PL/SQL procedure successfully completed.



NOT NULL Constraint in nested table

Nikhilesh, September 14, 2007 - 2:22 am UTC

Dear Tom,
There is an example in Oracle Docs

CREATE TYPE Worker AS OBJECT (name VARCHAR2(25) , dept VARCHAR2(15)); --this is object
CREATE TYPE Roster AS TABLE OF Worker; --this is collection
CREATE TABLE teams (team_no NUMBER, members Roster) NESTED TABLE members STORE AS teams_store;

this works fine but when i add not null constrain to worker

CREATE TYPE Worker AS OBJECT (name VARCHAR2(25)not null , dept VARCHAR2(15) not null); --this is object

it creates roster as object and can't create teams table.

I need to add constrains to my nested table data.

Thanks
Nikhilesh


Insert into nested table.

Karthick, January 09, 2008 - 7:00 am UTC

How can i do a insert into ... select * from in a nested table. Here is my senario.

I have create the following type

create or replace type hx_group_object as object(hx_field_name varchar2(100), hx_field_value varchar2(100))
/
create or replace type hx_group_tbl as table of hx_group_object
/

Now i create this nested table

create table hx_group_stg(
hx_sno integer,
hx_old_group hx_group_tbl,
hx_new_group hx_group_tbl)
nested table hx_old_group store as hx_old_group_tbl,
nested table hx_new_group store as hx_new_group_tbl,
/

Now i create these tables which will be the source of information for hx_group_stg

create table hx_names (name varchar2(100), fname varchar2(100), lname varchar2(100))
/
create table hx_group_config (hx_group_id integer, hx_sno integer, hx_field_name varchar2(100))
/

Here is the insert script for hx_names and hx_group_config

insert into hx_group_config (hx_group_id, hx_sno, hx_field_name) values (1, 1, 'NAME')
/
insert into hx_group_config (hx_group_id, hx_sno, hx_field_name) values (1, 2, 'FNAME')
/
insert into hx_group_config (hx_group_id, hx_sno, hx_field_name) values (1, 3, 'LNAME')
/
insert into hx_names (name, fname, lname) values('KARTHICK', 'KARTHICK', 'PATTABIRAMAN')
/
insert into hx_names (name, fname, lname) values('VIMAL', 'VIMAL', 'KANTH')
/
insert into hx_names (name, fname, lname) values('VIJAY', 'VIJAY', 'RENGANATHAN')
/

To insert into hx_group_stg i wrote the following insert statement

insert into hx_group_stg (hx_sno,hx_old_group)
values (1,
hx_group_tbl(
hx_group_object('NAME','KARTHICK'),
hx_group_object('FNAME','KARTHICK'),
hx_group_object('LNAME','PATTABIRAMAN')))
/
insert into hx_group_stg (hx_sno,hx_old_group)
values (2,
hx_group_tbl(
hx_group_object('NAME','VIMAL'),
hx_group_object('FNAME','VIMAL'),
hx_group_object('LNAME','KANTH')))
/
insert into hx_group_stg (hx_sno,hx_old_group)
values (3,
hx_group_tbl(
hx_group_object('NAME','VIJAY'),
hx_group_object('FNAME','VIJAY'),
hx_group_object('LNAME','RENGANATHAN')))
/

Can this be done in a single SQL like insert into .. select ...

Let me explain what i need to insert.

Hx_group_config is the configuration table where i have the list of fields for hx_field_name in hx_group_oblect.

And the hx_field_value will be derived from hx_names.

Can this be done.


Tom Kyte
January 09, 2008 - 8:08 am UTC

ugh...

using nested tables to store data - almost certainly a mistake. nested tables - great in plsql code, good in views - but to store the data which is obviously relational data - I would not.

ugh 2...

attribute name/value pairs - taking perfectly formed relational, useful data and making it impossible to use practicably or with any degree of performance at runtime...

You are taking good staged data and ruining it.


I modified your inputs so the fname is different from the name - to verify the right stuff is getting in the right place.

ops$tkyte%ORA10GR2> create sequence s;

Sequence created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into hx_group_stg( hx_sno, hx_old_group )
  2  select s.nextval,
  3         cast( multiset( select gc.hx_field_name,
  4                                decode( gc.hx_field_name, 'NAME', n.name, 'FNAME', n.fname, 'LNAME', n.lname )
  5                           from hx_names n, hx_group_config gc
  6                          where n.rowid = x.rowid
  7                          order by gc.hx_sno ) as hx_group_tbl )
  8    from hx_names x
  9  /

3 rows created.

ops$tkyte%ORA10GR2> select * from hx_group_stg;

    HX_SNO
----------
HX_OLD_GROUP(HX_FIELD_NAME, HX_FIELD_VALUE)
-------------------------------------------------------------------------------
HX_NEW_GROUP(HX_FIELD_NAME, HX_FIELD_VALUE)
-------------------------------------------------------------------------------
         1
HX_GROUP_TBL(HX_GROUP_OBJECT('NAME', 'KARTHICK'), HX_GROUP_OBJECT('FNAME', 'xKA
RTHICK'), HX_GROUP_OBJECT('LNAME', 'PATTABIRAMAN'))


         2
HX_GROUP_TBL(HX_GROUP_OBJECT('NAME', 'VIMAL'), HX_GROUP_OBJECT('FNAME', 'xVIMAL
'), HX_GROUP_OBJECT('LNAME', 'KANTH'))


         3
HX_GROUP_TBL(HX_GROUP_OBJECT('NAME', 'VIJAY'), HX_GROUP_OBJECT('FNAME', 'xVIJAY
'), HX_GROUP_OBJECT('LNAME', 'RENGANATHAN'))


Karthick, January 09, 2008 - 8:47 am UTC

I was holding to post this as a new question.But after seeing your comment on my approch let me post this here.

I have a list of fields

e.g.

A,B,C,D,E

i need to take data for these fields from available transaction table and load into "temporary" table.

currently i have created a table (temporary) with these fields A,B,C,D,E and loaded.

It works fine for me. But the problem now is iam asked to do the same for another set of
fields G,H,I,J,K they dont mind me writing a seperate process for this and have a seperate table for this. But i do because both the process does the same stuff.

In the future i may get some more set of fields who knows. so what i decided is to have a
configuration table

my_config_tbl

id field_name
------------------
01 A
01 B
01 C
01 D
01 E

Now i want to write a common process that will get id as an input and look for the fields in the my_config_tbl. for each new set of field_name i will give a new id like 01.02 and pick them.

Now the "temporary" table that i was talking above need to be structured. Three suggestion i have here are

1. Create it dynamically. (Creating database object on the fly dose not look got to me). The table will look some thing like this.

s_on old_A odl_B old_C old_D old_E new_A new_B new_C new_D new_E
-------------------------------------------------------------------------------------
1
2
3
4
5

2. create the table some thing like this.

s_no field_name old_value new_value
-------------------------------------------------
1 A
1 B
1 C
1 D
1 E
2 A
2 B
2 C
2 D
2 E

and so on..

3. create a nested table where you will have like

s_no old_value_nested new_value_nested
------------------------------------------------------
field_name field_value field_name field_value
---------------------- ----------------------
1 A A
B B
C C
D D
E E
2 A A
B B
C C
D D
E E


now what i do with this temporary table is that shuffle entire old value and put in new value.

If i use the 2nd method then for 1 lake records i will end up with 5 lack records.

So the shuffling will be expencive in method 2.

After doing the shuffling i need to update the transaction table with new values for the old values.

so my update gose like this if i use method 1.

update transaction_table
set (A,B,C,D,E) = (select A,B,C,D,E from temporary_table
where transaction_table_fields = temporary_table_old_value)
where exists(...)

and with method 2 my update will be some thing like this

update transaction_table
set A = (select new_value from temporary_table
where field_name = 'A'
and transaction_table_fields = temporary_table_old_value),
B = (select new_value from temporary_table
where field_name = 'B'
and transaction_table_fields = temporary_table_old_value),
C = (select new_value from temporary_table
where field_name = 'C'
and transaction_table_fields = temporary_table_old_value),
D = (select new_value from temporary_table
where field_name = 'D'
and transaction_table_fields = temporary_table_old_value),
E = (select new_value from temporary_table
where field_name = 'E'
and transaction_table_fields = temporary_table_old_value)
where exists(...)

and with method 3 my update will be some thing like this.

update hx_names t
set (A,B,C,D,E) =
(select new_A, new_B, new_C, new_D, new_E
from (select max(decode(o.field_name, 'A', o.field_value)) old_A,
max(decode(o.field_name, 'A', n.field_value)) new_A,
max(decode(o.field_name, 'B', o.field_value)) old_B,
max(decode(o.field_name, 'B', n.field_value)) new_B,
max(decode(o.field_name, 'C', o.field_value)) old_C,
max(decode(o.field_name, 'C', n.field_value)) new_C,
max(decode(o.field_name, 'D', o.field_value)) old_D,
max(decode(o.field_name, 'D', n.field_value)) new_D,
max(decode(o.field_name, 'E', o.field_value)) old_E,
max(decode(o.field_name, 'E', n.field_value)) new_E
from temporary_table s,
table(s.old_value_nested) o,
table(s.new_value_nested) n
where o.field_name = n.field_name
group by s.s_no) s
where t.A = s.old_A
and t.B = s.old_B
and t.C = s.old_C
and t.D = s.old_D
and t.E = s.old_E)
where exists (...)


My second and third update will be much complicated than method 1. But my only problem with method 1 is doing DDL in production which is by the way too bad.

I am looking at method 3 right now as my shuffling will be better than the method 2. but your comment made me to hold my approach.

Which one of this you will recoment. If you feel all the three are very very bad approch can you suggest some new one.


Ignore my above posting

Karthick Pattabiraman, January 09, 2008 - 8:59 am UTC

I have posted it as a new question

error while access return cursor to remote database from TABLE(CAST

A reader, May 10, 2010 - 2:31 pm UTC

i have problem while accessing type table and returning as cursor from remote database


SELECT *
from TABLE(CAST(output_data as EC_FETCH_RETURN_ARRAY));

when i try to fetch data from cursor. I got below error

ORA-24338: statement handle not executed

Please advice
Tom Kyte
May 10, 2010 - 7:12 pm UTC

give everything one would need to have to demonstrate this and make it AS SMALL AS POSSIBLE. If I look at it and know it could be made smaller, I'll just say "make it smaller please"

Can we use CTAS for a table with nested tables?

A reader, July 27, 2010 - 5:53 am UTC

Hi Tom,

A table contains mutiple nested tables and XML types cloumns and i want to create another table with some data from this table ,

Is it possible to use CTAS for a table with nested tables ?
if not - is there any alternative method?


CREATE TABLE X_Y_Z
AS
(
SELECT LD.* from
X LD,
Y TCA
WHERE LD.ORDERID = TCA.ORDERID
);

ORA-00932: inconsistent datatypes expected NBG_DEV2.COLL_LT got CHAR


please see below the defintion of the table X:
CUST NUMBER(10),
ORDERID NUMBER(10) NOT NULL,
TRNNO NUMBER(2) NOT NULL,
INPAYLOADTYPE NUMBER(1),
INPAYLOAD SYS.XMLTYPE,
OUTPAYLOADTYPE NUMBER(2),
OUTPAYLOAD SYS.XMLTYPE,
BUSINESSSTATUS NUMBER(2) NOT NULL,
s_DATETIME DATE,
R_DATETIME DATE,
SPECTCONDITION NUMBER(1),
ETRIGGER NUMBER(1),
CREATED DATE DEFAULT SYSDATE,
ACC_MASTER OBJ_LINE_ACCMASTER

nested table ACC_MASTER.mLTList store as list_nested_tab
(nested table mASList store as ancillary_nested_tab
nested table mDDIList store as ddi_nested_tab
nested table mMiscList store as misc_nested_tab)
;


Many thanks

Tom Kyte
July 27, 2010 - 12:18 pm UTC

ops$tkyte%ORA10GR2> create or replace type emp_type
  2  as object
  3  (empno       number(4),
  4   ename       varchar2(10),
  5   job         varchar2(9),
  6   mgr         number(4),
  7   hiredate    date,
  8   sal         number(7, 2),
  9   comm        number(7, 2)
 10  );
 11  /

Type created.

ops$tkyte%ORA10GR2> create or replace type emp_tab_type
  2  as table of emp_type
  3  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table dept_and_emp
  2  ( deptno, dname, loc, emps ) nested table emps store as emps_nt
  3  as
  4  select dept.*,
  5     CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm
  6                       from SCOTT.EMP
  7                       where emp.deptno = dept.deptno ) AS emp_tab_type )
  8    from SCOTT.DEPT
  9  /

Table created.

I'm not happy with Oracle's objects :(

adderek, July 28, 2010 - 5:24 pm UTC

Hello Tom,

1. You are showing CREATE TABLE and CREATE TYPE which are both DDL's. Are there any profits of using user type columns instead of separate tables (and optionally stored procs in place of type's methods)?

2. Could you, please, point to any documentation/information about the internal implementation of objects/types in Oracle?

3. Creation of a nested table column require that you create a "data-holder table" so the nesting idea is quite limited.
I have the feeling that there is a huge gap between JAVA-like objects idea and Oracle "objects".
Is there any change of significant improvement of the Oracle "objects"? Many people (including me) would like to simply load XML file into a DB row and then recursively analyze it... storing it as CLOB is the solution (I remember 4 ways defined in the documentation) but then DB won't be much better than a plain filesystem.

4. A little off-topic but don't you think that the code should have the ability to be nested as well? In java we can have proc/sub-proc/sub-sub-proc/sub-sub-sub-proc but Oracle offers only stored procs and packages with 2-levels depth (I would like to have pkg.proc and pkg.proc.subproc defined in separate containers like classes in JAVA, not a huge package holding all the code).

Regards
Tom Kyte
July 29, 2010 - 9:26 am UTC

1) that they are DDL is of no concern to me and I don't get why that "upsets" people.

Look - a view is DDL, there is nothing you can do in a view that you cannot just do in a query right - so why use views? I can take your 'exception' here and apply it to almost everything. You seem to be arguing against views, synonyms and the like????

2) the document http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e11822/toc.htm is about it. The internals are actually obscured a bit from view, that was part of the point. If you have access to my first book Expert one on one Oracle - I go into some detail on it using a bit of sleuthing - something anyone could do - but that is about it.


I also write in that book that I use objects for programming purposes in PL/SQL, I never use them to persist data in tables - I'll use object relational views to expose relational data as an object - but I stick with relational tables. With the exception of things like the XMLType and the like - I don't get into the object extensions as a storage mechanism - especially things like nested tables and varrays as persistent storage methods. I use them for PLSQL programming all of the time, but not for data storage personally.

3) ... I have the feeling that there is a huge gap between JAVA-like objects idea and
Oracle "objects".
...

how so - for example?

... Is there any change of significant improvement of the Oracle "objects"? ...

you would sort of have to list what you feel to be missing first.


If you want to load xml as a clob and just deal with it - that is your choice, you can certainly bypass all of the included feature/functionality and recode it yourself over and over again - lots of developers do that because they never want to spend the time becoming familiar with the 'evil database'.

Java is just a language, sql is just a language. Ignore either one at your own peril I guess.

4) You can have proc/sub-proc/sub-sub-proc/sub-sub-sub-proc in plsql - have you read the entire language spec yet? The scoping and visibility is different than Java, just like it is in C, and C++ and Ada, and Cobol and <any language here> (meaning, java is just a language, it is not the best language, it is not the perfect language, it is *just* a language...)


ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3      procedure p;
  4  end my_pkg;
  5  /

Package created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3
  4  procedure priv( y in out number )
  5  is
  6      procedure sub_priv( x in out number )
  7      is
  8          function sub_sub_priv return number
  9          is
 10          begin
 11              return 42;
 12          end;
 13      begin
 14          x := x+sub_sub_priv;
 15      end;
 16  begin
 17      sub_priv( y );
 18  end;
 19
 20
 21  procedure p
 22  is
 23      l_data number := 0;
 24  begin
 25      dbms_output.put_line( 'before ' || l_data );
 26      priv( l_data );
 27      dbms_output.put_line( 'after ' || l_data );
 28  end;
 29
 30
 31  begin
 32      dbms_output.put_line( 'you only see me once! during elaboration....' );
 33  end my_pkg;
 34  /

Package body created.

ops$tkyte%ORA11GR2> exec my_pkg.p
you only see me once! during elaboration....
before 0
after 42

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec my_pkg.p
before 0
after 42

PL/SQL procedure successfully completed.


A reader, July 29, 2010 - 10:21 am UTC

Hi Tom,

I could see some issues in my production database with the nested tables - when the schema was designed initially they created 'an object' as one of the columns in one table - and the definition of the object type contains '3 nested tables' and each nested table again contains one more nested table (nested nested tables)

I could see that the querying these tables is slow and also when I use CTAS statement to create with some data from this table - this is taking few hrs just for 80K records .

Generally asking - why the nested tables cause performance issue? is it because of the way they are stored in the schema physically ? and also is not good as performance wise to define an object as a column in a table?


Tom Kyte
July 29, 2010 - 12:18 pm UTC

insufficient data for me to answer here. You say 80k records but with nested tables that could be millions of real records. No idea what you did so I cannot comment.


if you are interested in the structure of a nested table, exactly what happens under the covers, dig up a copy of my first book Expert one on one Oracle or my current book Expert Oracle Database Architecture - they go into great detail.


In short:

a nested table will cause a 16 byte raw to be added to the parent table. A unique constraint will be placed on this column. This column will be auto-populated for us.

The nested table will be created with a column NESTED_TABLE_ID, a 16 byte raw column- that is NOT INDEXED. This is the foreign key back to the parent, you probably want to index this in most cases.


Or you might even want to make your nested table an IOT - index organized table. This is probably the case since you always access by the primary key (always go from parent to child). Say you have a EMP object and an EMP_TAB_TYPE - table of that object type - you could:

ops$tkyte%ORA11GR2> CREATE TABLE "OPS$TKYTE"."DEPT_AND_EMP"
  2  ("DEPTNO" NUMBER(2, 0),
  3   "DNAME"  VARCHAR2(14),
  4   "LOC"    VARCHAR2(13),
  5  "EMPS" "EMP_TAB_TYPE")
 12  NESTED TABLE "EMPS"
 13     STORE AS "EMPS_NT"
 14     ( (empno NOT NULL, unique (empno), primary key(nested_table_id,empno))
 15       organization index compress 1 )
 16     RETURN AS VALUE
 17  /
Table created.


that would store the nested table as an IOT.


But the bottom line here is the 16 byte raw column - totally not necessary, you already have a perfectly good primary key in your parent table (mine would be DEPTNO) and a perfectly good foreign key candidate in the child table - you do not need this extra 16 bytes of raw data per row per parent and child record per nested table - along with it's unique constraint and the index YOU must create.



A reply

adderek, August 17, 2010 - 4:55 pm UTC

Hi Tom,
Thanks for your reply.
Now my reply:
1. I'm still unable to see any real profit of using tables+types or tables only - but I guess that there must be explanation for that.

2. Thanks for the link and info about objects

3. Some items I am missing:
- No constructors
- No destructors/finalizers
- No "finally" block
- Exceptions propagation not possible - once I re-rise the exception the original one is lost (thus should be logged)
- I cannot get real call stack - but anyway DBMS_UTILITY helps a lot
- Difference between PL/SQL and SQL and data storage (ex. I cannot store clob as anydata into the table)

4. I'm using sub-sub-sub-sub procedures like this. But those are stores in a huge container that is very different from ex. JAVA.
When checking DBMS_UTILITY call stack I'm not getting package.procedure.subprocedure.subsubprocedure. Instead I'm getting package.procedure:line (what is difficult to use unless I have access to the archival version of that source from the execution time).
A workaround for code storage might be to split package body into multiple files (and directories) and then load them into DB using sqlplus and using @@procedure/procedure.sql, @@subprocedure.sql, etc.
Tom Kyte
August 19, 2010 - 1:39 am UTC

1) Well, there is the extremely popular XMLTYPE column - and the spatial types - there are uses for them.

In day to day tables we create for our applications - not typically - however they do have their uses.

I mostly use types in PLSQL code - not in my table creates.

3) there are constructors for types??

and you are trying to compare object relational types to java classes - they are different - if they were the same, we'd just be java all over again. They are different things, in different languages, used for different purposes.

You can get the real call stack. You told us how.



4) and if you don't have the archival source code - pkg.proc.sub.sub.sub would be useless as you don't know what that code was, is or anything. I'm not sure this is a valid argument for anything - if you don't know what the source was - a name is useless.

and since there could be 15 pkg.proc.sub.sub.sub's - you still need - the source code line or it is useless by itself as well.

Second reply - I'm a difficult person ;)

adderek, August 21, 2010 - 4:53 pm UTC

1. Thanks. Item closed.


3. I agree that it is the case - I'm missing some JAVA features. However:

- Call stack is just a text substitute of what other languages can offer:
"VARCHAR2(4000)" != "TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER"
"Exception" != "Exception.ToString()"
Text table with fancy formatting is different from an ordered collection (a stack) of calls.
I would like to have this improved at some point in PL/SQL - return a VARCHAR2A collection instead of concatenated strings.

- FINALLY block is missing thus some code must be redundant often in PL/SQL:
DECLARE
x NUMBER;
BEGIN
x := DBMS_SQL.OPEN_CURSOR;
...
DBMS_SQL.CLOSE_CURSOR(x);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_SQL.CLOSE_CURSOR(x);
WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(x);RAISE;
END;
I would like to have this improved at some point in PL/SQL.

- Difference between SQL and PL/SQL objects that you haven't commented - I assume that we just must live with that as you cannot afford re-architecture. Item closed.

- Exception propagation
There is no re-raise - we hav only a "raise a new exception from here, forget about the real cause".
I would like to have this improved at some point in PL/SQL.


4. You got strong and valid arguments here but you cannot deny that PL/SQL code is capped at schema.package.proc depth. This limit is a flaw on PL/SQL in my eyes.
I can use hierarchical queries and parent-child relation in the Oracle - relational database. Why this is not true for PL/SQL?
I assume that we must live with that - such a improvement might be a huge task. Item closed.

Nested Table - Array elements

Rajeshwaran, Jeyabal, October 24, 2010 - 7:17 am UTC

A quote from Expert-one-on-one-Oracle

<quote>
Nested tabel ʹArrayʹ elements have no specific order.The data in the collection may be returned in a very different order than it was in when you inserted it.
</quote>

rajesh@10GR2> CREATE or replace type l_array is table of number;
  2  /

Type created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> declare
  2     data l_array := l_array();
  3  begin
  4
  5     for i in 1..10
  6     loop
  7             data.extend;
  8             if mod(i,2) = 0 then
  9                     data(i) := i*2;
 10             else
 11                     data(i) := i;
 12             end if;
 13     end loop;
 14
 15     dbms_output.put_line (' Using loop ');
 16     for x in (select * from table(data))
 17     loop
 18             dbms_output.put_line (x.column_value);
 19     end loop;
 20  end;
 21  /
 Using loop
1
4
3
8
5
12
7
16
9
20

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>


Tom:

Tried multiple scenario's but not able to achieve.Can you please show me how the data in the collection may be returned in a very different order than it was in when you inserted it?
Tom Kyte
October 25, 2010 - 5:42 pm UTC

I said "inserted it"

you'd have to put it away in a table and fetch it back out. The point is - a nested table saved in a table does not promise the ordering will be preserved.

You are not using an insert and a select back out anywhere - so it doesn't apply

And furthermore - all we have said is "we don't promise to return it in the same order". Meaning "you better not rely on it - even if you observe it to be returned in order 5,000,000,000 times in a row - we have warned you"


Nested Table - Array elements

Rajeshwaran, Jeyabal, October 26, 2010 - 12:19 pm UTC

Thanks Tom. As per your suggestion did benchmarks in both Nested tables and Varray and now i am very clear.

Nested Table

rajesh@10GR2> create type too as object(
  2     id number,
  3     dt date,
  4     data varchar2(20)
  5  );
  6  /

Type created.

Elapsed: 00:00:00.15
rajesh@10GR2> create type foo is table of too;
  2  /

Type created.

Elapsed: 00:00:00.18
rajesh@10GR2>
rajesh@10GR2> create table t(
  2     x number,
  3     y foo)
  4  nested table y store as t_y;

Table created.

Elapsed: 00:00:00.11
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> INSERT INTO t
  2    (
  3      x,
  4      y
  5    )
  6  SELECT level ,
  7    CAST(multiset
  8    (SELECT too(level,sysdate,rpad('*',20,'*'))
  9    FROM dual
 10      CONNECT BY level <= 1000
 11    ) AS foo)
 12  FROM dual
 13  CONNECT BY level <= 5;

5 rows created.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> SELECT x,
  2    tab.id
  3  FROM t ,
  4    TABLE(t.y) tab
  5  WHERE t.x   = 2
  6  AND rownum <= 5;

         X         ID
---------- ----------
         2        191
         2        192
         2        193
         2        194
         2        195

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2>


Now data are not retrieved in the order they got inserted, but got displayed randomly. due to primary key and foreign key association between Parent table and child table (nested table) as per the Explanation available in Chapter 6 Expert-one-one-Oracle.

Now with Varrays data are in the order they got inserted.

Varrays

rajesh@10GR2> create type too as object(
  2     id number,
  3     dt date,
  4     data varchar2(20)
  5  );
  6  /

Type created.

Elapsed: 00:00:00.12
rajesh@10GR2> create type foo is Varray(1000) of too;
  2  /

Type created.

Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> create table t(
  2     x number,
  3     y foo);

Table created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> declare
  2     l_foo foo;
  3  begin
  4     for k in 1..5
  5     loop
  6             l_foo := foo();
  7             for i in 1..1000
  8             loop
  9                     l_foo.extend;
 10                     l_foo(l_foo.last) := too(i,sysdate,rpad('*',20,'*'));
 11             end loop;
 12     insert into t values (k,l_foo);
 13     end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> SELECT p.x,
  2    t_tab.id
  3  FROM t p,
  4  TABLE(p.y) t_tab
  5  where p.x = 2
  6  and rownum <= 5;

         X         ID
---------- ----------
         2          1
         2          2
         2          3
         2          4
         2          5

Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2>