Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle plsql collection

Posted on 2014-08-07
16
Medium Priority
?
269 Views
Last Modified: 2014-08-18
Can you please explain pl/sql bulk collection and its uses and also there excecption ? Please explain with example.?
And When to used it ?
0
Comment
Question by:digs developer
  • 7
  • 6
  • 3
16 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40247102
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
 

Author Comment

by:digs developer
ID: 40247134
Could you please give me the one live scenario so I can better understand.It will be help for me while coding.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247191
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40247210
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247212
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
 

Author Comment

by:digs developer
ID: 40247215
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247222
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247259
>>>>> 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
 

Author Comment

by:digs developer
ID: 40247279
Thank you !
0
 

Author Comment

by:digs developer
ID: 40247285
Could you please explain how we can handle the exceptions in collection and which are they ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247297
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
 

Author Comment

by:digs developer
ID: 40247344
Thank you !
0
 

Author Comment

by:digs developer
ID: 40247355
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40247620
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1660 total points
ID: 40247672
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
 

Author Closing Comment

by:digs developer
ID: 40267745
good
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question