Oracle PL SQL interview question | oracle pragma autonomous transaction real time use case

Подписаться 48 тыс.

Просмотров 37 тыс.

50% 1

Oracle PL SQL interview question | oracle pragma autonomous transaction real time use case
Function with DML/DDL Video links given below
Video No 13 : Part 1 : • Oracle interview quest...
Video No 13 : part 2 : • Oracle Interview Quest...
#oracle interview questions and answers
---------------------------------------------------------------------------------------------------------------------------
This channel is for learning Oracle SQL, PLSQL, DATABASE concepts, MYSQL, ETL, Mongo DB, Python,UNIX and related technologies.
/ sivaacademy
---------------------------------------------------------------------------------------------------------------------------
Home page: sivakacademy.b...
List of all oracle SQL/PLSQL Interview questions sivakacademy.b...
List of all PLSQL Scenario based Interview Questions sivakacademy.b...
List of all Subscriber questions and answers sivakacademy.b...
---------------------------------------------------------------------------------------------------------------------------
Oracle Interview questions playlist • oracle interview quest...
Oracle 18C New features playlist • Oracle 18C New Feature
Oracle 12C New features playlist • Oracle 12C New Feature...
Oracle PLSQL records and collections playlist • oracle plsql records a...
---------------------------------------------------------------------------------------------------------------------------
My video courses at UDEMY www.udemy.com/...
---------------------------------------------------------------------------------------------------------------------------
About Myself:
----------------------
I am Siva, [LEARN | CODE | TRAIN | SHARE].
Being in IT industry for more than 10 years.
In my day to day job, I work with database technologies including Oracle, Java, Python, MongoDB, talend and UNIX.
I am passionate about "DATA", coding & training.
In my spare time, I teach database technologies , ETL etc.
I am very glad that you are reading my Profile, and I value your time as well as mine.
Looking forward to see you in my videos
For any questions, please drop me a mail at siva.k.academy@gmail.com
---------------------------------------------------------------------------------------------------------------------------

Опубликовано:

30 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 110

@GautamKumar-ci4rz 4 года назад

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 .

@SivaAcademy 4 года назад

My pleasure, thank you 🙏, All the best 👍🌸🌸

@nareshreddy6450 4 года назад

What a explanation sir. You are king of SQL $ PLSQL it's my opinion sir .....

@SivaAcademy 4 года назад

😄😄 not sure whether I deserve this comment, however thanks for your comments 👍💐

@nk2506 3 года назад

@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

@devanaidu9406 4 года назад

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 😊

@SivaAcademy 4 года назад

🙏 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 👍💐💙

@sansurvase2 2 года назад

Agree

@pvinayak96 Год назад

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;

@YenagantiSrikanth 2 года назад

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.

@garjeshital219 4 месяца назад

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

@anshumankumar4993 2 месяца назад

Trigger is auto commit .. why do you need commit or rollback inside trigger ?? any use case where commit is required inside trigger?

@rambiradar7828 2 года назад

Can we not write the PRAGMA autonomous_transaction block in exception part instead of writing into another procedure ?

@omkarreddy9596 2 месяца назад

i have a doubt but what happen if commit will allowed in trigger and why oracle will restrict commit or rollback.

@venkatapothuri3703 3 года назад

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 ?

@SivaAcademy 3 года назад

Thank you, you can check ORACLE ERROR LOG TABLE concept

@rahitbasu460 Месяц назад

Thank you so much sir..please share some Functional video on oracle Finance module

@akshaykodekal8483 3 года назад

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

@abdulmazid7615 3 месяца назад

Dear sir can we have videos on performance tuning? Like hints etc?

@vinu2791 4 года назад

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..!!

@SivaAcademy 4 года назад

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

@devashishbiswas2867 5 месяцев назад

Thanks for the wonderful explanation!

@shashank2004 4 года назад

As always awsome explanation... I am eagerly awaiting your performance tuning videos

@SivaAcademy 4 года назад

Yeah, I know I have been telling you from long ago 🙂, will be starting soon, please stay tuned 👍💐

@vijaykumar-vx8dp Год назад

If commit is mention in pragma procedure what will happen

@RaxTubeYou 4 месяца назад

Amazing videos , you are really clear, informative and excellent at explaining the details without so much confusion. Brilliant thanks

@SivaAcademy 4 месяца назад

Thank you

@bharatgidh1585 Год назад

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?

@SivaAcademy Год назад

Hi bro, can you please paste your code here

@lispyleon2948 3 года назад

try to put video about REST AND SOAP

@nivethasomasundaram8871 2 года назад

Hi siva. I have a question..What will happen if we do not issue commit/rollback when pragma autonomous transaction is declared

@SivaAcademy 2 года назад

We will get run time error during execution

@harshalburkule1204 2 года назад

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...

@SivaAcademy 2 года назад

Yes we can, using dynamic sql., however not recommended to create using function, instead, we use procedure to create the same,

@anupdeshmukh2051 4 года назад

Very informative, keep it up and provide us more and more knowledgable videos.

@SivaAcademy 4 года назад

Welcome 🙏💐, please stay tuned for more videos

@sivapalanisamy8989 2 года назад

