run string as query in oracle

Question

i got a little problem in Oracle. I try to create a sequence for generating IDs in a table that already has data in it. I try to use the following anonymous block.

declare y varchar2(2000);
BEGIN
  SELECT 'CREATE SEQUENCE ID_SEQ MINVALUE 1 MAXVALUE 9999999999 START WITH ' || (max(ID)+1) || ' INCREMENT BY 1 CACHE 20;' INTO y FROM TEST_TABLE;
  --dbms_output.put_line(y);
  execute immediate y;
end;

I get the following error:

Error report:
ORA-00911: invalid character
ORA-06512: at line 5
00911. 00000 -  "invalid character"

If I execute the value of the y variable it works perfectly. I'm using SQL Developer as input interface and working on a 11g r2 Oracle server. I found similar codes where 'INCREMENT BY' parameter were script generated. Can someone explain my error?

Answer 1

When you execute immediate, the command you run should not have a semi-colon on the end; that is a command separator in SQL Developer (and SQL*Plus, and other clients), not part of the SQL statement itself.

SELECT 'CREATE SEQUENCE ID_SEQ MINVALUE 1 MAXVALUE 9999999999 START WITH '
    || (max(ID)+1) || ' INCREMENT BY 1 CACHE 20' INTO y FROM TEST_TABLE;

This is shown in the examples for plain SQL. Just to help confuse you though, if you are using PL/SQL within the dynamic SQL, you do still need semi-colons appropriate to PL/SQL itself - though not the executing / you'd use to run it directly from a client. This is shown in other examples.