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

Nice explanation with Nice example 👌

It's giving me empty table

Thanks!

Thanks Kishan, video is very informative.

Well done. Very nice.

WHICH WAY TO DELETE RECORD STORED IN NESTED TABLE

very nice video but where can we use objects

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

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

Does it work for insert and update also

muy bueno, excelente Kishan, gracias por compartir

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

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 😊

Thank you Kishan :D

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

Hi Kishan… I used in the same manner but its not returning any records..Here my code is create or replace TYPE EMP_OBJ_TYPE
AS OBJECT

(

ENAME VARCHAR2(10),

JOBD VARCHAR2(9),

SAL NUMBER);



create or replace TYPE EMP_TAB_TYPE

IS TABLE OF EMP_OBJ_TYPE;



create or replace FUNCTION EMP_RET_VAL( P_EMP_ID NUMBER)

RETURN EMP_TAB_TYPE

IS

P_NAME VARCHAR2(10);

P_JOB VARCHAR2(9);

P_SAL NUMBER ;

EMP_DETAILS EMP_TAB_TYPE := EMP_TAB_TYPE();

BEGIN

EMP_DETAILS.EXTEND();

SELECT ENAME,JOB,SAL

INTO P_NAME,P_JOB,P_SAL

FROM EMP

WHERE EMPNO = P_EMP_ID;

EMP_DETAILS(1) := EMP_OBJ_TYPE(P_NAME,P_JOB,P_SAL);

RETURN EMP_DETAILS;

END;



SELECT * FROM TABLE (EMP_RET_VAL(7389)); could you please check this one and suggest me to where I did mistake

Thanks Kishan :)

Thank you very much. Really good explanations

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

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

well explained..

its too nice explanation.....

Why can’t we use a sys_ref cursor?

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

Really Good help and for fresher really good help & Inputs

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?

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

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

Nice explanation, i am expecting more videos on plsql collections

Thanks kishan

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

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.

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.

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

Nice explanation but maintain screen quality while explain the program

confusing brother :(