Hi Shalex,
thank you very much for your Response, but I fear my explanations where insufficient.
I didn't "complain" about the Generation of SQL queries to retrieve data from the database, but about the ones generating the database tables.
But let's start with your questions:
(1) Exact Version of Oracle Server (according to select * from v$version):
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
INS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
(2) Value of the Provider manifest token: "Oracle, 11.2.0.2"
(3): As mentioned above I don't complain about the SQL Queries generated for INSERT/UPDATE/SELECT queries, but about the ones generated for the Schema Definition.
When I open the edml model in Entity Developer, and "Generate Database Script from Model" I get the following code generated (this is for the one table that is affected):
Code: Select all
-- Script was generated by Devart Entity Developer, Version 5.7.500.0
-- Script date 15.12.2014 09:22:56
-- Target Server: Oracle
-- Server Version: 11gR2
--
-- Creating a table CRITMANTEST.BOTTLENECK
--
CREATE TABLE CRITMANTEST.BOTTLENECK (
GLOBALBNID NUMBER(19) NOT NULL,
BNID NUMBER(19) DEFAULT Bottleneck_SEQ.curval NOT NULL,
SCID NUMBER NOT NULL,
PLANT NUMBER(19) NOT NULL,
BASETAKT NUMBER(19) NULL,
SUBSECTION NUMBER(19) NULL,
ISHARD NUMBER(1) NOT NULL,
CREATED TIMESTAMP(6) NOT NULL,
CREATOR VARCHAR2(32 CHAR) NOT NULL,
MAXCOUNT NUMBER(19) NOT NULL,
INTERVAL NUMBER(19) NOT NULL,
"COMMENT" VARCHAR2(1000 CHAR) NULL,
ISDENSITYCONSTR NUMBER(1) NOT NULL,
WORKAREA NUMBER(19) NULL,
STATION NUMBER(19) NULL,
STARTPAT NUMBER(10) NOT NULL,
ISACTIVE NUMBER(1) NOT NULL,
INPRODUCTION NUMBER(1) NOT NULL,
DBDATE TIMESTAMP(6) NOT NULL,
DBACTION VARCHAR2(6 CHAR) NOT NULL,
CRITERIONID CHAR(4) NOT NULL,
ENDPAT NUMBER(10) NOT NULL,
CONSTRAINT PK_BOTTLENECK PRIMARY KEY (GLOBALBNID)
);
--
-- Creating a sequence CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq"
--
CREATE SEQUENCE CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq" START WITH 1 INCREMENT BY 1;
--
-- Creating a trigger CRITMANTEST.AI$BOTTLENECK
--
CREATE OR REPLACE TRIGGER CRITMANTEST.AI$BOTTLENECK
BEFORE INSERT ON CRITMANTEST.BOTTLENECK
FOR EACH ROW
BEGIN
SELECT CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq".NEXTVAL INTO :NEW.GLOBALBNID FROM DUAL;
END;
/
The BNID column of this table should get as a default value the current value of the Bottleneck_SEQ sequence (which is just another sequence, that could be shared by multiple tables or, as in my case, only be used when there is no other value given, thus it's the DEFAULT, not the generated value).
Please don't insist on this being not useful or something like that, in fact it is useful as I trick EF to push new rows into the table instead of updating existing ones whenever an entity is changed. Nevertheless that's not relevant for this question.
My Problem is the Generation of the table with that column Definition as
"BNID NUMBER(19) DEFAULT Bottleneck_SEQ.curval NOT NULL", because Oracle starts to support sequence values as default values in the create table statement afaik with Version 12, thus this script, which states to be generated for target Version 11gR2 is not valid for that Server Version.
A valid script instead should omit the default value in the CREATE TABLE and instead set the default value in the Trigger, like:
Code: Select all
CREATE OR REPLACE TRIGGER "CRITMANTEST.AI$BOTTLENECK"
BEFORE INSERT ON CRITMANTEST.BOTTLENECK
FOR EACH ROW
BEGIN
SELECT CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq".NEXTVAL INTO :NEW.GLOBALBNID FROM DUAL;
IF :NEW.BNID IS NULL
THEN select BOTTLENECK_SEQ.curval into :new.bnid from dual;
END IF;
END;
The existing solution IMHO is the better way for Oracle Version 12 and above, but it's invalid for older Versions.
As an additional note: for constant default values it is possible to set them as default in earlier Versions as well, thus the Generation strategy works for the default values as supported by Standard edmx models, but not with the Extended Devart-Default values using functions, sequences or similar.
regards
Peter