Get Started with Table Functions Module 1: Overview and Fundamentals

[@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

[@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 !

[@tammarinwonderland1]

Good stuff!

[@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.

[@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.

[@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.

[@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?

[@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.