Oracle Interview Question - Can we use OUT and INOUT parameter in function

Oracle Interview Question - Can we use OUT and INOUT parameter in function?
List of all the SQL and PLSQL interview questions @ sivakacademy.b...

[@anpsir9652]

You are simply amazing. Your communication is also awesome

[@SivaAcademy]

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

[@ashokb1556]

what a explanation Siva Academy just phenomenal....

[@SivaAcademy]

🙏🙏🙏🙏

[@WorldEye88]

Very useful yet dense topic my friend. I like more the part where you show the collection and ref cursor use. That is clever. Thank you.

[@SivaAcademy]

Welcome 🙏🙏

[@vijeyatanag8685]

Super job ..very detailed explanations thanks Siva keep the knowledge flow going on ...I learn something new 😀

[@dhananjaynandgaonkar]

lots of love hats off to you

[@SivaAcademy]

🙏🙏🙏💐Welcome bro

[@pankajkharade6936]

Best teaching method with live examples

[@SivaAcademy]

Thanks bro 🙏

[@thiru601mbt6]

Great job...@ Your inputs are making us more confidents to learn more. Once again thanks

[@SivaAcademy]

My pleasure bro

[@sucharurani6834]

The explanation is really amazing and too much usefull ..Wants to learn in depth of performance tuning ,partition , algorithm of join in explan plan ,hints , optimiser ...Wants to know how it's happening internally ..

[@SivaAcademy]

Sure, please stay tuned for more in depth videos

[@Tipsypie1]

great work sir. Really informative.

[@SivaAcademy]

Thanks Shubhra Pandey :-)

[@Tipsypie1]

@@SivaAcademy Can we have a concise and clear vdo like this one about Performance tuning.

[@SivaAcademy]

@Shubhra Pandey, Sure, I am planning to start a new series on PLSQL and SQL performance tuning concepts. The scope of tuning concepts and methods are large., I will try my level best to make the learning easy and effective as much as possible. Stay tuned for tuning related videos... Thanks , Siva.

[@venuchindam6062]

Knowledge is DEVINE... 💐

[@SivaAcademy]

🙏🙏🙏

[@VermontCA1864]

Great job Siva!👍👍Learned something new today. Thank you.

[@SivaAcademy]

My pleasure bro

[@sivapriyaj7955]

Excellent explanation that helps lot and easy to understand

[@SivaAcademy]

Welcome

[@niranjandhavalikar]

Thanks Siva for e.g. of INOUT parameter in function .

[@SivaAcademy]

Welcome bro 🙏

[@gokulkannan9407]

Thank you for this session; it was very informative.

[@SivaAcademy]

Welcome bro

[@omm139]

Really excellent explanation. Super Sir. Thanks so much

[@SivaAcademy]

Welcome bro

[@krishchait6106]

Lv_namelist(lv_namelist.last() )= i... Can anyone explain this line? I know lv_namelist is a collection variable and lv_namelist.last means last value. But I am not able to gather the logic here... Please explain

[@humayunhimu6045]

wonderful explanation

[@SatyendraSingh-ul5iq]

there is a 1 single record in a table but i want to populate the data 10 times. Please answer

[@cambotourtech9237]

Thank for your sharing.

[@SivaAcademy]

Welcome

[@vinithab4828]

Just awesome

[@manjunathreddy3484]

Thanks for your class about function! What is table function or Pipeline function Most of the interviews they ask about these function

[@SivaAcademy]

sure, please stay tuned, its already part of my upcoming list, will try to post soon.

[@manjunathreddy3484]

Thanks for your reply! I am waiting for your video.. Please explain where we can use this function in Realtime....

[@poojagonla7824]

thankyou for examples .. worth watching

[@SivaAcademy]

Thank you

[@shivpriyatate827]

Good examples.

[@SivaAcademy]

Thank you

[@shubhamsatyam3350]

Sir i have a question related to sql please consider a scenario:- I have a table of employees working in different departments say emplyee E1 works in dept A,B,C,D,E,F E2 works in C,D,E,F E3 works in A,B,C Expected output is :- Return the details of employees only if that employee works in dept C and B For more clearity:- Since E1 works in dept C and B as one of their departments the query should give all the details of E1 including his other department details. Similarly E2 works in C dept but not in B so E2 data should not be returned. E3 works in both depts B and C so all his dept details should be returned.

