Oracle interview question : Can we use DML and DDL statements inside function?

Oracle interview question : Can we use DML and DDL statements inside function?
List of all the SQL and PLSQL interview questions @ sivakacademy.b...

[@ManojKumar-il8vt]

Nice explanation sir..

[@SivaAcademy]

Thanks Manoj

[@Ananda_vmp]

The same question I have been asked in capgemini ...

[@subhrapriyadarshini70]

I was never be clear with plsql until i have watched these videos. Thanks for the clear explanation

[@SivaAcademy]

@SubhraPriyadarshini, I'm glad it helped, It's my pleasure. Thanks for your comment :-)

[@sandeeps373]

Thanks Sir,The video is worth of watching,neatly explained and the contents in your channel seems unique. Keep uploading videos ..

[@SivaAcademy]

Thank you 🙏 🙏 🙏 sure, please stay tuned for more videos

[@vishalpathak8835]

Very nice video of plsql, well explained. I used to refer my friends "siva Academy" for plsql. Excellent work shiva🎉🥳🥳👏👏 with this video series i have learnt a lot, and switched 2 companies till date🙏🤩🤩

[@SivaAcademy]

My pleasure bro, thank you

[@pkrt085]

sir,I have just gone through your vedio..it really helpful for the beginners as well as for experienced professionals. thanks for this great work.

[@SivaAcademy]

Welcome 🙏, thanks for your comments bro 💐

[@monicabn3067]

Shiva truly your contents are spot on ❤😊

[@WorldEye88]

This is very helpful. I would suggest never executing DDL ststms in a function but if it must be done, now it is clear how to. Thank you.

[@SivaAcademy]

Welcome 🙏

[@gurumoorthysivakolunthu9878]

Thank you, Sir....
...
If we are calling the function seperately not in select statement -- do we still need to mention pragma_autonomous_transaction....?
Thank you, Sir....

[@akshaybhong2898]

Like all your videos sir...and your actually explaining with real time scenario so it will help very well to understand and explain in interviews..

[@SivaAcademy]

Its my pleasure thank you 💐🙏

[@premgona5587]

Good question... But as per my knowledge I feel like it is a dummy question... No one use Function to do the DML and DDL... Function generally used for computing the results means returning the calculations of some operation...
DDL statements can be applied directly by using the plsql anonymous block if we don't want to run sql statements.
For this question, my answer also is YES.. We can use DML and DDL inside a function by using the select statement as well as pl/sql anonymous block.
I have tried both the ways and it worked.

[@SivaAcademy]

Right, though we might not use DML in functions, many times this question is being asked in interviews.

[@somyashreem5109]

Please put video of complete SQL course...I am just liking your way of teaching..

[@SivaAcademy]

sure bro, please stay tuned

[@Ananda_vmp]

I have been asked the same question by the interviewer. I wish I would have seen your video before heading to the interview .

[@SivaAcademy]

No worries, now you will be able to answer 👍👍

[@naveenmogasala7327]

Siva bhayya,please upload your pic or come live once,we want to see you and love you for your wonderful and valuable videos, keeping going.

[@SivaAcademy]

Sure bro, I will do, thanks for your wonderful comments 🙏💐 and support

[@pradnyabapat9531]

Can we use commit in trigger body?

[@swamivivekananda-cyclonicm8781]

Fanstatic. Concept becomes clear with perfect example. Thank you.

[@SivaAcademy]

Welcome bro

[@pkdancereels]

Excellent explanation in your all videos 🕺🕺👌

[@SivaAcademy]

It's my pleasure,Thank you so much :-)

[@nitinmishra5027]

Very Nice explanation!!

[@himanshunikhade1593]

Wow what an explanation 👌🏼

[@peramsudheerreddy9888]

Tq u so much bro.this question exactly asking in interview.tq so much

[@SivaAcademy]

Welcome bro

[@rajasekarm3009]

how to create table and insert values by using DYNAMIC SQL
in stored procedure

[@ramkumarj4216]

Is there a way to verify the DDL & DML statement execution getting successful inside the PRAGMA autonomous_transaction function.

[@sheetalbahirat7664]

Procedure execution are inside the function is possible or not?

[@SatyendraSingh-ul5iq]

can we use DML inside a function if we do not call function with select statement.

[@hariharan-ze2zq]

How to find exact error line while using functions...is there any options...

[@ranjithavellaisamy15]

Hi,
Well explained! Just can you please confirm below one,
What if we call functions having DML/DDL (using dynamic sql) statements through anonymous pl/sql block instead of using SELECT? I guess Pragma_Autonomous_transaction is not required.
Is that correct ?

[@SivaAcademy]

@Rajitha, Thanks for your comments,
You are correct, if the function having DML/DDL(via dynamic SQL) is called through pl/sql, the function will get executed successfully.
This I have covered in the second part of video, You can look into this video here,
th-cam.com/video/CGLWDytoUeE/w-d-xo.html
Thanks,Siva

[@learntolive5001]

Nice Video!!! Is there any video on "Dynamic SQL"???

[@SivaAcademy]

Thank you, will post soon, please stay tuned

[@azharsha3476]

Really awesome ❤️👍

[@SivaAcademy]

Thank you

[@rekha2756-c9f]

Can you add videos for how create and drop TABLE in side a package please...

[@SivaAcademy]

Sure please stay tuned, this needs dynamic sql, will cover in upcoming videos

