Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Munzer.

Asked: March 24, 2002 - 9:52 pm UTC

Last updated: February 03, 2023 - 6:38 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom:

What is really the difference and when you would use each of oracle datatypes:

1. Index-by-tables
2. Nested Tables
3. Varrays

My understanding is that index by tables are for data of same type that is stored in memory.

For nested tables you can store the variables values in oracle table.

Varrays are same as nested except they are confined to a certain number.

AM I correct?

Would you use a nested table for two tables like a PO table and items table iinstead of referring to two tables.

Thank you,




and Tom said...

The major difference between:

(index by tables) and (nested tables/varrays)

is that index by tables are only available in PLSQL, nested tables/varrays are avaialable in both PLSQL *and* SQL.

Index by tables are basically "sparse" arrays that need no allocation. For example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number index by binary_integer;
3 data array;
4 begin
5 data(1000) := 1;
6 end;
7 /

PL/SQL procedure successfully completed.


Here plsql gladly accepts an entry in the 1,000'th slot -- without doing anything else. There is nothing in slots 1, 2, ... 999 (or 1001 or -1, -2,.... )

That array has allocated space for 1 element in the 1,000th position. Nested tables/varrays do not behave that way:


ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data(1000) := 1;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5

Here it is saying "you haven't allocated me any space up there...". So, we use the .extend attribute:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1000);
6 data(1000) := 1;
7 end;
8 /

PL/SQL procedure successfully completed.


and we can do so.

Note that we do have to allocate 1,000 entries:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1);
6 data(1000) := 1;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 6

the nested table/varray type isn't "sparse" like the index by table.


Day to day, in plsql code, i generally use index by tables exclusively. They are a little faster, a little more flexible. It is when I need to use the table type in SQL that I use a nested table (see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061

for an example of what I mean by that)....


There are other differences between varrays and nested tables when you use them for storage in a database table.  For example -- varrays maintain their "order".  Nested tables do not.  Varrays are stored in lobs -- nested tables in separate tables.  There are certain DML operations available for nested tables that are not avaialable for varrays.  See

http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76976/toc.htm <code>

for more details.


Rating

  (86 ratings)

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

Comments

Helena Markova, March 28, 2002 - 2:54 am UTC


Arun Panchal, March 28, 2002 - 1:59 pm UTC


Need Help!!!

Suwarna, August 03, 2003 - 9:20 am UTC

Hi Tom,

Sorry for not following your procedures/policies. Was really needing your help and hence added my query to Collection types question.

I have three tables. Structure of each is:
Cash: This table holds all payments made by customers by date by account_reference. Columns are:
cash_id number(22) Primary Key,
receipt_date date,
account_reference_number varchar2(13),
cash_amount number(22,2)

Charge: This table holds all the charges raised against the products(Water, Sewerage, etc) provided by Water system to the customers. Columns are:
charge_id number(22) Primary Key,
activity_date date,
account_reference_number varchar2(13),
billable_amount number(22,2)
charge_type number(22)

Cash_Application: This table holds combination of cash_id and charge_id for an account_reference with the cash_amount received for each charge. Columns are:
cash_application_id number(22) Primary Key,
fk_cash_id number(22),
fk_charge_id number(22),
account_reference_number varchar2(13),
applied_amount number(22,2)

Now, Cash_Application table needs to be loaded using Cash and Charge tables recursively. There are some rules to do cash allocation for each charge.

1. The earliest cash will be allocated to the earliest charge

2. If there are more than one charge records on the same date, then cash will be allocated in proportionally to each charge.Thus if cash_amount is x and there are three charges a, b and c, cash applied to each charge will be x*a/(a+b+c), x*b/(a+b+c), x*c/(a+b+c), respectively.
There are two possibilities ....
i.billable_amount for each charge will become zero after allocating the cash
ii. cash will be insufficient for sum(billable_amount) for an account_reference on a particular date.
In first case, if some more charges exist for the account, cash will be applied to those charges
In second case, new cash record will be brought in from cash table and it will be allocated proportionally to remaining charges

3. Any cash that remains after fully allocating all the charges on the account for all days, will remain un-allocated

Please guide on how this can be done using PL/SQL.

Thanks a ton!!!

Regards,
Suwarna.

Tom Kyte
August 03, 2003 - 9:26 am UTC



"hey, please code my system for me -- here are the requirements, go"

if you have a focused question, about how the technologies work, or about how plsql can do something, or how to write a tricky query or something -- I'm here.

"code my system".... no, not really.

sounds like you have solid specs there. turning specs like that into code is something programmers do.

I will do the coding as I am here for that ....

A reader, August 03, 2003 - 9:34 am UTC

Hi,

Thanks for going through the question. I really want to do the coding ... but not really knowing from where to start. Some pointers on this will be really appreciated. Have tried doing this using varray and using normal cursors. But getting messed up. Thought you can help me out (not very good of me expecting such a thing, but still thought!!!)

Regards,
Suwarna

Tom Kyte
August 03, 2003 - 10:14 am UTC

just sounds like you want to bulk collect the data into arrays of records from both tables and "bump and grind" the data procedurally according to your rules.

One example please!!!

Reader, August 05, 2003 - 4:43 am UTC

Hi Tom,

Will you please give an example for 'bulk collect the data into arrays of records from a table'?

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

ops$tkyte@ORA920> declare
  2          type empArray is table of emp%rowtype index by binary_integer;
  3
  4          l_data  empArray;
  5          n       number := 10;
  6
  7          cursor c is select * from emp;
  8  begin
  9          open c;
 10          fetch c bulk collect into l_data limit N;
 11          close c;
 12  end;
 13  /

PL/SQL procedure successfully completed.
 

I am gettin ORA error

Reader, August 07, 2003 - 5:58 am UTC

Hi Tom,

 I tried the above example by creating a record type and then table of records. Following is the code and error messages ....

SQL> ed
Wrote file afiedt.buf

  1  declare
  2      type cash_Rec is record (account_reference_number char(13), cash_id number(22), cash_amount decimal(22,2));
  3      type cash_Tab is table of cash_Rec index by binary_integer;
  4      l_cashTab cash_Tab;
  5      cursor cashCursor is
  6              select acc_ref_num, pk_cash_id, amount
  7              from cash
  8              order by acc_ref_num, rcpt_dt;
  9      l_counter integer := 10;
 10   begin
 11      open cashCursor;
 12      fetch cashCursor bulk collect into l_cashTab limit  l_counter;
 13   close cashCursor;
 14*  end;
SQL> /
    fetch cashCursor bulk collect into l_cashTab, l_cashTab, l_cashTab  limit  l_counter;
                                       *
ERROR at line 12:
ORA-06550: line 12, column 40:
PLS-00597: expression 'L_CASHTAB' in the INTO list is of wrong type
ORA-06550: line 12, column 5:
PL/SQL: SQL Statement ignored

Where am I going wrong????
 

Tom Kyte
August 09, 2003 - 4:31 pm UTC

ops$tkyte@ORA920> declare
  2          cursor cashCursor is
  3          select empno, ename, hiredate from emp;
  4
  5          type cash_tab is table of cashCursor%rowtype index by binary_integer;
  6
  7          l_cashTab cash_tab;
  8          l_counter number := 10;
  9  begin
 10          open cashCursor;
 11          fetch cashCursor bulk collect into l_cashTab limit l_counter;
 12          close cashCursor;
 13  end;
 14  /

PL/SQL procedure successfully completed.


<b>why do you have three tables in your into clause?</b> you only need one.
 

Corrections ....

A reader, August 08, 2003 - 11:33 am UTC

Some corrections to above code ....:))

SQL> ed
Wrote file afiedt.buf

  1  declare
  2      type cash_Rec is record (account_reference_number char(13), cash_id 
number(22), cash_amount decimal(22,2));
  3      type cash_Tab is table of cash_Rec index by binary_integer;
  4      l_cashTab cash_Tab;
  5      cursor cashCursor is
  6              select acc_ref_num, pk_cash_id, amount
  7              from cash
  8              order by acc_ref_num, rcpt_dt;
  9      l_counter integer := 10;
 10   begin
 11      open cashCursor;
 12      fetch cashCursor bulk collect into l_cashTab limit  l_counter;
 13   close cashCursor;
 14*  end;
SQL> /
    fetch cashCursor bulk collect into l_cashTab limit  
l_counter;
                                       *
ERROR at line 12:
ORA-06550: line 12, column 40:
PLS-00597: expression 'L_CASHTAB' in the INTO list is of wrong type
ORA-06550: line 12, column 5:
PL/SQL: SQL Statement ignored

Where am I going wrong????
 

Tom Kyte
August 10, 2003 - 11:51 am UTC

guess you are using 8i which did not support bulk collects into array of records, only into records of arrays.

Using 9.2 Oracle version

Su, August 11, 2003 - 4:28 am UTC

Hi Tom,

Oracle version is 9.2. Are some added privileges required to create arrays in the schema?

Regards.

Tom Kyte
August 11, 2003 - 7:40 am UTC

ops$tkyte@ORA920> create user a identified by a;

User created.

ops$tkyte@ORA920> grant create session, create type to a;

Grant succeeded.

ops$tkyte@ORA920>
ops$tkyte@ORA920> @connect a/a


a@ORA920>
a@ORA920> create type myArray as table of varchar2(250)
  2  /

Type created.
 

Example for sequentially accessing records in an Array

Reader, August 11, 2003 - 4:49 am UTC

Hi Tom,

Will you please give an example of how the records loaded by above example can be fetched or read sequentially and some manipulations done on the data.

Thanks and regards,
A reader


Tom Kyte
August 11, 2003 - 7:42 am UTC

A reader -- you do not know how to loop over an array?

for i in 1 .. array_variable.count loop
......




Upper limit on collection types

BK, September 02, 2003 - 6:20 pm UTC

Very good explanation, I still have one more doubt in my mind. What's the upper limit on different collection types? If dependent on hardware then what would be upper limit on a server with 1GB of RAM. I'm considering collecting around 50K records into a table type and passing this table to a procedure for dumping into a file. Would this approach be faster than writing to the file directly. Your insight is greatly appreciated.

Thanks,


Tom Kyte
September 02, 2003 - 9:15 pm UTC

that would be about 50 times more then I would personally feel comfortable with.

do 1000, write 1000, do 1000 more, write 1000 more.

no reason you have to "get them all", "write them all"

modularize your code and "get a few", "write a few", "get a few more", "write a few more"

need more explanation on upper limit of collection type

BK, September 18, 2003 - 11:30 pm UTC

Tom,
Could you elaborate on why you would not be comfortable with this approach. We have taken this approach keeping 4-5 thousand records per day in mind. Now program has to handle Initial load of 350K records, which was not anticipated. We have modularize our code and all our interfaces creates table type and one program takes care of writing to the file. Now if we want to follow 'Get few' and 'write few' approach, we are missing the last records. Here is the eg.

declare
TYPE varchar_table_type IS TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
v_pac_table varchar_table_type;
filedir varchar2(100) := '/u226/appl/aicdappl/11.5.0/data/outbound';
v_filename varchar2(50) := 'append_file.txt';
x_status varchar2(240);
x_msg varchar2(240);
cursor c1
is
select rownum||'-'||owner||'.'||object_name rec
from all_objects
where owner = 'AR'
and object_type = 'TABLE'
and rownum < 53;
begin
for x in c1 loop
v_pac_table(v_pac_table.count+1) := x.rec;
dbms_output.put_line('Count : '||v_pac_table.count);
IF (MOD(v_pac_table.count,10) = 0) Then
interface_util.create_file(filedir, v_filename, v_pac_table, x_status, x_msg);
v_pac_table.delete;
END IF;

end loop;
dbms_output.put_line('X_Status: '||x_status);
dbms_output.put_line('X_msg: '||x_msg);
end;

create file procedure opens file in an 'A' mode and writes data to the file. In this case how do we write the last two records ? Also I would greatly appreciate your input on optimum number of records each collection type can handle. Thanks,


Tom Kyte
September 20, 2003 - 4:52 pm UTC

because of "unanticipated things"

just put outside of the loop a call to interface_util.create_file if v_pac_table.count > 0 ??



Please provide more information

BK, September 22, 2003 - 12:58 pm UTC

Hi Tom, I wish I could reply before you could read my previous question. Tried for two days but couldn't access the site. It was very 'stupid' to ask, because it was so simple, I wasn't thinking right and I apologize for wasting your time.
Your replys to the questions asked have greatly influenced my design and coding approach. I vist this site atleast once a day. Great job Tom. As you have always done could you also please explain what 'unanticipated' things can happen and why. How do I find out optimum number of records each collection type can handle, I think Oracle community will better understand when to use what collection type. Thanks,


Tom Kyte
September 22, 2003 - 1:53 pm UTC

you used the term unanticipated!


Could you elaborate on why you would not be comfortable with this approach. .... which was not anticipated. .....


that is why i'm not comfortable with the "fill up an array without any bounds" approach -- the un-anticipated things in life will kill you.


the "optimum number" is purely a function of RAM here. I am not comfortable with more then a couple hundred, maybe few thousands, of entries "in memory". not very scalable after a while.



huss, January 17, 2004 - 5:27 am UTC

Dear Tom,
in this example
1 declare
2 type emp is record (name varchar2(50),id number(3));
3 type emp_Tab is table of emp index by binary_integer;
4 emps_list1 emp_tab;
5 emps_list2 emp_tab;
begin
...
...
...
-- I can do that
emps_list1:= emps_list2 ;
-- but it gives me error when try to do that
if ( emps_list1 = emps_list2) then ....
so , while that not work , how can i compare between the 2 tables of recored without loop inside them and compare it's fields one by one .



