Oracle ORA-02287: sequence number not allowed here

Oracle ORA-02287: sequence number not allowed here

Get Social!

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

You can now query the view without any pesky ORAs.

See the test script here.


Related posts:


Leave a Reply

Visit our advertisers

Search

Quick Poll

Do you use GlusterFS in your workplace?

Visit our advertisers