hierarchical list of values - 3 levels

siegwin.port wrote:

RETURN 'select nvl('||:P13_STUFE_2||',job) d,nvl('||:P13_STUFE_2||',job) r from emp WHERE deptno=nvl('''||:P13_STUFE_1||''',deptno)';
cascading LOV parent Item P13_STUFE_2
Page Items to submit

Result:
http://localhost:8080/apex/f?p=101:13:9704449646773:::::
Error during rendering of page item P13_STUFE_3. ORA-06550: line 1, column 24: PL/SQL: ORA-00936: missing expression ORA-00936: missing expression ORA-06550: line 1, column 13: PL/SQL: SQL Statement ignored ORA-00936: missing expression
Technical Info (only visible for developers)

is_internal_error: true
apex_error_code: WWV_FLOW_FORM.UNHANDLED_ERROR
ora_sqlcode: -6550
ora_sqlerrm: ORA-06550: line 1, column 24: PL/SQL: ORA-00936: missing expression ORA-00936: missing expression ORA-06550: line 1, column 13: PL/SQL: SQL Statement ignored ORA-00936: missing expression
component.type: APEX_APPLICATION_PAGE_ITEMS
component.id: 2521712617507612
component.name: P13_STUFE_3
error_backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325 ORA-06512: at "SYS.WWV_DBMS_SQL", line 1022 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN_UTIL", line 1246 ORA-06512: at "APEX_040200.WWV_FLOW_NATIVE_ITEM", line 1741 ORA-06512: at "APEX_040200.WWV_FLOW_NATIVE_ITEM", line 4242 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN", line 1251 ORA-06512: at "APEX_040200.WWV_FLOW_FORMS", line 999 ORA-06512: at "APEX_040200.WWV_FLOW_FORMS", line 1428


Please help me to filnd the error

When generating any dynamic SQL it's always advisable to log the generated statements before executing them for diagnostics/debugging. Change the LOV function body to:

declare
  l_lov_sql varchar2(4000);
begin
  l_lov_sql := 'select nvl('||:P13_STUFE_2||',job) d,nvl('||:P13_STUFE_2||',job) r from emp WHERE deptno=nvl('''||:P13_STUFE_1||''',deptno)';
  apex_debug.message('P13_STUFE_2 SQL: ' || l_lov_sql);
  return l_lov_sql;
end;

That said, the problem appears to be with the positioning of the NVLs. When P13_STUFE_2 has no value, then the statement generated will clearly cause a missing expression error:

select nvl(,job) d,nvl(,job) r from emp WHERE deptno=nvl('',dept no)

When P13_STUFE_2 is null, a valid column name must be included in the generated LOV query:

l_lov_sql := 'select ' || nvl(:P13_STUFE_2, 'job') || ' d, ' || nvl(:P13_STUFE_2, 'job') || ' r from emp WHERE deptno=nvl(:P13_STUFE_1, deptno)';

You also need to check/sanitise the value of P13_STUFE_2 to prevent the possibility of tampering with the value leading to SQL injection.