Tom Kyte
January 17, 2004 - 5:02 pm UTC

well, 10g will let you compare two collections -- but until then -- it is 100% "diy" (do it yourself)

you would have to procedurally do it element by element.

what is index by binary integer

A reader, February 02, 2004 - 3:19 am UTC

Hi

What is the difference between

type l_array is table of varchar2(256);

type l_array is table of varchar2(256) index by binary integer;

which is faster? When should index by binary integer used?

Tom Kyte
February 02, 2004 - 7:43 am UTC

one is a collection, the other a plsql index by table.

declare
type collection_type is table of varchar2(256);
type plsql_table_type is table of carchar2(256) index by binary_integer;

l_coll collection_type;
l_tab plsql_table_type;
begin

One (collection) needs to be "extended" to allocate space, the other does not.

l_coll.extend;
l_coll(1) := 'foo';

l_tab(1) := 'bar';


One (collection) can be initialized easily, the other -- not:


l_coll := collection_type( 'hello', 'world', 'foo', 'bar' );

l_tab(1) := 'hello';
l_tab(2) := 'world';
l_tab(3) := 'foo';
l_tab(4) := 'bar';



Those are the "major" differences -- I find plsql table types generally "easier" to use since they need not be extended and "contigous" (eg: in order to have l_coll(100) - I must have 1..99 allocated. In order to have l_tab(100) i only need that entry)



Why is this bulk collect behaving like this in Oracle 9i?

Raj, February 12, 2004 - 4:31 am UTC

Dear Tom,
Sorry for posting this here instead of waiting for you to 
be ready for taking further questions but can you tell me why the first code executed without errors but second doesnot?

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Feb 12 14:59:07 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: scott@testsystem
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> declare
  2  type emparray is table of emp%rowtype index by binary_integer;
  3  cursor c1 is select * from emp;
  4  rowarray emparray;
  5  begin
  6  open c1;
  7  fetch c1 bulk collect into rowarray limit 100;
  8  close c1;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> ed
Wrote file afiedt.buf

  1  declare
  2  cursor c1 is select empno,ename from emp;
  3  type emparray is table of c1%rowtype index by binary_integer;
  4  rowarray emparray;
  5  begin
  6  open c1;
  7  fetch c1 bulk collect into rowarray limit 100;
  8  close c1;
  9* end;
SQL> /
fetch c1 bulk collect into rowarray limit 100;
                           *
ERROR at line 7:
ORA-06550: line 7, column 28:
PLS-00597: expression 'ROWARRAY' in the INTO list is of wrong type
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored


SQL>
 

Tom Kyte
February 12, 2004 - 8:40 am UTC

it was an issue that is fixed.

ops$tkyte@ORA9IR2> declare
  2  type emparray is table of emp%rowtype index by binary_integer;
  3  cursor c1 is select * from emp;
  4  rowarray emparray;
  5  begin
  6  open c1;
  7  fetch c1 bulk collect into rowarray limit 100;
  8  close c1;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2  cursor c1 is select empno,ename from emp;
  3  type emparray is table of c1%rowtype index by binary_integer;
  4  rowarray emparray;
  5  begin
  6  open c1;
  7  fetch c1 bulk collect into rowarray limit 100;
  8  close c1;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from v$version;
 
BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
 

A reader, March 10, 2004 - 4:03 pm UTC

Tom,

For index-by-table i have some doubts with the difference regarding index by binary_integer / index by varchar2(size_limit)

I referred the docs which mention
<doc>
When you reference an element of an associative array that uses a VARCHAR2-based key, you can use other types, such as DATE or TIMESTAMP, as long as they can be converted to VARCHAR2 with the TO_CHAR function.
</doc>

I am still not clear as to why one would use index by varchar2.. can you please give me a example.

Thanks.

Tom Kyte
March 10, 2004 - 4:31 pm UTC

in other languages it is sometimes called an associative array.

array( 'VA' ) returns 'Virginia'
array( 'NY' ) returns 'New York'

and so on -- it is a lookup table.

OK

M.Srinivas, March 11, 2004 - 9:18 am UTC

Dear Tom,
I would like to pass an array into a procedure and do some
processing inside the procedure and return the processed
output also as an array.
For example:
sql> create type nums_va as varying array(5) of number;
and
pass this array into a procedure like
sql>create procedure p(array in out nocopy nums_va) as
...
Is it possible to have a structure like this?Could you please help?
Please do reply.
Bye!


Tom Kyte
March 11, 2004 - 1:29 pm UTC

sure, that works? what problem are you having exactly....

Table Record

Tk, April 07, 2004 - 10:41 pm UTC

Tom I have this package nothing fancy. However, cannot figure out why I am getting.

PLS-00382: expression is of wrong type
Using ora: 8.1.7.4
Here it is.

CREATE OR REPLACE package SRCH_PKG_1 is

TYPE t_stg_rec IS RECORD
(
ID varchar2(20),
SSN varchar2(9),
FIRST_NAME varchar2(50),
LAST_NAME varchar2(50)
);

TYPE t_stg_tab IS TABLE OF t_stg_rec;
i binary_integer := 1;

function SEARCH_RESULT (IN_FNAME in varchar2,
IN_LNAME in varchar2,
IN_SSN in varchar2,
IN_ID in varchar2
)
return t_stg_rec ;

end ;
/

CREATE OR REPLACE package body SRCH_PKG as

L_SSN BORROWER.SSN%TYPE ;
L_FNAME BORROWER.FIRST_NAME%TYPE ;
L_LNAME BORROWER.LAST_NAME%TYPE ;

function SEARCH_RESULT (IN_FNAME in varchar2,
IN_LNAME in varchar2,
IN_SSN in varchar2,
IN_ID in varchar2
)
return t_stg_rec is

tstgrec t_stg_rec;
tstgtab t_stg_tab;

L_ID varchar2(50) ;
L_REC_CTR number := 0 ;
L_TREC_CTR number := 0 ;

cursor cr (p_ID in varchar2, p_SSN in varchar2, p_FNAME in varchar2, p_LNAME in varchar2) is
select LA.APPL_ID, SSN, FIRST_NAME FNAME, LAST_NAME LNAME
from CRT.LOAN_APPLICATION LA, CRT.BORROWER B
where LA.LOAN_APPL_ID = B.LA_ID
and SSN = p_SSN
and FIRST_NAME like p_FNAME
and LAST_NAME like p_LNAME
;

BEGIN

tstgtab := t_stg_tab(tstgrec);
for tstgrec in cr (L_ID, L_SSN, L_FNAME, L_LNAME) loop

tstgtab.extend;
tstgtab(i) := tstgrec; --< ERROR!!!! PLS-00382: expression is of wrong type
i := i + 1;

-- some additional logic here

end loop ;

return tstgtab;
END SEARCH_RESULT;

END SRCH_PKG ;


Tom Kyte
April 08, 2004 - 10:06 am UTC


is
x number;
begin
for x in (select * from dual ) -- creates a brand spanking new X that never
-- existed
loop


you implicitly created a cursor record in your loop (you never declare loop variables, they just "appear")


looks like you might want to be coding:


your_table := your_table_type();
open cr( ... )
loop
fetch cr into your_record;
exit when cr%notfound;
your_table.extent;
your_table(your_table.count) := your_record;
end loop
close cr;



just a comment here -- your naming convention would drive me utterly nuts. what is the type, what is the variable -- they look the same to me. I would seriously give thought to changing that practice.

Thank you it worked

Tk, April 09, 2004 - 1:27 pm UTC

In regards to naming convention could not agree with you more. I changed it.

Could you suggest or better publish naming standards that you use.
What is better way of naming objects, types, and cursors, variable of above? I think this information will be very helpful for all levels of developers and DBAs.
Kind of setting a baseline.

In you book you do not really address this issue. However, I have seen a lot of poorly written code including mine.
So if you could include to your preaching on use of bind variable also humane code practices that would make a world of good.


Tom Kyte
April 09, 2004 - 4:13 pm UTC

local variables = l_<meaningful name>
global package variables = g_<meaningful name>
parameters = p_<meaningful name>

everything else -- <meaningful name>, but I would not tend to use "_" in a type name and no underscore in say a column/table name -- as the only difference, too confusing.

My types tend to end in "Type" or "_Type"

Tables, columns -- normal meaningful names.

packages many times end in _pkg and the procedures/functions therein have meaningful names.

nested table question

A reader, April 24, 2004 - 2:05 pm UTC

In your expert one on one book - p 250 (WROX ed), you have
given us the syntax of the nested storage clause.
I understand your exp, imp trick but how did you manage
to understand that syntax - esp. the part that allows
you to specify the columns of nested table as well.

"...store as emps_nt( empno not null, unique( empno),
primary key ( nested_table_id, empno)...."

I managed to figure out the syntax of giving oraganization
index without specifying the columns (you still have
to specify the primary key clause for the nested table)
- hoewver, I am curious how did you figure out the
above syntax (of specifying column details) in the first
place?

Also, the "unique" clause above - can that be applied
to a normal create table? I know that you can specify
PK creation as part of the table creation statement.


Thanx!


Tom Kyte
April 26, 2004 - 5:14 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96594/adobjdes.htm#441428

shows the structure - other parts of that doc go thru the rest.  that doc:

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96594/toc.htm <code>

is an entire book on this stuff.


unique, check, primary key, foreign key -- they can all be part of a CREATE TABLE

%ROWTYPE as IN parameter : mapping in jdbc

Puneet Jain, June 17, 2004 - 7:35 am UTC

Hi
I want to call a stored procedure having a %rowtype as input parameter from java code.

I dont know how to set the parameter

can u help me


Tom Kyte
June 17, 2004 - 10:16 am UTC

you cannot. PLSQL record types work in plsql only.

to pass/return a "record", you would need to use a SQL TYPE

create type foo as object (a int, b date, c varchar2(30) )
/

then you can use jpub to automate a class that maps to that record.

Shalu Aggarwal, July 15, 2004 - 1:46 pm UTC

Tom

I have one question on nested tables:

I have following table:
SQL> desc vn_algorithm
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 ALGORITHM_ID                              NOT NULL NUMBER(38)
 NAME                                      NOT NULL VARCHAR2(100)
 SECURITY_TYPE                                      SECURTY_LIST_NESTED_TABLE

How can I do a distinct select on column 'security_type' which is a nested table of security types.

I want something like this:
Select distinct security_type from vn_algorithm
where algorithm_id=1;

Thanks!
Shalu 

Tom Kyte
July 15, 2004 - 6:07 pm UTC

select distinct v2.column_value
from vn_algorithm v, table(v.security_type) v2
where v.algorithm_id = 1;

Shalu Aggarwal, July 15, 2004 - 2:26 pm UTC

I made a mistake, the sql I want is:

Select distinct security_type from vn_algorithm;



Tom Kyte
July 15, 2004 - 6:12 pm UTC

so, you did not really want a nested table at all ( i find that people never ever really do! they want real tables because they sometimes want to treat the nested table as a real table -- not sometimes really, frequently!) this is readson 1314 I don't use nested tables as a persistent storage mechanism -- i've never met a table I didn't want to query!


you can search for nested_table_get_refs on this site -- or in Expert One on One Oracle I cover that in the OR chapter.

Shalu Aggarwal, July 16, 2004 - 10:23 am UTC

Tom

It was really great to know this undocumented feature. And it helped figured my answer:

select distinct b.*
from
vn_interpolation_algorithm a,
table(a.SECURITY_TYPE) b;

But I am stuck again in next qry, which is somethin like this:

Select name
From vn_interpolation_algorithm
Where security_type='ABCD';

Meaning I want to select all the names where security type='ABCD'.

Either I get
ORA-00904: "B"."SECURITY_TYPE": invalid identifier
OR
ORA-01747: invalid user.table.column, table.column, or column specification
when I do something like:
Select name, b.*
from
vn_interpolation_algorithm a,
table(a.SECURITY_TYPE) b
where b.* = 'frmInt';

Pls advice what am i doing wrong.

Tom Kyte
July 16, 2004 - 1:40 pm UTC

undocumented???? kidding right? it is called unnesting, check out the application developers guide for OR features.


but it is TRULY looking like you don't want a nested table -- you seem to be wanting to use the table as a table -- awkward at best with nested tables. that's one of the main reasons I don't use them to store data.

in order to query that nested table -- you HAVE to do that unnesting -- you have to join, you do not have a table to query!!!

I would rethink your data model, you are using an inappropriate construct for the questions you want to ask.

Shalu Aggarwal, July 16, 2004 - 1:57 pm UTC

Thanks for bringing this point, I didn't know that I am using a wrong contruct.

It is a single column nested table. I cannot use a Varray because the no. of values is not fixed.

Do I have any other options except splitting this table into two master detail tables ?

Tom Kyte
July 16, 2004 - 2:13 pm UTC

it already is split into a master detail table.

you just lost most of the functionality and flexibility of the relational model is all.

you should just create a parent/child table straight out and use it -- like emp and dept.

Shalu Aggarwal, July 16, 2004 - 2:21 pm UTC

That's why i implemented nested table because I knew that they are master detail. I also knew I will loose some flexibility but didn't know that wld restrict me to write a simple sql.

Anyways, Is it a restriction of nested tables ? Can we not do that type of select from a nested table ? If we cannot, then what is the advantage of nested tables ? Pls help me clear my doubts.

Thanks!

Tom Kyte
July 16, 2004 - 10:50 pm UTC

nested tables are not master - detail. they are an object relational feature that allow you to realize what are known as "weak entities". they happen to be physically implemented as a master detail under the covers cause that is what you do in a relational database.

however, in 17 years -- I've never actually "seen" a true weak entity. An object you didn't want to query directly. Hence, I've yet to find the real world use of a nested table as a physical storage feature -- in plsql, they are awesome, as storage -- i'm not a fan.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:18200151434188 <code>

with a nested table - each row in the "parent" has a virtual table -- it is as if there were NO CHILD TABLE WHATSOEVER. that is what you asked for. each row in the parent has a "table", there is no single logical child table to query.



Shalu Aggarwal, July 16, 2004 - 3:16 pm UTC

Tom

I just figured out the qry w/o changing my construct:

select a.name
from
vn_interpolation_algorithm a,
table(a.SECURITY_TYPE) b
Where b.column_value='ABCD';

I really need your comments on this. Would also like to know what is the reason you didn't suggest me this. Because nested tables generally limits flexibility or something else.

Tom Kyte
July 16, 2004 - 10:56 pm UTC

because you so desperately want to use this security type nested table AS A TABLE -- as we all do.

so, nested tables are perhaps not the proper construct for you.

PK on Nested table

Branka, August 02, 2004 - 12:27 am UTC

Is it posible to make PK on the table that has nested table, that would have one column form table and one column from nested table to be unique?

I will use one of your exaples

  1  create or replace type
  2*  bmyArrayType as table of bmyScalarType
SQL> /

Type created.

  1  create table x
  2    ( id       int ,
  3      array myArrayType
  4    )
  5    nested table array store as X_Array_Data
  6    (
  7      (PRIMARY KEY (nested_table_id, X))
  8       ORGANIZATION INDEX
  9*   )
 10  /

Table created.

I would like to have primary key on columns id (from table x) and X (from nested table X_Array_Data). If I can not make primary key, can I make index? If not, how can I stop user from inserting duplicate records, and that to be on database level?
 

Tom Kyte
August 02, 2004 - 7:45 am UTC

well, not that I would ever use a nested table to store data on disk, but...

there is a 1:1 relationship between id and nested_table_id already (nested_table_id is a hidden unique 16 byte raw in table X, nested_table_id is a hidden non-indexed 16 byte rawn in table X_Array_Data). a primary key on (nested_table_id,column_value) in x_array_data gets you want you want.


But......


create table x ( id int primary key );
create table x_array_data( id references x, data int, primary key(x,data) ) organlization index;

achieves the same thing without the magic 16 byte raw and extra unique constraint.




Nested table PK

Branka, August 02, 2004 - 12:54 pm UTC

Is it posible to add PK on existing nested table?

  1   create or replace type
  2        bmyScalarType as object
  3       ( x int,
  4         y date
  5*      )
SQL> /

Type created.

  1  create or replace type
  2*     bmyArrayType as table of bmyScalarType
SQL> /

Type created.

  1   create table bx
  2        ( pk       int primary key,
  3          array myArrayType
  4       )
  5*      nested table array store as bX_Array_Data
SQL> /

Table created.

I would like to have same result as with statement. Is it posible?

  1  create table bx
  2    ( pk       int primary key,
  3      array myArrayType
  4    )
  5    nested table array store as bX_Array_Data
  6    (
  7      (PRIMARY KEY (nested_table_id, X))
  8       ORGANIZATION INDEX
  9*   )
 10  /
 

Tom Kyte
August 02, 2004 - 1:08 pm UTC

did you try the obvious:

ops$tkyte@ORA9IR2> alter table bx_array_data add constraint pk primary key(nested_table_id,x)
  2  /
 
Table altered.

that'll add a primary key constraint but -- it cannot be the same result as:

  5    nested table array store as bX_Array_Data
  6    (
  7      (PRIMARY KEY (nested_table_id, X))
  8      <b> ORGANIZATION INDEX</b>
  9*   )

since in order to be organization index, you must have specified the primary key -- so, can you add a primary key?  YES.  can you add a primary key and make it an IOT?  NO. 

nested table

Branka, August 02, 2004 - 1:03 pm UTC

What is difference between statements

create table x
2 ( pk int primary key,
3 array myArrayType
4 )
5 nested table array store as X_Array_Data

and

create table x
2 ( pk int primary key,
3 array myArrayType
4 )
5 nested table store as X_Array_Data


Tom Kyte
August 02, 2004 - 2:09 pm UTC

ops$tkyte@ORA9IR2>  create table bx
  2        ( pk       int primary key,
  3          array myArrayType
  4       )
  5       nested table store as bX_Array_Data
  6  /
     nested table store as bX_Array_Data
                        *
ERROR at line 5:
ORA-00905: missing keyword
 
 
ops$tkyte@ORA9IR2>  create table bx
  2        ( pk       int primary key,
  3          array myArrayType
  4       )
  5       nested table array store as bX_Array_Data
  6  /
 
Table created.
 

Collection Variable Types

Phil Garriss, January 03, 2005 - 4:09 pm UTC

This was very useful especially when you describe the difference between plsql arrays and non plsql types. However I do have a follow-up question. That is how is this better then inserting the array into a global temporary table? Does this cover security concerns better or is it faster or does it take less space allocation? I am trying to determine if I should move in this direction.

Thanks.

Tom Kyte
January 03, 2005 - 10:31 pm UTC

if you are programming something that needs an array for part of its procedural algorithm - why would you put it into a table?

These are in memory arrays, procedurally accesses.

global temporary tables are -- tables.

at the end of the day you make your decision to either use an array or a table -- based on what you NEED to accomplish.

Collection Variable Types

Phil Garriss, January 04, 2005 - 2:57 pm UTC

Ok. Thanks. It would be nice if Oracle would allow you to create the SQL object (the table) within PLSQL so that you could drop it later. That way you would not have to maintain various object and table types on the server. That means a lot more paper work and time checking things in and out. In other words our group was maintaining global temp tables but now we will have to maintain object and table types. However it is better then the 8i solution where the global temp table was the only alternative.

Tom Kyte
January 05, 2005 - 8:58 am UTC

and just what is wrong with maintaining things in the server????

it is part of your application.
it needs to be there.
think of it like a "view"

it is a relevant, necessary portion of your application that needs to be documented, configuration managed, etc etc etc.

Collection Vs GTT

Phil Garriss, January 05, 2005 - 2:06 pm UTC

Ok. I agree that it is nice to document the array specs. Perhaps someone will want to use it again. But the main question I have is this: if we have to create something on the server (ie. a type or a gtt) then is there a useful advantage to creating the table type over the gtt. For instance do the global temporary tables take up a certain amount of space on the server? Do the arrays get extended and deallocated by the procedure? Would an array work faster then using a global temporary table? If so then I do see the advantage of using this convention. Thanks.

Tom Kyte
January 05, 2005 - 7:10 pm UTC

arrays are arrays

tables are tables

I don't see how you can even compare them. You use each when they make sense. If you need the data in an array, a gtt doesn't make sense, and vice versa.

gtts consume no storage on disk, until you use them and only for that duration.

arrays are in memory things, allocated and extended as you use them.

Use whichever makes the most sense in given your appliations logic

Array Vs Global Temp Table

Phil Garriss, January 06, 2005 - 10:44 am UTC

There is a definite performance difference! I ran two similar reports. One was inserting data into a global temporary table and passing the data to the report. The other was inserting data into an array and passing that back through the reference cursor. The first report took me 14 seconds to run. The second report took 4 seconds. So it must be easier for Oracle to extend and insert data in an array then for it to insert and commit the transactions in a table.

Tom Kyte
January 06, 2005 - 11:29 am UTC

you cannot make broad sweeping generalizations like that.

seriously.

for every case where you say "arrays are pure good"

I'll give you a counter case where their user would be the kiss of death.


the are TWO TOOLS, use them as appropriate to your circumstance.




Arrays Vs Global Temporary Table

Phil Garriss, January 07, 2005 - 10:16 am UTC

Can you give me a simple example. Nothing fancy. Just a real life situation where I would use each. Thanks.

Tom Kyte
January 07, 2005 - 10:24 am UTC

collection
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061 <code>


now, if the thing we were "in-ing" on had thousands of elements -- no way I'd put that into an in memory collection -- that would go into a GTT and we'd use that in the subquery instead.




Collection Vs Global Temporary Table

Phil Garriss, January 07, 2005 - 11:28 am UTC

Thanks. That is exactly what I was wanting to know. Have a great weekend!

Inserted into Varray's and Extending them

Roger, May 15, 2005 - 3:35 pm UTC

Greetings Jedi Master Tom,

Are these assertions right?

1) Varray's are immutable data types.
So if you want to insert (or delete/update) something in a Varray after it has been initialized, you have to create a new varray and manually initialize it with the changes? If I am wrong, can you please give an example. I have checked out this example from Oracle's documentation which made me make the "Varray's are immutable" statement:

CREATE PROCEDURE add_project (
dept_no IN NUMBER,
new_project IN Project,
position IN NUMBER) AS
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE dept_no = dept_id FOR UPDATE OF projects;
my_projects.EXTEND; -- make room for new project
/* Move varray elements forward. */
FOR i IN REVERSE position..my_projects.LAST - 1 LOOP
my_projects(i + 1) := my_projects(i);
END LOOP;
my_projects(position) := new_project; -- add new project
UPDATE department SET projects = my_projects
WHERE dept_no = dept_id;
END add_project;

2) Extending a Varray is not useful because when you initialize the array, you specify the upper limit of the varray which cannot be changed. I have seen your examples (and the ones in Oracle documentation) that describe why/how to extend a nested table but not come across any code that extend's varrays. If extending varray's is necessary, can you please give an example.

I searched your site thoroughly before posting these questions. If you have answered these questions before I am sorry to waste your time.

Before posting I also made sure that I read the PLSQL Dev Guide Chapter on Collections and Records at:

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#20023 <code>

Thanks!

Tom Kyte
May 15, 2005 - 7:55 pm UTC

1) you cannot perform DML on varrays, you select them out, modify them procedurally and put them back -- as you have

2) if you didn't extend it, what happens in your code?  

ops$tkyte@ORA10G> declare
  2          l_data myArray;
  3  begin
  4          select data into l_data from t where id = 1;
  5          l_data(l_data.count+1) := 4;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5
 
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2          l_data myArray;
  3  begin
  4          select data into l_data from t where id = 1;
  5          l_data.extend;
  6          l_data(l_data.count) := 4;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.


you use extend to ALLOCATE memory in the collection.

 

Varray's

Roger, May 16, 2005 - 12:22 pm UTC

Tom,

Thanks for your reply. A followup question for point (2). In your example, is it safe to assume that myArray is a nested table based on your previous posts on this discussion? i.e.

<quote>
@ORA920> create type myArray as table of varchar2(250)
2 /

Type created.
</quote>

I had specifically asked if you you need to extend a VARRAY not a nested table. I understand that if you don't extend a nested table you will get the "ORA-06533: Subscript beyond count" error. But why would you need to extend a Varray if a Varray cannot increase in size once its been defined. Thanks alot. May the force be with you ;)




Tom Kyte
May 16, 2005 - 1:20 pm UTC

it was a varray.



ops$tkyte@ORA9IR2> create or replace type myArray as varray(20) of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data myArray := myArray();
  3  begin
  4          l_data(1) := 0;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 4
 

a varray must allocate storage just as a nested table 

it has an absolute UPPER BOUND on the number of elements, but has none by default. 

a related question

Amiel, May 16, 2005 - 1:34 pm UTC

Dear tom,
First of all I would like to say that this site is amazing.
Here is my problem:
In the application server we call a pl-function that does an insert.
Because we canÂ’t pass it a pl-table, we have to call it n times for each line.
I thought of building anonyms pl block that will construct the whole insert.
E.g.:
before –
Application server calls in a loop for a Pl that does an insert –
//pseudo c# syntax:
for (int i =0; i <= 100; I++)
Dbclass.InsertUsingPl(bind variable, etcÂ…);
Problem: extra traversing from the application server to the db server.
After -
Application server builds at once anonyms Pl block and then send it to the db –
//pseudo c# syntax:
for (int i =0; i <= 100; I++)
Building a large string that looks like that:
“Declare
Type rec is record(
t number(7),
x number(2),
y char(1));
Type Tbl is table of rec index by pls_integer;
lTbl Tbl;
Begin
lTbl(1).t := 4890045;
lTbl(1).x := 1;
lTbl(1).y := '0';
lTbl(2).t := 4890045;
lTbl(2).x := 2;
lTbl(2).y := '1';
lTbl(3).t := 4890045;
lTbl(3).x := 23;
lTbl(3).y := 'Z';


ForAll i in 1 .. lTbl.Count
Insert Into sample_table values lTbl (i);
End;”
Problem: pollution of the shared pool. Each time we will sent it it will have a line in v$sqlarea.

I did a test of my own (can’t reproduce it here – need an application server) and
Figure it out that the second method is slightly faster.

Can you please give me your opinion about it?
Amiel.
p.s.
a sample script:
create table sample_table (t number (7), x number(2), y char(1));

create or replace function f_ins_sample_table(p_t in number,
p_x in number,
p_y in char,
error out varchar2) return number is
begin
insert into sample_table (t, x, y) values (p_t, p_x, p_y);
return 1;
exception
when others then
error := sqlerrm;
return - 1;
end;
OR
Declare
Type rec is record(
t number(7),
x number(2),
y char(1));
Type Tbl is table of rec index by pls_integer;
lTbl Tbl;
Begin
lTbl(1).t := 4890045;
lTbl(1).x := 1;
lTbl(1).y := '0';
lTbl(2).t := 4890045;
lTbl(2).x := 2;
lTbl(2).y := '1';

ForAll i in 1 .. lTbl.Count
Insert Into sample_table values lTbl (i);
End;






Tom Kyte
May 16, 2005 - 1:51 pm UTC

<quote>Because we canÂ’t pass it a pl-table</quote>

why not?


but you must have figured wrong, if you are trying to tell me that hard parsing was faster.

Question may be stupid but still an attemp

Jupiter, May 16, 2005 - 3:59 pm UTC

Hi Tom,
Luv the site and ur answers.

Question:
The way one could say
create table abc as select * from abc1@otherenv
and a table with exact structure could be created
similarly

Is there a way where I could do the above for a package
like
create package abc as select package b from @otherenv

Thanks a lot as always?

-- So apart Oracle any plans for the long weekend?
Salute to you answers




Tom Kyte
May 16, 2005 - 5:07 pm UTC

you would have to extract the pacakge out -- it would be easy to write a stored procedure to do that -- and execute the create or replace.


there is a long weekend looming? ;)

Varrays

Roger, May 16, 2005 - 6:28 pm UTC

Thanks Tom for explaining what the EXTEND method of a collection does.

BTW, on a side note, what is your opinion on Oracle certifications? OCP? OCM? Do you think they are valuable? Are you an OCM?

And to digress even more, when are you watching the final episode of Stars Wars? I will not ask any more non-technical questions here. I know of your blog. Thanks.

Tom Kyte
May 16, 2005 - 8:51 pm UTC

I'm neither OCP nor OCM.


(star wars never grabbed me, it's all about star trek ;)

Amiel, May 17, 2005 - 1:38 am UTC

first of all, thanks for the very fast response
(wrote the response at night, got the answer in the morning)
the reason that we cant use a pl/table come from the .net provider for oracle. Reading your answer, i thing of doing something like sending a big string to the sp, and use a static smart sql (the kind that you learned us to wrote) that will do insert as select straight to the table.
what do you think about it (the insert as select idea)?
regards, amiel

.NET and PL/SQL Associative Array

Mark A. Williams, May 17, 2005 - 8:45 am UTC

Hi Amiel,

Are you using the Oracle Data Provider for .NET? The Oracle provider does support the use of PL/SQL Associative Arrays (formerly known as "Index-By Tables") beginning with the 9.2.0.4.01 version of the provider. The Microsoft provider does not support this functionality so this might be another reason to use the Oracle provider if you are not already.

If you point your browser to the Oracle Data Provider for .NET homepage on the Oracle Technology Network, then scroll to the bottom of the page you will find 2 sample chapters from my book. One of them (Chapter 5) illustrates using PL/SQL Associative Arrays for insert and select operations.

The ODP.NET homepage can be found here:
</code> http://www.oracle.com/technology/tech/windows/odpnet/index.html

Also, the ODP.NET forums on OTN can be found here:
http://forums.oracle.com/forums/forum.jsp?forum=146 <code>

Hope that helps a bit,

- Mark

Tom Kyte
May 17, 2005 - 9:40 am UTC

thanks mark!

amiel, May 17, 2005 - 10:24 am UTC

Mark - thank you very much, we are not using this provider,
But the native microsoft provider
so, i ask again, is doing something like sending a big string to the
sp, and use a static smart sql (the kind that you learned us to wrote) that will
Do insert as select straight to the table sound like a good idea?


Tom Kyte
May 17, 2005 - 11:24 am UTC

I'd rather use a global temporary table as a method of passing sets of parameters. you should be able to array insert into that, stored procedure would just read from it

A reader, May 17, 2005 - 11:41 am UTC

I am glad Amiel (from tel aviv ) you are using this site :-)

Asif.

amiel, May 18, 2005 - 5:27 am UTC

tom, thanks agin.
could you please clarify about the gtt idea (we will stil have to insert data to it),
i thing thet my aim is to minimize the traversing between the apllication server and the db server.
i thougt of doing somthing like thet:

create table sample_table (t number (7), x number(2), y char(1));

create or replace function f_ins_using_str(p_t in varchar2,
error out varchar2)
return number is
begin

--'1234567, 1,*#1234567, 2,*#1234567, 3,*#1234567, 4,*#1234567, 5,*'
insert into sample_table
(select * from (select To_Number(Trim(substr(Column_Value, 1, 7))) as t,
To_Number(Trim(substr(Column_Value, 9, 3))) as x,
substr(Column_Value, 13, 1) as y
from (SELECT TRIM(Substr(p_t,
Instr('#' || p_t || '#',
'#',
1,
Rownum),
Instr('#' || p_t || '#',
'#',
1,
Rownum + 1) -
Instr('#' || p_t || '#',
'#',
1,
Rownum) - 1)) Column_Value
FROM (select level
from dual
connect by level <=
(Length(p_t) -
Length(REPLACE(p_t, '#', '')) + 1)))));
return sql%rowcount;;
exception
when others then
error := sqlerrm;
return - 1;
end;

set serveroutput on

declare
ll number;
err varchar2(400);
begin
ll := f_ins_using_str('1234567, 1,*#1234567, 2,*#1234567, 3,*#1234567, 4,*#1234567, 5,*', err);
dbms_output.put_line(to_char(ll)) || 'record was written ');
end;

5 record was written

select * from sample_table
T X Y
1234567 1 *
1234567 2 *
1234567 3 *
1234567 4 *
1234567 5 *


asif - tel aviv ruls :)

Tom Kyte
May 18, 2005 - 9:01 am UTC

i thing thet my aim is to minimize the traversing between the apllication server
and the db server.


and that is why i said "array insert into gtt, call procedure"

A reader, May 18, 2005 - 9:50 am UTC

in order not to die without understanding the your answer,
i must ask agin - how does the data move from the application server to the db server? as i wrote before, i can't send an array.
what will i gain from doing insted of insert * n times
right to my table,
doing it as you say i will do insert to a gtt and then insert to my table.
could you please be more clear on the gtt


Tom Kyte
May 18, 2005 - 10:24 am UTC

I talked with Mark Williams about this

basically the driver you are using supports "not too many things", including not support plsql tables and not supporting array inserts

IF you use the oracle driver, written with a thought towards performance in Oracle (instead of the MS driver) you'll have

a) a driver with all of the functions you have now.... (eg: you don't have to change existing stuff)

b) that supports plsql tables
c) and array inserts (batching on client)

else, you are "doing it yourself" and that string "trick" is ugly (and who is to say that parsing is going to beat out network traffic performance wise)

A reader, May 18, 2005 - 1:07 pm UTC

tom and mark, thank you all very much.
the it is not up to me to decied about the driver but i will pass the information on.
p.s. 1: the string trick is very ugly but it was fun to write it...
p.s. 2: what do you mean when you say array procecing in the client?

Tom Kyte
May 18, 2005 - 1:16 pm UTC

the client would batch up say "50 rows" and then have them sent over all at once.

Rajesh, August 03, 2005 - 6:44 am UTC

How to Handle ora-06550 error in PL/SQL block

Tom Kyte
August 03, 2005 - 11:41 am UTC

[tkyte@desktop tkyte]$ oerr ora 6550
06550, 00000, "line %s, column %s:\n%s"
// *Cause: Usually a PL/SQL compilation error.
// *Action:
[tkyte@desktop tkyte]$


you haven't gotten into the block :) you cannot catch and handle an exception in the code that won't compile!!

ORA-06550

Rajesh, August 03, 2005 - 6:45 am UTC

How to Handle ora-06550 error in PL/SQL block

May be a bug

Marcio Portes, August 03, 2005 - 5:13 pm UTC

Tom, do you know if it is a bug?
The code below runs against 8i but not over 9i/10g.

create table x_tab ( cod number, nam varchar2(10) );

create or replace type xr_tab as object ( cod number, nam varchar2(10) );
/
create or replace type xt_tab as table of xr_tab;
/

declare
cursor c1 is select * from x_tab;
t_tab xt_tab := xt_tab();
rec c1%rowtype;
begin
for rec in c1
loop
t_tab.extend;
t_tab(t_tab.last) := rec;
end loop;
end;
/

Regards,

Tom Kyte
August 03, 2005 - 8:21 pm UTC

it would have been a bug in 8i if it worked. It should not have.

Initialize Varray of records upon creation ...

VKOUL, August 11, 2005 - 2:43 pm UTC

Please go thru the following :
***************************************************
DECLARE
  type flowType       is record (priority number(1), fwdFlow number, revFlow number);
  TYPE flowtype_table IS VARRAY(3) OF flowtype;
  flow flowtype_table := flowtype_table(flowType(1,2,3), flowType(4,5,6), flowType(7,8,9));
BEGIN
    null;
END;
/
***********************************************************

On executing the above PL/SQL block, I get this

SQL> l
  1  DECLARE
  2    type flowType       is record (priority number(1), fwdFlow number, revFlow number);
  3    TYPE flowtype_table IS VARRAY(3) OF flowtype;
  4    flow flowtype_table := flowtype_table(flowType(1,2,3), flowType(4,5,6), flowType(7,8,9));
  5  BEGIN
  6      null;
  7* END;
SQL> 
SQL> 
SQL> /
  flow flowtype_table := flowtype_table(flowType(1,2,3), flowType(4,5,6), flowType(7,8,9));
                                        *
ERROR at line 4:
ORA-06550: line 4, column 41:
PLS-00222: no function with name 'FLOWTYPE' exists in this scope
ORA-06550: line 4, column 8:
PL/SQL: Item ignored


SQL> define
DEFINE _DATE           = "11-AUG-2005 11:29:43 AM" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1001000400" (CHAR)
DEFINE _RC             = "1" (CHAR)
SQL> 
SQL> 

How Can I Initialize Varray of records upon creation in declaration section with some static values ?

Thanks
 

Tom Kyte
August 12, 2005 - 8:10 am UTC

it is a record, records cannot be initialized like that, if you use an object type instead, they can be. language limitation.

Thanks Tom

VKOUL, August 12, 2005 - 2:47 pm UTC


Working with Collections and Bulk Collect

Yuan, November 08, 2005 - 4:11 pm UTC

I am trying to do something with collections and bulk collect and not having much luck. To illustrate what I'd like to do, I'll explain without using bulk.

declare
type lt is table of integer;
ltbl lt := lt(1, 2, 3);
begin
-- STEP 1
for i in ltbl.first .. ltbl.last loop
delete from testa where id = ltbl(i);
if sql%rowcount > 0 then
ltbl.delete(i);
end if;
end loop;
-- STEP 2
for i in ltbl.first .. ltbl.last loop
delete from testb where id = ltbl(i);
if sql%rowcount > 0 then
ltbl.delete(i);
end if;
end loop;
end;

Suffice it to say that different logic is happening in steps 1 and 2, but if any elements in the collection are used in any given step, I want it to not be considered in any subsequent steps. Now would I be able to accomplish this taking advantage of the performance gains of bulk collect? I don't have a preference of using a varray or an index-by table.

I tried this:

declare
type lt is table of integer;
ltblRemaining lt := lt(1, 2, 3);
ltblDelete lt;
begin
forall i in ltblRemaining.first .. ltblRemaining.last
delete from testa where id = ltblRemaining(i) returning i bulk collect into ltblDelete;
end;

But it's not letting me return i. If I return the element of i, how do I delete from ltblRemaining those that are also in ltblDelete?

Tom Kyte
November 08, 2005 - 10:36 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#32583 <code>

use bulkrowcount like you are currently using sql%rowcount.

Fantastic!

Yuan, November 09, 2005 - 7:03 am UTC

Exactly what I need! Thanks!

index by binary_integer OR index by pls_integer

Ravi Kumar, November 11, 2005 - 4:33 am UTC

I have read somewhere that we should always use index by pls_integer NOT binary_interger.

Is that right ? can you please explain.

Thanks & Regards
Ravi Kumar

Tom Kyte
November 12, 2005 - 8:17 am UTC

doesn't really matter in current releases of the database as they are currently the same.

