Create Oracle sequence using ExecuteNonQuery

Question

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?

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
– Alex Poole, Commented Feb 20, 2018 at 11:28
It's probably easer to put this into a PL/SQL procedure. Similar to this or this — user330315
– user330315, Commented Feb 20, 2018 at 12:23

Dima Yankin · Accepted Answer · 2018-02-20 13:45:02Z

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

edited Feb 20, 2018 at 13:45

user330315

answered Feb 20, 2018 at 11:30

Dima Yankin

4602 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Create Oracle sequence using ExecuteNonQuery

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