Your videos are very useful I have learnt a lot from you Thank you! we can use %found and %notfound with implicit cursors DECLARE FNAME EMP.FIRST_NAME%TYPE; ROWCOUNT NUMBER; BEGIN SELECT FIRST_NAME INTO FNAME FROM EMP WHERE EMPLOYEE_ID = 108; ROWCOUNT := SQL%ROWCOUNT; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(ROWCOUNT||' ROW AFFECTED '); ELSIF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('NO ROWS GET AFFECTED'); END IF; END; /
Great video series! 16:30 Interestingly enough - SQL%isopen returns FALSE even if we are actually looping through the implicit cursor in a FOR loop - where we would presume that it is open. I did not expect that! DECLARE emp_row emp%ROWTYPE; row_count number; BEGIN for emp_row in (select ename from emp) loop dbms_output.put_line('Name : '||emp_row.ename||' (cursor is OPEN='||(case when SQL%isopen then 'TRUE' else 'FALSE' end)||') '); row_count:=SQL%rowcount; end loop; dbms_output.put_line(row_count||' row(s) found'); dbms_output.put_line(' (cursor is OPEN='||(case when SQL%isopen then 'TRUE' else 'FALSE' end)||') '); END;
We can have FOUND and NOTFOUND attributes with implicit cursors. SQL%FOUND has one of these values: If no SELECT or DML statement has run, NULL. If the most recent SELECT or DML statement returned a row, TRUE. If the most recent SELECT or DML statement did not return a row, FALSE. SQL%NOTFOUND has one of these values: If no SELECT or DML statement has run, NULL. If the most recent SELECT or DML statement returned a row, FALSE. If the most recent SELECT or DML statement did not return a row, TRUE.
A procedure have passing an input parameters those parameters will pass the 2nd,3rd,4th and 5th cursors for the same parameters how to pass that parameters into n cursors and it is stored into the table Eg: Proc a(p_id number) as Cursor c is select name,no from emp where id={p_id}; Cursor d is select dname,dno,femail from dept where id={p_id}; cursor e is select cname,cstreet,cdo from city c,dept d where c.id=d.id and c.id={p_id}; begin For I in c; Insert a record into emp table; End loop; For i1 in d; Insert the same {Id} into dept table; End loop; For i2 in e; Same as above End loop; End;
Dear Sir, Thanks for all your valuable videos. Please help us understand following.. 1) What would happen if a cursor finds NULL values in a table/column, Will it exit from there or it would keep on moving till the whole rows of that table gets eventually checked, let say there are millions of rows. 2) Please help us guide installing Oracle and configuring it in our system for our learning purpose. I did all the steps and installed plugged in database, however I am not able to create any tables as I do not have privilege for this. 3) Can we see the status of records being currently merged into a table i.e. current progress of merge going on OR how many records are still there to be updated if millions of records are there. This would decide us to either wait it for or cancel that session and try new one.
Open c_cur; Loop Fetch c_cur into c_cur_extn; For i in c_cur_extn.first..c_cur_extn.last loop DBMS_OUTPUT.PUT_LINE('DETAILS :'||c_cur.fname||','||c_cur.lname); End loop; Exit when c_cur%notfound; End loop; Close c_cur; If my cursor data comes 0 records it throws an exception may i know the reason
@Bharathi, it depends on how we use like, for example '%A%' may not use index, however 'A%' likely to use index. I will try to post an video with examples soon
@Bharathi, No problem at all, always welcome, If i can able to answer, I will be more happy, however due to time constraint I am not able to reply as soon as possible, whenever I see the massages, i always try to reply.... i would suggest, you consolidate all the questions, and send to my mail, so that I can address whenever I am recording the related topics.
@baharathi, Merge statement is basically to do insert or update as part of single statement, whenever the record is present, this will update, if not present it will to insert. will explain with example later.
@Shumaila, Every new version of oracle introduces few new features, and upgrades to existing features, you can always to oracle documentation of version specific page. In this channel, whatsoever the new feature, i have explored, I have posed as separate "new feature video series" for oracle 12C, 18C and 19C. You can find the links here. But this is not the end of the list, I would recommend to refer to documentation, because, what I am posting here is only developer specific features. th-cam.com/play/PLb1qVSx1k1VrrqOl24XR9BtCFMNwcRbhr.html th-cam.com/play/PLb1qVSx1k1Vqe06V1tIBcvnslMPWX69bW.html th-cam.com/play/PLb1qVSx1k1VqM1u2IHWzZIgziqejl6wx-.html Thanks, Siva
@Shumaila, Yes you can drop and recreate an index at any time you want. regarding cluster and non-cluster index, i will post as a seperate video. Please stay tuned.
I am eating all your youtube content about oracle pl/sql as a hungry wolf. Keep this awsome work up, thank you!
just one point we can use %found and %not found with implicit cursors i have referred many resources and there are examples as well.thank you.
Thank you so much sir for creating your valuable PL/SQL videos
Welcome bro
No words to say, great effort
Many many thanks
Your videos are very useful
I have learnt a lot from you
Thank you!
we can use %found and %notfound with implicit cursors
DECLARE
FNAME EMP.FIRST_NAME%TYPE;
ROWCOUNT NUMBER;
BEGIN
SELECT FIRST_NAME INTO FNAME FROM EMP WHERE EMPLOYEE_ID = 108;
ROWCOUNT := SQL%ROWCOUNT;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(ROWCOUNT||' ROW AFFECTED ');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NO ROWS GET AFFECTED');
END IF;
END;
/
Great video series!
16:30 Interestingly enough - SQL%isopen returns FALSE even if we are actually looping through the implicit cursor in a FOR loop - where we would presume that it is open. I did not expect that!
DECLARE
emp_row emp%ROWTYPE;
row_count number;
BEGIN
for emp_row in (select ename from emp) loop
dbms_output.put_line('Name : '||emp_row.ename||' (cursor is OPEN='||(case when SQL%isopen then 'TRUE' else 'FALSE' end)||') ');
row_count:=SQL%rowcount;
end loop;
dbms_output.put_line(row_count||' row(s) found');
dbms_output.put_line(' (cursor is OPEN='||(case when SQL%isopen then 'TRUE' else 'FALSE' end)||') ');
END;
Thank you for all your video's. One correction for this vedio, we can use found and notfound attribute with implicit cursor
Write a program in PL/SQL to show the uses of SQL%FOUND to determine if a DELETE statement affected any rows
Nice explanation and can you please make a video on how to Debugging concept please ?
Great. Content
Welcome bro
Thanks again Siva, the tutorial was very details, please make videos on performance tuning, that would be greatly helpful.
Sujaa
Welcome, Sure, due to time constraint, i am not able to post all requests, however, all these are part of my upcoming list, please stay tuned.
We can have FOUND and NOTFOUND attributes with implicit cursors.
SQL%FOUND has one of these values:
If no SELECT or DML statement has run, NULL.
If the most recent SELECT or DML statement returned a row, TRUE.
If the most recent SELECT or DML statement did not return a row, FALSE.
SQL%NOTFOUND has one of these values:
If no SELECT or DML statement has run, NULL.
If the most recent SELECT or DML statement returned a row, FALSE.
If the most recent SELECT or DML statement did not return a row, TRUE.
yes its correct
Great Videos
It's my pleasure,Thank you
hii sir plz make a session on ''oracle hints' and performance tunning... its very needed .
thanks for this good session..
@Rahul, Sure, will be starting performance related videos soon, Please stay tuned
Good explanation .....👏👍
Thank you 😃
A procedure have passing an input parameters those parameters will pass the 2nd,3rd,4th and 5th cursors for the same parameters how to pass that parameters into n cursors and it is stored into the table
Eg:
Proc a(p_id number)
as
Cursor c is select name,no from emp where id={p_id};
Cursor d is select dname,dno,femail from dept where id={p_id};
cursor e is select cname,cstreet,cdo from city c,dept d where c.id=d.id and c.id={p_id};
begin
For I in c;
Insert a record into emp table;
End loop;
For i1 in d;
Insert the same {Id} into dept table;
End loop;
For i2 in e;
Same as above
End loop;
End;
Normal cursor can be passed as parameter between procedure?
@Shumaila, Normal cursor cannot be passed, however you can use the refcursor, will cover this in next set of videos
Good job sir...
Thanks Bro :-)
Why '%NOTFOUND' is separate cursor attribute in Oracle, if '%FOUND' true/false can manage?
Just two different methods for same functionality, if will check and update you if there is any specific use case.
Happy Teachers Day :)
🙏🙏🙏Thank you
Dear Sir,
Thanks for all your valuable videos.
Please help us understand following..
1) What would happen if a cursor finds NULL values in a table/column, Will it exit from there or it would keep on moving till the whole rows of that table gets eventually checked, let say there are millions of rows.
2) Please help us guide installing Oracle and configuring it in our system for our learning purpose. I did all the steps and installed plugged in database, however I am not able to create any tables as I do not have privilege for this.
3) Can we see the status of records being currently merged into a table i.e. current progress of merge going on OR how many records are still there to be updated if millions of records are there. This would decide us to either wait it for or cancel that session and try new one.
Sir where the cursor memory created
Open c_cur;
Loop
Fetch c_cur into c_cur_extn;
For i in c_cur_extn.first..c_cur_extn.last loop
DBMS_OUTPUT.PUT_LINE('DETAILS :'||c_cur.fname||','||c_cur.lname);
End loop;
Exit when c_cur%notfound;
End loop;
Close c_cur;
If my cursor data comes 0 records it throws an exception may i know the reason
I created a column with index, I am using the same column in the like clause. Will incident be considered?
@Bharathi, it depends on how we use like, for example '%A%' may not use index, however 'A%' likely to use index. I will try to post an video with examples soon
10 on 10✌✌✌
Thank you bro!!!
I have asked many questions, pls forgive me. Please reach to me if you need any help in framing the content or anything related to it
@Bharathi, No problem at all, always welcome, If i can able to answer, I will be more happy, however due to time constraint I am not able to reply as soon as possible, whenever I see the massages, i always try to reply.... i would suggest, you consolidate all the questions, and send to my mail, so that I can address whenever I am recording the related topics.
About merge statment
@baharathi, Merge statement is basically to do insert or update as part of single statement, whenever the record is present, this will update, if not present it will to insert. will explain with example later.
Latest version of oracle?
@Shumaila, As of Apr-19, Oracle 19C is the latest database version released by oracle
@@SivaAcademy as interview point of view how can answer this ques... What's new in 19c than previous version?
One more ques index can be drop and updated?second what difference between cluster db object and cluster & non- cluster index
@Shumaila, Every new version of oracle introduces few new features, and upgrades to existing features, you can always to oracle documentation of version specific page.
In this channel, whatsoever the new feature, i have explored, I have posed as separate "new feature video series" for oracle 12C, 18C and 19C. You can find the links here. But this is not the end of the list, I would recommend to refer to documentation, because, what I am posting here is only developer specific features.
th-cam.com/play/PLb1qVSx1k1VrrqOl24XR9BtCFMNwcRbhr.html
th-cam.com/play/PLb1qVSx1k1Vqe06V1tIBcvnslMPWX69bW.html
th-cam.com/play/PLb1qVSx1k1VqM1u2IHWzZIgziqejl6wx-.html
Thanks,
Siva
@Shumaila, Yes you can drop and recreate an index at any time you want. regarding cluster and non-cluster index, i will post as a seperate video. Please stay tuned.
this video is completely blurred
Oracle scdelor
@bharathi, to schedule the PLSQL jobs inside database, we can use scheduler, THis is very similar to cron jobs in unix, however this is for database.