[@rekha2756-c9f]

Thank you so much, I know only we can use pragma for using DML and DDL in function, but understood more from this video, nice explanation...

[@SivaAcademy]

Welcome 🙏💐

[@SUJATAMANNA]

Hi...can we use commit inside a stored procedure?
I undeestand in function we can do that.

[@SivaAcademy]

Yes we can use commit in procedure

[@vishalsaxena5081]

declare
n0 number(38):=&n0;
n1 number(38):=&na;
n2 number(38):=&n2;
res number(38'2);
begin
case n0
when 1 then
res:=n1+n2;
dbms_output.put_line('Sum ='||res);
end case;
exception
when no_data_found then
dbms_output.put_line('Number not valid')
end;
/
agr m invalid value enter krra hu to error handle hokr program success ho jaega but m chahta hu k after exception block program again ask me value n number of value means koi bhi value
in short m exception block k bad phr se code run krna chahta hu jo begin section m h

[@ashtyson7831]

If am using ddl in function dynamically using execute immediate and pragma autonomous transaction through select statement..it is showing INSUFFICIENT PRIVILEGES

[@SivaAcademy]

@Ash Tyson, This should work. Here is a sample code, that i have tested, and its working for me......
create table t1(c1 number);
CREATE OR REPLACE FUNCTION fn_test1 RETURN NUMBER
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'truncate table t1';
RETURN 1;
END;
/
select fn_test1
from dual;
Please check, whether you are able to do dynamic sql with ddl command in some other procedure........

[@narendrascollections7445]

Can we write autonomous function in SQL server

[@rajeshnaidu9331]

Hi siva,
You are the amazing person , sharing knowledge not expecting anything .
Your all wishes come true in quick way.
Can you please make video on pragma autonomous transaction .

[@SivaAcademy]

It's my pleasure,Thank you so much :-)

[@akshaybhong2898]

Once in interview interviewer asked me one question. ..
If u have 100000 lines of code and there is runtime error occurred. ..supposed junk value is inserted into a some tables..
How can u resolved it...

[@RAMPRASAD-sv7is]

Can we call a procedure inside a function and vice versa . Please explain

[@SivaAcademy]

yes, we can

[@ankithag9152]

Hi sir, can you explain the difference between PL/SQL and Java with SQL?

[@SivaAcademy]

Please elaborate your question... Is it Java vs oracle, or sql in Java vs plsql

[@SANDEEPSUBH]

You take class of Postgresql DBA ?

[@SivaAcademy]

No

[@harishharish-ug7vz]

Sir please make a video on partitions and differences between cluster and indexes

[@SivaAcademy]

@Harish, sure, Please stay tuned.

[@mustaqali8849]

Can you explain bulk collect

[@SivaAcademy]

Please have a look into this video
th-cam.com/video/qH47MCoWoNU/w-d-xo.html

[@simhadrisuresh8630]

I have one doubt sir can I modified the views sir

[@SivaAcademy]

Yes, you can, recreate view with new query

[@pankajkharade6936]

nice 👌🏼👌🏼

[@SivaAcademy]

Thanks

[@bharathipandian45]

Hi siva, why procedure cannot be called from sql statement...

[@SivaAcademy]

@Barathi, procedures are meant for logical implementation, and its not necessary to sent out the value, however function must return value...., and functions meant for computational purpose., may be thats one reason why proc are not supported from SQL statement.....However in with clause, the procedures are supported from recent versions....throug we cannot call directly, we can call procedure via functions.

[@chsurekha4126]

If we dont use the function in select statement, then will it allow us to do dmls and ddls without autonomous transaction aa sir.?

[@SivaAcademy]

Yes, this restriction is applicable only if used as part of select statement

[@shaheenshaikh6701]

Sir im getting insufficient privileges error on executing execute immediate in function last one plz tell why its so

[@SivaAcademy]

@Shaheen, this might be due to permission/privilege issue, can you try executing "execute immediate" outside of function, ie., in a simple anonymous block

[@vikitech9029]

Sorry for the delayed reply but you can grant the privileges to your current user to overcome the issue. "connect/ as sysdba;
grant all privileges to hr;"

[@wishyfisy]

Can I send questions now days

[@SivaAcademy]

Yes, you can send questions to [e-mail]

[@wishyfisy]

@@SivaAcademy have sent you a mail with questions and also some request, thanks for replying

[@SivaAcademy]

Welcome

[@govindugovindu617]

FUNCTION COMPILED SUCCESSFULLY BUT IN EXECUTING IN SELECT STATEMENT IT IS ASKING INSUFFICIENT PRIVILEGES
CAN YOU GIVE ME THE SOLUTION FOR THIS

[@SivaAcademy]

Can you please try as an anonymous block rather than trying in function, check whether you are able to execute ddl from anonymous block using dynamic sql

[@sateeshbabu5792]

Thanks sir

[@SivaAcademy]

Welcome 🙏

[@manjutharak]

Q) Can we use Procedure inside the function ? -- I gave the answer YES because in 19c we can use
Q) Can we use a Function inside the Procedure? -- I gave the answer YES
These are interview questions asked in google company please help us to get an answer

[@SivaAcademy]

Answer is Yes, but with few limitations, I will cover in detail with examples and limitations soon, please stay tuned

[@narendrascollections7445]

Can we write autonomous function in SQL server

[@SivaAcademy]

I am not sure about SQL server