This content has been marked as final. Show 17 replies
I use a lot of these queries. My region call is just "return /*select 1 from dual */ someschema.somepackage.someprocedure;"
It looks as if
'*/select 1 from dual/*'
is commented out. Is this correct?
With this ability, what is the best technique by which to set the Report Column Attributes?
Yeah, I had to comment it out. that way you can set the column attributes. Without this, you'll only get generic columns. It depends on what you want to do. For an MRU, you need to use htmldb_item.whatever to set your column aliases for referencing. If it is just to display, then you can do it from the region definition
Yeah, I had to comment it out. that way you can setHey, Scott, how does this work? Why does that select 1 from dual make HTML DB dive into the function and pull the column definitions from it? Where is this documented?
the column attributes. Without this, you'll only get
generic columns. It depends on what you want to do.
[I hate generic columns because that page takes too long to load, I have to give column headings for each column, its hard to map the query with the Report Attrbutes page, etc]
Thanks Scott for your help. Its a weriod outcome, but it works.
This is something that ought to be in a Tips and Tricks/Knowledge base/How to/FAQ area.
It actually is a bug. I found this a while back, and one of the HTMLDB guys helped me figure this out. I believe it will be fixed in the next version of HTMLDB. I'm glad I can help someone out :)
What is the bug? The fact that you need the dummy select from dual to make HTML DB use Generic columns?
So when it is fixed, you can just do
and HTML DB will just dive into the function and use Generic columns?
I've managed to get this working using the dummy select so my report attributes have the correct aliases. However, my column headings are still disabled - is there anyway to change the headings? If I leave them, they return from the function still with the underscores (ex: PROJECT_ID). If I rename my columns in my query, it changes the alias in the attributes as well as the heading (ex: Project ID) which I suppose may be ok as I don't reference very many of these columns...but if I can somehow have PROJECT_ID as alias and Project ID as heading, that would be the ideal situation.
Sorry, one more question that I need help on.
I have this for my region source:
return /*select 1 from dual*/ xxtpm.tpm_tool_pkg.get_search_sql('MAIN');
This works fine, but I actually need to pass in an item value - P32_PILLAR_RES which has a default value of 'SEC' - and I cannot seem to get it to work correctly.
return /*select 1 from dual*/ xxtpm.tpm_tool_pkg.get_search_sql(:P32_PILLAR_RES);
errors with 'Query cannot be parsed within the Builder, etc.' when I hit Apply Changes.
I've also seen this syntax in a posting, but it returns the same error.
return /*select 1 from dual*/ xxtpm.tpm_tool_pkg.get_search_sql('P32_PILLAR_RES');
Any suggestions on what I'm missing?
Has this problem been fixed in APEX 3.0
I am having the exact same problem that anasazii posted. I am using APEX 3.0.1.00.07.
I have verified using sql developer that my function returns valid queries in all cases. I have tried several different methods of calling my function, all of which fail except for the case where I hard-code the parameter to the function. Any time I move to use either an APEX variable, an application item, or a page item I receive this error:
Function returning SQL query: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the generic columns checkbox below the region source to proceed without parsing.
(ORA-01403: no data found)
Generic query columns does not solve the problem either.
Does anyone have a solution for this? My team cannot upgrade to later versions of APEX due to incompatibility with other aspects of our system, so that is out of the question.
Thanks for any help,
Janel posted two different problems so I'll need you to describe everything about your situation. If you can demonstrate it on apex.oracle.com that would be ideal.
Sorry for the confusion. My problem is as follows:
This statement is not accepted as my report region source when the region source type is set to PL/SQL function body returning a sql query.
return /*select 1 from dual*/ generate_my_trips_report( :P7_USER_ID,
I actually found that this was accepted when I used generic columns (unlike what I stated before, as before I was trying to use an application item as a parameter to the function, and not just all page items). I keep getting the "No data found" error when I try to use any other variable type (application item, or apex defined item). When I hard-code the parameters as Janel did I can remove the generic column condition.
My workaround is as follows:
1) hard-code the parameters to the function and get the query specific columns to work.
2) Completely avoid all APEX validations by simply (and highly not recommended I'm sure) modifying my region source in WWV_FLOW_PAGE_PLUGS.
3) Run page and magic, everything works as I would have expected it to just by plugging in the items through the APEX development interface.
I will try to reproduce this on apex.oracle.com when I have some downtime from working on this app :).