Thanks for the question, play.

Asked: August 05, 2001 - 8:32 pm UTC

Last updated: November 20, 2006 - 1:46 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom

what is the difference between a varray and a nested table, can you give me a very very simple example , and explain the difference
when should I use varrays and when should I use nested tables

the same with pl/sql record and pl/sql table
say I hvae a curser select ename,deptno from emp;
I can declare and use a pl/sql record or pl/sql table to hold these values, can you tell me when should I choose to use pl/sql record and when should I use pl/sql table.

lastly, can you give me a simple example for ref cursor

what is the difference between a regular explicit cursor and a ref cursor

and Tom said...

a varray is fixed in size (its N elements long and no longer)
a nested table is inifinite in size potentially

a varray stores data in a fixed order, element(1) of a varray is well known and will be the same time after time
a nested table does not assure the order will be preserved, the value of element(1) may change over time (all of the elements will be there, but in different orders)

a varray is stored as a blob with the table
a nested table is stored in a separate physical database table as a parent child table.

In PLSQL code (when I do not plan on creating a database column of that type) -- I use nested tables exclusively. I cannot think of a good reason to constrain my array size artificially when I don't have to. In PLSQL code, there is very little difference between a varray and nested table type (the fixed order issue isn't an issue with PLSQL nested tables, they are just like varrays -- its only when you SAVE the nested table to a database table that the order might be different when you read it back in)

In SQL (using these in create tables) -- well, there I don't really have any advice. I've used a varray everynow and then but stick to relation constructs pretty much. If I've a need of a nested table construct, I set up a parent child relationship.

I cannot compare a record to a table -- they are orthogonal to eachother. You can have a table of records... A record is used to hold a row, you can use a table of records to hold many rows at the same time. They are somewhat apples and oranges.

simple ref cursor, sure:

scott@ORA8I.WORLD> variable x refcursor
scott@ORA8I.WORLD> begin open :x for select * from dept; end;
2 /

PL/SQL procedure successfully completed.

scott@ORA8I.WORLD> print x

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

The 2 major differences between an static explicit cursor and a ref cursor are:

o the ref cursor is parsed each time it is opened in plsql. The static cursor will be parsed ONCE per session typically (unless it gets flushed out)

o the ref cursor can point to a different query time it is opened (hence the need to parse each time, its not static). the static query cannot.

Rating

(7 ratings)

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

Comments

How to print the ref cursor returned from a multi-row Function.

Sandy, August 06, 2001 - 5:16 pm UTC

That was a good explanation. Tom if you could also demonstrate "How to print the ref cursor returned from a multi-row Oracle Function", assuming the function returns a ref-cursor type. I tried to print the output of "mce_output_qry_func" as given below.

SQLPLUS> variable x refcursor
SQLPLUS> begin x := mce_output_qry_func('CALENDAR',660,922,1); end;

It did'nt work. Would be of great help if you could get me that.

good explanation !

A reader, August 09, 2001 - 1:42 am UTC

Number of limit on PL/SQL table

A reader, September 23, 2002 - 12:15 pm UTC

Hi, Tom,

We got a problem on number of limit on Pl/SQL table.

Suppose we use DBMS_SQL package dynamically bind variables, decribe variable and get column values from any query. And further suppose we declare a pl/sql table like:
type vc_arr is table of varchar2(2000) index by
binary_integer;
g_cvalue vc_arr;
use g_cvalue to hold values of some of the columns,
if the query returns more than millions of records, do we have a limit number of records for g_cvalue variable to hold these many of records? If it does, what is the work around?

Thanks

September 23, 2002 - 1:24 pm UTC

You would NEVER want to fetch millions of rows.

You should fetch maybe 100 at a time, process them, fetch the next 100.

The APIs support this (array fetching of N records at a time), look at the define_array procedure.

varrays, nested tables, and pl/sql records, and pl/sql tables, and ref cursor

Bal Krishan Mittal, March 24, 2004 - 12:24 am UTC

dear sir , the answer you gave were very usefull for me even if i did not raised these questions but still while exploring your site the answers you gave were very usefull and reader friendly.
Thanks.
Bal Krishan Mittal.
The Energy and Resources Institute (TERI),
New Delhi.
balkrishan_mittal@hotmail.com

want to ask

bal krishan mittal, March 24, 2004 - 3:33 am UTC

sir i want to ask that how can i put my questions to you.

March 24, 2004 - 9:00 am UTC

when taking new questions, there is a button/link on the home page.

Thanks for the response

Archana, January 08, 2005 - 1:47 pm UTC

Got an idea about some of the DS in PL/SQL .

Thanks

Can we get only distinct value from a plsql record

A reader, November 20, 2006 - 11:29 am UTC

Dear Tom,

is it possible to get only distinct values from a given plsql record (or plsql table)

Thanks in advance

November 20, 2006 - 1:46 pm UTC

you can write any code you want :)

but think about it "a record", it is a bunch of attributes, there is no "distinct over attributes"

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer