![]() ![]() The number of output rows will be the sum of the replication factor values. In the diagram below, the input value from row 2 will not appear in the output resultset. You will get it on 2 rows if the replication factor is 2, etc… If the replication factor is 0 for that input row, you will not see that value in the output. If a column from an input row is marked with pass_through=TRUE, you will get that value in your output if replication factor is 1. You can have 0, 1 or more output rows for any specific input row. If you do not call row_replication procedure during a particular fetch_rows, then you get one to one row output. You can do that for any of the fetch_rows calls but do not have to do so for all of them. You can control the number of output rows counted for each and every input row by populating an array with a numeric value for every input row. The procedure version requires an argument that is an index-by table you populate with a numeric value for every input row in that fetch_rows call. ![]() There is a capability to specify how many output rows there are for any given input row using DBMS_TF.row_replication procedure (or a scalar parameter). ![]() In other words you might set all of the input columns to pass_through=FALSE, but the PTF will only produce the number of rows from your New_Columns tables that match the number of rows of your input resultset. Whether you have any passthrough columns or not, there is still a relationship between the number of input rows and the number of output rows the function will produce from your New_Columns tables. ![]() You can add new columns to the output resultset. You can choose to include columns from the input row in the output row by setting the column pass_through flag to TRUE. By default there is a one to one relationship on the number of rows OUT to the number of rows IN. The general design pattern for a PTF is that it transforms rows from one result set into another. This article from Oracle Magazine by Chris Saxon goes along with one of them and I found it helpful. Enter the search term “polymorphic table function”. There are a suite of example PTF implementations in the Oracle LiveSql collection. He has simple examples to lead you in gently. The best beginner introduction to PTFs I found was by Tim Hall Polymorphic Table Functions in Oracle Database 18c. The types and constants are scattered through the documentation which makes them a little harder to put together than looking straight at the package spec. When I want to really understand the types and constants I found it easier to look directly at the DBMS_TF package specification in the database using the Toad or SqlDeveloper schema browser. PL/SQL Packages and Types Reference has documentation for the DBMS_TF package that provides the server side implementation. Oracle’s introduction to Polymorphic Table Functions is in the Database PL/SQL Language Reference 12.6 Overview of Polymorphic Table Functions. Polymorphic Table Function Tutorials and Examples As I’ll demonstrate later, the amount of code needed to produce a CSV file generator is substantially less using a PTF than DBMS_SQL. Ordinary Pipelined Table Functions require that you define the output type at compile time.Įven if you can define your output resultset at compile time, the PTF functionality takes care of many of the details you must otherwise encode manually with DBMS_SQL. If you want to see how that is done, I have a User Defined Type Object app_dbms_sql_udt you can check out. Ordinary Pipelined Table Functions already let you consume any type of input, but you must build your own code to figure out what is in it, generally by using DBMS_SQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |