CODEWITHSUNDEEP
plsqloracle-apex
Michael Frey
Michael Frey

Reputation: 918

Using a statically assigned application item as SQL in Oracle APEX

In my Oracle Apex 19.3 application I have a SQL statement that needs to be used on several pages and changes slightly based on the user that is logged in. So that I do not need to duplicate this code over and over on each page I generate this statement as an application item called: QUERY_BASED_ON_USER.

An application computation then statically sets it to SELECT j.* FROM table(pkg_jobstatus.report()) j WHERE j.id IN (:USERIDS)

(USERIDS is a separate application item)

I wish to use the application item QUERY_BASED_ON_USER as the sql statement for a table. When setting the data source to PL/SQL and using the following code,

BEGIN
    return :QUERY_BASED_ON_USER;
END;

I get this error: PL/SQL function body did not return a value.

I tried debugging this by settings a static page region to: &QUERY_BASED_ON_USER. and it outputs the query correctly.

My assumption is that the code editor does not evaluate the application computation and thus it returns an empty string, which it then refuses to validate or save. But I do not know how to validate this or how to work around this.

How can I use the application item as the sql statement?

Upvotes: 0

Views: 323

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

You need to set "Use Generic Column Names" to true, and specify the number of columns your query will return:

enter image description here

Then the query is not parsed until runtime, when the item value is available.

Upvotes: 1

Related Questions