Using a function in a report query

10-11  Source: Network gathering  Views:1 

Why can't I call a function (basically a query) in a report query like I can from from SQL?
Consider the following:
Select MyFunctionName(arg) from dual;
From SQL, it works great! No problem:
In a report query, [Application/Shared Components/Report Queries] It doesn't work. I get an XML error:
XML Parsing Error: not well formed
<MyFunctionName('arg')>&lt;?xml version="1.0"?&gt;
Looks like APEX is not even executing the function...
See I have a table with a CLOB field that contains properly formatted HTML. I cannot simply query for that content, since Oracle escapes the tags in the conversion to XML. I have to (somehow) tell Oracle to keep it's grubby mitts off my CLOB content. As far as I can tell, using DBMS_XMLGEN.setConvertSpecialChars is the only way to keep Oracle from escaping the html stored in the table--in fact, the DBMS_XMLGEN documentation states that it why this functionality exists in the first place!
This is why I am trying to use a function. Simply queries don't work. The function I wrote works beautifully in SQL, but not in a report query...
Edited by: Vorlon on Aug 5, 2010 1:20 PM
Note from the first post:
"In a report query, [Application/Shared Components/Report Queries]" Let me clarify: This report query is used in a BI Publisher report. Getting the information to the screen isn't the problem. I have that working. :-) It's just when I try to get the report to print that I see APEX has escaped the output before reaching BI Publisher--so BI Publisher is not the issue. I have a xsl template that will process the embedded HTML, but APEX is escaping my data before I can act on it. In other words:
It seems Apex is not creating the XML properly, since it escapes everything. Even when the function passes back data correctly.
Has anyone else seen this? Ideas for a workaround?
Related articles
  • Using a function in a report query 10-11

    Why can't I call a function (basically a query) in a report query like I can from from SQL? Consider the following: Select MyFunctionName(arg) from dual; From SQL, it works great! No problem: In a report query, [Application/Shared Components/Report Q

  • Using a function module in ABAP Query 10-11

    Hi All, I want to use a function module in ABAp query in the Record Processing Section. In the report program we click on Pattern button to inser the function module so that all the Importing and exporting parameter automatically appears. Similarly i

  • How to use oracle functions in Crystal Reports XI using Oracle Server 10-11

    Hi all, Is it possible  to use oracle functions in Crystal Reports XI using Oracle Server as Data Source. If i try to use a procedure,i am getting error with message "Invalid Arguement Provided". Functions are not visible objects like tables,vie

  • Use of writeChanges() / conformResultsInUnitOfWork in report query 10-11

    I have a usecase where i am saving data in different tables in same transaction I want to use ReportQuery so as to use functions likeaddAverage. ReportQuery query = new ReportQuery(res); query.setReferenceClass(Subject.class); query.addAverage("avera

  • Using Match function in FR Reports 10-11

    Hi, Can anyone pls. let me know how i can use Match function in FR ? I've a Report where i'm using 2 Grids. In one Grid1 i'm using Entity under page and ICP dimension as Column. In Grid2, i'm using ICP under page and would like to use Match function

  • How to use email function in crystal report ? 10-11

    Post Author: kudo CA Forum: .NET Hi I'm a novice by touching .net not more than 2 months. Can somebody guide me how to use email function provided in crystal report components?(Better put a sample code so that I can understand well.)  ps: I'm using V

  • How to use MAX function in SSAS MDX Query 11-30

    I want to run this Query with MAX Condition on LAST_DATA_UPDATE Column .Hi Ashishsingh, According to your description, you want to know how to use MAX function in SQL Server Analysis Services MDX Query, right? In this case, please refer to the link b

  • Using Sql Functions in Crystal Reports 10-11

    Can SQL table-valued functions be used in Crystal Reports?  The only options I see in the Database Expert are Tables, Views, and Stored Procedures.Hi Dan, Are you referring to user-defined SQL functions? If yes, then these are not accessible in Cryst

  • ORA-03113 when using connect by in a report query ? 11-30

    Hi, Oracle 10g r2, ApEx 4.0.2. I have a classic report on page zero, I need the following query to display my data : select      distinct '<input class="radio_report_search_photos" name="radio_selected_photo" type="radio"

  • Is it possible to use aggregation function in SQ Sap query? 11-30

    I need to create a sap query like using SELECT MAX(begda) statement. Please teach me how... Thank u in advance..Hi Ella, You can very well use the aggregate functions in sql which is usually a performance tuning technique. A calculation that is made

  • Using stored function in the Report Trigger 11-30

    I have faced this problem in Reports6.0. When a stored function is called in the after form trigger as below val := func1(abc,def); the report is hanging If I write like this i.e., select func1(abc,def) into val from dual; it is working fine . Is thi

  • Using concatenate function to write report comments 11-30

    Hi am a high school teacher and have just got my first iPad - lovinging it!!! and am using Numbers and Pages on both my Mac at home and my iPad, but have hit a bit of a snage with using the concatenate funtion in my report generating spreadsheet. We

  • Using arcsine function on WebI reports 11-30

    Hi All, I could not find the arcsine function to be included in the WebI report. Is there a way I can enforce WebI to return arcsine values. Can I create a formula? Please suggest. Regards, Faisal Vakil Edited by: Faisal Vakil on Nov 5, 2010 11:12 PM

  • Using BOUSER or CurrentUser() in Report Query Filter 11-30

    Post Author: Sandiegouser CA Forum: WebIntelligence Reporting We are using LDAP for Authentication. I was to be able to use the LDAP user id to filter what the user sees in a report..using either @variable('BOUSER') or CurrentUser() Thank youIt's mor

  • Using custom functions in a query 11-30

    Hi, I have a query in a PLSQL procedure where some of the fields are data retreived from custom functions, i. e. select a, b, funcThatNeedsA(a), funcThatNeedsB(b) from xxxx where ... but I also could use those functions by making a loop to a cursor i

  • Error using Rank function in Answers 11-30

    Hi All, Am trying to generate a report in Answers which lists Top Accounts with Revenue. I Ranked the Revenue field and it is returning me correct values. ( Rank(account.revenue) ) But, when I try to filter on this field and restrict the rows which s

  • Bind Variables and Shared Component Report Query 10-11

    I have a query in a region report which I have replicated to a shared component report query. Both queries reference page items as bind variables in the where clause. The report region on screen shows the correct results but the report query shows "n

  • Use sql function decode with Zend code 10-11

    Hi, I need to use sql function DECODE() in a query for PHP Code. This query works well in sql*plus but PHP returns the following error message: Parse error: syntax error, unexpected T_STRING in C:\Program Files\Zend\Apache2\htdocs\Compta\compta2.php

  • Using Multiple Functions with in members selection of Hyperion Financial St 10-11

    Hello everyone, I am trying to retrieve all the level 0 entity members from a specific Level 3 entity member. Can I use multiple Functions with in Report studio to do this?? Or is creating an alt hierarcy my only option? ThanksYou can try doing Syste

  • Relative function in FR reports 11-30

    I want to use a function in FR reports equal to the @Relative function in Essbase NOT the Relative Function found in FR (as it seems to only work on SAP / SSAS database. Basically I want the user to select POV x and take all the Lvl 0 members under t