Oracle plsql collection

Can you please explain pl/sql bulk collection and its uses and also there excecption ? Please explain with example.?
And When to used it ?
digs developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The online docs have everything you need:
5 PL/SQL Collections and Records

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

as for when to use it:
When you need to store data in in-memory tables.

Mainly when you need to reuse the collection and/or need to move forwards and backwards through the data.
0
digs developerAuthor Commented:
Could you please give me the one live scenario so I can better understand.It will be help for me while coding.
0
sdstuberCommented:
This example will copy the table 10 rows at a time.
10 is only for demonstration,  in real code you should bulk collect without limit if you can.
If you must put a limit, then go as big as your system will allow


CREATE TABLE my_tables
AS
    SELECT *
      FROM user_tables
     WHERE 1 = 0;

DECLARE
    TYPE input_tab IS TABLE OF user_tables%ROWTYPE
        INDEX BY PLS_INTEGER;

    CURSOR cur
    IS
        SELECT * FROM user_tables;

    v_tab input_tab;
BEGIN
    DELETE my_tables;

    COMMIT;

    OPEN cur;

    LOOP
        FETCH cur BULK COLLECT INTO v_tab LIMIT 10;  

        EXIT WHEN v_tab.COUNT = 0;

        FORALL i IN v_tab.FIRST .. v_tab.LAST
            INSERT INTO my_tables
                 VALUES v_tab(i);

        COMMIT;
    END LOOP;
END;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
I could make up an example but it would be pretty much a copy of what is in the docs.

From a personal experience, I've created a TABLE of RECORDS keyed on a unique id.

For example:
type bookRec is record(
number_of_pages number,
author varchar2(100)
);

then a table of them:
type bookTab is table of bookRec index by binary_integer;

Then I can bulk collect into it.

Not I can move forward, backwards, or just loop through them as I need to complete any task.

What you have to ask is would a normal cursor loop accomplish the same thing?

If so, use a cursor loop.  I would only use a collection when it was more efficient or requirements made me.
0
sdstuberCommented:
same example, but this time without limits and looping


DECLARE
    TYPE input_tab IS TABLE OF user_tables%ROWTYPE
        INDEX BY PLS_INTEGER;

    v_tab input_tab;
BEGIN
    DELETE my_tables;

    COMMIT;

    SELECT *
      BULK COLLECT INTO v_tab
      FROM user_tables;

    FORALL i IN v_tab.FIRST .. v_tab.LAST
        INSERT INTO my_tables
             VALUES v_tab(i);

    COMMIT;
END;
0
digs developerAuthor Commented:
1. limit 10 means it inserts the 10 reacords at a time am I right ?
2.Suppose we do not mentioned the linmit value then what it takes?
3. What is the default valus for limit ?
4.Suppose there are 10,00,000 records need to insert then is it necessory to give limit value ? If yes then on what bases will we decide the limit value ? Is there any formula ?
0
sdstuberCommented:
Also note,  you use bulk collect when you might have to reuse the collection (thus saving the io of querying the tables again),  or if you must break work into pieces because it's too big for your system to handle it.

Ideally, the code above would not use collections or loops at all.
Instead it would look something like this...


BEGIN
    DELETE my_tables;

    INSERT INTO my_tables
        SELECT * FROM user_tables;

    COMMIT;
END;
0
sdstuberCommented:
>>>>> 1. limit 10 means it inserts the 10 reacords at a time am I right ?

Sort of- the limit is on the SELECT ,  NOT the INSERT - so it's fetching 10 rows from the cursor.
The FORALL INSERT doesn't use limit, it's using the array, which, of course only has 10 elements because that's all that is selected.


>>>> 2.Suppose we do not mentioned the linmit value then what it takes?

Then the entire cursor would be read into the array regardless of size.  If that exceeds your available memory then it will fail.


>>>> 3. What is the default valus for limit ?

If you use LIMIT but don't give a value that's an error.  If you don't use limit, then you'll fetch all

>>>> 4.Suppose there are 10,00,000 records need to insert then is it necessory to give limit value ? If yes then on what bases will we decide the limit value ? Is there any formula ?

10million records might be a lot if those records are blobs of movies,  if it's 10 million numbers, then it probably won't be a problem.

There's no specific formula but the space is your PGA.

So, start with your pga, subtract everything you're doing that is NOT your collection.
Whatever is left divide by the size of one record.
You should be able to approximately that many records
0
digs developerAuthor Commented:
Thank you !
0
digs developerAuthor Commented:
Could you please explain how we can handle the exceptions in collection and which are they ?
0
sdstuberCommented:
That's pretty open ended and will probably chain into many questions which isn't really how EE works.

It should be one question per thread.

Open a new question, using a specific example and explain the type of exception you're expecting and how you'd want to handle it.

Do you need any further assistance on this one in showing how to declare a collection and bulk collect?
If so, ask away, if not, please remember to close the question and your previous questions too
0
digs developerAuthor Commented:
Thank you !
0
digs developerAuthor Commented:
1. Is it necessory to give limit clause in collection?
2.If I am not mentioned the limit clause then it any effect on performance ?
3.Mentioned the limit clause is good or not ?
 I am still confused .
0
slightwv (䄆 Netminder) Commented:
What I tried to mention in my first post:

There are no hard and fast rules like 'When green, use collections', 'when blue use limit'.

You need to understand the concepts and what things mean.  Then when you encounter a specific situation, you can go 'OH, I can use ??? here'.

In other words, you can drive a nail with a screwdriver but a hammer is much better for the task.

Once you learn that, you see a screw, use a screwdriver.  You see a nail, use a hammer.
0
sdstuberCommented:
1. Is it necessory to give limit clause in collection?

syntax - no it's not necessary.
practically  - yes, sometimes


2.If I am not mentioned the limit clause then it any effect on performance ?

the fewer operations you perform the better as far as performance is concerned.
so, if you loop 10 times and execute a statement each time that is worse than executing one statement.

but - if your system can't handle all of the data, executing one giant statement might not be feasible even if it's preferable.


3.Mentioned the limit clause is good or not ?

neither and both

if you use it when it's appropriate, it's good.

when you use it when it's not, it's bad


if you're trying to get someone to explain every possible scenario that might be good or bad - that is not going to happen
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
digs developerAuthor Commented:
good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.