Oracle: SQL Dynamic cursor statement

Question

I have a dynamic temporary table like below.

Table name for assumption: TB_EMP_TEMP_TABLE

Column1 | column2      | column3
Emp_NM  | EMP_ID       |TB_EMP_DTLS
Emp_Adr | EMP_ID       |TB_EMP_DTLS
Emp_Sal | EMP_ID       |TB_EMP_OTHER

The above data is retrieved as a Cursor(Emp_cursor) and i need to construct a dynamic SQL Query as below based on cursor data.

Expected Output:

SELECT TB_EMP_DTLS.EMP_NM,TB_EMP_DTLS.EMP_Adr,TB_EMP_OTHER.EMP_SAL 
FROM TB_EMP_DTLS,TB_EMP_OTHER 
WHERE TB_EMP_DTLS.EMP_ID=TB_EMP_OTHER.EMP_ID

I havent worked extensively on PLSQL/Cursor concepts. How the cursor can be looped to get expected output.

How do you know which column to use as a join condition? Can you rely on the column name and always join on columns with the same name? Also, please post the way your cursor is cedined, opened, ... so that people can understand your starting point — Aleksej
– Aleksej, Commented Feb 22, 2017 at 6:59

mrkovec · Accepted Answer · 2017-02-27 10:56:28Z

If i understand it right, you want column1 values selected from column3 tables joined by column2 columns.

It's not elegant but should work:

select listagg(v, ' ') within group (order by n asc) my_cursor from (
    with 
        tb as (select distinct column3 val from tb_emp_temp_table), --tables
        sl as (select distinct column3||'.'||column1 val from tb_emp_temp_table), --selected columns
        pr as (select distinct column3||'.'||column2 val from tb_emp_temp_table) --predicates
    select 1 n, 'SELECT' v from dual
    union
    select 2 n, listagg(val, ', ') within group (order by val) v from sl  
    union 
    select 3 n, 'FROM' v from dual
    union
    select 4 n, listagg(val, ', ')  within group (order by val) v from tb  
    union 
    select 5 n, 'WHERE' v from dual
    union
    select 6 n, listagg(pra.val||'='||prb.val, ' AND ')  within group (order by pra.val) v from pr pra, pr prb where pra.val != prb.val
)

Collectives™ on Stack Overflow

Oracle: SQL Dynamic cursor statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related