CODEWITHSUNDEEP
sqloracle-databasesequences
Omar.Ebrahim
Omar.Ebrahim

Reputation: 874

Create Oracle sequence using ExecuteNonQuery

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?

Upvotes: 1

Views: 139

Answers (1)

Dima Yankin
Dima Yankin

Reputation: 460

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

Upvotes: 2

Related Questions