Forum Stats

  • 3,854,533 Users
  • 2,264,379 Discussions
  • 7,905,718 Comments

Discussions

"ORA-01403: no data found" for "PL/SQL Function Body returning SQL Query", Apex 22.1

Larry H
Larry H Member Posts: 46 Red Ribbon
edited Aug 15, 2022 4:43PM in APEX Discussions

Hi, Gurus,

I am using apex.oralce.com, 22.1

In a region of cards, I use "PL/SQL Function Body returning SQL Query", here is the code:

DECLARE

 mychoice VARCHAR2(1);

 mysql VARCHAR2(2000);

BEGIN

mysql := 'SELECT ID, PREFERENCE, CITY||'', ''||STATE AS PLACE FROM SAMPLE_USER';

SELECT PREFERENCE INTO mychoice FROM SAMPLE_USER WHERE ID = v('USER_ID');

IF mychoice IN (1,2) THEN

mysql := 'SELECT ID, PREFERENCE, CITY||'', ''||STATE AS PLACE FROM SAMPLE_USER

WHERE PREFERENCE='||mychoice;

return (mysql);

END;

As you can see, if Preference in 1 or 2, add a "where clause", anything other than 1 or 2, no "where clause" necessary, but I got "no data found", if I change v('USER_ID') to a number that exists, validation passes.

I did research and tried various things, none works, please advise, I really appreciate it!

Answers

  • Scott Wesley
    Scott Wesley Member Posts: 6,260 Gold Crown

    Are you saying that the first query returns no values? What value does v('USER_ID') return? Have you sent this to debug log?

    Or are you getting an actual error displayed for the region? In which case it could be your return statement only executes when mychoice in (1,2).

    Your second query really should use a bind variable instead of a literal value, which can contribute to a flooded SQL pool, and extra hard parsing work.

    Depending on where mychoice comes from, you could just have a standard query

    where (:P1_MYCHOICE not in (1,2) and id = :USER_ID)
    or (:P1_MYCHOICE in (1,2) and preference = :P1_MYCHOICE)
    
  • Larry H
    Larry H Member Posts: 46 Red Ribbon

    Hello, Scott,

    Thank you for the recommendation for the bind variable, excellent advice. However my problem remains:

    "no data found" is seen at the page design time, the function body can not be compiled. But I am sure at runtime, it will have value, as a matter of fact, if I change v('USER_ID') to an actual value, say 1234, everything works as expected. I know at the design time, the value is null, but how do I make it compile.

    Take a look at https://community.oracle.com/tech/developers/discussion/973922/pl-sql-function-body-return-sql-query-no-data-found-problem one suggested: "To remove the error Edit the region and choose 'Use Generic Column Names (parse query at runtime only)', I just couldn't find this option, notice the post was 2009, many versions ago, is it deprecated? Do we still have that option in 22.1? Please let me know where to find it, that may solve the problem.

    The logic is simple, under certain conditions, the returned SQL will fetch smaller set of rows from table, otherwise the returned SQL will fetch all rows in the table. My function above goes like this: first suppose the returned SQL will fetch all rows (without where clause), then check a condition, if the value of the condition is certain value, overwrite the SQL by adding a where clause, then return SQL.

    Please advise, thank you!

  • Larry H
    Larry H Member Posts: 46 Red Ribbon
    edited Aug 16, 2022 1:18PM

    Since I couldn't compile it, I can't run it, so no debug info.

    About v('USER_ID'): USER_ID is a shared component, available to every page once logged in.

  • Scott Wesley
    Scott Wesley Member Posts: 6,260 Gold Crown

    Did you check the location of your return function?

    Your code above seems incomplete.

  • Larry H
    Larry H Member Posts: 46 Red Ribbon
    edited Aug 17, 2022 12:53AM

    Hi, Scott,

    What do you mean location of return function?

    The complete code is in the initial question above.

    The question really is: at design time, :USER_ID is of course NULL, but it will have value at runtime.

    I am sure the problem is:

    SELECT PREFERENCE INTO mychoice FROM SAMPLE_USER WHERE ID = v('USER_ID');

    because if I changed it to:

    SELECT PREFERENCE INTO mychoice FROM SAMPLE_USER WHERE ID = 1234;

    the validation passed, it compiled, and everything worked as expected. BUT why would Apex tries to evaluate this variable at design time? if it is something else, then "no data found" is misleading.

    Take a look at https://community.oracle.com/tech/developers/discussion/973922/pl-sql-function-body-return-sql-query-no-data-found-problem one suggested: "To remove the error, Edit the region and choose 'Use Generic Column Names (parse query at runtime only)', I just couldn't find this option, notice the post was 2009, many versions ago, is it deprecated? Do we still have that option in 22.1? Please let me know where to find it, that may solve the problem.

  • Scott Wesley
    Scott Wesley Member Posts: 6,260 Gold Crown

    There is no END IF above.

    The behaviour does sound a little weird, which is why I also suggested if your true solution lent itself to just beingi normal SQL with an OR statement in where clause.

    The other solution could be to add a union that just selects the relevant number of columns from dual. If necessary, this could possibly be configured to only return when APP_BUILDER_SESSION is not null.

  • Larry H
    Larry H Member Posts: 46 Red Ribbon

    Hi Scott,

    I found a workaround, I got the value set before the page is rendered, so I don't have to do:

    SELECT PREFERENCE INTO mychoice FROM SAMPLE_USER WHERE ID = v('USER_ID');

    inside the function body, then it compiles, and works as expected.

    This could be a bug :)

    Thank you VERY much for your help!