[@SivaAcademy]

Hi Shubhan, Whats the structure of table data? can you give me the sample data in row wise format?

[@shubhamsatyam3350]

Sample data:- Emp_id Emp_name Dept_no Joining_date 1 , scott,A,10-may-2018 1,scott,B,20-June-2018 1, scott,C,20-August-2019 1,scott,D,19-June-2020 2,tiger,C,12-May-2018 2,tiger,B,10-January-2017 2,tiger,D,19-September-2019 2,tiger,E,13-March-2019 2,tiger,F,10-October-2020 3,Sam,A,19-January-2016 3,Sam,C,09-October-2018 3,Sam,B,17-December-2019 3,Sam,D,01-June-2019 4, John,A,10-may-2018 4, John,E,31-March-2019 4, John,F,16-may-2016 4, John,G,12-March-2018 if the employee works in departments B and C, return all the department details related to that employee. Expected output:- 1, scott,A,10-may-2018 1,scott,B,20-June-2018 1, scott,C,20-August-2019 1,scott,D,19-June-2020 2,tiger,C,12-May-2018 2,tiger,B,10-January-2017 2,tiger,D,19-September-2019 2,tiger,E,13-March-2019 2,tiger,F,10-October-2020 3,Sam,A,19-January-2016 3,Sam,C,09-October-2018 3,Sam,B,17-December-2019 3,Sam,D,01-June-2019

[@sameerahmad7592]

Excellent Sir...

[@SivaAcademy]

Thank you

[@lakshman1216]

hi can we pass a parameters to running program in plsql? is it possible.

[@rohitkumar-bf9xe]

please make a video on to create a function to take a number and display its square using inout ...

[@anjaliasia5853]

Thanks, you are doing good job.

[@SivaAcademy]

Thank you 🙏

[@sankalpsaxena3195]

Thanks Siva ..Nicely explained

[@SivaAcademy]

Welcome bro 🙏

[@SreeramReddyB]

What if I use into keyword in select statement to store value of function return and use variables to get out parameter values ? Declare Outvar number; Retvar number; Begin Select fnsqcu(2,outvar) into retvar from dual; End Will this work ?

[@tcguna]

He covered this question @ 3:05.. Have a look bro.

[@SAK-y6j]

Excellent!!

[@amitajhariya9521]

Hello sir , thanks you for making pl SQL learning videos it's really help. The please work on audio quality of video. Thank you so much.

[@SivaAcademy]

@Amita, thanks for your comment, yeah in recent videos I have corrected little, please let me know if it's quality is still not good

[@amitajhariya9521]

@@SivaAcademy The quality is good now😊

[@saiswaroop3570]

Sir can we multiple records using functions using record data type, and can bulk collect used with records?

[@bharathipandian45]

Siva, need unix and sql plsqk production support interview questions

[@shelkevikram]

You are awesome.

[@SivaAcademy]

Thank you so much :-)

[@anjaliasia5853]

Can you please explain about explain plan in query execution.

[@SivaAcademy]

Sure please stay tuned for performing tuning videos, will explain there

[@bharathipandian45]

Hi siva, what is type? Why we use it for?

[@ManojKumar-il8vt]

Hi sir can u tell me can we call the dml & ddl statement inside the functions if yes then how & why it's not a gud practice to call dml inside function. Also share the diff between function & procedure ??

[@SivaAcademy]

Hi Majon, yes you can call, already posted on the same question, Please look into below mentioned videos kzbin.info/www/bejne/r6q1fmCmZ92ZkMU kzbin.info/www/bejne/eZzOepWpnsRmmcU

[@abhijitmane9949]

Great , Thanks :)

[@SivaAcademy]

Thank you

[@debayannew5083]

why we can't call any function with OUT/INOUT parameter from select statements??

[@venkatasubbareddy7407]

1.can we call a procedure inside a function? 2.can function have DML statements?

[@SivaAcademy]

