Getting Started with PL/SQL Table Functions 3: Streaming Table Functions

Streaming table functions are functions executed in the FROM clause of a query as part of a sequence of data transformations, all occurring within a single SQL statement. This video shows how to define a streaming table function and use it in Oracle SQL.

========================================
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.

[swapnasagarpanda]

@steven, I read one of your Blogpost on Bulk Collect Into clause with Limit and I see you have suggested to use COUNT Method to perform the Exit condition check from the Loop instead of the %NOTFOUND attribute of the cursor and you have explained the issue with %NOTFOUND. You have used that same approach here as well. But my question is, can't we just write the Exit statement first after opening the cursor and then do the Fetch, in that way we can use the %NOTFOUND and also we don't have to do the fetch when we don't have any data. Or is Fetch better after Opening the Cursor, please let me know the rational behind doing Fetch first and then writing the Exit condition. Thanks in advance

[AshishSingh-quns]

What can we achieve by using strong ref cursor which cannot be achieved using a weak ref cursor? What is the default return type of a ref cursor? Hi Steve, can you please answer above questions.

[sachinzawar]

Hello Sir, Nice insights.... One query. As you used Object type of a table in which we are going to insert rows using collection. Can't we use %Rowtype while declaring collection itself. I think It will be convenient if new column gets added to table. In this case we will have to update object type everytime.... Please Correct me if I am going wrong anywhere.