CODEWITHSUNDEEP
oracle-databaseplsql
kiric8494
kiric8494

Reputation: 205

Writing dynamic SQL query using execute immediate

I have written a PLSQL code where I am trying to introduce execute immediate statement but in that need to create SQL statement dynamically

Below is my following statement something is fishy in this not getting executed

tblname := 'emp';
v_tbl   := 'emp_123';

execute immediate 'CREATE TABLE' || tblname || '  AS select * from ' || v_tbl;

Any solution is much appreciated

Upvotes: 0

Views: 156

Answers (2)

Sven Ketz
Sven Ketz

Reputation: 46

While the answer @sachin-padha delivered should be correct, i want to point out the real problem with OTs code:

'CREATE TABLE' || tblname is missing a between the keyword TABLE and the injected table name.
So the executed statement looks like this:
CREATE TABLEemp AS select * from emp_123
This will give a ORA-00901: invalid CREATE command exception

Upvotes: 0

Sachin Padha
Sachin Padha

Reputation: 221

You have not declared the datatype of variables and also make sure that emp_123 table is available in the schema. Below is the sample code:

DECLARE

  Tblname VARCHAR2(6) := 'emp';
  v_Tbl   VARCHAR2(9) := 'emp_123';
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE ' || Tblname || '  AS select * from ' || v_Tbl;
END;

Upvotes: 3

Related Questions