benchmark it (and in the future when you read things like that - but don't see an example showing 'why', question it immediately! it could be an old wives tale otherwise)

pb, January 10, 2006 - 9:59 pm UTC


MG, January 18, 2006 - 12:30 pm UTC

Hi Tom,

I have setup second database from existing database. Then I have following error on one schema.

(1). Package:

CREATE OR REPLACE PACKAGE FONDSRPT.query_idx_tst AUTHID DEFINER
AS
TYPE idx_his_wgh_1_t IS RECORD (
idx_cus VARCHAR (10),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR (10),
fx_cur VARCHAR (3),
wgh NUMBER,
asof DATE,
val NUMBER,
fx_rate NUMBER
);

TYPE idx_his_wgh_1_ref_t IS REF CURSOR
RETURN idx_his_wgh_1_t;

TYPE idx_his_yld_t IS RECORD (
idx_cus VARCHAR2 (10 BYTE),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR2 (10 BYTE),
asof DATE,
yield NUMBER
);

TYPE idx_his_yld_tab_t IS TABLE OF idx_his_yld_t;

....

FUNCTION idx_his_yld (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_yld_tab_t PIPELINED;

END;
/

CREATE OR REPLACE PACKAGE BODY FONDSRPT.query_idx_tst
IS
FUNCTION idx_his_yld (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_yld_tab_t PIPELINED
IS
in_rec_d0 idx_his_wgh_1_t;
in_rec_d1 idx_his_wgh_1_t;
in_rec_tbl idx_his_wgh_1_tab_t := idx_his_wgh_1_tab_t ();
in_cmp_tbl idx_his_wgh_1_tab_t := NULL;
out_rec idx_his_yld_t;
i1 INTEGER;
i2 INTEGER;
n_weight NUMBER;
v_idx_cus VARCHAR (10);
d_idx_from DATE;
d_asof DATE;
n_yield_day NUMBER;
n_yield_tot NUMBER;
BEGIN
in_rec_tbl.EXTEND (10);
i1 := in_rec_tbl.FIRST;
n_weight := 0;
v_idx_cus := NULL;
d_idx_from := NULL;
d_asof := NULL;
n_yield_tot := 1;

LOOP
FETCH c_idx_his
INTO in_rec_tbl (i1);

EXIT WHEN c_idx_his%NOTFOUND;

IF (NOT (v_idx_cus IS NULL))
AND (v_idx_cus = in_rec_tbl (i1).idx_cus)
AND (NOT (d_idx_from IS NULL))
AND (d_idx_from = in_rec_tbl (i1).idx_from)
AND (NOT (d_asof) IS NULL)
AND (d_asof = in_rec_tbl (i1).asof)
THEN
v_idx_cus := in_rec_tbl (i1).idx_cus;
d_idx_from := in_rec_tbl (i1).idx_from;
d_asof := in_rec_tbl (i1).asof;
n_weight := n_weight + in_rec_tbl (i1).wgh;
ELSE
--check for new index or initial index
IF (v_idx_cus IS NULL)
OR (v_idx_cus <> in_rec_tbl (i1).idx_cus)
THEN
n_yield_tot := 1;
in_cmp_tbl := NULL;
--new index has been detected
--ouput the initial yield of one
out_rec.idx_cus := in_rec_tbl (in_rec_tbl.FIRST).idx_cus;
out_rec.idx_dat_ref :=
in_rec_tbl (in_rec_tbl.FIRST).idx_dat_ref;
out_rec.idx_dat_ref_val :=
in_rec_tbl (in_rec_tbl.FIRST).idx_dat_ref_val;
out_rec.idx_from := in_rec_tbl (in_rec_tbl.FIRST).idx_from;
out_rec.idx_from_factor :=
in_rec_tbl (in_rec_tbl.FIRST).idx_from_factor;
out_rec.idx_nam := in_rec_tbl (in_rec_tbl.FIRST).idx_nam;
out_rec.asof := in_rec_tbl (in_rec_tbl.FIRST).asof;
out_rec.yield := n_yield_tot;-- * (out_rec.idx_from_factor);
PIPE ROW (out_rec);
END IF;

--new index-section in benchmark detected
--yield to be continued
IF d_idx_from IS NULL OR (d_idx_from <> in_rec_tbl (i1).idx_from)
THEN
--n_yield_tot := 1;
in_cmp_tbl := NULL;
END IF;

v_idx_cus := in_rec_tbl (i1).idx_cus;
d_idx_from := in_rec_tbl (i1).idx_from;
d_asof := in_rec_tbl (i1).asof; --discard
n_weight := in_rec_tbl (i1).wgh;
in_rec_tbl (in_rec_tbl.FIRST) := in_rec_tbl (i1);
i1 := in_rec_tbl.FIRST;
END IF;

-- out_rec.yield := n_weight;
-- PIPE ROW (out_rec);
IF (n_weight = 1)
THEN
-- out_rec.yield := 100;
-- PIPE ROW (out_rec);
IF in_cmp_tbl IS NULL
THEN
in_cmp_tbl := in_rec_tbl;
-- out_rec.yield := 101;
-- PIPE ROW (out_rec);
ELSE
-- out_rec.yield := 102;
-- PIPE ROW (out_rec);
n_yield_day := 0;

FOR i2 IN in_rec_tbl.FIRST .. i1
LOOP
IF (in_cmp_tbl (i2).val) <> 0
THEN
n_yield_day :=
n_yield_day
+ ( ( in_rec_tbl (i2).val
/ NVL (in_rec_tbl (i2).fx_rate, 1)
)
/ ( in_cmp_tbl (i2).val
/ NVL (in_cmp_tbl (i2).fx_rate, 1)
)
* in_rec_tbl (i2).wgh
);
END IF;
END LOOP;

in_cmp_tbl := in_rec_tbl;
n_yield_tot := n_yield_tot * n_yield_day;
out_rec.idx_cus := in_rec_tbl (in_rec_tbl.FIRST).idx_cus;
out_rec.idx_dat_ref :=
in_rec_tbl (in_rec_tbl.FIRST).idx_dat_ref;
out_rec.idx_dat_ref_val :=
in_rec_tbl (in_rec_tbl.FIRST).idx_dat_ref_val;
out_rec.idx_from := in_rec_tbl (in_rec_tbl.FIRST).idx_from;
out_rec.idx_from_factor :=
in_rec_tbl (in_rec_tbl.FIRST).idx_from_factor;
out_rec.idx_nam := in_rec_tbl (in_rec_tbl.FIRST).idx_nam;
out_rec.asof := in_rec_tbl (in_rec_tbl.FIRST).asof;
out_rec.yield := n_yield_tot;-- * (out_rec.idx_from_factor);
PIPE ROW (out_rec);
END IF;

i1 := in_rec_tbl.FIRST;
n_weight := 0;
--v_idx_cus := NULL;
--d_idx_from := NULL;
--d_asof := NULL;
ELSE
i1 := i1 + 1;
END IF;
END LOOP;

CLOSE c_idx_his;

RETURN;
END;
...
END; /* Package */
/


(2). The function : idx_his_yld in this package is calling from following view.

CREATE OR REPLACE FORCE VIEW FONDSRPT.V_IDX_CUS_YIELD
(IDX_CUS, IDX_DAT_REF, IDX_DAT_REF_VAL, IDX_FROM, IDX_FROM_FACTOR,
IDX_NAM, ASOF, YIELD)
AS
SELECT distinct idx_cus, idx_dat_ref, idx_dat_ref_val, idx_from, idx_from_factor,
idx_nam, asof, yield
FROM TABLE
(fondsrpt.query_idx_tst.idx_his_yld
(CURSOR
(SELECT t3.*
FROM v_idx_his_yld t3,
(SELECT tidxday.idx_cus, tidxday.idx_from, MAX (tidxday.asof) AS asof, 'EOM' AS valtype
FROM (SELECT tfundstatic.per_idx_cus,
MAX
(tfundvalue.fp_pre_datum
) AS fp_pre_dat_ubound,
TRUNC
(tfundvalue.fp_pre_datum,
'MM'
) fp_pre_month
FROM v_fun_his_per tfundvalue,
fund tfundstatic
WHERE tfundvalue.dd_fon_nr =
tfundstatic.fond_id
GROUP BY tfundstatic.per_idx_cus,TRUNC(tfundvalue.fp_pre_datum,'MM')) tfundmonth,
(SELECT idx_cus, idx_from, asof,SUM (wgh) wgh_sum,TRUNC (asof,'MM') asof_month FROM v_idx_his_yld GROUP BY idx_cus, idx_from, asof) tidxday
WHERE tidxday.idx_cus = tfundmonth.per_idx_cus AND tidxday.asof_month = tfundmonth.fp_pre_month AND tidxday.wgh_sum = 1 AND tidxday.asof < tfundmonth.fp_pre_dat_ubound
GROUP BY tidxday.idx_cus,
tidxday.idx_from,
tidxday.asof_month
UNION
SELECT tidxday.idx_cus, tidxday.idx_from,MAX (tidxday.asof) AS asof,'SOF' AS valtype
FROM (SELECT tfundstatic.per_idx_cus,
MIN
(tfundvalue.fp_pre_datum
) AS fp_pre_dat_ubound
FROM v_fun_his_per tfundvalue,
fund tfundstatic
WHERE tfundvalue.dd_fon_nr =
tfundstatic.fond_id
GROUP BY tfundstatic.per_idx_cus) tfundstart,
(SELECT idx_cus,MIN (idx_from) AS idx_from,asof, SUM (wgh) wgh_sum
FROM v_idx_his_yld
GROUP BY idx_cus, asof) tidxday
WHERE tidxday.idx_cus =
tfundstart.per_idx_cus
AND tidxday.wgh_sum = 1
AND tidxday.asof <
tfundstart.fp_pre_dat_ubound
GROUP BY tidxday.idx_cus, tidxday.idx_from) t4
WHERE t3.idx_cus = t4.idx_cus
AND t3.idx_from = t4.idx_from
AND t3.asof = t4.asof
)
)
) --439 |;;


My problem is This view is working fine in original database. But new database it is not working. it gives following error:

ERROR at line 4:
ORA-06533: Subscript beyond count
ORA-06512: at "FONDSRPT.QUERY_IDX_TST", line 297


Line 297 in Package as below in above function.

FETCH c_idx_his INTO in_rec_tbl (i1);


The database infrastructure is exactly same as original database.

Could you please tell what I have missed to get above error?


Thank you

Tom Kyte
January 19, 2006 - 8:02 am UTC

you have a bug in your code on line 297.


You shall have to employ conventional debugging techniques on your code to figure it out.

Hint:

BEGIN
in_rec_tbl.EXTEND (10);
i1 := in_rec_tbl.FIRST;

your array permits only 1..10 as subscripts as you have coded, i1 must have a value outside of this range somehow.

try using dbms_output to print out values of variables as you go through and see what you see...

Bulk collect in index by varchar2 pl/sql tables

Ashutosh Upadhyay, June 21, 2006 - 12:22 pm UTC

Tom,
I'm not able to populate index by varchar2 pl/sql table using bulk collect. Currently I'm doing it using a cursor loop.

Please tell if it is possible. I'm using Oracle9i db.

Program:

DECLARE
TYPE recratepref IS RECORD (
rate_type cltms_product.rate_type%TYPE,
rate_code_pref cltms_product.rate_code_pref%TYPE
);

TYPE tblratepref IS TABLE OF recratepref
INDEX BY cltms_product.product_code%TYPE;

v_rate_pref tblratepref;
cnt NUMBER;
t_rate_type cltms_product.rate_type%TYPE;
t_rate_code_pref cltms_product.rate_code_pref%TYPE;
BEGIN
FOR m IN (SELECT product_code, rate_type, rate_code_pref
FROM cltms_product)
LOOP
v_rate_pref (m.product_code).rate_type := m.rate_type;
v_rate_pref (m.product_code).rate_code_pref := m.rate_code_pref;
END LOOP;
END;

Tom Kyte
June 22, 2006 - 11:31 am UTC

not using an associative array like that, you are not filling an array in from 1..N-records, you are creating a sparse array whose indices are m.product_code.

Is that an appropriate way?

Khurram Siddiqui, September 13, 2006 - 1:27 pm UTC

hi tom,

please correct me 

someone asked at otn ....

Please try to get output from Pl/Sql block with Message . you will get the error.please insert more than one values and then test. 

here is his code

1 declare
2 cursor c1 is
3 select * from borrower;
4 begin
5 for w in c1 loop
6 message('contact name: '||w.name);
7 for i in 1..w.tools.count loop
8 message(w.tools(i));
9 end loop;
10 end loop;
11* end;
SQL> /
message(w.tools(i));
*
ERROR at line 8:
ORA-06550: line 8, column 1:
PLS-00306: wrong number or types of arguments in call to 'MESSAGE'
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored 

I replied
DBMS_OUTPUT.PUT_LINE is an overloaded procedure which accept the static type e.g varchar,number.

Why do you want to print anyway if you want to get the value from outside then use reference cursor (not sure cause not tested yet).

then i tried in this way 

SQL> DROP TABLE borrower
  2  / 
 
Table dropped.
 
SQL> DROP TYPE tools_va
  2  / 
 
Type dropped.
 
SQL> DROP TYPE tool_ty
  2  / 
 
Type dropped.
 
SQL> CREATE TYPE tool_ty AS OBJECT  (toolname varchar2(25))
  2  / 
 
Type created.
 
SQL> CREATE OR REPLACE TYPE tools_va as VARRAY(10) OF  tool_ty
  2  / 
 
Type created.
 
SQL> CREATE TABLE  borrower
  2  (name  VARCHAR2(25),
  3   tools TOOLS_VA)
  4  / 
 
Table created.
 
SQL> INSERT INTO borrower VALUES ('A',tools_va(tool_ty('a')))
  2  / 
 
1 row created.
 
SQL> INSERT INTO borrower VALUES ('B',tools_va(tool_ty('b')))
  2  / 
 
1 row created.
 
SQL> INSERT INTO borrower VALUES ('C',tools_va(tool_ty('c')))
  2  / 
 
1 row created.
 
SQL> CREATE OR REPLACE PACKAGE mypackage AS 
  2    TYPE c1  IS REF CURSOR; 
  3    FUNCTION get_data RETURN  c1;
  4  END; 
  5  / 
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY mypackage AS
  2  FUNCTION get_data RETURN c1 IS
  3  c  c1;
  4  BEGIN
  5    OPEN  c FOR SELECT p.tools  FROM borrower p;
  6    RETURN c;
  7  END;
  8  END;
  9  / 
 
Package body created.
 
SQL> VAR res REFCURSOR;
SQL> BEGIN
  2   :res:=mypackage.get_data;
  3  END;
  4  .
SQL> / 
 
PL/SQL procedure successfully completed.
 
SQL> PRINT res
 
TOOLS(TOOLNAME)
--------------------------------------------------------------------------------
TOOLS_VA(TOOL_TY('a'))
TOOLS_VA(TOOL_TY('b'))
TOOLS_VA(TOOL_TY('c'))


i wana ask is my assumption correct or there is more reliable and simple way to achive the op's requirment.


Khurram 

 

Tom Kyte
September 13, 2006 - 3:08 pm UTC

having no clue what "message" is.... Not sure what to say

what message is

Khurram Siddiqui, September 13, 2006 - 3:52 pm UTC

Sorry tom "message" was at form builder side sp actually it is equvalent to DBMS_OUTPUT.PUT_LINE 

here i paste the code again 

1 declare
2 cursor c1 is
3 select * from borrower;
4 b tools_va;
5 begin
6 for w in c1 loop
7 dbms_output.put_line('contact name: '||w.name);
8 for i in 1..w.tools.count loop
9 b:=tools_va(tool_ty((i)));
10 dbms_output.put_line(b);
11 end loop;
12 end loop;
13* end;
SQL> /
dbms_output.put_line(b);
*
ERROR at line 10:
ORA-06550: line 10, column 5:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 10, column 5:
PL/SQL: Statement ignored 

i made assumption that DBMS_OUTPUT.PUT_LINE is an overloaded procedure which accept the static type e.g varchar,number.So i made assumption 

"Why do you want to print anyway if you want to get the value from outside then use reference cursor."

SQL> DROP TABLE borrower
  2  / 
 
Table dropped.
 
SQL> DROP TYPE tools_va
  2  / 
 
Type dropped.
 
SQL> DROP TYPE tool_ty
  2  / 
 
Type dropped.
 
SQL> CREATE TYPE tool_ty AS OBJECT  (toolname varchar2(25))
  2  / 
 
Type created.
 
SQL> CREATE OR REPLACE TYPE tools_va as VARRAY(10) OF  tool_ty
  2  / 
 
Type created.
 
SQL> CREATE TABLE  borrower
  2  (name  VARCHAR2(25),
  3   tools TOOLS_VA)
  4  / 
 
Table created.
 
SQL> INSERT INTO borrower VALUES ('A',tools_va(tool_ty('a')))
  2  / 
 
1 row created.
 
SQL> INSERT INTO borrower VALUES ('B',tools_va(tool_ty('b')))
  2  / 
 
1 row created.
 
SQL> INSERT INTO borrower VALUES ('C',tools_va(tool_ty('c')))
  2  / 
 
1 row created.
 
SQL> CREATE OR REPLACE PACKAGE mypackage AS 
  2    TYPE c1  IS REF CURSOR; 
  3    FUNCTION get_data RETURN  c1;
  4  END; 
  5  / 
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY mypackage AS
  2  FUNCTION get_data RETURN c1 IS
  3  c  c1;
  4  BEGIN
  5    OPEN  c FOR SELECT p.tools  FROM borrower p;
  6    RETURN c;
  7  END;
  8  END;
  9  / 
 
Package body created.
 
SQL> VAR res REFCURSOR;
SQL> BEGIN
  2   :res:=mypackage.get_data;
  3  END;
  4  .
SQL> / 
 
PL/SQL procedure successfully completed.
 
SQL> PRINT res
 
TOOLS(TOOLNAME)
--------------------------------------------------------------------------------
TOOLS_VA(TOOL_TY('a'))
TOOLS_VA(TOOL_TY('b'))
TOOLS_VA(TOOL_TY('c'))

So is my assumption is correct or there is some more reliable way?? 

i hope you got what i wana make sure from you.

sorry to bothering you again by repasting the code.

Thanx a lot for yours prompt response

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

 
Khurram 
 

Tom Kyte
September 14, 2006 - 8:54 am UTC

don't really know what the underlying question behind the question here is (lots of stuff - read these pretty quick).

there are many valid, useful, ok ways to display data. for the original poster - they needed to convert any non-number, string, date type to a number, string, date type since message (the forms function) and/or dbms_output only accept those types.

Bulk Collect into Table of Varray

Gwen, January 10, 2007 - 12:50 pm UTC

Tom,

I can bulk collect into three separate tables of, say, VARCHAR2(30)s.

DECLARE

TYPE varlist_typ IS TABLE OF VARCHAR2(30);

list1_tab varlist_typ;
list2_tab varlist_typ;
list3_tab varlist_typ;

TYPE ref_cur_typ IS REF CURSOR;

ref_cur ref_cur_typ;

BEGIN
OPEN ref_cur FOR 'SELECT field_1, field_2, field_3 FROM my_table WHERE id = 1000';
FETCH ref_cur BULK COLLECT INTO list1_tab, list2_tab, list3_tab;
CLOSE ref_cur;

dbms_output.put_line (list1_tab(1) || ' ' || list2_tab(1) || ' ' || list3_tab(1));
dbms_output.put_line (list1_tab(2) || ' ' || list2_tab(2) || ' ' || list3_tab(2));
END;

I can loop through the records in each table.
Now I also need to loop though all the fields as a VARRAY. So I'd like to declare something like this:

DECLARE

TYPE varray_typ IS VARRAY(3) of VARCHAR2(30);
TYPE varray_tab_typ IS TABLE OF varray_typ;

varray_tab varray_tab_typ;

TYPE ref_cur_typ IS REF CURSOR;

ref_cur ref_cur_typ;

BEGIN
OPEN ref_cur FOR 'SELECT field_1, field_2, field_3 FROM my_table WHERE id = 1000';
FETCH ref_cur BULK COLLECT INTO varray_tab???, varray_tab???, varray_tab???;
CLOSE ref_cur;
END;

How can I refer to each element in the varray for the bulk collect? Do I need to initialize the varray and how is this done?

I have simplified this down - what I really need is a varray with 300 elements.
Individually named items won't work for later processing. Also, the OPEN and FETCH will be using dynamic strings.
I believe I can do the FETCH with EXECUTE IMMEDIATE as long as my variables are globals.

Thanks for your help!
Gwen

GTT vs. PL/SQL Tables

Jhon, February 02, 2007 - 7:20 pm UTC

Hi Tom,

Sorry for the intrusion in this thread since I could not post a new question to you. I kinda hoping you will be able to help me to find some concrete samples showing the effectiveness of GTT against P/SQL tables (bulk collect) in a process or vice-versa. In short, I like to know which one is faster and will have less SGA usage.

The process requires data gathering from huge tables (with of course some conditions), then manipulate the data. The final results will then be saved to a different physical table.

BTW, I'm using Oracle DB v9.2.0.4.

Thanks is advance
Tom Kyte
February 03, 2007 - 7:35 pm UTC

think of plsql tables as arrays, programming constructs.

if you are going to do massive sql statements against them, then you want tables - not in memory arrays.

neither take SGA if you are using dedicated server. plsql tables - uga memory and that is in the pga in dedicated server. temp - that is pga memory.

in shared server, the plsql table memory would come from the SGA since the uga is in the sga in shared server.

GTT vs. PL/SQL Tables

Jhon, February 05, 2007 - 1:11 pm UTC

Hi Tom,

This is a follow-up question. Which is much faster between the two for data processing? Also, do you have some sort of example that shows the different behavior of the process? Or, point me to the right documentation.

Thanks much.
Tom Kyte
February 05, 2007 - 6:20 pm UTC

gtt's are
no wait - plsql tables are
no wait - they are the same


in short "it depends on what the heck you are doing"

do you need an array in your procedural code? plsql table type

do you need to use a scratch table for intermediate processing? global temporary table.

Error

Naga, February 23, 2007 - 2:47 pm UTC

Hi Tom,

Sorry for posting my question here .
But i am really need ur help.

I have to create a procedure that returns a nested table .
and use that nested table in ProC.

Attached is my code ...Please me I am not sure What is going wrong?

the procedure is created?
But when i use select * from table (cast(positionspkg_getpositions() as position_table_type))

its throwing me an error...
SET SERVEROUTPUT ON;

CREATE OR REPLACE PACKAGE POSITIONS_PKG
IS

TYPE position_rec_type IS RECORD
        (
                AccountKey VARCHAR2(16),
                SecurityKey VARCHAR2(16),
                AccountType NUMBER(3),
                Symbol VARCHAR2(16),
                ChargeScheduleKey VARCHAR2(16),
                SecurityDescription VARCHAR2(100),
                UnderlyingSecurityKey VARCHAR2(16)
        );

TYPE  position_table_type IS TABLE OF position_rec_type;


function getPositions return position_table_type;

END;

/
COMMIT;
SHOW ERRORS;


CREATE OR REPLACE PACKAGE BODY POSITIONS_PKG
AS

function getPositions return position_table_type
is

cursor c_positions IS

SELECT
        pos.accountkey,
        pos.securitykey,
        pos.accounttype,
        sec.symbol,
        cust.chargeschedulekey,
        sec.securitydescription,
        sec.underlyingsecuritykey
FROM
        positions pos INNER JOIN securities sec
                ON ( pos.securitykey = sec.securitykey )
        LEFT JOIN positionstrategies posstrat
                ON (pos.accountkey =posstrat.accountkey) and (pos.accounttype = posstrat.accounttype)
        INNER JOIN customers cust
                ON (pos.accountkey = cust.customerkey )
        LEFT JOIN securitymarginmanagement secmar
                ON (pos.securitykey = secmar.securitykey)
        LEFT JOIN positionmarginmanagement posmar
                ON (pos.accountkey = posmar.accountkey)
        LEFT JOIN securitiesfixedincome secfix
                ON (pos.securitykey = secfix.securitykey)
FOR v_data_position in c_positions loop
begin
    positiontable.extend;
    --positionrec.AccountKey := v_data_position.accountKey;
    --positionrec.Symbol := v_data_position.symbol;
    --positiontable.extend;
    positiontable(n_pos) := positionrec;
    n_pos := n_pos + 1;
end ;
end loop;

/*      open c_positions;
        loop
           fetch c_positions into positionrec;
        exit when c_positions%NOTFOUND;
         positiontable.extend;
         for i in 1..positiontable.COUNT loop
         positiontable(i) := positionrec;
         end loop;
        end loop;
        close c_positions; */
return positiontable;

END;
END;
/
COMMIT;
SHOW ERRORS;


select * from table(cast(positions_pkg.getpositions() as position_table_type))
2 /
select * from table(cast(positions_pkg.getpositions() as position_table_type))
*
ERROR at line 1:
ORA-00902: invalid datatype


Tom Kyte
February 26, 2007 - 10:54 am UTC

you want to create SQL types - not PLSQL types.


create or replace type position_rec_type as object
(
AccountKey VARCHAR2(16),
SecurityKey VARCHAR2(16),
AccountType NUMBER(3),
Symbol VARCHAR2(16),
ChargeScheduleKey VARCHAR2(16),
SecurityDescription VARCHAR2(100),
UnderlyingSecurityKey VARCHAR2(16)
)
/

create or replace type position_table_type IS TABLE OF position_rec_type
/

Unnesting Null Objects

Brett, March 16, 2007 - 8:34 am UTC

I hope that I'm missing something very basic here. I'm trying to unnest collections that may be null in 9.2.0.7.
Here's a simplistic example:
--Type creations
CREATE TYPE test_type AS OBJECT (test_type1 VARCHAR2(30),
test_type2 VARCHAR2(30));
/
CREATE TYPE test_array AS TABLE OF test_type;
/
--Table creations
CREATE TABLE test
(test1 VARCHAR2(30),
test2 VARCHAR2(30),
test3 test_array)
NESTED TABLE test3 store AS test3_tab;
--Test data
INSERT INTO test (test1,test2)
VALUES (1,1);
INSERT INTO test (test1,test2,test3)
VALUES (2,2,test_array(test_type('A','A'),test_type('B','B')));

Now, once I start pulling the data out, I see that I have 2 rows in the parent table:
SELECT * FROM test;

But, when I try to unnest the collections, I only get data for one of the parent rows because one of the has no data in the nested table:
SELECT a.test1, a.test2, b.test_type1, b.test_type2
FROM test a,
TABLE (a.test3) b;

I can get the parent row back by creating a null object in the nested table, but I am still missing the parent row 1:
INSERT INTO test (test1,test2,test3)
VALUES (3,3,test_array(test_type(NULL,NULL)));
SELECT a.test1, a.test2, b.test_type1, b.test_type2
FROM test a,
TABLE (a.test3) b;

I can update row 1 so that it has a null object in the nested table and then retrieve it:
UPDATE test
SET test3 = test_array(test_type(NULL,NULL))
WHERE test1 = 1;
SELECT a.test1, a.test2, b.test_type1, b.test_type2
FROM test a,
TABLE (a.test3) b;

And now finally, my question:
Is there some sort of outer join syntax for unnesting collections or do I simply have to ensure that an object exists in the nested table for every row in the parent table? I've looked through documentation and can't seem to find anything.

Answering my own question

Brett, March 16, 2007 - 9:29 am UTC

I knew that once I posted the question, I would stumble upon the results:

SELECT a.test1, a.test2, b.test_type1, b.test_type2
FROM test a,
TABLE (a.test3)(+) b;


Tom Kyte
March 17, 2007 - 3:51 pm UTC

ops$tkyte%ORA10GR2> SELECT a.test1, a.test2, b.test_type1, b.test_type2
  2  FROM test a, TABLE (nvl(a.test3,test_array(test_type(null,null)))) b
  3  /

TEST1    TEST2    TEST_TYPE1   TEST_TYPE2
-------- -------- ------------ ------------
1        1
2        2        A            A
2        2        B            B


is "yet another way"

Real World Use of Nested Tables ? OR might be not

Rakesh, April 07, 2007 - 1:12 pm UTC

Hi ,
I wanted to ask this very sticky(to me) question.I read that real world use of nested tables are scarce,wanted to confirm if this is a veritable nested table scenarion.My case is if there are different departments,with each department offering multiple subjects,each independent of the other.i.e both history and sociology department may offer one subject of common interest.Must i use nested tables or go for parent-child table relationship.
Please and Kindly Clarify
Tom Kyte
April 09, 2007 - 10:23 am UTC

if history and sociology departments offer the same subject that would RULE OUT nested tables.

both departments need to point to the same subject record.


Hi. PL/SQL Tables with .net

Mohanraj K., September 18, 2007 - 11:44 am UTC

Hello Sir,
I am new to this forum.

I searched through all odp.net forum, and some sample chapters, but unable to clearly use it.

Can i get any help on How to PASS PL/SQL tables ( or some table like information )into stored procedures from .NET (ODP.NET) and process that table( and referencing its elements) of information in PL/SQL.

I read about Associative Arrays samples, It seems like some what oldage, can we access the type table of (element type) in the .net itself.

Please give me clear example on passing parameters of Associative Arrays in stored procedures from .NET

Thanks in Advance.


Reverse Engineering

A developer, November 21, 2007 - 3:29 am UTC

Tom,

We have talked a lot about how to populate various data structures. However, in my case Java code will be used to populate a RECORD type.( That's nice, since I don't have to worry about it at all).

Now, I have to apply some logic on this RECORD type and subsequently update a couple of tables. I am not sure what the best way to go about this is.

Please guide me.
Tom Kyte
November 20, 2007 - 1:20 pm UTC

and I unfortunately and not even sure what you are asking for.

Bulk collect into object table

Vinay Chandrakant, December 03, 2007 - 10:00 am UTC

Hi Tom,
I have an extremely basic question - I am not sure what mistake I'm making in the code below, or if what I am attempting is actually possible:

SQL> create or replace type t_object_type as object (enterprise varchar2(40), qtysum number);
2 /

Type created

SQL> create or replace type t_object_table as table of t_object_type;
2 /

Type created

create or replace function f_temp return number is
rec_variable t_object_table;
begin
execute immediate
'select enterprise, sum(quantity) qtysum
from orders
group by enterprise'
bulk collect into rec_variable
;
return 0;
end f_temp;

On executing this, I get the following error:

ORA-00932: inconsistent datatypes: expected - got -

Using a 'record' in place of an object works. Meaning, 'create type t_record_type is record (enterprise varchar2(40), qtysum number)

You might question the use of dynamic sql here. Actually in the actual application, the query would be built dynamically. And, the object types for the query would be created in the dictionary automatically. I have provided this simple example only to keep the test case 'short, concise and complete'!

Thanks and Regards,
Vinay Chandrakant

Tom Kyte
December 03, 2007 - 11:34 am UTC

select t_object_type( enterprise, sum(quantity) from orders ....


you need to select your scalar object - into a table of objects!

Table of objects

Vinay Chandrakant, December 04, 2007 - 8:15 am UTC

Hi Tom,
Thanks a lot - though the question was really silly after all - I guess I must've been too tired and wasn't even looking at my own code properly :-(

Thanks!!
Vinay

Transient object types

Vinay Chandrakant, December 04, 2007 - 8:29 am UTC

Hi Tom,
I am not sure if this would be considered a 'followup' - it deals with objects and collections though - so I thought this is the right place. Please let me know if this was the wrong place to post this.

I have transient types created in the dictionary to match the structure of the resultset of a dynamically generated query. But I can't seem to be able to use them like this:

select "SYSTPQHCzGF7IbMngRAADunUR+w=="(enterprise,quantity) ... into my_object_table

What I get is: ORA-22833: Must cast a transient type to a persistent type
Is there a 'workaround' or some way I can use it this way?

What I need is:

I have a certain dynamically generated query and I need to collect the resultset into a collection for subsequent processing. The object type is available in the dictionary for me to use (as a transient type). Short of using dynamic PL/SQL to create an anonymous block that declares a object type to match the structure of my resultset, is there any way I can use the available structure of the transient type?

Thanks and Regards,
Vinay Chandrakant

compare collections column names

Ram Joshi, December 26, 2007 - 6:17 am UTC

Hi Tom,

I'm on Oracle 9i and I want to load data from a work table to the base table.

There are couple of things that im unclered with..

1.) Im using DBMS_SQL package to read the data from Work table along with column names using DBMS_SQL.DESCRIBE_COLUMNS .
2.) The column names in the second (base) table is EXACTLY identical to the work table ,However with few extra columns as well as the order of columns is also not the same.
3.) I have declared two TYPES based on the both tables and I just need to navigate (loop) through the record columns compare it with the first record column and if the column names are similar...Add the column value in the record variable column name.
But Im unable to do so as i cannot get the column names dynamically in record type variable as well as compare it also.
Can u please help me on this?

Just to illustrate :
TAble 1(Work TAble) : EMP (Ename VARCHAR2(200)
SAL VARCHAR2(200)
DEPT VARCHAR2(200)
HIREDATE VARCHAR2(200)
)

TAble2 (Base TAble) : EMP_BASE (Ename VARCHAR2(200)
HIREDATE DATE
DEPT VARCHAR2(200)
SAL Number
)
(Please note ORDER of columns is NOT same in EMP_BASE table as well as the DATA TYPE !!!)

TYPE emp_tab IS TABLE OF EMP%rowtype;
emp_rec emp_tab := emp_tab();

TYPE emp_base_tab IS TABLE OF EMP_BASE%rowtype;
emp_base_rec emp_base_tab := emp_base_tab();

I want to loop through ALL the columns of EMP table one by one,Compare it with the column name of EMP_BASE table and if it is the same then I need to put the value against that column using the record types.

So could you please guide me as to how do i navigate through the record type variable and to get the column name also?

I hope you got my problem....

Regards
Ram.
Tom Kyte
December 26, 2007 - 9:05 am UTC

"U" is unavailable.

"I" can unambiguously state that records are a compile time thing, you use them procedurally and you know what they look like at compile time.

They are not a runtime introspection sort of thing. They are static. There is no "dynamic access to them".


sounds a lot like you just want to MERGE - eg: there should be no code written here at all, a single SQL DML statement is what you were looking for.

Still....

Ram Joshi, December 27, 2007 - 6:26 am UTC

Hi Tom,

First of all I appollogize for the "U" thing.I'm really SORRY for that...

So does this mean that We cannot get the column names of the record ?

I mean if we have two record variables rec_var1 and rec_var2 and say ORDER_KEY is the FIRST column in rec_var1 and say FIFTH column in rec_var2 ,So when I'm looping through EACH column of rec_var1 and only when the corresponding column in rec_var2 is matched ,I want to assign that value to this column of rec_var2.

So my question is how do I come to know if the FIFTH column is the ORDER_KEY column in rec_var2?

How do I get the colun name of the record?

Cant we get this info dynamically ?

I mean is this really NOT POSSIBLE in PL/SQL??

Thanks in advance once again,
Ram.

Tom Kyte
December 27, 2007 - 9:45 am UTC

You know the column name of the record implicitly - you CREATED IT.

Records are statically defined at compile time. You create them way before the code is ever run.

array in an object

sara, January 03, 2008 - 12:36 pm UTC

Hi Tom,
I have number array type and trying to use it in an object as follows:

CREATE OR REPLACE TYPE child_item_tab AS TABLE OF NUMBER;
/

CREATE OR REPLACE TYPE obj_child_item_tab AS TABLE OF child_item_tab;
/

CREATE OR REPLACE TYPE cd_typ
AS OBJECT (
PARENT_ITEM_ID NUMBER
, CHILD_ITEMS obj_child_item_tab
);
/

CREATE OR REPLACE TYPE obj_cd_typ AS TABLE OF cd_typ;
/

SELECT i.item_id
, CAST (SELECT ir.child_item_id
FROM item_relation ir
WHERE parent_item_id = i.item_id
AS obj_child_item_tab) CHILD_ITEMS
FROM item i
WHERE i.item_id in (1,2,3);

Could you please let me know where the select fails.

Thanks

Sara
Tom Kyte
January 03, 2008 - 3:34 pm UTC

I don't have an ITEM table...

Collection Parameters

carmen, June 23, 2009 - 3:56 am UTC

I have a procedure that modifies the password of an Oracle user with a random generated password. But now I have a new requirement:

The new password must be encrypted with a list of n public Keys (PuKs of RSA) sent by client with n=1..few dozens, and all resulting encrypted strings must be sent to the end client.


These are my ideas to achieve it:


1) Receive the Puks list as a parameter of the procedure, being :

A- an associative array where the index will be the PuK string (128 bytes), and the content will be the encrypted password for that PuK (OUT).

type t_puk_encrypted_list is table of varchar2(256) index by varchar2(256);

PROCEDURE crypt_pass (p_password varchar2(15), p_puk_encrypted_list IN OUT)


B- an associative array with an integer index, and other parameter for returning the encrypted password list, being the index the reference between the two parameters, I mean: p_encrypted_list(i) will be the encrypted password with the p_puk_list(i)

type t_puk_list is table of varchar2(256);
type t_encrypted_list is table of varchar2(256);
PROCEDURE crypt_pass (p_password varchar2(120), p_puk_list IN, p_encrypted_list OUT)


2) I will need using operative system tools to encrypt the password with RSA, since dbms_crypto doesn't support this algorithm.

