CODEWITHSUNDEEP
sqloracle-databaseplsqlcursordynamic-sql
Karthik
Karthik

Reputation: 145

Oracle: SQL Dynamic cursor statement

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.

Upvotes: 0

Views: 125

Answers (1)

mrkovec
mrkovec

Reputation: 376

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
)

Upvotes: 1

Related Questions