TOraScript and NEXTVAL

[redhair]

First you have to create a sequence :

Code: Select all

CREATE SEQUENCE s_test
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE;

If you execute the following SQL statements with a TOraScript component, you get the
error message : ORA-02287:sequence number not allowed here.

Code: Select all

ALTER SEQUENCE s_test INCREMENT BY 100000;
SELECT s_test.NEXTVAL FROM dual;  -- Error ORA-02287
ALTER SEQUENCE s_test INCREMENT BY 1;

Details :
If the DataSet property of the TOraScript component is empty,
you get no error.
But in my TOraScript component the DataSet property
is filled with a TSmartQuery reference.


IDE: C++Builder XE4 Update 1 / ODAC 9.0.2 Professional
Direct mode and OCI mode

Thank you.

[AlexP]

Hello,

We couldn't reproduce the problem. Please check whether your query is correct. This error can occur, for example, when parentheses are inserted after a NEXTVAL expression, i.e.:

SELECT s_test.NEXTVAL FROM dual -- OK
SELECT s_test.NEXTVAL() FROM dual -- ORA-02287

[redhair]

But in my TOraScript component the DataSet property
is filled with a TSmartQuery reference.

After your reply, I've tried to build a testproject
and I've got no SQL-runtime errors at all.
Then I changed the "QueryRecCount" property of the referenced
TSmartQuery to true, the error occurred again.

[AlexP]

Hello,

When enabling the QueryRecCount option, before executing a query, we execute an additional query to retrieve records, i.e.:

Code: Select all

SELECT COUNT(*) FROM (source query)

Oracle doesn't allow using sequences
in subqueries, therefore the error occurs