0

I am trying to create a table inside a procedure but getting some issues related to quote identifier or may be something else.

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable';
END;

This's working fine however I need to create my table as

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1='XXX' and col2='Y'';
END;

above statement is throwing errors.

I tried to change like this but this is also not working.

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1="XXX" and col2="Y"';
END;

1 Answer 1

1
begin
  execute immediate 'create table t1 as select * from dba_users where username  =''BP''';
end;
/

SQL> select username from t1;

USERNAME
----------
BP

Double quotes (") have a different meaning than having mutliple single quotes after each other (''). Use this:

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1=''XXX'' and col2=''Y''';
END;
2
  • how to execute multiple create statements in procedure.. for example if I have to execute similar types of statement s as shown above Jan 10, 2020 at 16:46
  • 1
    @vikrantrana Specify multiple execute immediate statements. Jan 10, 2020 at 17:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.