1

Trying to create a sequence in Oracle using the ExecuteNonQuery

The SQL is

col a new_value SEQ
SELECT NVL(MAX(ID)+1,1) a FROM DBUSER.TABLENAME;
CREATE SEQUENCE DBUSER.SEQ_ACCOUNT
INCREMENT BY 1 START WITH &SEQ NOCACHE

Ideally, what I'm trying to do is create a sequence where the start value is the next available value in the column (so 2 if the ID is 1 etc)

But I'm getting the ORA-00900 Invalid SQL error. This runs fine in SQL developer and SQL plus so I know it's valid SQL. Is it something that can only be run in SQL Developer and SQL Plus?

Omar.Ebrahim
  • 862
  • 1
  • 10
  • 30
  • 3
    It's two SQL statements (one DML, one DDL) and you're probably only allowed to execute one at a time; `col[umn]` is a *client* command, and the `&` is a *client* substitution variable..Those mean something to SQL Developer and SQL\*Plus, but not all clients, and not for this kind of call. Is there a reason you're not querying the current maximum with one (query) call, then doing a second (non-query) call to create the sequence? – Alex Poole Feb 20 '18 at 11:28
  • 1
    It's probably easer to put this into a PL/SQL procedure. Similar to [this](https://stackoverflow.com/q/18072966/330315) or [this](https://stackoverflow.com/q/51470/330315) –  Feb 20 '18 at 12:23

1 Answers1

2

col a new_value SEQ only works in Oracle native clients like SQLPlus, or SQLDeveloper. You should use some other way to retrieve result of NVL(MAX(ID)+1,1).

Of course there can be other errors with using ExecuteNonQuery. It's hard to say without actual code.

UPDATE:

Here is the working example for you. It is made by using anonymous PL-SQL block in query text:

cmd.CommandText = "declare v_sql varchar2(255); begin select 'create sequence DBUSER.SEQ_ACCOUNT start with ' || NVL(MAX(t.ID)+1,1) || ' increment by 1 nocache'  into v_sql from DBUSER.TABLENAME t; execute immediate v_sql; end;";
cmd.ExecuteOracleNonQuery(out os);
Dima Yankin
  • 365
  • 1
  • 12