1

I am using oracle sql developer. In that code, I need to use execute immediate statement for creating sequence.

My requirement is to check whether the new sequence to be created is present or not. If it is there, then the code has to replace it. If it is not there, then the code has to create it.

But when I used the create or replace statement, it errors out. If I used create statement alone, its working fine.

Then, I used some case statements to find whether the sequence that we are trying to create exists or not and then I have used the create statement alone to do this.

My question is why we are not able to use the create or replace statement inside execute immediate statement?

Kalyan
  • 358
  • 1
  • 3
  • 10
  • Sounds like you are creating an atomic operation. I did this to prevent race conditions in a concurrent selection on a queue. I'm not sure about Oracle, but this answer seems to have it, "merge": http://stackoverflow.com/questions/9332360/oracle-equivalent-to-mysql-insert-ignore – Flosculus Sep 11 '15 at 08:02

1 Answers1

0

The issue here isn't using execute immediate, it's the statement you're trying to execute. As can be seen in the documentation, Oracle's create sequence syntax simply doesn't have an or replace variant, regardless of how you're executing it.

The two options you have are either to check whether the sequence exists before attempting to re-create it, or just trying to create it and catching the error.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • I can understand how can we work on with only the create statement. But, the execute immediate statement doesn't allows or replace variant. Is this the normal behavior of that statement? – Kalyan Sep 11 '15 at 09:20
  • 1
    @Kalyan The issue has nothing to do with `execute immediate`. There simply is no `or replace` variant. – Mureinik Sep 11 '15 at 09:23