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.

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

Hi Tom,

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

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.
 

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????
 

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.
 

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????
 

Hi Tom,

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

Regards.

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.
 

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

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,
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,

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,

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 .

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?

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>
 

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
 

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.

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 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 ;

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.

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!

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

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

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 

I made a mistake, the sql I want is:

Select distinct security_type from vn_algorithm;

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.

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 ?

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

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.

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?
 

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  /
 

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. 

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

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.
 

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.

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.

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.

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.

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

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

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!

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.

 

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 ;)

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. 

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;

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

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.

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

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

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?

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

Asif.

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 :)

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 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?

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

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

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,

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
 

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?

Exactly what I need! Thanks!

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

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,
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;

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 

 

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,

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

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

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.

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

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.

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;

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

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

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.

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.

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

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

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

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.

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.

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

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.

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.

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

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.

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.

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;

I wanted to say that sql developer is not running or debugging this function so how i execute it?

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.

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?

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)

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')

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

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