Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
hierarchical list of values - 3 levels

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
Best 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
-
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.