Forum Stats

  • 3,837,502 Users
  • 2,262,265 Discussions
  • 7,900,307 Comments

Discussions

hierarchical list of values - 3 levels

siegwin.port
siegwin.port Member Posts: 811 Bronze Badge
edited Nov 9, 2013 7:20AM in APEX Discussions

Hi,

I have to create a hierarchical list of values over 3 levels

1. Level: (called P13_STUFE_1)

select loc d,deptno r

from dept

order by 1

2. Level (called P13_STUFE_2)

select column_name d,column_name r from user_tab_columns

where table_name='EMP'

cascading LOV parent Item P13_STUFE_1

Page Items to submit P13_STUFE_1

3. Level (called P13_STUFE_3)

Depending on the results of LOV 1 and LOV 2 there should be a LOV shwing  the content of the selected column and department like

P13_STUFE1 = 10

P13_STUFE_2 = ENAME

P13_STUFE_3 =

                                   CLARK

                                   KING

                                   MILLER

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

Regards

Siegwin

Tagged:

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,095 Red Diamond
    Answer ✓
    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.

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,095 Red Diamond
    Answer ✓
    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.

This discussion has been closed.