Using Oracle Table functions using user parameters in reports (3.5)

[Brett]

Hi, newbie here.

Report Designer 3.5.0 -GA 10345

Anybody know how to utilitize an Oracle table functions in a query where report parameters are part of the call? I have no problem getting a query to work using a report param in a where clause, but it doesn't seem to work using same thing in pipelined table function call. Here is the call

select XYZ.*, XYZ.last_name||', '||XYZ.first_name "PATIENT_FULL_NAME"
from table(MY_FUNCTION(
null
,null
,null
,${P_DOC_SID}
)) "XYZ"

The ${P_DOC_SID} works just fine if I take the query out of the table function itself and use it in the report. Just fine. It just doesn't like ${P_DOC_SID} used in the table function call.

If I just hardcode a value in the above call for ${P_DOC_SID}, it works just fine as well (so I know its not the table function itself that is the problem). I also know this call works in Oracle Reports.

Anybody have any experience with this?

Thanks,
Brett

[Larry Hardebeck]

FYI

I have a lot of Mod/Pl/SQL reports I am trying to convert using the Pentaho Report designer tool (Vers. 3.8.1-GA-14286). I am having the same issues Brett has been having and it is very frustrating. I was able to get two reports converted and it worked just fine. Another developer in our group could not get his Oracle pipeline function to work using two simple date parameters. I spent a lot of time with him and we could not see how his report should have been any different than a couple of reports I converted using date parameters. I am now stuck on a report that uses a pipeline function and interacts with a Multi-list screen. The have selected several items in the Multi-list drop down window and displayed them in the report header and they show up fine. However; when I try to pass them as a string to the Oracle pipeline function nothing works. I just get an error when I try to preview the pipeline function. The parameter I am trying to pass the Multi-list values is a varchar2 data type so that should not be any problem.

Any help on this would be greatly appreciated.

Thanks,
Larry Hardebeck

[hansva]

Hey there,

I suspect that the answer to this question will be in the JDBC documentation of oracle...
it could be a limitation of the oracle JDBC driver or a different syntax has to be used for the JDBC driver.

Hope this helps you a bit...

Greetz,
Hans