How to return multiple values from a function in oracle pl/sql ? (without using out parameter) 

Nice explanation with Nice example 👌

Thank you! This saved me for a final submission. Could not find a source that better clarified the topic than this

Thank you Kishan. I just followed what you mentioned in the videos step by step. It's very clear. Just pasting what I created with bulk collect. CREATE or REPLACE TYPE emp_obj_t AS OBJECT (empno NUMBER,ename VARCHAR2(200),deptno NUMBER); CREATE OR REPLACE TYPE emp_tab_t AS TABLE OF emp_obj_t; CREATE OR REPLACE FUNCTION emp_tab RETURN emp_tab_t IS TYPE emp_rec IS RECORD (empno NUMBER,ename VARCHAR2(200),deptno NUMBER); TYPE emp_tab IS TABLE OF emp_rec; emp_blk emp_tab; emp_recs emp_tab_t; BEGIN emp_recs:=emp_tab_t(); SELECT empno,ename,deptno BULK COLLECT INTO emp_blk FROM emp; FOR i IN 1..emp_blk.count LOOP emp_recs.extend(); emp_recs(i):=emp_obj_t(emp_blk(i).empno,emp_blk(i).ename,emp_blk(i).deptno); END LOOP; RETURN emp_recs; EXCEPTION WHEN no_data_found THEN raise_application_error(-20001,'Source table is empty'); END; / SELECT * FROM TABLE(emp_tab);

Well done. Very nice.

Thanks Kishan, video is very informative.

muy bueno, excelente Kishan, gracias por compartir

Thank you so much this video is help me lot and very god example

Supper brother , explanation supper love it please make more videos we can learn easily 😊

Hi Kishan. Thanks for making video and sharing it for everyone to learn. I understood returning multiple values from a procedure but I did not understand this particular video because of concepts like OBJECT TYPE, NESTED TABLE TYPE, .EXTEND() and BULK COLLECT. It is my request to make a separate video first on concepts which you have mentioned in this video like OBJECT TYPE, NESTED TABLE etc. Also are you planning to make any ORACLE PERFORMANCE TUNING videos where you explain about different concepts like EXPLAIN PLAN, ORACLE HINTS etc. It would be very helpful if you make a series about ORACLE PERFORMANCE TUNING. Again many thanks for your efforts.

Really Good help and for fresher really good help & Inputs

Nice explanation, i am expecting more videos on plsql collections Thanks kishan

Thanks!

Kindly share always queries whatever you use so that we could replicate same for better understanding...Great Explanations

Thank you very much. Really good explanations

its too nice explanation.....

Hi Kishan, Great explanation. Thanks. I had 1 query regarding BULK COLLECT method you have used I tried the statement: SELECT FIRST_NAME,LAST_NAME,DEPARTMENT_NAME BULK COLLECT INTO....... instead of using EMP_OBJ_TYPE(FIRST_NAME,LAST_NAME,DEPARTMENT_NAME) BULK COLLECT INTO... The first statement throws compilation error while creating function. Can you please explain why passing it as OBJ_TYPE is mandatory and why oracle throws error for normal SELECT INTO. Thanks.

Is this the same when you do with create or replace type body with member function?

Nice video. Can you please create a video on pipelined functions having pipe row? Thanks.

Explantion is very good. Please take care of Video Quality:)

Thanks Kishan :)

I need to do the same thing but in a procedure, is there anyway to pass my function into a procedure to display.

it's pretty good. pls also explain through blog.

So in order to return multiple values from a function. Only to make an object is the only option? Or having multiple out parameters will also do. I mean both the options would work?

Thank you Kishan :D

well explained..

WHICH WAY TO DELETE RECORD STORED IN NESTED TABLE

very nice video but where can we use objects

Does it work for insert and update also

Good content.. Do you have an idea how can i work with the same function in java ??

Why can’t we use a sys_ref cursor?

It's giving me empty table

No need for nested table. As you are returning only one record you can declare as record is enough.

Nice explanation but maintain screen quality while explain the program

Write a function to return the name of the student whose mark is maximum

confusing brother :(