PL/SQL tutorial 27: How To Create An Explicit Cursor In Oracle Database By Manish Sharma

[@mingyan8081]

For anyone with doubts why there is an extra row printed out at last. here is the answer: as mentioned in the video, curse_name%NOTFOUND returns false when the PREVIOUS FETCH returns a row. If false, after each fetch, SQL will give the value to the variable v_name , and then, print out the v_name; ........ when the cursor coming to the last tuple, fetch statement will give the last value to v_name; then print out v_name; Notice at this time, %NOTFOUND returns false because there is a row, the last row, so loop one more time. this time, fetch statement will not give anything to v_name, v_name stays the same as last time, which is the last row, then print out v_name AGAIN; this time, %NOTFOUND returns true, terminate the loop. to avoid this scenario, you can put exit when %notfound between fetch statement and print out, instead of putting it at last line. also you can use a while loop FETCH cursor_name into v_name; while cursor_name%NOTFOUND LOOP DBMS_OUTPUT.PUT_LINE(v_name); FETCH GET_CURSOR INTO v_name; end loop; close cursor_name; end;

[@pranaytanniru7764]

begin DBMS_OUTPUT.PUT_LINE('thank you so much for giving the solution to the problem'); end; /

[@CharlieBaker707]

It might be simpler to just bring the exit when line up before the dbms output line. This solves the problem for me.

[@achillles6838]

thank you so much

[@fishingkh60]

ty, I found putting the exit when statement between the fetch and output statements to be the simplest, yet perfect solution.

[@Otterhapist]

just switch the exit and dbms line it solves the problem just fine

[@devarshupadhyay]

you are awesome sir , youtube par bahot kam information hai pl/sql topic par so,please aise hi video banate rahiye hum students ko bahot help milti hai,once again thanks a lot Sir

[@Rebellionrider]

Thank you so much. Aapka ye message mere liye bohat motivating hai. I’ll keep on uploading. Aacha ek bata batao kya aapne mere reels dekhe jo abhi upload kar raha hu SQL pe. Please kuch feedback jarur dena.

[@TendsToWonder]

Man this guy i still saving me even after 4 years

[@palayoub373]

Amazing tuto! I think, the exiting statement is better to be placed right after the FETCH statement so the last output ligne doesn't get duplicated.

[@shreyanshjain7445]

yess, amazing tutorial! 💯 yep, exactly..!

[@pavankumarbadiginchula5445]

Ya correct

[@pankajpandey9186]

content and the way you present, both are awesome.

[@amitdey5903]

The print statement will be after the exit condition, else the last row is getting printed twice. :)

[@sandipdigambarnarwade284]

Mine too

[@nagarajb779]

Thanks for ur uploads expecting more videos on advanced topics like collections, bulk collect and PT.

[@kirangowda624]

Hi Manish, Thanks for the video. Well I have one query, can you please tell me why is the Bruce record repeated twice ? In the Employee table, we have only one entry for bruce with id = 4. Thanks in Advance :)

[@aslylee586]

yours tutorail is help me much more thanxxxxx so much

[@Rebellionrider]

+asly lee Thanks a lot for your appreciation. Always happy to help.

[@techprogrammer4408]

thank u so much for oracle tutorial video..................

[@rahulpanda9256]

Hi Manish.....no doubt it is awesome....if you can also upload a video related to the use of cursor I. e. why when to use it and it's benefits...it would be wonderful.....thanks a lot.....it helps a lot....thanks again...

[@mohitsaxena4643]

sir why there are 2 bruce in the output?? the exit when statement should be written after the fetch statement..

[@JadeclonOfficial]

thanks for this advice

[@samsevyareddy5057]

When it gives us the output directly with loops I mean implicitly then whats the use of using explicit cursors or can u pls tell the cases where explicit cursors are created over implicit

[@nehagaikwad9251]

Hello Sir, i have passed SQL 1z0-061 , now i want to appear for 1z0-062 for OCA and I was looking for your tutorials for that. But I couldnt find ay. Kindly help on how to learn it and from where(specifically online). Thank you

[@IAmESG]

I'd do the line of EXIT WHEN then add the DBMS_OUTPUT.PUT_LINE to avoid repeating values for the last execution

[@subhajitkarmakar4046]

absolutely..this is the right way to retrieve values.

[@lguzman1509]

nice tip

[@armystloveot7]

smart move

[@santoshchary1631]

hello Manish...if you write an example code for user handling exception with a wonderful example like which you have given in other sessions then that is also very useful for many..... :)

[@rafaelbicalho5288]

Thank you. Helped me a lot! :-)

[@nirajbava799]

set serveroutput on; / declare name varchar(20); cursor ck is select name from names where length(name)

[@leewook4601]

Thanks for sharing this video! How to return cursor type variable to the procedure.... like how to write out parameter of cursor type variable in procedure thanks in advance!!

[@sumaiyaqureshi809]

Can we use variables from array and use array elements according to requirements in place of where clause in cursor, means where clause should be work according to array element each time

[@arunkumardachepalli6318]

Sir, we can get the data in table by simply writing a select querry that you wrote then what is the advantage of creating cursor.

[@taiwodavidakinde2769]

Arun kumar Dachepalli Cursors becomes handy when you are dealing with records in the millions......look up Google for more information

[@sameer26121980]

As Manish explained, even when you write a simple query, Implicit cursor is created. But you will not have control over that cursor. If we need to control the cursor (like storing values in variables and manipulating them in a complex manner, like using loops, IF ELSE conditions, etc.), then we can declare an explicit cursor.

[@SivaKumar-rv1nn]

Thankyou sir

[@pranavsrivastava8474]

Could you please explain why your output returned 'Bruce' twice in your result?

[@МаратКасимов-ц5с]

Hi, because the result statement on the screen is after the statement of the end of the cycle. Therefore, the cursor displays output 2 times.It is necessary to swap the 12 and 13 lines of code. First comes 13 line, and then 12.

[@armystloveot7]

@@МаратКасимов-ц5с ya i got it

[@heroman2937]

@@МаратКасимов-ц5с Thx a lot for the info, needed exactly that for a project xD

[@uttamdas-bv3wi]

Hi Manish, while i am trying to connect to the HR schema the below error is getting can you suggest me how to solve that? "Invalid connection operation operation specified and verify the URL format for the specified driver" Thanks Uttam

[@bhagyalakshmibadiginchala38]

Which editor you have used will you please say

[@abottlefullofwater]

It is the SQL Developer

[@Syedahad07]

Nice

[@stanislavezhevski2877]

i consider this like an array isn't?

[@sinanhos4962]

when I compile it, I always get the error message "RA-00942: table or view does not exist". How can I solve the error? "

[@Knight.94]

perhaps you are mistyping the table name or maybe the table does not exist.

[@raviTeja-mf1wi]

WHY BRUCE COMES 2 TIMES IN THE OUTPUT

[@bonganiaubrey]

/* - Create a PL/SQL block that will prompt the user to input a department number and then displays the relevant information about the employee; refer to the sample output given below. Enter value for department: 10 Department 10 (Accounting) is in New York has 3 employee(s) with total salary R8750.00 */ DECLARE v_deptno NUMBER(2) := &deptno; v_dname VARCHAR2(14); v_loc VARCHAR2(13); v_empno NUMBER(4); v_sal NUMBER(7,2); BEGIN SELECT d.dname, d.loc, count(e.empno), sum(e.sal) INTO v_dname, v_loc, v_empno, v_sal FROM emp e, dept d WHERE e.deptno = d.deptno AND e.deptno = v_deptno GROUP BY d.dname, d.loc, e.empno, e.sal; DBMS_OUTPUT.PUT_LINE('Department '||v_deptno||' ('||v_dname||') is in '||v_loc||' has '||v_empno||' employee(s) with total salary R'||v_sal); END; / I am getting the error below, I wanna archive the output above. What approach can I use? ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 8

[@rantubiswas4381]

DECLARE v_deptno NUMBER(2):=&deptno; v_dname dept.dname%type; v_loc dept.loc%type; v_empno emp.empno%type; v_sal emp.sal%type; CURSOR c1 IS SELECT d.dname,d.loc,count(e.empno),sum(e.sal) FROM emp e INNER JOIN dept d ON e.deptno=d.deptno AND e.deptno=v_deptno ; BEGIN OPEN c1; LOOP FETCH c1 INTO v_dname,v_loc,v_empno,v_sal; DBMS.OUTPUT.PUT_LINE('Department'||v_deptno||'('||v_dname||')' 'is in '||d_loc||' has '||v_empno||' employees with total salary '||v_sal); EXIT WHEN c1%NOTFOUND; END LOOP CLOSE c1; END; You can use that one although i didn't run if you found any errorsor please let put here in the comment

[@aslylee586]

plz upload form and reports in oracle

[@BeggarPerson]

Anyone Watching in 2022?

[@Rebellionrider]

These tutorials are still very much useful.

[@fyzzergaming4822]

Watching after 7 years

[@Rebellionrider]

Wow, after 7 years? That's dedication! 😲 Thanks for sticking around. If there's anything specific you'd like to see or learn about in the world of SQL, just let me know! Also, if you could quickly master any advanced SQL skill, what would it be and why? Your long-term support means a lot! 🌟🚀

[@Simmlex]

So... Why is this thing useful? You can achieve the same result with a simple 2 line SQL code.

[@lguzman1509]

i guess because you can execute functions or another actions with the result of the cursor.

[@Otakuhiroshi]

Anyone watching in 2024 ?

[@aok5307]

here is my cursor example. DECLARE v_fname employee.first_name%type; v_lname employee.last_name%type; v_id employee.employee_id%type; CURSOR cur_Gordon IS SELECT first_name, last_name, employee_id FROM employee WHERE employee_id

[@madhubhardwaj4512]

Sir, we can get the data in table by simply writing a select query then what is the advantage of creating cursor.

[@onyx6128]

same thought