Previous attempt to post seems to have gone missing.

Have a process that imported XML data files correctly using the DAL generated by the previous version of LLBL (1.0.2004.2), upgraded both the Oracle Data Provider and LLBL, regenerated the DAL and the process creates an error when inserting a record.

Current version of LLBL is 1.0.2005.1; current Oracle.DataAccess.dll version is 9.2.0.700, Generated option is Self-service 2 class .Net version 1.1 (VisualBasic)

The error details:

'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: ORA-00972: identifier is too long. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteActionQuery(IActionQuery queryToExecute, ITransaction containingTransaction)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.AddNew(IEntityFields fields, ITransaction containingTransaction)
   at SquadManningDAL.EntityClasses.PERSONEntityBase.InsertEntity()
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.CallInsertEntity()
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PersistQueue(ArrayList queueToPersist, Boolean insertActions, ITransaction transactionToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save(IPredicate updateRestriction, Boolean recurse)
   at SquadManningDAL.EntityClasses.PERSONEntityBase.Save(IPredicate updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save()
   at SquadManning.Business.Components.TransferBC.StorePERSON(DataRow& pidrData, Transaction& TransferTransaction, Boolean pblnIsImport) in C:\SquadManning\Components\TransferBC.vb:line 7489
   at ssptTransfer.TransferRun.ImportXMLFile(String pistrFilename, StreamWriter& w) in C:\SquadManning\SSPTTransfer\frmTransferRun.vb:line 604'

ORA-00972 in Oracle means that one of the TableNames or ColumnNames is judged to be more that 30 characters long. Neither is the case. Studying the SQL being issued to the database revealed that the DAL was adding quotes to the column names, and maybe Oracle was including these in the count of characters of the column name. There were 3 columnnames of 29 characters so squeezed one of these to 28, regenerated the DAL, ran the process and the offending save did not error ! Crashed the process, amended the other two columnnames to be less than 28, regenerated the DAL, ran the process, the process errored at same previously ffending Save statement.

Any suggestions gratefully received, Paul.

The quotes aren't taken into account, is my understanding from the Oracle docs.

Could you post the query, if possible, and explain which columns you renamed which worked and which didn't after a rename?

Many thanks for the fast response. Sorry about the smileys - don't know how to turn them off !

Original Table:

CREATE TABLE Person ( per_identifier NUMBER(10) NOT NULL CONSTRAINT per_pk PRIMARY KEY USING INDEX TABLESPACE index01, per_OfficialNumber_text VARCHAR2 (255) CONSTRAINT per_ono_uni UNIQUE USING INDEX TABLESPACE index01, per_Surname_text VARCHAR2 (255), per_Initials_text VARCHAR2 (255), per_CurrentGender_text VARCHAR2 (255), per_Birth_date DATE NOT NULL, per_MinimumTimeAshore NUMBER (10), per_MRGTransition_date DATE NOT NULL, per_TaggedWMOUnit_code NUMBER (10), per_Forenames_text VARCHAR2 (255), per_CurrentMaritalStatus_text VARCHAR2 (255), ser_identifier NUMBER (10) NOT NULL CONSTRAINT per_ser_fk REFERENCES Ser (ser_identifier), LastUpdatedDate TIMESTAMP, LastUpdatedBy VARCHAR2 (255), Deleted_Indicator VARCHAR2 (1)) TABLESPACE data01 /

per_CurrentMaritalStatus_text renamed per_CurrentMaritalStatus_txt on the database (in Designer I left the 'fieldname' as per_CurrentMaritalStatus_text so that I don't have to rework the code, but the target has changed to the new name).

Query that is failing:

INSERT INTO "SMMOB1"."PERSON" ("PER_IDENTIFIER", "PER_OFFICIALNUMBER_TEXT", "PER_SURNAME_TEXT", "PER_INITIALS_TEXT", "PER_CURRENTGENDER_TEXT", "PER_BIRTH_DATE", "PER_MINIMUMTIMEASHORE", "PER_MRGTRANSITION_DATE", "PER_TAGGEDWMOUNIT_CODE", "PER_FORENAMES_TEXT", "PER_CURRENTMARITALSTATUS_TXT", "SER_IDENTIFIER", "LASTUPDATEDDATE", "LASTUPDATEDBY", "DELETED_INDICATOR", "PER_ONBOARD_IND", "PER_DISCHARGED_DATE") VALUES (:PER_IDENTIFIER1, :PER_OFFICIALNUMBER_TEXT2, :PER_SURNAME_TEXT3, :PER_INITIALS_TEXT4, :PER_CURRENTGENDER_TEXT5, :PER_BIRTH_DATE6, :PER_MINIMUMTIMEASHORE7, :PER_MRGTRANSITION_DATE8, :PER_TAGGEDWMOUNIT_CODE9, :PER_FORENAMES_TEXT10, :PER_CURRENTMARITALSTATUS_TEXT11, :SER_IDENTIFIER12, :LASTUPDATEDDATE13, :LASTUPDATEDBY14, :DELETED_INDICATOR15, :PER_ONBOARD_IND16, :PER_DISCHARGED_DATE17)
    Parameter: :PER_IDENTIFIER1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17.
    Parameter: :PER_OFFICIALNUMBER_TEXT2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: D242629X.
    Parameter: :PER_SURNAME_TEXT3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: SDYTBGWWQKXIOVMVOQD441.
    Parameter: :PER_INITIALS_TEXT4 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: D.
    Parameter: :PER_CURRENTGENDER_TEXT5 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :PER_BIRTH_DATE6 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 31/12/4712 00:00:00.
    Parameter: :PER_MINIMUMTIMEASHORE7 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 266.
    Parameter: :PER_MRGTRANSITION_DATE8 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 01/10/2002 00:00:00.
    Parameter: :PER_TAGGEDWMOUNIT_CODE9 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :PER_FORENAMES_TEXT10 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :PER_CURRENTMARITALSTATUS_TEXT11 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :SER_IDENTIFIER12 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
    Parameter: :LASTUPDATEDDATE13 : DateTime. Length: 0. Precision: 0. Scale: 6. Direction: Input. Value: 21/10/2005 11:37:07.
    Parameter: :LASTUPDATEDBY14 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: pastakeon.
    Parameter: :DELETED_INDICATOR15 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :PER_ONBOARD_IND16 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :PER_DISCHARGED_DATE17 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.

Happy to provide the details of the other 2 columns that I renamed, however the XML tables containing these other columns are processed well after Person, so I am not sure whether that will help.

I think it's the PER_CURRENTMARITALSTATUS_TEXT11 parameter, at least that's the only > 30 char element in the query. THe parameter name is retrieved from the field name + a number to make it unique.

I can change that in the DQE for you, but I've to do some tests to see if this is really the issue. I'll check it out. Either way, if you rename the field to a shorter name, it should work (and you did that and it indeed did work, so I think it's the parameter name). What I wonder though is: why did it work with 1.0.2004.2, as the parameter naming scheme hasn't changed...

It's indeed the parameter. I'll see what I can do to fix this.

(edit) Fixed in next build. A parameter which will grow in length over the 30 char limit, will be called LGPPARAM_ instead and the unique value is appended to that so no dupes are occuring. THis won't affect query execution, it will affect query readability if you're tracing the code, but that's not solvable I think in this case. The parameter isn't chopped off at pos 27 because it can lead to dupes potentially.

I have renamed all of the 29-long field names on the db, and renamed the FieldName in the designer so that they are 28 or less. Process now works.

Still unsure why it fails now and worked before - perhaps the new Oracle Data Provider is having some impact ?

Many thanks for your help. Paul

PaulH wrote:

I have renamed all of the 29-long field names on the db, and renamed the FieldName in the designer so that they are 28 or less. Process now works.

Still unsure why it fails now and worked before - perhaps the new Oracle Data Provider is having some impact ?

Many thanks for your help. Paul

I have no idea, but it could be indeed that it allowed parameters of length > 30 before but now it doesn't.

Anyway, with the new Oracle DQE you'll never run into this issue again, it will manage the parameter names for you and will make sure they're never too long.