-2
begin
execute immediate select ename, 
                         empno 
                  from  emp 
                  where ename !='James';
end;
/

getting

ORA-00904: invalid identifier error while executing above code.

And is that possible to have multiple execute immediate statements create table , drop table and create indexes also ?

APC
  • 144,005
  • 19
  • 170
  • 281
Vijay
  • 21
  • 1
  • 6

1 Answers1

2

Well, what you wrote is invalid, but something like this isn't:

SQL> set serveroutput on
SQL> declare
  2    l_ename emp.ename%type;
  3    l_empno emp.empno%type;
  4  begin
  5    execute immediate 'select ename, empno from emp where ename = ''KING'''
  6      into l_ename, l_empno;
  7
  8    dbms_output.put_line(l_ename ||' '|| l_empno);
  9  end;
 10  /
KING 7839

PL/SQL procedure successfully completed.

SQL>

You could have left it without the INTO clause, but - the result would go nowhere:

execute immediate 'select ename, empno from emp where ename = ''KING''';

Multiple execute immediates - why wouldn't they be possible?

SQL> begin
  2    execute immediate 'create table test (id number, name varchar2(10))';
  3    execute immediate 'create index i1_test_id on test (id)';
  4    execute immediate 'drop table test';
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks that works. But for the below code it throws error: begin execute immediate 'CREATE TABLE emp as SELECT emp_name, substr(XMLAGG(XMLELEMENT(emp_group, nvl2(DA17.emp_sypp_name, ',' || DA17.emp_supp_name, '')) ORDER BY RECORD_NUMBER).extract ('//text()').getClobVal(),2) AS emp_supp_name FROM emp17da17 WHERE DA17.emp_supp_name IS NOT NULL GROUP BY DA17.emp_supp_name'; end;/ – Vijay Jul 26 '18 at 05:51
  • 1
    @Vijay - "throws an error" isn't helpful, but for a start you need ro escape single quotes within the statement string, as shown in the answer. But you also need to consider if you really want to do this sort of thing in PL/SQL (and therefore dynamically) at all. – Alex Poole Jul 26 '18 at 06:21