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?

Alex Poole · Accepted Answer · 2013-03-07 09:07:40Z

9

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.

edited Mar 7, 2013 at 9:07

answered Mar 7, 2013 at 8:58

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

run string as query in oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related