run string as query in oracle

execute-immediate, ora-06512, oracle, sequence

Solution

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.

Problem

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?

Original source