Get Started with Table Functions Module 1: Overview and Fundamentals

This video is part of the Oracle Dev Gym class "Getting Started with Table Functions". This module offers an overview of table functions: what they are, how they work, and a simple example of a table function that returns a collection of scalar values.
You can watch this video independent of the class, but then you miss out on the videos and quizzes. So we encourage you to go to the page below and register for the class!
devgym.oracle....
========================================
Practically Perfect PL/SQL with Steven Feuerstein
Copyright © 2018 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[@adarshsrivastava4805]

You are a BEAUTY sir.Loved the Way you explained it sir simple and to the point ,In a single shot I got Every point quite clearly.HUGE LOVE FROM INDIA SIR... RESPECT.

[@PracticallyPerfectPLSQL]

You are very kind. Please make sure to try the whole class at devgym.oracle.com

[@GautamKumar-ci4rz]

Awesome Steve , This is best video to understand table function. Thanks for sharing this

[@shashank2004]

Awsome video sir, pls make a video on hints and pragma inline, pragma serially reusable, pragma restrict refrence and i want to know about the scope of PLSQL in future?

[@PracticallyPerfectPLSQL]

Thanks for the request, Shashank. Will add to list. restrict_references, by the way, isn't really used anymore. Serially reusable - check out my LiveSQL script: livesql.oracle.com/apex/livesql/file/content_C1W6NZ9AL8CN7Z3T6GBZBIUNR.html

[@tammarinwonderland1]

Good stuff!

[@hubbellrowe9012]

How do we compile PL/SQL queries on our local machine? The sqldeveloper program downloaded from oracle does not work for my Ubuntu 22.04 machine and I see no alternatives. Is there any normal CLI PL/SQL compiler that would be easy to download as is available for other SQL languages? I really think that any introduction to a language should include at least some information on how a user can download and use this language on their local machine, or all the tutorials under the sun will not be of any use. Any help would be greatly appreciated.

[@praveenkumar-fx5wx]

Hi Sir, Great video .. thanks for sharing. However suppose we have lots of data data in the collection (l_collection.count=300,000) .Now when we use table function what about the performance (will there be any performance issue) and memory usage (will the memory usage be very high ) ?

[@PracticallyPerfectPLSQL]

Then you should consider a switch to pipelined table functions, which bypass the memory issue. I strongly encourage you to take my table functions class at the dev gym: devgym.oracle.com/pls/apex/dg/class/get-started-with-pl-sql-table-functions.html

[@praveenkumar-fx5wx]

Practically Perfect PL/SQL with Steven Feuerstein thanks sir !

[@abhi1302]

I used Table function to return multiple error/Exception on a set of record.. But what about pipe line functions current example we are not using pipeline function..

[@PracticallyPerfectPLSQL]

Did you watch the pipelined table function video? It's #4 in the series (and please do take the class at devgym.oracle.com). You can safely switch to pipelined, and get reduction in PGA consumption and possible improvements in performance. The only downside is you cannot call the function outside of a SQL statement.

[@PracticallyPerfectPLSQL]

Check out my separate video on pipelined table functions.

[@Bob-ig4bi]

I copied your list_of_names code, but get an error. CREATE OR REPLACE TYPE list_of_names_t IS TABLE OF VARCHAR2(100); / DECLARE happyfamily list_of_names_t := list_of_names_t (); children list_of_names_t := list_of_names_t (); grandchildren list_of_names_t := list_of_names_t (); parents list_of_names_t := list_of_names_t (); BEGIN /* Can extend in "bulk" - 6 at once here ---- indexes in nested tables and varrays start at 1, not 0 */ happyfamily.EXTEND(7); happyfamily(1) := 'Veva'; happyfamily(2) := 'Chris'; happyfamily(3) := 'Lauren'; happyfamily(4) := 'Loey'; happyfamily(5) := 'Juna'; happyfamily(6) := 'Eli'; happyfamily() := 'Steven'; /* Individual extends */ children.EXTEND; children (children.LAST) := 'Chris'; children.EXTEND; children (children.LAST) := 'Eli'; children.EXTEND; children (children.LAST) := 'Lauren'; grandchildren.EXTEND; grandchildren (grandchildren.LAST) := 'Loey'; grandchildren.EXTEND; grandchildren (grandchildren.LAST) := 'Juna'; /* Multiset operators on nested tables */ parents := (happyfamily MULTISET EXCEPT children) MULTISET EXCEPT grandchildren; DBMS_OUTPUT.PUT_LINE ('Grandparents:'); FOR l_row IN 1 .. parents.COUNT LOOP DBMS_OUTPUT.PUT_LINE (parents (l_row)); END LOOP; happyfamily.DELETE; DBMS_OUTPUT.PUT_LINE ('Size of Happy Family: ' || happyfamily.COUNT); DBMS_OUTPUT.PUT_LINE (':=)'); END; /

[@Bob-ig4bi]

[Error] Execution (5: 23): ORA-06550: line 2, column 23: PLS-00201: identifier 'LIST_OF_NAMES_T' must be declared ORA-06550: line 2, column 23: PL/SQL: Item ignored ORA-06550: line 3, column 23: PLS-00201: identifier 'LIST_OF_NAMES_T' must be declared ORA-06550: line 3, column 23: PL/SQL: Item ignored ORA-06550: line 4, column 23: PLS-00201: identifier 'LIST_OF_NAMES_T' must be declared ORA-06550: line 4, column 23: PL/SQL: Item ignored ORA-06550: line 5, column 23: PLS-00201: identifier 'LIST_OF_NAMES_T' must be declared ORA-06550: line 5, column 23: PL/SQL: Item ignored ORA-06550: line 8, column 5: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 8, column 5: PL/SQL: Statement ignored ORA-06550: line 9, column 5: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 9, column 5: PL/SQL: Statement ignored ORA-06550: line 10, column 5: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 10,line 10,䮉櫘翹

[@melokorn]

Which kind of error?

[@abdullahsiddique7787]

What is the future scope of pl sql as a career

[@slither366]

I have watching this in 2023 :), I don't know if this is continying works in the same way or this have been any modifications.

[@umrbekmatrasulov4141]

this video is awful and not useful for beginners, there is absolutely no information about which software this guy is using, how to install server, code editor, and there is no explanation for each row. At 5:00 just shows to you a bunch of code - and here we go, understand it how you want

[@krakajak67]

If you are starting with Oracle Table Functions as a beginner, you have very much got a long way to go.

[@umrbekmatrasulov4141]

@@krakajak67 ok. Do you have any recommendations where to start?

[@slither366]

@@umrbekmatrasulov4141 you need to check the other videos for beginners.