Community
Participate
Working Groups
lonneke.dikmans@vennster.nl reports: I now run into another weird problem: i managed to generate a war file for a PL/SQL API that is part of the E-Business Suite. It is part of the PAY_ELEMENT_ENTRY package. Now when I call this procedure using DBWS, it generates a call to the pl/sql package with names that are too long.... it gives me the following error: <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> <env:Body> <env:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/"> <faultcode>ns0:Server</faultcode> <faultstring>PAY_ELEMENT_ENTRY_API failed: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-06550: line 11, column 3: PLS-00114: identifier 'P_COST_ALLOCATION_KEYFLEX_ID_T' too long ORA-06550: line 19, column 3: PLS-00114: identifier 'P_PERSONAL_PAYMENT_METHOD_ID_T' too long ORA-06550: line 71, column 3: PLS-00114: identifier 'P_ENTRY_INFORMATION_CATEGORY_T' too long ORA-06550: line 109, column 408: PLS-00114: identifier 'P_COST_ALLOCATION_KEYFLEX_ID_T' too long ORA-06550: line 109, column 762: PLS-00114: identifier 'P_PERSONAL_PAYMENT_METHOD_ID_T' too long ORA-06550: line 109, column 2886: PLS-00114: identifier 'P_ENTRY_INFORMATION_CATEGORY_T' too long Error Code: 6550 Call: DECLARE P_VALIDATE_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); P_EFFECTIVE_DATE_TARGET DATE := :2; P_BUSINESS_GROUP_ID_TARGET NUMERIC := :3; P_ORIGINAL_ENTRY_ID_TARGET NUMERIC := :4; P_ASSIGNMENT_ID_TARGET NUMERIC := :5; P_ELEMENT_LINK_ID_TARGET NUMERIC := :6; P_ENTRY_TYPE_TARGET VARCHAR(255) := :7; P_CREATOR_TYPE_TARGET VARCHAR(255) := :8; P_COST_ALLOCATION_KEYFLEX_ID_TARGET NUMERIC := :9; P_UPDATING_ACTION_ID_TARGET NUMERIC := :10; P_UPDATING_ACTION_TYPE_TARGET VARCHAR(255) := :11; P_COMMENT_ID_TARGET NUMERIC := :12; P_REASON_TARGET VARCHAR(255) := :13; P_TARGET_ENTRY_ID_TARGET NUMERIC := :14; P_SUBPRIORITY_TARGET NUMERIC := :15; P_DATE_EARNED_TARGET DATE := :16; P_PERSONAL_PAYMENT_METHOD_ID_TARGET NUMERIC := :17; P_ATTRIBUTE_CATEGORY_TARGET VARCHAR(255) := :18; P_ATTRIBUTE1_TARGET VARCHAR(255) := :19; P_ATTRIBUTE2_TARGET VARCHAR(255) := :20; P_ATTRIBUTE3_TARGET VARCHAR(255) := :21; P_ATTRIBUTE4_TARGET VARCHAR(255) := :22; P_ATTRIBUTE5_TARGET VARCHAR(255) := :23; P_ATTRIBUTE6_TARGET VARCHAR(255) := :24; P_ATTRIBUTE7_TARGET VARCHAR(255) := :25; P_ATTRIBUTE8_TARGET VARCHAR(255) := :26; P_ATTRIBUTE9_TARGET VARCHAR(255) := :27; P_ATTRIBUTE10_TARGET VARCHAR(255) := :28; P_ATTRIBUTE11_TARGET VARCHAR(255) := :29; P_ATTRIBUTE12_TARGET VARCHAR(255) := :30; P_ATTRIBUTE13_TARGET VARCHAR(255) := :31; P_ATTRIBUTE14_TARGET VARCHAR(255) := :32; P_ATTRIBUTE15_TARGET VARCHAR(255) := :33; P_ATTRIBUTE16_TARGET VARCHAR(255) := :34; P_ATTRIBUTE17_TARGET VARCHAR(255) := :35; P_ATTRIBUTE18_TARGET VARCHAR(255) := :36; P_ATTRIBUTE19_TARGET VARCHAR(255) := :37; P_ATTRIBUTE20_TARGET VARCHAR(255) := :38; P_INPUT_VALUE_ID1_TARGET NUMERIC := :39; P_INPUT_VALUE_ID2_TARGET NUMERIC := :40; P_INPUT_VALUE_ID3_TARGET NUMERIC := :41; P_INPUT_VALUE_ID4_TARGET NUMERIC := :42; P_INPUT_VALUE_ID5_TARGET NUMERIC := :43; P_INPUT_VALUE_ID6_TARGET NUMERIC := :44; P_INPUT_VALUE_ID7_TARGET NUMERIC := :45; P_INPUT_VALUE_ID8_TARGET NUMERIC := :46; P_INPUT_VALUE_ID9_TARGET NUMERIC := :47; P_INPUT_VALUE_ID10_TARGET NUMERIC := :48; P_INPUT_VALUE_ID11_TARGET NUMERIC := :49; P_INPUT_VALUE_ID12_TARGET NUMERIC := :50; P_INPUT_VALUE_ID13_TARGET NUMERIC := :51; P_INPUT_VALUE_ID14_TARGET NUMERIC := :52; P_INPUT_VALUE_ID15_TARGET NUMERIC := :53; P_ENTRY_VALUE1_TARGET VARCHAR(255) := :54; P_ENTRY_VALUE2_TARGET VARCHAR(255) := :55; P_ENTRY_VALUE3_TARGET VARCHAR(255) := :56; P_ENTRY_VALUE4_TARGET VARCHAR(255) := :57; P_ENTRY_VALUE5_TARGET VARCHAR(255) := :58; P_ENTRY_VALUE6_TARGET VARCHAR(255) := :59; P_ENTRY_VALUE7_TARGET VARCHAR(255) := :60; P_ENTRY_VALUE8_TARGET VARCHAR(255) := :61; P_ENTRY_VALUE9_TARGET VARCHAR(255) := :62; P_ENTRY_VALUE10_TARGET VARCHAR(255) := :63; P_ENTRY_VALUE11_TARGET VARCHAR(255) := :64; P_ENTRY_VALUE12_TARGET VARCHAR(255) := :65; P_ENTRY_VALUE13_TARGET VARCHAR(255) := :66; P_ENTRY_VALUE14_TARGET VARCHAR(255) := :67; P_ENTRY_VALUE15_TARGET VARCHAR(255) := :68; P_ENTRY_INFORMATION_CATEGORY_TARGET VARCHAR(255) := :69; P_ENTRY_INFORMATION1_TARGET VARCHAR(255) := :70; P_ENTRY_INFORMATION2_TARGET VARCHAR(255) := :71; P_ENTRY_INFORMATION3_TARGET VARCHAR(255) := :72; P_ENTRY_INFORMATION4_TARGET VARCHAR(255) := :73; P_ENTRY_INFORMATION5_TARGET VARCHAR(255) := :74; P_ENTRY_INFORMATION6_TARGET VARCHAR(255) := :75; P_ENTRY_INFORMATION7_TARGET VARCHAR(255) := :76; P_ENTRY_INFORMATION8_TARGET VARCHAR(255) := :77; P_ENTRY_INFORMATION9_TARGET VARCHAR(255) := :78; P_ENTRY_INFORMATION10_TARGET VARCHAR(255) := :79; P_ENTRY_INFORMATION11_TARGET VARCHAR(255) := :80; P_ENTRY_INFORMATION12_TARGET VARCHAR(255) := :81; P_ENTRY_INFORMATION13_TARGET VARCHAR(255) := :82; P_ENTRY_INFORMATION14_TARGET VARCHAR(255) := :83; P_ENTRY_INFORMATION15_TARGET VARCHAR(255) := :84; P_ENTRY_INFORMATION16_TARGET VARCHAR(255) := :85; P_ENTRY_INFORMATION17_TARGET VARCHAR(255) := :86; P_ENTRY_INFORMATION18_TARGET VARCHAR(255) := :87; P_ENTRY_INFORMATION19_TARGET VARCHAR(255) := :88; P_ENTRY_INFORMATION20_TARGET VARCHAR(255) := :89; P_ENTRY_INFORMATION21_TARGET VARCHAR(255) := :90; P_ENTRY_INFORMATION22_TARGET VARCHAR(255) := :91; P_ENTRY_INFORMATION23_TARGET VARCHAR(255) := :92; P_ENTRY_INFORMATION24_TARGET VARCHAR(255) := :93; P_ENTRY_INFORMATION25_TARGET VARCHAR(255) := :94; P_ENTRY_INFORMATION26_TARGET VARCHAR(255) := :95; P_ENTRY_INFORMATION27_TARGET VARCHAR(255) := :96; P_ENTRY_INFORMATION28_TARGET VARCHAR(255) := :97; P_ENTRY_INFORMATION29_TARGET VARCHAR(255) := :98; P_ENTRY_INFORMATION30_TARGET VARCHAR(255) := :99; P_OVERRIDE_USER_ENT_CHK_TARGET VARCHAR(255) := :100; P_EFFECTIVE_START_DATE_TARGET DATE; P_EFFECTIVE_END_DATE_TARGET DATE; P_ELEMENT_ENTRY_ID_TARGET NUMERIC; P_OBJECT_VERSION_NUMBER_TARGET NUMERIC; P_CREATE_WARNING_TARGET BOOLEAN; BEGIN PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY(P_VALIDATE=>P_VALIDATE_TARGET, P_EFFECTIVE_DATE=>P_EFFECTIVE_DATE_TARGET, P_BUSINESS_GROUP_ID=>P_BUSINESS_GROUP_ID_TARGET, P_ORIGINAL_ENTRY_ID=>P_ORIGINAL_ENTRY_ID_TARGET, P_ASSIGNMENT_ID=>P_ASSIGNMENT_ID_TARGET, P_ELEMENT_LINK_ID=>P_ELEMENT_LINK_ID_TARGET, P_ENTRY_TYPE=>P_ENTRY_TYPE_TARGET, P_CREATOR_TYPE=>P_CREATOR_TYPE_TARGET, P_COST_ALLOCATION_KEYFLEX_ID=>P_COST_ALLOCATION_KEYFLEX_ID_TARGET, P_UPDATING_ACTION_ID=>P_UPDATING_ACTION_ID_TARGET, P_UPDATING_ACTION_TYPE=>P_UPDATING_ACTION_TYPE_TARGET, P_COMMENT_ID=>P_COMMENT_ID_TARGET, P_REASON=>P_REASON_TARGET, P_TARGET_ENTRY_ID=>P_TARGET_ENTRY_ID_TARGET, P_SUBPRIORITY=>P_SUBPRIORITY_TARGET, P_DATE_EARNED=>P_DATE_EARNED_TARGET, P_PERSONAL_PAYMENT_METHOD_ID=>P_PERSONAL_PAYMENT_METHOD_ID_TARGET, P_ATTRIBUTE_CATEGORY=>P_ATTRIBUTE_CATEGORY_TARGET, P_ATTRIBUTE1=>P_ATTRIBUTE1_TARGET, P_ATTRIBUTE2=>P_ATTRIBUTE2_TARGET, P_ATTRIBUTE3=>P_ATTRIBUTE3_TARGET, P_ATTRIBUTE4=>P_ATTRIBUTE4_TARGET, P_ATTRIBUTE5=>P_ATTRIBUTE5_TARGET, P_ATTRIBUTE6=>P_ATTRIBUTE6_TARGET, P_ATTRIBUTE7=>P_ATTRIBUTE7_TARGET, P_ATTRIBUTE8=>P_ATTRIBUTE8_TARGET, P_ATTRIBUTE9=>P_ATTRIBUTE9_TARGET, P_ATTRIBUTE10=>P_ATTRIBUTE10_TARGET, P_ATTRIBUTE11=>P_ATTRIBUTE11_TARGET, P_ATTRIBUTE12=>P_ATTRIBUTE12_TARGET, P_ATTRIBUTE13=>P_ATTRIBUTE13_TARGET, P_ATTRIBUTE14=>P_ATTRIBUTE14_TARGET, P_ATTRIBUTE15=>P_ATTRIBUTE15_TARGET, P_ATTRIBUTE16=>P_ATTRIBUTE16_TARGET, P_ATTRIBUTE17=>P_ATTRIBUTE17_TARGET, P_ATTRIBUTE18=>P_ATTRIBUTE18_TARGET, P_ATTRIBUTE19=>P_ATTRIBUTE19_TARGET, P_ATTRIBUTE20=>P_ATTRIBUTE20_TARGET, P_INPUT_VALUE_ID1=>P_INPUT_VALUE_ID1_TARGET, P_INPUT_VALUE_ID2=>P_INPUT_VALUE_ID2_TARGET, P_INPUT_VALUE_ID3=>P_INPUT_VALUE_ID3_TARGET, P_INPUT_VALUE_ID4=>P_INPUT_VALUE_ID4_TARGET, P_INPUT_VALUE_ID5=>P_INPUT_VALUE_ID5_TARGET, P_INPUT_VALUE_ID6=>P_INPUT_VALUE_ID6_TARGET, P_INPUT_VALUE_ID7=>P_INPUT_VALUE_ID7_TARGET, P_INPUT_VALUE_ID8=>P_INPUT_VALUE_ID8_TARGET, P_INPUT_VALUE_ID9=>P_INPUT_VALUE_ID9_TARGET, P_INPUT_VALUE_ID10=>P_INPUT_VALUE_ID10_TARGET, P_INPUT_VALUE_ID11=>P_INPUT_VALUE_ID11_TARGET, P_INPUT_VALUE_ID12=>P_INPUT_VALUE_ID12_TARGET, P_INPUT_VALUE_ID13=>P_INPUT_VALUE_ID13_TARGET, P_INPUT_VALUE_ID14=>P_INPUT_VALUE_ID14_TARGET, P_INPUT_VALUE_ID15=>P_INPUT_VALUE_ID15_TARGET, P_ENTRY_VALUE1=>P_ENTRY_VALUE1_TARGET, P_ENTRY_VALUE2=>P_ENTRY_VALUE2_TARGET, P_ENTRY_VALUE3=>P_ENTRY_VALUE3_TARGET, P_ENTRY_VALUE4=>P_ENTRY_VALUE4_TARGET, P_ENTRY_VALUE5=>P_ENTRY_VALUE5_TARGET, P_ENTRY_VALUE6=>P_ENTRY_VALUE6_TARGET, P_ENTRY_VALUE7=>P_ENTRY_VALUE7_TARGET, P_ENTRY_VALUE8=>P_ENTRY_VALUE8_TARGET, P_ENTRY_VALUE9=>P_ENTRY_VALUE9_TARGET, P_ENTRY_VALUE10=>P_ENTRY_VALUE10_TARGET, P_ENTRY_VALUE11=>P_ENTRY_VALUE11_TARGET, P_ENTRY_VALUE12=>P_ENTRY_VALUE12_TARGET, P_ENTRY_VALUE13=>P_ENTRY_VALUE13_TARGET, P_ENTRY_VALUE14=>P_ENTRY_VALUE14_TARGET, P_ENTRY_VALUE15=>P_ENTRY_VALUE15_TARGET, P_ENTRY_INFORMATION_CATEGORY=>P_ENTRY_INFORMATION_CATEGORY_TARGET, P_ENTRY_INFORMATION1=>P_ENTRY_INFORMATION1_TARGET, P_ENTRY_INFORMATION2=>P_ENTRY_INFORMATION2_TARGET, P_ENTRY_INFORMATION3=>P_ENTRY_INFORMATION3_TARGET, P_ENTRY_INFORMATION4=>P_ENTRY_INFORMATION4_TARGET, P_ENTRY_INFORMATION5=>P_ENTRY_INFORMATION5_TARGET, P_ENTRY_INFORMATION6=>P_ENTRY_INFORMATION6_TARGET, P_ENTRY_INFORMATION7=>P_ENTRY_INFORMATION7_TARGET, P_ENTRY_INFORMATION8=>P_ENTRY_INFORMATION8_TARGET, P_ENTRY_INFORMATION9=>P_ENTRY_INFORMATION9_TARGET, P_ENTRY_INFORMATION10=>P_ENTRY_INFORMATION10_TARGET, P_ENTRY_INFORMATION11=>P_ENTRY_INFORMATION11_TARGET, P_ENTRY_INFORMATION12=>P_ENTRY_INFORMATION12_TARGET, P_ENTRY_INFORMATION13=>P_ENTRY_INFORMATION13_TARGET, P_ENTRY_INFORMATION14=>P_ENTRY_INFORMATION14_TARGET, P_ENTRY_INFORMATION15=>P_ENTRY_INFORMATION15_TARGET, P_ENTRY_INFORMATION16=>P_ENTRY_INFORMATION16_TARGET, P_ENTRY_INFORMATION17=>P_ENTRY_INFORMATION17_TARGET, P_ENTRY_INFORMATION18=>P_ENTRY_INFORMATION18_TARGET, P_ENTRY_INFORMATION19=>P_ENTRY_INFORMATION19_TARGET, P_ENTRY_INFORMATION20=>P_ENTRY_INFORMATION20_TARGET, P_ENTRY_INFORMATION21=>P_ENTRY_INFORMATION21_TARGET, P_ENTRY_INFORMATION22=>P_ENTRY_INFORMATION22_TARGET, P_ENTRY_INFORMATION23=>P_ENTRY_INFORMATION23_TARGET, P_ENTRY_INFORMATION24=>P_ENTRY_INFORMATION24_TARGET, P_ENTRY_INFORMATION25=>P_ENTRY_INFORMATION25_TARGET, P_ENTRY_INFORMATION26=>P_ENTRY_INFORMATION26_TARGET, P_ENTRY_INFORMATION27=>P_ENTRY_INFORMATION27_TARGET, P_ENTRY_INFORMATION28=>P_ENTRY_INFORMATION28_TARGET, P_ENTRY_INFORMATION29=>P_ENTRY_INFORMATION29_TARGET, P_ENTRY_INFORMATION30=>P_ENTRY_INFORMATION30_TARGET, P_OVERRIDE_USER_ENT_CHK=>P_OVERRIDE_USER_ENT_CHK_TARGET, P_EFFECTIVE_START_DATE=>P_EFFECTIVE_START_DATE_TARGET, P_EFFECTIVE_END_DATE=>P_EFFECTIVE_END_DATE_TARGET, P_ELEMENT_ENTRY_ID=>P_ELEMENT_ENTRY_ID_TARGET, P_OBJECT_VERSION_NUMBER=>P_OBJECT_VERSION_NUMBER_TARGET, P_CREATE_WARNING=>P_CREATE_WARNING_TARGET); :101 := P_EFFECTIVE_START_DATE_TARGET; :102 := P_EFFECTIVE_END_DATE_TARGET; :103 := P_ELEMENT_ENTRY_ID_TARGET; :104 := P_OBJECT_VERSION_NUMBER_TARGET; :105 := SYS.SQLJUTL.BOOL2INT(P_CREATE_WARNING_TARGET); END; bind => [:1 => 0, :2 => 2012-01-01 00:00:00.0, :3 => null, :4 => null, :5 => 60272.0, :6 => 1908.0, :7 => E, :8 => F, :9 => null, :10 => null, :11 => null, :12 => null, :13 => null, :14 => null, :15 => null, :16 => 2012-01-01 00:00:00.0, :17 => null, :18 => null, :19 => null, :20 => null, :21 => null, :22 => null, :23 => null, :24 => null, :25 => null, :26 => null, :27 => null, :28 => null, :29 => null, :30 => null, :31 => null, :32 => null, :33 => null, :34 => null, :35 => null, :36 => null, :37 => null, :38 => null, :39 => null, :40 => null, :41 => null, :42 => null, :43 => null, :44 => null, :45 => null, :46 => null, :47 => null, :48 => null, :49 => null, :50 => null, :51 => null, :52 => null, :53 => null, :54 => null, :55 => null, :56 => 100.0, :57 => null, :58 => null, :59 => null, :60 => null, :61 => null, :62 => null, :63 => null, :64 => null, :65 => null, :66 => null, :67 => null, :68 => null, :69 => null, :70 => null, :71 => null, :72 => null, :73 => null, :74 => null, :75 => null, :76 => null, :77 => null, :78 => null, :79 => null, :80 => null, :81 => null, :82 => null, :83 => null, :84 => null, :85 => null, :86 => null, :87 => null, :88 => null, :89 => null, :90 => null, :91 => null, :92 => null, :93 => null, :94 => null, :95 => null, :96 => null, :97 => null, :98 => null, :99 => null, :100 => null, P_EFFECTIVE_START_DATE => :101, P_EFFECTIVE_END_DATE => :102, P_ELEMENT_ENTRY_ID => :103, P_OBJECT_VERSION_NUMBER => :104, P_CREATE_WARNING => :105] Query: ValueReadQuery(name="PAY_ELEMENT_ENTRY_API" )</faultstring> </env:Fault> </env:Body> </env:Envelope> Kind regards Lonneke
Created attachment 209403 [details] fix very long pl/sql identifiers
Created attachment 209405 [details] fix very long pl/sql identifiers When generating the anonymous PL/SQL block, some temp variables are created based on 'argName'_TARGET (or _COMPAT). If these variables' names are longer than 30 characters, an exception is thrown. The long argName is hashed by a java.security.MessageDigest using the 'SHA1' algorithm (same as used by Git). The number of bits is truncated to 112 and then converted to a hex string. The final step is to prepend 'a_' to the hash value (PL/SQL identifiers can't begin with a number) creating a brand new 30 character identifier. The probability of hash collision is extremely low - the IPSEC security portion of TCP/IP uses 'HMAC SHA1' truncated to 96 bits as per RFC-2104; the above truncation has 16 more bits.
Created attachment 209406 [details] fix very long pl/sql identifiers
fixed in SVN 10684: - when we generate the anonymous PL/SQL block, we often must create temp variables that are based upon the names of arguments to a StoredProcedure. We add the suffix '_TARGET' or '_COMPAT'. Unfortunately, PL/SQL identifiers can only be 30 chars long. - now whenever we find an identifier that is >(30 - LENGTH('_TARGET)), we take the SHA-1 MessageDigest of the identifer. This produces a 160-bit hash value, which is also longer than 30 chars. However, upon doing some research, discovered that truncating SHA-1 hashs to as low as 96 bits is universally regarded as valid (e.g. RFC 2404 IPSEC does this). In version checked in here, we truncate to 112-bits and then prepend '_T' (or '_C') to make a unique, valid PL/SQL identifier
meant to say "prepend 'T_' or 'C_'", not '_T'
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
