Yes, you can see below:
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts
TYPE dnames_tab IS TABLE OF dnames_var;
SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
Associative array are are primarily key value pair. You can define it like this
type tbl is table of varchar2(100) index by varchar2(100);
lang_list('INDIA') := 'HINDI';
lang_list('US') := 'ENGLISH';
lang_list('FRANCE') := 'FRENCH';
Its all documented please refer to the document.
Yes i know. I am readin it from Feurstein's book.
No - I don't think you DO know. Or you would not have ask this:
I mean syntactically only index by clause is the difference right?
As Karthick_Arp already told you it is ALL documented, with examples, in the Oracle documentation
. . .
Oracle docs are good enough for understanding syntaxes.Not good enough for learning.They are like Unix man command:p
Did you read the document link posted by rp0428? It works for me, always have worked.
racle docs are good enough for understanding syntaxes.Not good enough for learning.They are like Unix man command:p
This is the question you ask:
Can we use bulk collect with nested tables and varrays?
And this is a quote from that doc link I provided:
The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, and then use those associative arrays with bulk constructs (the
And if you look at example 5-20 you will see this code:
Type NameSet IS TABLE OF c1%ROWTYPE;
EndCounter NUMBER := 10;
SeniorSalespeople := EmpList_arr();
SELECT first_name, last_name, hire_date
BULK COLLECT INTO SeniorTen
WHERE job_id = 'SA_REP'
ORDER BY hire_date;
So how is that doc 'Not good enough for learning' when it 1) DIRECTLY answers your question about using bulk collect with arrays and 2) has example code that illustrates using BULK COLLECT INTO a nested table?