informatica +pl/sql, working with function and reference cursor

Question

I have a package and I have to execute a function from package whose parameter is session date and return value is a reference cursor. When I connect to database and execute the function, I am getting proper output. When I am executing the same in informatica, it's going wrong at date part. 1) Firstly what can be an approach to execute the function and obtain the data in informatica 2) is this possible by source qualifier transformation or SQL transformation? 2) if there is feasibility how can I pass the date to Oracle

Answer 1

Likely what you are looking for is Oracle Pipelined data function. the query/function call will be an informatica "override SQL".

In this format:

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