Comments
| Pipelined Table Functions - Improve performance of ETL processes by pipelining all transformation functions. |
Sarah said... This was helpful... I discovered a limitation when using pipelined tables stored in a variable. When I attempted to use one in a SELECT used for an INSERT, compiling the package disconnected me from Oracle! The pipelining function must be called directly in the select statement. Using the variable in UPDATEs and MERGEs worked fine.In package spec: TYPE work_row_typ IS RECORD (ID work.id%TYPE, and so on); TYPE work_tbl_typ IS TABLE OF work_row_typ; In package body: function GET_WORKLOAD_DATA return MYPKG.work_tbl_typ pipelined IS blah END GET_WORKLOAD_DATA; function main_fcn(whatever) return integer IS tblWork MYPKG.work_tbl_typ; begin This caused compile to disconnect from Oracle: SELECT * INTO tblWork FROM TABLE(GET_WORKLOAD_DATA()); This compiled without error: SELECT * BULK COLLECT INTO tblWork FROM TABLE(GET_WORKLOAD_DATA()); This caused compile to disconnect from Oracle: INSERT INTO MyTbl SELECT col list FROM TABLE(tblWork); This compiled without error: INSERT INTO MyTbl SELECT col list FROM TABLE(GET_WORKLOAD_DATA()); I put the results of the function into a variable because it is needed in multiple SQL statements. Didn't want to call the function multiple times. Data is only deterministic in the short run, so didn't want to use that. |
Rachit said... So far the most correct explanation of 'why Piped?'.... |
DO NOT ask technical questions here, that's what my forum is for!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!