CODEWITHSUNDEEP
oracle-databaseplsql
ShivaP
ShivaP

Reputation: 29

How to use dynamic sql in with clause of oracle plsql

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;

Upvotes: 0

Views: 2980

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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;

Upvotes: 1

Related Questions