Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 368396

Summary:

PL/SQL identifier too long

Product:

z_Archived

Reporter:

Mike Norman <michael.norman>

Component:

Eclipselink

Assignee:

Mike Norman <michael.norman>

Status:

RESOLVED FIXED

QA Contact:

Severity:

major

Priority:

Version:

unspecified

Target Milestone:

---

Hardware:

All

OS:

All

Whiteboard:

Attachments:

Description	Flags
fix very long pl/sql identifiers	none
fix very long pl/sql identifiers	none
fix very long pl/sql identifiers	none

Description Mike Norman

2012-01-11 16:06:51 EST

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

Comment 1 Mike Norman

2012-01-12 14:31:08 EST

Created attachment 209403 [details]
fix very long pl/sql identifiers

Comment 2 Mike Norman

2012-01-12 14:39:53 EST

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.

Comment 3 Mike Norman

2012-01-12 14:44:44 EST

Created attachment 209406 [details]
fix very long pl/sql identifiers

Comment 4 Mike Norman

2012-01-13 13:48:15 EST

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

Comment 5 Mike Norman

2012-01-19 10:03:33 EST

meant to say "prepend 'T_' or 'C_'", not '_T'

Comment 6 Eclipse Webmaster

2022-06-09 10:02:58 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink