Informatica +PL/SQL，使用函数和引用光标

Question

我有一个包，我必须从包中执行一个函数，其参数是会话日期，返回值是参考游标。当我连接到数据库并执行函数时，我得到了正确的输出。当我在信息中执行相同的操作时，它在日期部分出错。1）首先，执行功能并在信息中获取数据的方法是什么2） 这可以通过源限定符转换或 SQL 转换来实现吗？2）如果有可行性，我如何将日期传递给甲骨文

Answer 1

您可能正在寻找的是Oracle流水线数据函数。 查询/函数调用将是信息"覆盖SQL"。

采用以下格式：

SELECT * from TABLE(f_output_query_results(
'X_PROCESS_NBR',
'X_EXCEPTION_TXT',
'COUNT (*) ',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'CNTL_EXCEPTION',
'X_LEVEL_CD IN (''E'', ''F'') AND TRUNC(X_EXCEPTION_TS) > ''20-JAN-2010'' ',
'GROUP BY X_PROCESS_NBR,X_EXCEPTION_TXT',
'List of serious errors',
NULL,
'HTML',
'TEST.HTML'));
CREATE OR REPLACE FUNCTION &&EVL_TARGET..f_output_query_results ( -- Builds an SQL statement from the passed arguments, executes it and return the results in a file or as a table of values
   p_varchar1         IN VARCHAR2, -- The first column of the SELECT statement (VARCHAR2)
   p_varchar2         IN VARCHAR2, -- The second column of the SELECT statement (VARCHAR2)
   p_varchar3         IN VARCHAR2, -- The third column of the SELECT statement (VARCHAR2)
   p_varchar4         IN VARCHAR2, -- The fourth column of the SELECT statement (VARCHAR2)
   p_varchar5         IN VARCHAR2, -- The fifth column of the SELECT statement (VARCHAR2)
   p_number1          IN VARCHAR2, -- The sixth column of the SELECT statement (NUMBER)
   p_number2          IN VARCHAR2,-- The seventh column of the SELECT statement (NUMBER)
   p_date1            IN VARCHAR2,   -- The eighth column of the SELECT statement (DATE)
   p_date2            IN VARCHAR2,   -- The ninth column of the SELECT statement (DATE)
   p_from_clause      IN VARCHAR2,                         -- A valid SQL FROM clause
   p_where_clause     IN VARCHAR2 DEFAULT '1=1',          -- A valid SQL WHERE clause
   p_group_sort       IN VARCHAR2 DEFAULT 'ORDER BY 1', -- A valid SQL GROUP BY and/or ORDER BY phrase
   p_rpt_title        IN VARCHAR2 DEFAULT 'Query Results',  -- the title to appear at the top of the report
   p_report_date   IN DATE DEFAULT SYSDATE - 1,       -- an optional date to display in the report header
   p_out_format       IN VARCHAR2 DEFAULT 'HTML',     -- the format of the report file HTML or CSV
   p_file_name        IN VARCHAR2 DEFAULT NULL -- name of file to write out to
                                              )
   RETURN typ_query_info_table
   PIPELINED