For each element of the collection, call to operative system shell to generate the encrypted password and load the array with it.

I have read that the accesses to Operative System from database might be done using dbms_scheduler. Change the password

is an on-demand request.

Considering the number of PuKs is unknown, am I wrong thinking the associative array is a valid approach or it would be better a nested table of records with two elements (PuK,encrypted password), or may be two lists (2 sql types): one of Puks (IN) and other for encrypted password (OUT) where the index i would be the reference for the two list?

Would you use dbms_scheduler to call operative system utilities in this case?

Any comment will be welcome.

Thanks in advance.


Tom Kyte
June 26, 2009 - 9:00 am UTC

this is a wacky weird 'requirement' (since N-1 of the return values would be utterly useless - they wouldn't be the password or anything, not sure at all what the N-1 outputs of encrypted things would be useful for - I cannot imagine)

but suffice to say - only plsql understands "index by tables that are not indexed by binary_integer" - meaning, if you use the first approach, only plsql would be able to interface with it.


and I don't know what dbms_scheduler would be invoking - if you want to encrypt, we do encryption already - dbms_crypto, there would be nothing at the OS level to invoke.

collection parameters

Carmen, July 01, 2009 - 4:08 am UTC

Thanks Tom,

N-1 will be the number of end users (application users) that will connect to same database user, whose password it's being changed. As each end user has its own pair public / private key, and as the encryption algorithm is RSA(asymmetric => public key for encripting and a private key for decrypting) then I have to encrypt the random generated password with N-1 public keys (puk). This is the reason for what I am using the plsql types:

type t_listapuk as table of varchar2(300); 
type t_listaenc as table of varchar2(150); 

Following documentation dbms_crypto only supports Symmetric algorithms (encryption key=decription key), so the only way to encrypt with RSA will be using operative system tools (openssl). I have implemented a job (dbms_scheduler), which calls a program with an argument, an its value (will be the puk) changes for each job execution, it means N-1 job runs with flag use_current_session = true, because with false, trying to read into the out parameter the file generated by the script fires ORA-29283 (file has not been generated yet).

Basically:
LOOP 
    write_file_puk
    dbms_scheduler.set_job_argument_value
    dbms_scheduler.run_job
    read_file_encrypted 
END

It works, but, I have doubts about the efficiency (2 seconds for each iteration , shell script runs in 0.04 seconds). Could I define an out argument for the program that sets the value for the collection without reading it from plsql?

Besides, this is the session information, after several runs (with 2 or 3 puks):

NAME SID
STATISTIC#      VALUE
-------------------------------------------------- ----- ---------- ----------
session uga memory                                   198         20    1010328
session uga memory max                               198         21    2254552
session pga memory                                   198         25    1740616
session pga memory max                               198         26    2723656

Do you think the resources use is acceptable?

Thanks again for your help.

Tom Kyte
July 06, 2009 - 7:03 pm UTC

sorry, this still makes *no sense*

so what if there are n-1 users, they each have their own credentials.

Baskar

A reader, December 28, 2010 - 9:28 am UTC

Hi Tom,

Can you please give me an example of passing a collections as parameter to a procedure present in another oracle DataBase?

Thanks in Advance
Baskar
Tom Kyte
December 28, 2010 - 10:24 am UTC

collections are not database link friendly - object types are not database link friendly.

You can use plsql index by tables of records if you like. You would just refer to the remote plsql index by table type in the local code. That is, the remote procedure would have some type defined - say "x"

You would usually:

create synonym remote_pkg_name for remote_pkg_name@remote_db_link;


and in your code, locally, you can:

my_variable remote_pkg_name.x;


define a local variable of the remote type.

Varray causing slowness?

Devinder, September 13, 2011 - 4:34 pm UTC

I have a type defined as
CREATE OR REPLACE TYPE MDARCHIVE."GLOBAL_NUMBER_VARRAY" as VARRAY(3000) OF NUMBER;

I ma using it in a sql like this

select distinct snap.redpairclip, snap.tenor_months, snap.timestamp, snap.bid_size, snap.offer_size, snap.bid_level, snap.offer_level,
snap.quotetype, snap.holding_company, snap.dealer, snap.marketsegment
from marketdata_mainventorysnapshot snap, (
select i.redpairclip, i.tenor_months, i.holding_company, max(i.timestamp) timestamp
from marketdata_mainventorysnapshot i
where i.invtype in ('I', 'S') and
i.timestamp between to_date('09/10/2011 06:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM') and to_date('09/12/2011 06:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
and holding_company = 'JPM'
and marketsegment in (select * from TABLE(global_number_varray(18, 16, 24, 22, 23, 20, 28, 19)))
and quotetype in ('F', 'I')
group by i.redpairclip, i.tenor_months, i.holding_company
) temp
where snap.redpairclip = temp.redpairclip
and snap.tenor_months = temp.tenor_months
and snap.timestamp = temp.timestamp
and snap.holding_company = 'JPM';

This is the row source operation
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
32 32 32 HASH UNIQUE (cr=32618887 pr=0 pw=0 time=277903063 us cost=2087 size=165 card=1)
175 175 175 FILTER (cr=32618887 pr=0 pw=0 time=277900844 us)
4800 4800 4800 HASH GROUP BY (cr=32618887 pr=0 pw=0 time=277902432 us cost=2087 size=165 card=1)
41469 41469 41469 NESTED LOOPS (cr=32618887 pr=0 pw=0 time=361730433 us)
141740424 141740424 141740424 NESTED LOOPS (cr=457874 pr=0 pw=0 time=83983725 us cost=2086 size=165 card=1)
284 284 284 HASH JOIN (cr=1751 pr=0 pw=0 time=65757 us cost=74 size=52 card=1)
284 284 284 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=1751 pr=0 pw=0 time=66028 us cost=44 size=50 card=1)
284 284 284 TABLE ACCESS BY LOCAL INDEX ROWID MARKETDATA_MAINVENTORYSNAPSHOT PARTITION: 1 1048575 (cr=1751 pr=0 pw=0 time=66013 us cost=44 size=50 card=1)
284 284 284 INDEX RANGE SCAN AK2_MKT_MAINVHOLD PARTITION: 1 1048575 (cr=1606 pr=0 pw=0 time=63765 us cost=44 size=0 card=1)(object id 89794)
8 8 8 COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=10 us cost=29 size=16336 card=8168)
141740424 141740424 141740424 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=456123 pr=0 pw=0 time=59143520 us cost=44 size=0 card=145141)
141740424 141740424 141740424 INDEX RANGE SCAN AK2_MKT_MAINVHOLD PARTITION: 1 1048575 (cr=456123 pr=0 pw=0 time=32921885 us cost=44 size=0 card=145141)(object id 89794)
41469 41469 41469 TABLE ACCESS BY LOCAL INDEX ROWID MARKETDATA_MAINVENTORYSNAPSHOT PARTITION: 1 1 (cr=32161013 pr=0 pw=0 time=204450023 us cost=2012 size=113 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
32 HASH (UNIQUE)
175 FILTER
4800 HASH (GROUP BY)
41469 NESTED LOOPS
141740424 NESTED LOOPS
284 HASH JOIN
284 PARTITION RANGE (ALL) PARTITION: START=1 STOP=
1048575
284 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX
ROWID) OF 'MARKETDATA_MAINVENTORYSNAPSHOT' (TABLE)
PARTITION: START=1 STOP=1048575
284 INDEX MODE: ANALYZED (RANGE SCAN) OF
'AK2_MKT_MAINVHOLD' (INDEX) PARTITION: START=1 STOP=
1048575
8 COLLECTION ITERATOR (CONSTRUCTOR FETCH)
141740424 PARTITION RANGE (ALL) PARTITION: START=1 STOP=1048575
141740424 INDEX MODE: ANALYZED (RANGE SCAN) OF
'AK2_MKT_MAINVHOLD' (INDEX) PARTITION: START=1 STOP=
1048575
as is obvious it runs very slow ( abt 3 min ) as it is scanning 141740424 rows ( dont know why)

however if I replace "marketsegment in (select * from TABLE(global_number_varray(18, 16, 24, 22, 23, 20, 28, 29)))" in the sql statement with "marketsegment in (18, 16, 24, 22, 23, 20, 28, 19)"
the row source operation changes and it becomes
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
32 32 32 HASH UNIQUE (cr=52197 pr=0 pw=0 time=1463479 us cost=49 size=149 card=1)
175 175 175 NESTED LOOPS (cr=52197 pr=0 pw=0 time=63908 us)
1159 1159 1159 NESTED LOOPS (cr=51630 pr=0 pw=0 time=65254 us cost=48 size=149 card=1)
32 32 32 VIEW (cr=238 pr=0 pw=0 time=2914 us cost=4 size=44 card=1)
32 32 32 FILTER (cr=238 pr=0 pw=0 time=2849 us)
32 32 32 HASH GROUP BY (cr=238 pr=0 pw=0 time=2662 us cost=4 size=50 card=1)
284 284 284 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=238 pr=0 pw=0 time=3319 us cost=3 size=50 card=1)
284 284 284 INLIST ITERATOR (cr=238 pr=0 pw=0 time=3218 us)
284 284 284 TABLE ACCESS BY LOCAL INDEX ROWID MARKETDATA_MAINVENTORYSNAPSHOT PARTITION: 1 1048575 (cr=238 pr=0 pw=0 time=2121 us cost=3 size=50 card=1)
284 284 284 INDEX RANGE SCAN AK2_MKT_MAINVHOLD PARTITION: 1 1048575 (cr=93 pr=0 pw=0 time=1065 us cost=2 size=0 card=1)(object id 89794)
1159 1159 1159 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=51392 pr=0 pw=0 time=1453729 us cost=44 size=0 card=62)
1159 1159 1159 INDEX RANGE SCAN AK2_MKT_MAINVHOLD PARTITION: 1 1048575 (cr=51392 pr=0 pw=0 time=1453067 us cost=44 size=0 card=62)(object id 89794)
175 175 175 TABLE ACCESS BY LOCAL INDEX ROWID MARKETDATA_MAINVENTORYSNAPSHOT PARTITION: 1 1 (cr=567 pr=0 pw=0 time=2890 us cost=45 size=105 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
32 HASH (UNIQUE)
175 NESTED LOOPS
1159 NESTED LOOPS
32 VIEW
32 FILTER
32 HASH (GROUP BY)
284 PARTITION RANGE (ALL) PARTITION: START=1 STOP=
1048575
284 INLIST ITERATOR
284 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX
ROWID) OF 'MARKETDATA_MAINVENTORYSNAPSHOT' (TABLE)
PARTITION: START=1 STOP=1048575
284 INDEX MODE: ANALYZED (RANGE SCAN) OF
'AK2_MKT_MAINVHOLD' (INDEX) PARTITION: START=1 STOP=
1048575
1159 PARTITION RANGE (ALL) PARTITION: START=1 STOP=1048575
1159 INDEX MODE: ANALYZED (RANGE SCAN) OF
'AK2_MKT_MAINVHOLD' (INDEX) PARTITION: START=1 STOP=
1048575
175 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF
'MARKETDATA_MAINVENTORYSNAPSHOT' (TABLE) PARTITION: START=1
STOP=1

and its scanning only 1159 rows, obviously the query runs very fast here ( under a second). Can you please explain why using a varray causing a scan of 141740424 rows.

Tom Kyte
September 14, 2011 - 7:03 pm UTC

not knowing the schema sort of impairs the ability to figure out what is going on where.

do you have an explain plan with the predicates at the bottom of it (showing filters and access bits) and also explain the schema a bit. Not knowing what the indexes are actually on sort of makes this hard.

ops$tkyte%ORA11GR2> explain plan for select * from dual where dummy = 'X';

Explained.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
<b>
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUMMY"='X')
</b>
13 rows selected.


that bit in bold (along with the rest) would be very helpful

Type varialbe from SQL Developer

A reader, June 22, 2012 - 9:29 am UTC

Following function is not running from SQL Developer.
1-How i get it executed from sql prompt.
2-How to set values for p_empno.
3-How a default value can be set for p_empno
4-How a null value can be passed to p_empno. in case of null will it return error.



CREATE OR REPLACE
PACKAGE TEST_PKG AS

TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
FUNCTION INST_EMP(P_EMPNO IN T_NUMBER ) RETURN NUMBER;

END TEST_PKG;
CREATE OR REPLACE
PACKAGE BODY TEST_PKG AS

FUNCTION INST_EMP(P_EMPNO IN T_NUMBER ) RETURN NUMBER
AS
v_empno number;
v_cnt number;
BEGIN
FOR i IN P_EMPNO.FIRST..P_EMPNO.LAST Loop
v_empno := P_EMPNO(i);
insert into emp(empno) values (v_empno);
end loop;
select count(*) INTO v_cnt from emp;
return v_cnt;
END INST_EMP;

END TEST_PKG;
Tom Kyte
June 22, 2012 - 4:55 pm UTC

just run it????

execute test_pkg.inst_emp( 1234 );


if you want a default value of p_empno, just use the syntax from plsql for htat

create function foo( x in number := 123 ) return ...


as for the error if null


if (p_empno is null) then raise_application_error( -20001, 'error!' ); end if;



Type varialbe from SQL Developer

A reader, June 23, 2012 - 1:04 pm UTC

I wanted to say that sql developer is not running or debugging this function so how i execute it?
Tom Kyte
June 24, 2012 - 12:32 pm UTC

I'm not sure what you mean...


but, you can just type it into the sql window and run it


begin procedure_name; end;


varrays maintain their "order" and nested table doesnt .

A reader, June 20, 2013 - 7:32 am UTC

Hi Tom,

In this page you have written like "varrays maintain their "order". Nested tables do not.".

Is it like we cant delete few elements in a varry but we can ,in nested tables.

varrays maintain their order means, 3rd element will come after 1st and 2nd ?(gap free).

If not could you please explain what does that mean varrays maintains order when is used as table storage.


Tom Kyte
June 20, 2013 - 2:32 pm UTC

a varray is stored as a blob, the first element will be the first element, the second the second, and so on. it is like a serialized object. It is written to a flat structure.

varray's are not "sparse". there is a first, second, third, .... Nth element to them - in order.


a nested table is stored as rows in a child table on disk, rows in a table have no order. rows are not retrieved in the order of insertion necessarily in a relational database, we do not have the ability to retrieve them "in order"


see:
http://docs.oracle.com/cd/E11882_01/appdev.112/e11822/adobjcol.htm#i467558

for current documentation.

got it totally ,thank you :)

A reader, June 21, 2013 - 5:08 am UTC


Since Associative Array is sparse, please guide on this...

Vrushali, September 17, 2014 - 7:41 am UTC

Hello Tom,

I am new to the concept of collections and I want to use the associative array (being it sparse) in a subquery.
Is it ever possible to use collections in SQLs?

Please have a look at below code:

create table ba_wg
(ba number,
wg number,
val varchar2(10));

insert into ba_wg values(1,1,'First');
insert into ba_wg values(1,2,'Second');
insert into ba_wg values(1,3,'Third');
insert into ba_wg values(1,4,'Fourth');


NOW the proc is like this:


create or replace procedure coll_demo(a_ret out varchar2)
as
TYPE char_aat_1 IS TABLE OF date INDEX BY VARCHAR2(3);
char_aat char_aat_1;
aa varchar2(3);
a_val varchar2(10);
begin
for i in(select * from ba_wg) loop
aa := i.ba || i.wg;
char_aat(i.ba || i.wg) := sysdate + i.wg;
dbms_output.put_line(char_aat(aa));
end loop;
/*************************************/
--this part throws error
select val into a_val from ba_wg x
where char_aat(x.ba||x.wg) = sysdate;
dbms_output.put_line(char_aat('11'));
/*************************************/
end;
/

Can you help me understand why can't we use it like this and what can be possible workaround?

why ORA-22814 in using Collect

Rajeshwaran, Jeyabal, February 01, 2023 - 3:09 am UTC

Team,

the following demo is from ATP 21c instance, can you help me to understand why i got ORA-22814 when using "COLLECT" function call.

demo@ATP21C> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

demo@ATP21C> select sys.odcinumberlist(10,20,30,40) from dual;

SYS.ODCINUMBERLIST(10,20,30,40)
------------------------------------------------------------------------------------
ODCINUMBERLIST(10, 20, 30, 40)

demo@ATP21C> select cast( collect(deptno) as sys.odcinumberlist )
  2  from dept;
select cast( collect(deptno) as sys.odcinumberlist )
             *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type


demo@ATP21C> select cast( collect(cast( deptno as number )) as sys.odcinumberlist )
  2  from dept;

CAST(COLLECT(CAST(DEPTNOASNUMBER))ASSYS.ODCINUMBERLIST)
------------------------------------------------------------------------------------
ODCINUMBERLIST(10, 20, 30, 40)

demo@ATP21C> set linesize 71
demo@ATP21C> desc dept
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 DEPTNO                              NOT NULL NUMBER(2)
 DNAME                                        VARCHAR2(14)
 LOC                                          VARCHAR2(13)

Connor McDonald
February 03, 2023 - 6:38 am UTC

It has been that way for as long as I can remember.

This from 19 and 21

SQL> create or replace type numlist as table of number;
  2  /

Type created.

SQL> select cast( collect(deptno) as numlist ) from dept;
select cast( collect(deptno) as numlist ) from dept
             *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type


SQL> create or replace type charlist as table of varchar(100);
  2  /

Type created.

SQL> select cast( collect(dname) as charlist) from dept;

CAST(COLLECT(DNAME)ASCHARLIST)
-------------------------------------------------------------------------
-------
CHARLIST('ACCOUNTING', 'RESEARCH', 'SALES', 'OPERATIONS')


I've seen people do this as well

SQL> select cast( collect(deptno+0) as numlist ) from dept;

CAST(COLLECT(DEPTNO+0)ASNUMLIST)
-----------------------------------------------------------
-------
NUMLIST(10, 20, 30, 40)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library