Keep up good work. Please post videos for Oracle Apex. Thanks in advance.

@SivaAcademy 2 года назад

Will upload soon

@arijitroy7253 3 года назад

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.

@madhan.muthusamy 4 года назад

In exception, can I put rollback before insert, will it execute??

@prema716 3 года назад

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

@shilpapangerkar8098 3 года назад

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

@karthiarumugam4666 3 года назад

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?

@swamivivekananda-cyclonicm8781 2 года назад

Awesome. Superb. Undisputedly you deserve most KPM Award - (Knowledge per Minute. Award)

@SivaAcademy 2 года назад

🙏Thank you so much

@VijayKumar-eq2te 2 года назад

hi sir send pdf notes , for reference

@chinnaraomaroji3315 3 года назад

Hi sir, please explain performance tuning videos

@SivaAcademy 3 года назад

Sure please stay tuned

@patrikmelichercik7709 4 года назад

hi Siva,what is the the usage of commit inside the trigger if it is not allowed without pragma autonomous transaction

@moviemaRKer 3 года назад

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??

@SivaAcademy 3 года назад

Yes bro, basically to roll back the complete transaction happened within the procedure and to capture only the error

@sairamtheexplorer 4 года назад

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 :)

@SivaAcademy 4 года назад

Welcome 🙏💐

@pritammishra9850 4 года назад

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

@SivaAcademy 4 года назад

Thank you 🙏💐, you can drop mail to Siva.k.academy@gmail.com

@deepakvadivel4479 2 года назад

like ur way of teaching very good ...

@SivaAcademy 2 года назад

Thank you

@dkoothan360 4 года назад

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...

@SivaAcademy 4 года назад

Sure bro, please stay tuned I will add it to upcoming videos

@mobilelaptop4025 3 года назад

Only 20 questions links in your blogspot

@SivaAcademy 3 года назад

Yeah I will try to update soon, please stay tuned

@rajivshivaraman6476 4 года назад

Thank You So Much Siva Sir 🙏......Very clear explanation.

@SivaAcademy 4 года назад

Welcome 🙏💐

@kaleeswari628 4 года назад

Hi Siva .I need one help. Can you please tell me how to debug plsql package having more than 10000 lines..thanks in advance

@SivaAcademy 4 года назад

Sure please stay tuned, I will explain in upcoming videos

@rajashriwatve3533 4 года назад

Thanks for these helpful video series... Can you also put video for Performance Tuning

@SivaAcademy 4 года назад

Sure please stay tuned 👍 starting soon

@kaviyarajk5663 4 года назад

good bro..nice explanation..please post the table partitioning in oracle 11g examples and type of partitioning...

@SivaAcademy 4 года назад

Sure please stay tuned I will cover in upcoming videos

@rameshramajayam9751 2 года назад

Great Explanation.... Awesome ...Keep Upload more videos ..

@SivaAcademy 2 года назад

Thank you bro

@shekharrohit1 4 года назад

Thank you for explaining all topics in such a simple way, if you could please share videos on Regexp topic as well.

@SivaAcademy 4 года назад

Welcome, I am yet to post on regular expression, please stay tuned 👍

@Pintukalia12 4 года назад

Best videos ever i seen

@SivaAcademy 4 года назад

Thank you 🙏💐

@vishwas22khare 4 года назад

Sir but rollback at the end cancel valid + invalid both transaction ,what if I need to roll back only invalid transaction.

@SivaAcademy 4 года назад

Invalid transaction (third insert in the video) never get inserted at all, it will throw the error and comes to exception handling section

@mohammadfazil971 4 года назад

Super siva sir.. Will wait for your upcoming videos..

@SivaAcademy 4 года назад

Welcome, please stay tuned 👍💐

@mondemlanshmi 2 года назад

Best exploration tqq

@SivaAcademy 2 года назад

Welcome

@sonukumar-nx8tt 3 года назад

Great explanation 👌

@jagadishdogga4886 4 года назад

Can we use pragma autonmous transaction for mutating table error in triggers

@SivaAcademy 4 года назад

Yes, I will post an example on mutating trigger+autonomus transaction, please stay tuned

@shashank2004 4 года назад

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?

@SivaAcademy 4 года назад

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

@shashank2004 4 года назад

@@SivaAcademy thanks bro

@haris4437 4 года назад

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

@SivaAcademy 4 года назад

Yes, thas possible, however in real time we wont use like that, because mostly the transaction would be handled in the calling API

@haris4437 4 года назад

@@SivaAcademy thanks! for the response

@subhashisoracle3944 4 года назад

SKYESS

@iswaryarajendran2022 4 года назад

Very neat explanation 👌

@SivaAcademy 4 года назад

Welcome 🙏💐

@SujitKumar-wg7sz 4 года назад

Sir what will happen if we didn't mention rollback in exception section, does two values inserted successfully

@SivaAcademy 4 года назад

Yes, first two values will be inserted, if you commit, it will be saved, you can try by putting commit instead of rollback

@jagadishdogga4886 4 года назад

Then what will happen to fourth record

@SivaAcademy 4 года назад

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

@SujitKumar-wg7sz 4 года назад

@@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

@SivaAcademy 4 года назад

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.