Hi everyone,
I have a problem in showing value on page item of oracle apex 20.
I write a query by selecting a view created from a table db link. I run it on DB, it is fine. I try it on object browser of oracle apex, it shows me data. But when I run same query on select list, modal lov, interactive report, it returns nothing. I don’t understand why that reason is. Thank you for your time and in advance.
@Vinish: hi u,
I try to
but oracle apex have no result. I think oracle apex have problem with connecting DB link.
Hi Vinish, I find a way to solve my problem on Apex.
On DB, I create view by
<code>
materialized view refresh force on demand start with
</code>
And when I run on select list of page item, it shows me value.
@Vinish: Thank you for your time. But the view is not allowed for the public. So I can’ t test on your way. But I think when I grant the view for my user. I test on DB it is fine, so the view should be shown data.
I think that view needs the grants for the Oracle Apex users. Or try to hardcode the schema name before the view name (yourSchema.yourView).
Also, try to specify the SQL query for the select list without using the view. For example:
Thank you @Vinish . Everything you mentioned I tried before but it was not successfully. I try select a,b directly to talbe@dblink but it is not done. As I mentioned before, on sql commands of sql workshop, I try select a,b directly to talbe@dblink, it shows me data. Eventually, I don’t know why
Ok. Currently, I am unable to test this case in Oracle Apex, but I can suggest one more thing. Try using PL/SQL function body returning SQL query for Select List source type. Maybe, in this case, it will execute using dynamic SQL and perhaps fetch data, but I am not sure.
oh, that method I have never tried it. Now I test select by function body below as:
Declare
v_sql varchar2(32767);
begin
v_sql :=’select exp_name, exp_code from edg_budget_codes_v’;
return (v_sql);
end;
But it still does not show anything.
Try giving Select grant to all the users for this view. Execute the following statement in the schema where you created the view: