Sure, We have a video on Dynamic Sql, here's the link th-cam.com/video/ExJEISDShgw/w-d-xo.html 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 :)
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);
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.
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 !!!
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?
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.
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
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?
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
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.
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).
@@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 !!
Nice explanation with Nice example 👌
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 :)
It will help me, if you will make a video about Ref Cursor and Dynamic sql.
Sure, We have a video on Dynamic Sql, here's the link th-cam.com/video/ExJEISDShgw/w-d-xo.html
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 :)
Kishan Mashru, Thank you :)
here is the video on ref cursor th-cam.com/video/j19nfKTxMT4/w-d-xo.html
hope you enjoy it!!!
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);
Good work!!! :)
Thanks Kishan, video is very informative.
Well done. Very nice.
muy bueno, excelente Kishan, gracias por compartir
Supper brother , explanation supper love it please make more videos we can learn easily 😊
Thank you so much this video is help me lot and very god example
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.
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 !!!
Thanks Kishan.. Will be looking forward to your videos..
Watch Steven Feurenstein's video series on PLSQL Collections.
Kindly share always queries whatever you use so that we could replicate same for better understanding...Great Explanations
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" !!!
Thanks..
Really Good help and for fresher really good help & Inputs
Thanks Shyam, I am glad you enjoyed it :) Hope you have liked the video and subscribed to our channel!!!
Is this the same when you do with create or replace type body with member function?
Nice explanation, i am expecting more videos on plsql collections
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.
A better option would be to use sys_refcursors.
th-cam.com/video/j19nfKTxMT4/w-d-xo.html
Does it work for insert and update also
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?
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.
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
yup, you will get the values in your out params, make sure you handle/sync them properly with the return statement of the function.
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?
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
Thank you very much. Really good explanations
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.
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).
WHICH WAY TO DELETE RECORD STORED IN NESTED TABLE
very nice video but where can we use objects
Why can’t we use a sys_ref cursor?
Explantion is very good. Please take care of Video Quality:)
Thanks Raj :) Hope you have subscribed to the channel and liked the video!!!
Yess!!! Keep Going ! Good luck.
Nice video. Can you please create a video on pipelined functions having pipe row? Thanks.
its too nice explanation.....
Thanks Teju!!! Hope you have liked the video and subscribed to our channel!!!
it's pretty good. pls also explain through blog.
Thanks!
Thanks Kishan :)
Welcome!!!
Good content.. Do you have an idea how can i work with the same function in java ??
Create a connection to the database and invoke the function!!!
@@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 !!
Thank you Kishan :D
well explained..
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.
Write a function to return the name of the student whose mark is maximum
Nice explanation but maintain screen quality while explain the program
sure!!! Thank you :)
confusing brother :(
which part? try this one out th-cam.com/video/zVs9fKSc-7M/w-d-xo.html