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

[@ipseetasahu9328]

Nice explanation with Nice example 👌

[@KishanMashru]

Thanks, I hope you have given a "like" to the video!!! Please let me know if you are looking for any specific topic, would be happy to help :)

[@ipseetasahu9328]

It will help me, if you will make a video about Ref Cursor and Dynamic sql.

[@KishanMashru]

Sure, We have a video on Dynamic Sql, here's the link kzbin.info/www/bejne/e6mtdnyJebibndk please do like the video if you enjoy watching it, and we will shortly come up with Ref Cursors in Oracle PL/SQL. Please subscribe so that you will get a notification when we upload the video :)

[@ipseetasahu9328]

Kishan Mashru, Thank you :)

[@KishanMashru]

here is the video on ref cursor kzbin.info/www/bejne/oGKcn5mBid2AipY hope you enjoy it!!!

[@vayunandu]

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

[@KishanMashru]

Good work!!! :)

[@Bendiksen4]

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

[@nileshpatil4068]

Thanks!

[@chandraneeldwaraki672]

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.

[@KishanMashru]

Ya if you are new to Nested Tables and Bulk Collect this video will be a bit complicated to understand, I will make a Video series on Oracle Collections and before that will make few videos on performance tuning !!!

[@chandraneeldwaraki672]

Thanks Kishan.. Will be looking forward to your videos..

[@akashjain135]

Watch Steven Feurenstein's video series on PLSQL Collections.

[@nileshpatil4068]

Thanks Kishan, video is very informative.

[@rameshch6903]

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

[@TheDMTLover]

Well done. Very nice.

[@sateeshbabu5792]

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

[@shyamkollimarla1384]

Really Good help and for fresher really good help & Inputs

[@KishanMashru]

Thanks Shyam, I am glad you enjoyed it :) Hope you have liked the video and subscribed to our channel!!!

[@chandraneeldwaraki672]

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.

[@akashjain135]

The collection in which you are fetching the 3 attributes is EMP_DETAILS. Now, EMP_DETAILS is of table type EMP_TBL_TYPE. Each row of EMP_TBL_TYPE consists of the object type EMP_OBJ_TYPE. So while populating the table type , you need to write something like below:- EMP_DETAILS(1) := EMP_OBJ_TYPE(attribute1,attribute2,attribute3); Similarly while bulk collecting into the EMP_DETAILS you need to use EMP_OBJ_TYPE(attribute1,attribute2,attribute3).

[@SujitKumar-wg7sz]

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

[@KishanMashru]

Ya, will be posting them on my blog soon :) thanks for the comment, hope you have subscribed to our channel and given the video a "thumbs up" !!!

[@SujitKumar-wg7sz]

Thanks..

[@patinoricardo]

muy bueno, excelente Kishan, gracias por compartir

[@ravishettiyar3262]

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

[@Mahvendren]

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

[@rishinigam8773]

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

[@KishanMashru]

A better option would be to use sys_refcursors. kzbin.info/www/bejne/oGKcn5mBid2AipY

[@milindbidve446]

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

[@TheSoulamimukherjee]

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?

[@KishanMashru]

good question, you are kind of right. See its all abt the requirement if you have a single records but multiple values to be returned from a function like id, name for a SINGLE employee you can simply do it with multiple out parameters, but when you need to return MULTIPLE rows containing id and name for a bunch of employee you would need the object based shown in d video. Hope this helps.

[@TheSoulamimukherjee]

Kishan Mashru yes that was helpful. So in order to return multiple values I can use multiple out parameters. So If i need to return empid ename n salary. Write it in the same way like a procedure

[@KishanMashru]

yup, you will get the values in your out params, make sure you handle/sync them properly with the return statement of the function.

[@TheSoulamimukherjee]

Kishan Mashru okay krishna. So it will be e.g emp id , ename n address Then return varchar2 is V_empid varchar2(100), V_ename varchar2(20), V_addr varchar2 (30) Like this?

[@KishanMashru]

CASE STUDY -- DROP THE TABLE, IF EXISTS DROP TABLE TEST_EMP; -- CREAT ETABLE CREATE TABLE TEST_EMP ( EMP_ID NUMBER, EMP_NAME VARCHAR2(30), DEPT NUMBER ); -- INSERT DATA INSERT INTO TEST_EMP VALUES (1, 'KISHAN',10); INSERT INTO TEST_EMP VALUES (2, 'MASHRU',20); -- COMMIT DATA COMMIT; -- FUNCTION CREATE OR REPLACE FUNCTION GET_NAME_DEPT (F_ID NUMBER, F_NAME OUT VARCHAR2, F_DEPT OUT VARCHAR2) RETURN NUMBER IS BEGIN SELECT EMP_NAME, DEPT INTO F_NAME, F_DEPT FROM TEST_EMP WHERE EMP_ID = F_ID; RETURN F_ID; END; / --EXEC TEST DECLARE FUNC_OUTPUT NUMBER; FUNC_NAME VARCHAR2(50); FUNC_DEPT VARCHAR2(50); BEGIN FUNC_OUTPUT := GET_NAME_DEPT(1,FUNC_NAME,FUNC_DEPT); DBMS_OUTPUT.PUT_LINE('FUNCTION OUTPUT : '||FUNC_OUTPUT||' NAME : '||FUNC_NAME||' DEPT : '||FUNC_DEPT); END; / -- OUTPUT -- FUNCTION OUTPUT : 1 NAME : KISHAN DEPT : 10

[@bowser9775]

Thank you very much. Really good explanations

[@abderrahimhaddadi4023]

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

[@KishanMashru]

Create a connection to the database and invoke the function!!!

[@abderrahimhaddadi4023]

@@KishanMashru Yes, about invoking the function, what should be the return type ? there's a trick (types.ARRAY, ''nestedtableType') , but I still dont know how to retrieve the data. I would be very grateful if you help me with this Kishan !!

[@parthparth8680]

very nice video but where can we use objects

[@poornimas620]

Does it work for insert and update also

[@tejujagadale7458]

its too nice explanation.....

[@KishanMashru]

Thanks Teju!!! Hope you have liked the video and subscribed to our channel!!!

[@rajvizag6757]

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

[@KishanMashru]

Thanks Raj :) Hope you have subscribed to the channel and liked the video!!!

[@rajvizag6757]

Yess!!! Keep Going ! Good luck.

[@parthparth8680]

WHICH WAY TO DELETE RECORD STORED IN NESTED TABLE

[@mahendrababu5516]

Why can’t we use a sys_ref cursor?

[@shrikantpatil2094]

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

[@ramramaraju2221]

Thanks Kishan :)

[@KishanMashru]

Welcome!!!

[@NewZenContent]

Thank you Kishan :D

[@aruljebin]

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

[@visakviz4690]

well explained..

[@akankshawakhure9402]

It's giving me empty table

[@paladugulasudha2016]

Nice explanation but maintain screen quality while explain the program

[@KishanMashru]

sure!!! Thank you :)

[@ramyalakshmi5594]

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

[@aravindmadurai9743]

confusing brother :(

[@KishanMashru]

which part? try this one out kzbin.info/www/bejne/sIfWapmBiMhgba8