I am creating an IR on PLSQL Function Returning SQL Query.
Trying to validate the region I get this error:
ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 4, column 6: ORA-00936: missing expression</p>".
The region code:
DECLARE
v_sql VARCHAR2(4000);
BEGIN
v_sql := ACCSPREC.DW_AV_PROTO.Q_AV_SAFIS_ETRIPS_F(
P_AV_NAME => 'AV_NAME',
P_TIME_LEVEL => :P1480_TIME_LEVEL,
P_SPECIES_LEVEL => :P1480_SPECIES_LEVEL,
P_GEAR_LEVEL => :P1480_GEAR_LEVEL,
P_AREA_LEVEL => :P1480_AREA_LEVEL,
P_FISHER_LEVEL => :P1480_FISHER_LEVEL,
P_DEALER_LEVEL => :P1480_DEALER_LEVEL,
P_VESSEL_LEVEL => :P1480_VESSEL_LEVEL,
P_GRADE_LEVEL => :P1480_GRADE_LEVEL,
P_MARKET_LEVEL => :P1480_MARKET_LEVEL,
P_APP_ID => :APP_ID,
P_PAGE_ID => :APP_PAGE_ID,
P_SESSION => :APP_SESSION,
P_ALL_SPECIES_FLAG => :P1480_ALL_SPECIES_FLAG,
P_ALL_GEARS_FLAG => :P1480_ALL_GEARS_FLAG,
P_ALL_AREAS_FLAG => :P1480_ALL_AREAS_FLAG,
P_ALL_FISHERS_FLAG => :P1480_ALL_FISHERS_FLAG,
P_ALL_DEALERS_FLAG => :P1480_ALL_DEALERS_FLAG,
P_ALL_VESSELS_FLAG => :P1480_ALL_VESSELS_FLAG,
P_ALL_GRADE_FLAG => :P1480_ALL_GRADE_FLAG,
P_ALL_MARKET_FLAG => :P1480_ALL_MARKET_FLAG );
RETURN v_sql;
END;
when I add the line:
v_sql := 'SELECT 1 FROM DUAL';
Of course all validates/compiles OK, as expected.
When I use a simpler version of this funtion, all works OK, as expected.
The function runs and debugs OK in SQL Developer.
I am guessing there is something that happens during APEX region validation that I am not aware of that is causing the return of a SQL query that does not parse.
I added default values to the function definition for all values down ot p_app_id, p_page_id, etc. The code handles NULLs for the rest of the parameters.
This has to be something simple I am just not seeing or not aware of in that Validate ...
Any suggestions? TY
A simple RETURN <function call> gives the same error.
This simpler similar function works fine
