Hi Siva, I don't have words to thank you. I got new job in this covid situation by listening to your videos. Just want to say that you are doing a fantastic job . God bless you always .
Hi Sir, you are the best faculty for SQL and PLSQ sir, I have never seen this kind of explanation,thanks a lot for sharing your knowledge us..with free of cost..you are kind person..thank u sir.. please Keep continue sir 😊
🙏 its my pleasure, not sure whether I deserve this comment, however thanks for your comment. Always I try my level best to post best free educational videos here, please stay tuned 👍💐💙
Thanks to Siva Academy folks making these videos, they are crystal clear.. If we need to million rows to get inserted .. and a couple of thousand rows fails.. how do we capture the INSERT STATEMENTS which gets failed besides the SQL errors and error msgs you have explained ?
@siva academy: You are doing amazing work and helping us to understand concepts clearly. Thanks a lot I have a question on using pragma autonomous transaction in triggers. Please explain in what scenarios we need to use commit in triggers
Hi Siva, Thanks for the transparent covering of the topics. I would request you to Share the reference link like in this video you indicated to go through 13 Part 1 & Part 2. If you tell the same link has been given in the description, it will be easy for the users to navigate the supporting videos..!!
Hi Vinodh, Added the links in the description, normally for every video i used to give, i forgot to add for this video, please check the description now, Thanks, Siva
Thanks for the very useful interview questions, these were the questions I faced in my initial stage,but at that time, I missed your videos. Anyway very informative and useful.Thank You :)
No words to say about your videos. Very very useful and clearly explained what is expected by everyone and interviewer...I have not seen these kind of videos ever before... Any plan to upload Unix videos?
Thnx a lot buddy for such a lucid explanation. Could u pls explain further what oracle internally does when we use pragma in triggers having commit/pragma in a function containing DML Being used in a select query. Thanks.
Great sir.. Aswome explanation...I'm requesting you to kindly post video for how to debug the procedure which has more than 3k line nd calling different procedures... Thanks in advance...
how to delete entry in trigger , which is created with help of pragma autonomous transcation for ex- trying to delete a record frm emps table , but got error saying - trigger error whiich has pragma autonomous transcation along with commit
hi siva, i have an doubt while roll back at last moment after came from pragma our actual correct value also rollbacked. In our insert statement values 1 and 100 are correct right.then if we do rollback all values are deleted.how we avoid this
Hi Siva, Thank you for explaining the concepts very easily. Just one question regarding the example where we have created a procedure to insert into error_log, why we have not used DECLARE section ? I am getting compile error while trying to add declare section. can you please guide on this?
Can you please post a video on real time usage of each index. If i have to create index on Gender, Marital status, Age then which index should be used. Also what is difference between Primary key and Unique not null key
We can write this also na, why do we need pragma specifically.. CREATE TABLE T (SAL NUMBER); CREATE TABLE PF_LOG_ERR (ERROR_NO NUMBER, ERROR_MSG VARCHAR2(100)); DECLARE lv_error_no NUMBER; lv_error_msg VARCHAR2(100); BEGIN INSERT INTO T VALUES('10000'); INSERT INTO T VALUES('20100'); INSERT INTO T VALUES('20000$'); INSERT INTO T VALUES('35980'); COMMIT; EXCEPTION WHEN OTHERS THEN lv_error_no :=sqlcode; lv_error_msg := SQLERRM(lv_error_no); ROLLBACK; --PR_ERROR_LOG(lv_error_no,lv_error_msg); INSERT INTO PF_LOG_ERR VALUES (lv_error_no, lv_error_msg); COMMIT; END; / Please help on this.
Hello Sir, as you showed the commit can be written in a trigger by using pragma autonomous transaction, can we call procedure using pragma autonomous transaction , as i was asked if procedures can be called in trigger? thanks
Hi Siva, Huge applause to all your content in this Channel. I have a doubt here at (5:33) you have created a procedure to insert error logs, But why should that procedure be "autonomous ". Even the normal procedure with Commit gives the same results right??
Can't we use rollback first and then insert into error_log instead of pragma autonomous_transaction? For example, EXECEPTION WHEN OTHERS THEN rollback; insert into error_log values (); END;
If any exception happen, control will go to exception block, it won't execute subsequent records, you can look into exception handling videos posted earlier
@@SivaAcademy sir actually I want to know if I didn't mention either commit or rollback in exception section and exception occur , then what will happen
Then also first two records will be inserted, but till you commit the session before disconnecting it won't be saved permanently. Only on commit execution in the session, the changes made will be saved, will explain specifically about this scenario soon, please stay tuned.
I want to insert 10 records in a table. Received error in 7th row, expecting 7th row to be skipped and handled in exception block and stored in error log table . However remaining records to be loaded into table Please let me know how it can be achieved?
Hi Shashank, Search for Oracle DML error log tables, this is what exactly you are looking for, was preparing content some time back, but yet to post this, will try to post by next week with examples
Hi Siva, I don't have words to thank you. I got new job in this covid situation by listening to your videos. Just want to say that you are doing a fantastic job . God bless you always .
My pleasure, thank you 🙏, All the best 👍🌸🌸
What a explanation sir. You are king of SQL $ PLSQL it's my opinion sir .....
😄😄 not sure whether I deserve this comment, however thanks for your comments 👍💐
Hi Sir, you are the best faculty for SQL and PLSQ sir, I have never seen this kind of explanation,thanks a lot for sharing your knowledge us..with free of cost..you are kind person..thank u sir.. please Keep continue sir 😊
🙏 its my pleasure, not sure whether I deserve this comment, however thanks for your comment. Always I try my level best to post best free educational videos here, please stay tuned 👍💐💙
Agree
Thanks to Siva Academy folks making these videos, they are crystal clear..
If we need to million rows to get inserted .. and a couple of thousand rows fails.. how do we capture the INSERT STATEMENTS which gets failed besides the SQL errors and error msgs you have explained ?
Thank you, you can check ORACLE ERROR LOG TABLE concept
Amazing videos , you are really clear, informative and excellent at explaining the details without so much confusion. Brilliant thanks
Thank you
Awesome. Superb. Undisputedly you deserve most KPM Award - (Knowledge per Minute. Award)
🙏Thank you so much
@siva academy: You are doing amazing work and helping us to understand concepts clearly. Thanks a lot
I have a question on using pragma autonomous transaction in triggers. Please explain in what scenarios we need to use commit in triggers
Very informative, keep it up and provide us more and more knowledgable videos.
Welcome 🙏💐, please stay tuned for more videos
Hi Siva,
Thanks for the transparent covering of the topics. I would request you to Share the reference link like in this video you indicated to go through 13 Part 1 & Part 2. If you tell the same link has been given in the description, it will be easy for the users to navigate the supporting videos..!!
Hi Vinodh,
Added the links in the description, normally for every video i used to give, i forgot to add for this video, please check the description now,
Thanks,
Siva
Great Explanation.... Awesome ...Keep Upload more videos ..
Thank you bro
As always awsome explanation... I am eagerly awaiting your performance tuning videos
Yeah, I know I have been telling you from long ago 🙂, will be starting soon, please stay tuned 👍💐
Best videos ever i seen
Thank you 🙏💐
Thanks for the very useful interview questions, these were the questions I faced in my initial stage,but at that time, I missed your videos.
Anyway very informative and useful.Thank You :)
Welcome 🙏💐
Thanks for the wonderful explanation!
like ur way of teaching very good ...
Thank you
No words to say about your videos. Very very useful and clearly explained what is expected by everyone and interviewer...I have not seen these kind of videos ever before...
Any plan to upload Unix videos?
Thnx a lot buddy for such a lucid explanation. Could u pls explain further what oracle internally does when we use pragma in triggers having commit/pragma in a function containing DML Being used in a select query.
Thanks.
Super siva sir.. Will wait for your upcoming videos..
Welcome, please stay tuned 👍💐
Great explanation 👌
Thank you so much sir..please share some Functional video on oracle Finance module
Thank You So Much Siva Sir 🙏......Very clear explanation.
Welcome 🙏💐
Keep up good work. Please post videos for Oracle Apex. Thanks in advance.
Will upload soon
Great sir.. Aswome explanation...I'm requesting you to kindly post video for how to debug the procedure which has more than 3k line nd calling different procedures... Thanks in advance...
Sure bro, please stay tuned I will add it to upcoming videos
how to delete entry in trigger , which is created with help of pragma autonomous transcation
for ex-
trying to delete a record frm emps table , but got error saying - trigger error whiich has pragma autonomous transcation along with commit
Thank you for explaining all topics in such a simple way, if you could please share videos on Regexp topic as well.
Welcome, I am yet to post on regular expression, please stay tuned 👍
Very neat explanation 👌
Welcome 🙏💐
i have a doubt but what happen if commit will allowed in trigger and why oracle will restrict commit or rollback.
Best exploration tqq
Welcome
hi siva,
i have an doubt while roll back at last moment after came from pragma our actual correct value also rollbacked. In our insert statement values 1 and 100 are correct right.then if we do rollback all values are deleted.how we avoid this
hi Siva,what is the the usage of commit inside the trigger if it is not allowed without pragma autonomous transaction
Hi Siva, Thank you for explaining the concepts very easily. Just one question regarding the example where we have created a procedure to insert into error_log, why we have not used DECLARE section ? I am getting compile error while trying to add declare section.
can you please guide on this?
Hi bro, can you please paste your code here
Simply best...
Thanks a lot 😊
Dear sir can we have videos on performance tuning? Like hints etc?
Can you please post a video on real time usage of each index.
If i have to create index on Gender, Marital status, Age then which index should be used.
Also what is difference between Primary key and Unique not null key
We can write this also na, why do we need pragma specifically..
CREATE TABLE T (SAL NUMBER);
CREATE TABLE PF_LOG_ERR (ERROR_NO NUMBER, ERROR_MSG VARCHAR2(100));
DECLARE
lv_error_no NUMBER;
lv_error_msg VARCHAR2(100);
BEGIN
INSERT INTO T VALUES('10000');
INSERT INTO T VALUES('20100');
INSERT INTO T VALUES('20000$');
INSERT INTO T VALUES('35980');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
lv_error_no :=sqlcode;
lv_error_msg := SQLERRM(lv_error_no);
ROLLBACK;
--PR_ERROR_LOG(lv_error_no,lv_error_msg);
INSERT INTO PF_LOG_ERR VALUES (lv_error_no, lv_error_msg);
COMMIT;
END;
/
Please help on this.
Hello Sir, as you showed the commit can be written in a trigger by using pragma autonomous transaction, can we call procedure using pragma autonomous transaction , as i was asked if procedures can be called in trigger? thanks
good bro..nice explanation..please post the table partitioning in oracle 11g examples and type of partitioning...
Sure please stay tuned I will cover in upcoming videos
Thanks for these helpful video series... Can you also put video for Performance Tuning
Sure please stay tuned 👍 starting soon
For the first use case of autonomous transaction, can we do this in the exception block, pls confirm ..rollback then insert into log table then commit
Yes, thas possible, however in real time we wont use like that, because mostly the transaction would be handled in the calling API
@@SivaAcademy thanks! for the response
Very useful...
Welcome 🙏💐
Hi Siva, Huge applause to all your content in this Channel. I have a doubt here at (5:33) you have created a procedure to insert error logs, But why should that procedure be "autonomous ". Even the normal procedure with Commit gives the same results right??
Yes bro, basically to roll back the complete transaction happened within the procedure and to capture only the error
Very useful
Thanks a lot
Hi siva. I have a question..What will happen if we do not issue commit/rollback when pragma autonomous transaction is declared
We will get run time error during execution
great......
🙏👍
Great Bro
Thank you
Hi Sir, I like all your video going one by one.
My question is that can we create table through function if yes then how to create..
Please help me...
Yes we can, using dynamic sql., however not recommended to create using function, instead, we use procedure to create the same,
In exception, can I put rollback before insert, will it execute??
Trigger is auto commit .. why do you need commit or rollback inside trigger ?? any use case where commit is required inside trigger?
try to put video about REST AND SOAP
Can we not write the PRAGMA autonomous_transaction block in exception part instead of writing into another procedure ?
Hi sir, please explain performance tuning videos
Sure please stay tuned
If commit is mention in pragma procedure what will happen
Can we use pragma autonmous transaction for mutating table error in triggers
Yes, I will post an example on mutating trigger+autonomus transaction, please stay tuned
The best faculty of SQL plsql...sir I need some basic programming practice of plsql...
And have some query about my project....how can I contact u
Thank you 🙏💐, you can drop mail to Siva.k.academy@gmail.com
Sir but rollback at the end cancel valid + invalid both transaction ,what if I need to roll back only invalid transaction.
Invalid transaction (third insert in the video) never get inserted at all, it will throw the error and comes to exception handling section
Can't we use rollback first and then insert into error_log instead of pragma autonomous_transaction?
For example,
EXECEPTION
WHEN OTHERS THEN
rollback;
insert into error_log values ();
END;
Hi Siva .I need one help.
Can you please tell me how to debug plsql package having more than 10000 lines..thanks in advance
Sure please stay tuned, I will explain in upcoming videos
Sir what will happen if we didn't mention rollback in exception section, does two values inserted successfully
Yes, first two values will be inserted, if you commit, it will be saved, you can try by putting commit instead of rollback
Then what will happen to fourth record
If any exception happen, control will go to exception block, it won't execute subsequent records, you can look into exception handling videos posted earlier
@@SivaAcademy sir actually I want to know if I didn't mention either commit or rollback in exception section and exception occur , then what will happen
Then also first two records will be inserted, but till you commit the session before disconnecting it won't be saved permanently. Only on commit execution in the session, the changes made will be saved, will explain specifically about this scenario soon, please stay tuned.
I want to insert 10 records in a table. Received error in 7th row, expecting 7th row to be skipped and handled in exception block and stored in error log table . However remaining records to be loaded into table Please let me know how it can be achieved?
Hi Shashank, Search for Oracle DML error log tables, this is what exactly you are looking for, was preparing content some time back, but yet to post this, will try to post by next week with examples
@@SivaAcademy thanks bro
Only 20 questions links in your blogspot
Yeah I will try to update soon, please stay tuned
hi sir send pdf notes , for reference
SKYESS