This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jun 20, 2008 5:18 AM by 593012 RSS

Pl/Sql Function Body Returning Sql Query Problem

441000 Newbie
Currently Being Moderated
Why is this code:

DECLARE
q VARCHAR2(32767); -- query
BEGIN
q := someschema.somepackage.someprocedure;
RETURN q;
END;

Which is attributed to be:

SQL Query (Pl/Sql Function Body Returning Sql Query).

Is Giving me this error:

report error:
ORA-01403: no data found

'someprocedure' Returns a valid SQL Statement in a VARCHAR2 String.

Any ideas?

Regards,

Greg.
  • 1. Re: Pl/Sql Function Body Returning Sql Query Problem
    423655 Newbie
    Currently Being Moderated
    I use a lot of these queries. My region call is just "return /*select 1 from dual */ someschema.somepackage.someprocedure;"
  • 2. Re: Pl/Sql Function Body Returning Sql Query Problem
    441000 Newbie
    Currently Being Moderated
    It looks as if

    '*/select 1 from dual/*'

    is commented out. Is this correct?
  • 3. Re: Pl/Sql Function Body Returning Sql Query Problem
    441000 Newbie
    Currently Being Moderated
    With this ability, what is the best technique by which to set the Report Column Attributes?
  • 4. Re: Pl/Sql Function Body Returning Sql Query Problem
    423655 Newbie
    Currently Being Moderated
    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
  • 5. Re: Pl/Sql Function Body Returning Sql Query Problem
    VANJ Journeyer
    Currently Being Moderated
    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.
    Hey, 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?

    [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]
  • 6. Re: Pl/Sql Function Body Returning Sql Query Problem
    441000 Newbie
    Currently Being Moderated
    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.
  • 7. Re: Pl/Sql Function Body Returning Sql Query Problem
    423655 Newbie
    Currently Being Moderated
    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 :)
  • 8. Re: Pl/Sql Function Body Returning Sql Query Problem
    VANJ Journeyer
    Currently Being Moderated
    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
    return pkg.func;
    and HTML DB will just dive into the function and use Generic columns?
  • 9. Re: Pl/Sql Function Body Returning Sql Query Problem
    431127 Newbie
    Currently Being Moderated
    Hi all,

    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.

    Thanks!
    Janel
  • 10. Re: Pl/Sql Function Body Returning Sql Query Problem
    431127 Newbie
    Currently Being Moderated
    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?
    Thanks!!!
    Janel
  • 11. Re: Pl/Sql Function Body Returning Sql Query Problem
    512085 Newbie
    Currently Being Moderated
    Hi Guys,

    Has this problem been fixed in APEX 3.0

    Cheers.
  • 12. Re: Pl/Sql Function Body Returning Sql Query Problem
    593012 Newbie
    Currently Being Moderated
    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,
    Adam
  • 13. Re: Pl/Sql Function Body Returning Sql Query Problem
    60437 Employee ACE
    Currently Being Moderated
    Adam,

    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.

    Scott
  • 14. Re: Pl/Sql Function Body Returning Sql Query Problem
    593012 Newbie
    Currently Being Moderated
    Scott,

    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,
    :P7_STATUS_ID,
    :P7_START_DATE_FROM,
    :P7_START_DATE_TO );

    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 :).

    Thanks,
    Adam
1 2 Previous Next