APEX Classic Report (based on Function) doesn't work

Question

APEX 5 - I have a Classic Report (based on Function) from the following statement:

declare 
    bamsan varchar(3000);
begin
    if v('foo')= 'lansan' then
        bamsan := 'select id, cpu_cores, cpu_model, disk_gb from physical_servers where PHYSICALSERVERID like (select LEFTPHYSICALDEVICEID from SAN_PHYS_CON where CONNECTIONID like :meklet_value)';
    elsif v('foo')= 'storage' then 
        bamsan := 'select id, modelis, customerstoragename from storages where storageid like :meklet_value';
    elsif v('foo')= 'clusters' then 
        bamsan := 'select id, memberhostid from clusters_pools where clusterpoolid = :meklet_value';
    elsif v('foo')= 'hostname' then 
        bamsan := 'select id, cpu_cores, cpu_model, disk_gb from physical_servers where PHYSICALSERVERID like (select parentid from hosts where hostname like :meklet_value)';
    else 
        bamsan := 'select id, cpu_cores, cpu_model, disk_gb from physical_servers where PHYSICALSERVERID like :meklet_value';
    end if;
    return bamsan;
end;

The choice elsif v('foo')= 'clusters'... brings up the error:

ORA-20001: Error fetching column value: ORA-01403: no data found

I'm 100% sure that the table and the columns exist (I can run this select from SQLDeveloper and Apex SQL Commands). Is this really an APEX bug or am I missing something?

It's not the query that's raising no_data_found: notice that it's actually ORA-20001: Error fetching column value - this is raised by Apex after getting the results of the query - I suspect you have a LOV-type column in the report definition that fails to find the value for one or more rows returned by that query. — Jeffrey Kemp
Could this have something to do with the different number of columns you select? How many columns are displayed in the Page Designer? — User42

Fernando Madrigal Fernando Madrigal · Accepted Answer · 2017-05-25T15:36:48

in this case the problem is your dinamic query because in some selects are different number of columns... yo could select the option "Use Generic Column Names"

APEX Classic Report (based on Function) doesn't work

1 Answers