MOCKSTACKS
EN
Questions And Answers

More Tutorials









Oracle Bulk collect

Bulk data Processing


local collections are not allowed in select statements. Hence the first step is to create a schema level collection. If the collection is not schema level and being used in SELECT statements then it would cause "PLS-00642: local collection types not allowed in SQL statements"

CREATE OR REPLACE TYPE table1_t IS OBJECT (
a_1 INTEGER,
a_2 VARCHAR2(10)
);

--Grant permissions on collection so that it could be used publically in database

 GRANT EXECUTE ON table1_t TO PUBLIC;
 CREATE OR REPLACE TYPE table1_tbl_typ IS TABLE OF table1_t;
 GRANT EXECUTE ON table1_tbl_typ TO PUBLIC;

--fetching data from table into collection and then loop through the collection and print the data.

DECLARE
 table1_tbl table1_tbl_typ;
 BEGIN
 table1_tbl := table1_tbl_typ();
 SELECT table1_t(a_1,a_2)
 BULK COLLECT INTO table1_tbl
 FROM table1 WHERE ROWNUM<10;
 FOR rec IN (SELECT a_1 FROM TABLE(table1_tbl))--table(table1_tbl) won't give error)
 LOOP
 DBMS_OUTPUT.put_line('a_1'||rec.a_1);
 DBMS_OUTPUT.put_line('a_2'||rec.a_2);
 END LOOP;
 END;
/


Conclusion

In this page (written and validated by ) you learned about Oracle Bulk collect . What's Next? If you are interested in completing Oracle tutorial, your next topic will be learning about: Oracle Real Application Security.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.