Hi Siva, Thank you again. For advance and expert level if you could cover any or all of the below topics it would be great. DBMS_alert DBMS_job DBMS_output DBMS_scheduler UTL_file UTL_mail DBMS_SQL and Dynamic SQL Working with large objects Debugging PL/SQL Routines PL/SQL Profile Regards, Sujaa
Hi Siva, I watched all of your posted videos of oracle Plsql. All concepts you explained is very clear. It is more helpful. I have a doubt in this mutating trigger, Can we use Pragma autonomous transaction in row level trigger to avoid the mutating error ?
Hi siva, how are you? H Got a doubt. What is answer we should say to interview by modifying to row level trigger to stat levl the mutating issue resolved or by global variable defined in the package
Hi Bharathi, Answers should cover both the points, ie., moving the logic from row level to statement level trigger, and using the global variable to pass data from statement level trigger to row level trigger.
Hi Siva, I faced this question >>> We have a table with millions of records, and it is not partitioned! > can we partition a table with data? > How would you delete one row from this huge terabyte table, what would be the approach? This was difficult to answer, as the size of the database I work on is very small. My question is how to work on scenarios where terabytes and petabytes are used. >>> Explain AWR reports >>> What is algorithm improvements >>> Explain Normalization and when and why denormalization is needed. Thanks and Regards, Sujaa
Hi Sir very i nice explanation than you sir, but i itried my system it showing below error please let me know that reason. ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.TRIG_BEFORE_UP_SAL", line 4 ORA-04088: error during execution of trigger 'SCOTT.TRIG_BEFORE_UP_SAL'
no data found will be throws if there is no rows present in table that you are trying to select from. Please check your table, and sql statement you have written in trigger
for will iterate one by one, hence there is context switching between SQL & PLSQL engine will be there, whereas forall will do all in single context switching between sql & plsql engine....to know about context switching you can look into this video th-cam.com/video/qH47MCoWoNU/w-d-xo.html
Sir, By using PRAGMA AUTONOMOUS_TRANSACTION+TCL Command we can resolve the mutating error. CREATE OR REPLACE TRIGGER TRG_MUTATION BEFORE UPDATE OF SAL ON EMP FOR EACH ROW DECLARE LV_MAX_SAL NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN SELECT MAX(SAL) INTO LV_MAX_SAL FROM EMP; IF :NEW.SAL
Thank you! You can't imagine how much I had to endure the sufferment from this issue. Thank you!!!!!!
Hi Siva, Thank you again.
For advance and expert level if you could cover any or all of the below topics it would be great.
DBMS_alert
DBMS_job
DBMS_output
DBMS_scheduler
UTL_file
UTL_mail
DBMS_SQL and Dynamic SQL
Working with large objects
Debugging PL/SQL Routines
PL/SQL Profile
Regards,
Sujaa
Sure I will add these to my to-do list 👍👍
Hi Siva, I watched all of your posted videos of oracle Plsql. All concepts you explained is very clear. It is more helpful.
I have a doubt in this mutating trigger, Can we use Pragma autonomous transaction in row level trigger to avoid the mutating error ?
Hi siva, how are you? H
Got a doubt. What is answer we should say to interview by modifying to row level trigger to stat levl the mutating issue resolved or by global variable defined in the package
Hi Bharathi,
Answers should cover both the points, ie., moving the logic from row level to statement level trigger, and using the global variable to pass data from statement level trigger to row level trigger.
Thank you siva.
Hi Siva, I faced this question
>>> We have a table with millions of records, and it is not partitioned!
> can we partition a table with data?
> How would you delete one row from this huge terabyte table, what would be the approach?
This was difficult to answer, as the size of the database I work on is very small.
My question is how to work on scenarios where terabytes and petabytes are used.
>>> Explain AWR reports
>>> What is algorithm improvements
>>> Explain Normalization and when and why denormalization is needed.
Thanks and Regards,
Sujaa
Sure please stay tuned, adding all these questions to my upcoming list
So whenever there is an update on triggering table, Statement Level Trigger will always be fired before Row Level Trigger?
Thank you for the good advise. My concern are global variables that remain with the last value executed in the same session.
Get it reset at after statement trigger
Hi Siva,
Can you please explain how the update is automatically rolled back when the conditions are not met? without raising exception explicitly.
Why is that the statement level trigger not result in mutation error?
hi sir may i know when will be the index goes for unusable status
How do we get the sum from table1 and update in table2 using trigger
It's not then same, because if you have more than one CEO, its gonna give you too many rows, where you pass the parameter of department?
A BEFORE INSERT trigger will not cause a mutating table error as long as the triggering INSERT statement is a single row insert (INSERT-VALUES).
Super Explanation
Thank you
Could you have a session on Table partition and connect by clause.
Sure please stay tuned
Hi Shiva.. Thanks for the videos.. They are really helpful. Can you make some videos regarding apps initialize, moac, profile options.
Thanks bro 🙏
How to know or identify when should be use statement level trigger or row level trigger?
Is there any way to avoid mutating error I think one more option is there ?
How to explain realtime project
What happend with the department?
Hi Sir very i nice explanation than you sir, but i itried my system it showing below error please let me know that reason.
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.TRIG_BEFORE_UP_SAL", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRIG_BEFORE_UP_SAL'
no data found will be throws if there is no rows present in table that you are trying to select from. Please check your table, and sql statement you have written in trigger
different between for and forall
for will iterate one by one, hence there is context switching between SQL & PLSQL engine will be there, whereas forall will do all in single context switching between sql & plsql engine....to know about context switching you can look into this video th-cam.com/video/qH47MCoWoNU/w-d-xo.html
Sir, By using PRAGMA AUTONOMOUS_TRANSACTION+TCL Command we can resolve the mutating error.
CREATE OR REPLACE TRIGGER TRG_MUTATION
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
DECLARE
LV_MAX_SAL NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT MAX(SAL) INTO LV_MAX_SAL FROM EMP;
IF :NEW.SAL
Pls replay
Sorry, just now replied... Thx for asking :-) will try to post in details as separate video soon, please stay tuned