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?
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);