this works just as well: DECLARE CURSOR exp_cur IS select first_name from hr.employees;
TYPE nt_fName IS TABLE OF VARCHAR(20); fname nt_fName; BEGIN OPEN exp_cur; FETCH exp_cur BULK COLLECT INTO fname; FOR i IN fname.FIRST..fname.LAST LOOP dbms_output.put_line(i||' - '||fname(i)); END LOOP; CLOSE exp_cur; END;
It means no need to use loop while fetching cursor into table using bulk collect? only one loop to be used which is to print table elements? please reply
I don't get line 12. We bulk collect the records and fname.COUNT is 107. When exactly will the fname.COUNT be 0? Isn't fname.COUNT always 107 after the bulk collect and during the session? Also, why do we bulk collect the data inside the loop? Can't we just bulk collect it in a single shot like bulk collect is supposed to do?
HI Mahish, Thanks for your nice tutorial. But is that required to create the outer loop ?? As per my understanding, if we are using bulk collect then automatically entire set of rows will be fetched( bulk collected) into PLSQL table variable ? Please correct me
HELLO, I DON'T UNDERSTAND WHY YOU ENTER AS EXIT CONDITION FROM THE LOOP: EXIT WHEN FNAME.COUNT = 0. IF YOU USE BULK TO POPULATE FNAME, IT SHOULD NEVER HAVE COUNT = 0 BECAUSE IT HAS JUST BEEN POPULATED. THANKS AND CONGRATULATIONS FOR THE VIDEOS ;)
one silly clarification needed, since bulk collect will combine all the records in single chunk only, so why you have used Loop here. that first loop will always be execute only 1 time. ryte?
Hey Ganesh,Did you find the answer of your question. I was also thinking the same question but still am not able to find the correct answer.please can you help?
In this example, you have written Bulk collect inside the cursor Loop. It will execute 107 time but in the first time itself bulk collect fetch 107 time. How it works? Clarity me
Manish what if the data present in table is not too long it's short. I mean what if there are not too many records like suppose only 6 records are present in employees table then in that case we need to use BULK COLLECT clause or it is of no use. ?? can u answer my question plz?? I have a doubt regarding this
I don't understand why did you put FETCH BULK COLLECT inside loop. And I don't understand why you used EXIT WHEN fName.COUNT = 0; instead of EXIT WHEN exp_cur.%NOTFOUND...? fName.COUNT should never be zero unless we delete all the members, or am I totally wrong? Something is not quite correct here, or at least not quite clear. Once said that, I must also say that I like your videos!
Hi Manish sir, For printing when I write the 'FOR' statement after closing the cursor , its getting compiled but no output is coming . could you plz help me to understand. Thanks
Manish using 1..collection. Count gives all the record on the same way can I use collection. First.. Collection. Last to get all the record if yes what is the diff
why you give "select first_name from employees" in declaration section. i dont understand this. can you please explain why cant it be in execution section
this works just as well:
DECLARE
CURSOR exp_cur IS
select first_name from hr.employees;
TYPE nt_fName IS TABLE OF VARCHAR(20);
fname nt_fName;
BEGIN
OPEN exp_cur;
FETCH exp_cur BULK COLLECT INTO fname;
FOR i IN fname.FIRST..fname.LAST
LOOP
dbms_output.put_line(i||' - '||fname(i));
END LOOP;
CLOSE exp_cur;
END;
It means no need to use loop while fetching cursor into table using bulk collect? only one loop to be used which is to print table elements? please reply
I don't get line 12. We bulk collect the records and fname.COUNT is 107. When exactly will the fname.COUNT be 0? Isn't fname.COUNT always 107 after the bulk collect and during the session? Also, why do we bulk collect the data inside the loop? Can't we just bulk collect it in a single shot like bulk collect is supposed to do?
Thank you so much for your great work to share knowledge with no cost......
I am glad you enjoyed the content. Your appreciation means a lot to me.
Thank you so much for the videos. I am starting my career as database developer and I find your videos highly useful.
Ur my god .bcz of ur video only I could live n this word
HI Mahish, Thanks for your nice tutorial. But is that required to create the outer loop ?? As per my understanding, if we are using bulk collect then automatically entire set of rows will be fetched( bulk collected) into PLSQL table variable ? Please correct me
HELLO, I DON'T UNDERSTAND WHY YOU ENTER AS EXIT CONDITION FROM THE LOOP: EXIT WHEN FNAME.COUNT = 0.
IF YOU USE BULK TO POPULATE FNAME, IT SHOULD NEVER HAVE COUNT = 0 BECAUSE IT HAS JUST BEEN POPULATED.
THANKS AND CONGRATULATIONS FOR THE VIDEOS ;)
same doubt
Why did you put Fetch statment inside the LOOP??? I kept it outside of loop, means w/o the loop, still working the same as when kept inside.
I copied the code and took the LOOP and EXIT line out and it works perfectly fine. After the CLOSE I coded the the count on fname.COUNT and got 107.
one silly clarification needed,
since bulk collect will combine all the records in single chunk only, so why you have used Loop here. that first loop will always be execute only 1 time. ryte?
Hey Ganesh,Did you find the answer of your question. I was also thinking the same question but still am not able to find the correct answer.please can you help?
Hi, your doubt is valid. Here loop won't be needed. Just 3 steps... open... Fetch.... Close...
I have the same question. That bit of code doesn't make sense.
sir, can you please tell how much videos are left for uploading so we can have a better idea of the things remaining.
thank you,
videos are awesome.
Great Sir... could you please provide a video for disadvantage of bulk collect
In this example, you have written Bulk collect inside the cursor Loop. It will execute 107 time but in the first time itself bulk collect fetch 107 time. How it works? Clarity me
In this exemple you used the nested table, Why when we used bulk collection it's not necessary to use the method extend?
Hi brother ,I want print output first name and salary for 109 records it's possible or not possible.
Manish what if the data present in table is not too long it's short. I mean what if there are not too many records like suppose only 6 records are present in employees table then in that case we need to use BULK COLLECT clause or it is of no use. ?? can u answer my question plz?? I have a doubt regarding this
why use loop while bulk collect .it store all values with out using loop
I don't understand why did you put FETCH BULK COLLECT inside loop.
And I don't understand why you used
EXIT WHEN fName.COUNT = 0;
instead of
EXIT WHEN exp_cur.%NOTFOUND...?
fName.COUNT should never be zero unless we delete all the members, or am I totally wrong?
Something is not quite correct here, or at least not quite clear.
Once said that, I must also say that
I like your videos!
yes i am thinking the same but i am not sure if i am correct.
second time when loop runs then collection will fed with null rows as cursor is already at the end left nothing to read
but you are correct we don't need any loop as it is bulk processing .
ya loop is not required since its bulk collect .
Agreed !
Hi Manish sir, For printing when I write the 'FOR' statement after closing the cursor , its getting compiled but no output is coming . could you plz help me to understand. Thanks
Manish using 1..collection. Count gives all the record on the same way can I use collection. First.. Collection. Last to get all the record if yes what is the diff
if data is not more in a table then in that case will context switch happen or not??
Great bro
Hello sir ...where we can find videos later than no. 74?
why you give "select first_name from employees" in declaration section. i dont understand this. can you please explain why cant it be in execution section
Renu saraMathi Still needed?
Git page is not available. 404 found error is coming.