Yes, you can call procedure from function, also you can have DML statements in function also, however there are few restrictions, please look into this video kzbin.info/www/bejne/r6q1fmCmZ92ZkMU

[@ramkumarj4216]

Can you please provide the details of other sys objects like ODCIVARCHAR2LIST that can be used in for storing data temporarily.

[@venkatasubbareddy7407]

how to add primary key constraint for a column which has already duplicate values?

[@SivaAcademy]

Please look into this video kzbin.info/www/bejne/iXfPnoihbq6InZo

[@ankursrivastava4164]

Hello Siva,Thanks for all your videos.I have one confusion if possible can you please clear that? While explaining about return statement you return collection variable via return statement ,I did not understand the use of extend and last method there ?

[@SivaAcademy]

Extend method is to allocate memory, and last method to get the subscript of last element

[@vilasgaikwad4141]

Can you please make videos on performance tuning interview questions

[@SivaAcademy]

@Vilas, Thanks for your comment, Sure, Please stay tuned for performance related videos, will be posting soon.

[@TinyTuber31]

Sir a video on oracle architecture please would be appreciated......mostly asked question in interviews....u explain so nicely....really helped me

[@shankar-22440]

Sir If we want to prepare the interview questions means your website have a 33 questions that is enough or either we want to study the 70 questions!!!!

[@SivaAcademy]

Hi Shankar, all questions are important, I just updated the link, thanks for letting me know.

[@manishmanveer8867]

find out the 3rd highest salary from table for each years fallowed by each department

[@manoharreddybusupalli4771]

Hi sir, please provide oracle installation link and installation process video

[@TinyTuber31]

can we have multiple out parameters in a function sir??

[@SivaAcademy]

yes, you can have multiple out and INOUT parameters too

[@Ganeshay-09]

siva sir ... can we call function into procedure

[@SivaAcademy]

Yes Ganesh

[@hello2439]

then why we use procedure,when both proc and func gives multiple values?

[@SivaAcademy]

Though function return/OUT more than one more than one values, there are restrictions using such function sql.

[@manochoco23]

I understood only key learning from this video. 😅

[@mahendrababu5516]

Can we pass the result sets as a collection to the java/dot net application from a function? Generally we will do it using sys_refcursor, If yes what is the best way among the two and what is the difference bw two methods?

[@SivaAcademy]

@Mahendra, yes, you can pass the collection to java, will post a separate video on how to pass as parameter.

[@mahendrababu5516]

Siva Academy If yes which is the best way sys_refcursor or collection and why

[@Ganeshay-09]

Plzz increase ur voice in all vdos.. Plz explain loudly..

[@SivaAcademy]

Yes bro, hopefully recent videos are better

[@ManojKumar-il8vt]

Nice

[@SivaAcademy]

Thanks Manoj

[@gautam26trivedi]

hi shiva can I get yours videos on regular expressions

[@SivaAcademy]

Hi Gautam, I am yet to post on regular expression, please stay tuned

[@gautam26trivedi]

@@SivaAcademy please do it fast sir because waiting from long time.

[@SivaAcademy]

Sure bro 👍

[@shalinis6299]

Hi sir, Can you make the video for UTL_FILE Concept in Plsql.

[@SivaAcademy]

Sure, please stay tuned

[@KarthikRampur-ql2td]

Does with clause support 12c only??

[@SivaAcademy]

@Karthik, With clause is available from previous releases of oracle itself, however the "FUNCTION" support as part of with clause is available from 12c only, you can look into this video for more information about 12c with clause feature, kzbin.info/www/bejne/qHvYg4qsbNBgsNE Thanks, Siva

[@gokulkannan9407]

PLS-00382: expression is of wrong type

[@kristipatisurya2604]

WE WANT TO GENERATE SEQUENCE NUMBERS USING STORED FUNCTION AND WE CALL FUNCTION IN INSERT STATEMENT IT WILL AUTOGENERATE THE UNIQUE IDS PLZ DO A VIDEO ON THIS CONCEPT

[@csekathir1]

SQL query tuning step by step sir

[@SivaAcademy]

@kathir, sure. Will post as part of tuning video series