I am able to fetch the respective row data but I am not able to form the correct pl/sql query to get emp names based on department. For example if I have 5 employees under dept1, then I should be able to pass that input as below.
java -jar test.jar -dept_name dept1 - emp_name emp1,emp2,emp3,emp4,emp5
Wait there is something I am missing here
- the pl/sql code should fetch the department names of all the selected rows from emp table.
- Next it should fetch employess names under each department for the selected checkboxes.
Where should the result of this be displaye?
first dept_names should be fetched as a cursor. And then upon looping the cursor, i should get emp_names for that department into a variable using listagg
That is not what i am asking.
where do you see the result of your selection after processing?
and is the ref cursor a must?
I am printing the clob and checking the input being passed to the jar. No cursor is not a must. myCLOB:=myCLOB||'java -jar test.jar -dept_name="'||dept_names_REC.dept_name||'" -emp_names="'||str_emp_names||'" apex_debug.info('myCLOB'||myCLOB);
please, could you describe your requirements in more detail (ideally either on known emp/dept tables and use existing column names, or use select some values from dual)? It's not obvious (in fact it's confusing and does not correspond with your requirements) why you're using an outer loop and two inner loops.
Also it's not that easy to call a java program (if you really intend to invoke an external Java program). It cannot be invoked directly, you have to create a (scheduler) job to execute a Java program and pass the necessary parameters into this job.
Hi All, Many thanks for your inputs.This issue is resolved by using cursors for the APEX_APPLICATION.G_F01 array.
select dept_name,listagg(emp_name,',') WITHIN GROUP (ORDER BY emp_name) emp_names from emp where instr(':'||APEX_UTIL.TABLE_TO_STRING(APEX_APPLICATION.G_F01)||':',':'||emp.ID||':') >0 group by dept_name;