How to use dynamic sql in with clause of oracle plsql

Question

I am using with clause in my function where passing table name as parameter. So want use this table name in the query but giving table doesnot exits.Sample query

with EMP_A as(
select EMPNO, SAL 
from EMP 
where DEPTNO in (select DEPTNO from P_TABLE_NAME))

select * from EMP;

Firstly, you cannot have dynamic table name in SQL. You need to (ab)use EXECUTE IMMEDIATE in PL/SL. Secondly, your CTE and tablename are different. Your CTE is emp_a, while the table you are referring to is emp. — Lalit Kumar B
– Lalit Kumar B, Commented Apr 6, 2015 at 12:08

Lalit Kumar B · Accepted Answer · 2015-04-06 13:23:54Z

1

In your below posted query:

 With emp_a as ( 
       select empno,sal 
       from emp 
       where deptno in(select     deptno from p_table_name)
    )
    select * from emp;

Firstly, you cannot have dynamic table name in SQL. Object names must be static. You need to (ab)use EXECUTE IMMEDIATE in PL/SQL to make it a dynamic SQL.
Secondly, your CTE and tablename are different. Your CTE is emp_a, while the table you are referring to is emp.
Thirdly, you use the WITH clause, i.e. subquery factoring when you have to use the subquery multiple times. Therefore, the CTE would act as a temporary taboe to hold the subquery resultset.

See the documentation to learn more about EXECUTE IMMEDIATE.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

UPDATE An example

You could use a string variable to assign the dynamic query.

DECLARE
  p_table_name VARCHAR2(30);
  l_sql     VARCHAR2(32767);
  l_value.  NUMBER;
BEGIN
   p_table_name := 'DEPARTMENT';
   l_sql := 'WITH clause...' || p_table_name || ' with clause contunied';
   EXECUTE IMMEDIATE l_sql INTO l_value;

edited Apr 6, 2015 at 13:23

answered Apr 6, 2015 at 12:12

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ShivaP Over a year ago

Thanks lalit for quick reply.I have wrote here sample code.How to use execute immediate inside the in clause. Please suggest

Lalit Kumar B Over a year ago

See the link I posted. You need to build the dynamic SQL. Then execute immediate the literal.

ShivaP Over a year ago

Hi, I have to use with clause but in this with clause i cannot write execute immeidate sql.

ShivaP Over a year ago

Actaul my requirement is to pass the table name from function paramter this should be in the with clause of inner query.So it is giving error that table not exist.

ShivaP Over a year ago

with EMP_A as( select EMPNO, SAL from EMP where DEPTNO in (execute immediate 'select DEPTNO from ' || P_TABLE_NAME)) select * from EMP_A;

|

Collectives™ on Stack Overflow

How to use dynamic sql in with clause of oracle plsql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related