I have problem with sequences (on Oracle) and .Net provider. For code genereated for ODP.NET everything is ok, but for MS provider for ORACLE not.

When I set "IsIdentity", then I can't save new item to entity (only new item).

Table for example:

CREATE SEQUENCE CONFIG_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 20 NOORDER ;

CREATE TABLE CONFIG ( KEY_ID NUMBER NOT NULL, KEY_NAME VARCHAR2(50) NOT NULL, KEY_VALUE VARCHAR2(1000) NULL, PRIMARY KEY(KEY_ID) ) ;

CREATE TRIGGER BI_CONFIG before insert on "CONFIG" for each row begin

select "CONFIG_SEQ".nextval into :NEW.KEY_ID from dual; end; ;

There is an error when I try to save entity:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of an action query: An exception was caught during the execution of a sequence retrieval query: ORA-00936: missing expression\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="11072006" RuntimeVersion="2.0.0.0" QueryExecuted="\r\n\tQuery: INSERT INTO \"CBS_OWN\".\"CONFIG\" (\"KEY_ID\", \"KEY_NAME\", \"KEY_VALUE\") VALUES (:KeyId1, :KeyName2, :KeyValue3)\r\n\tParameter: :KeyId1 : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.\r\n\tParameter: :KeyName2 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"d\".\r\n\tParameter: :KeyValue3 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"d\".\r\n"

When I don't choose "IsIdentity" then entity saving is successful. When I use code generated for ODP.NET provider then entity saving is successful, but I have to use MS .NET provider for ORACLE.

Could you help me?

Thank you

Jiri Ceska

P.S. I am sorry for my bad english.

Could you disable the trigger, please? If you specify 'isidentity' and select a sequence you can't use triggers on the table to set the sequence.

Thank you for your response.

It's strange. I tried to drop the trigger.

Without tigger (ORA-01400: cannot insert NULL into ):

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of an action query: ORA-01400: cannot insert NULL into (\"CBS_OWN\".\"CONFIG\".\"KEY_ID\")\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="11072006" RuntimeVersion="2.0.0.0"

QueryExecuted="\r\n\tQuery: INSERT INTO \"CBS_OWN\".\"CONFIG\" (\"KEY_ID\", \"KEY_NAME\", \"KEY_VALUE\") VALUES (:KeyId1, :KeyName2, :KeyValue3)\r\n\tParameter: :KeyId1 : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.\r\n\tParameter: :KeyName2 : AnsiString. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: \"q\".\r\n\tParameter: :KeyValue3 : AnsiString. Length: 1.

Console.WriteLine(configEntity.Fields["KeyId"].IsIdentity);

True

Generated code with sequence(CBS_OWN.CONFIG_SEQ) :

/// <summary>Inits ConfigEntity's mappings</summary> private void InitConfigEntityMappings() { base.AddElementMapping( "ConfigEntity", "xxxx", @"CBS_OWN", "CONFIG", 3 ); base.AddElementFieldMapping( "ConfigEntity", "KeyId", "KEY_ID", false, (int)OracleType.Number, 22, 0, 38, true, "CBS_OWN.CONFIG_SEQ", null, typeof(System.Decimal), 0 ); base.AddElementFieldMapping( "ConfigEntity", "KeyName", "KEY_NAME", true, (int)OracleType.VarChar, 50, 0, 0, false, "", null, typeof(System.String), 1 ); base.AddElementFieldMapping( "ConfigEntity", "KeyValue", "KEY_VALUE", true, (int)OracleType.VarChar, 1000, 0, 0, false, "", null, typeof(System.String), 2 );

}

However on real system I can't drop the trigger. I have to use table with the trigger. Is there an another way?

Thank you

Jiri Ceska

If you drop the trigger, you have to select identity + a sequence in the designer.

You have to do that anyway, so please define the field as identity + select a sequence (the sequence teh trigger uses)

If you have to use the trigger, please add this line to the appSettings of the config file of your app: <add key="OracleTriggerSequences" value="true"/>

(this is also documented in 'application configuration through config files' in the documentation )

Thank you. I know <add key="OracleTriggerSequences" value="true"/>. It's fine with ODP.NET provider, but it's bad with MS .net provider for ORACLE (my first post).

Jiri Ceska

Jirka wrote:

Thank you. I know <add key="OracleTriggerSequences" value="true"/>. It's fine with ODP.NET provider, but it's bad with MS .net provider for ORACLE (my first post). Jiri Ceska

I didn't understand from your first post you used this setting with the MS provider, I thought you just used an isidentity marked field with a sequence.

I'll see if I can reproduce it using MS' oracle provider.

When I use the settings, that you give me, than it works only for "Oracle provider". It doesn't work for "MS provider for Oracle". When I call "Save" on your generated code data is saved in database, however during the process of retrieving the value of "new id"(sequence) I get an exception:

An exception was caught during the execution of an action query: An exception was caught during the execution of a sequence retrieval query: ORA-00936: missing expression

Although I didn't want to use "Oracle provider" I did in the end. Just now it works fine with "Oracle provider".

You can consider this done.

I still don't understand why it didn't works on "MS provider for Oracle".

Thank you for your time.

Best regard Jiri Ceska

Jirka wrote:

When I use the settings, that you give me, than it works only for "Oracle provider". It doesn't work for "MS provider for Oracle". When I call "Save" on your generated code data is saved in database, however during the process of retrieving the value of "new id"(sequence) I get an exception:

An exception was caught during the execution of an action query: An exception was caught during the execution of a sequence retrieval query: ORA-00936: missing expression

Although I didn't want to use "Oracle provider" I did in the end. Just now it works fine with "Oracle provider".

You can consider this done.

I still don't understand why it didn't works on "MS provider for Oracle".

Thank you for your time.

I'll still try to fix this for the MS provider. The DQE simply shouldn't create faulty SQL. The two DQEs share the the same code so it's a little odd the query is wrong for one of them, but it might be that the MS provider has some bugs or the query needs to have a different format. I'll check it out.

I can't reproduce it.

HR.LOCATIONS, simple insert:

LocationsEntity location = new LocationsEntity();
location.City = "Den Haag";

Assert.IsTrue( adapter.SaveEntity( location, true, null, true ) );

trigger sequences set to true in config. Trigger defined:

CREATE OR REPLACE TRIGGER "HR"."TR_LOCATIONS"
before insert on HR.LOCATIONS
for each row
begin
    SELECT HR.LOCATIONS_SEQ.NEXTVAL into :NEW.LOCATION_ID from dual;
end;

Using latest runtime lib builds.