0

I am using ESQL to fetch data from Oracle and create a XML message. Everything works fine except that a column name FILENAME is pruned automatically to 20 character.Part of the ESQL creating the XML message:

CREATE COMPUTE MODULE FileExpectedEvent
    CREATE FUNCTION Main() RETURNS BOOLEAN
    BEGIN
        DECLARE now CHARACTER;
        SET now = CAST(CURRENT_DATE AS CHARACTER FORMAT 'yyyy-MM-dd');

        CALL CopyMessageHeaders();      
        DECLARE DB_Schema NAME BAM_DB_Schema;
        SET Environment.Variables.ExpectedFiles.Rec[]
                = (SELECT TRIM(A.FILENAME) AS FILENAME, A.EXPECTED_DATE, A.EXPECTED_TIME, A.EXPECTED_ALERT_SENT, A.ARRIVED_DATE, A.ARRIVED_TIME
                   FROM Database.DB_Schema.BAM_EXPECTED_FILES AS A 
                   WHERE A.EXPECTED_DATE = now 
                   AND (A.EXPECTED_ALERT_SENT IS NULL OR A.EXPECTED_ALERT_SENT <> 'Y'));


        DECLARE  ResultRef REFERENCE TO Environment.Variables.ExpectedFiles.Rec[1];
        DECLARE expectedTimeStamp TIMESTAMP;


        DECLARE ns1 NAMESPACE 'http:///XYZ.xsd'; 

        WHILE LASTMOVE(ResultRef) = TRUE DO 
           SET OutputRoot.MQMD.Version = 2;
           SET OutputRoot.MQMD.Format = 'MQSTR   '; 
           SET OutputRoot.XMLNSC.ns1:ExpectedFile.(XML.NamespaceDecl)xmlns = 'http:///XYX.xsd';                                      
           SET OutputRoot.XMLNSC.ns1:ExpectedFile.ns1:FileName = ResultRef.FILENAME;

This is the sample XML generated

<NS1:ExpectedFile> 
 <NS1:FileName> OH_ODJFS_DAILY_83420</NS1:FileName> 
 <NS1:ExpectedDate> 2014-09-25</NS1:ExpectedDate> 
 <NS1:ExpectedTime> 07:00:00</NS1:ExpectedTime> 
 <NS1:ExpectedAlertTime> 2014-09-25T13:00:00-05:00</NS1:ExpectedAlertTime> 
 <NS1:ExpectedDateTimeStamp> 2014-09-25T12:00:00-05:00</NS1:ExpectedDateTimeStamp> 
 </NS1:ExpectedFile>

Everything is fine as expected but <NS1:FileName> OH_ODJFS_DAILY_83420</NS1:FileName> is limited to length 20 automatically. File name above 20 is pruned.[This works fine with DB2]

Need help on this...thanks in advance

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Atul Rai
  • 9
  • 4
  • Is there some maximum limit for ESQL variables or the size depends on some environment variable settings... – Atul Rai Sep 27 '14 at 06:39
  • Anyone one here having any idea about restriction in length of variables in ESQL? Need help urgently – Atul Rai Sep 27 '14 at 12:58

0 Answers0