1 2 Previous Next 19 Replies Latest reply on Aug 29, 2018 1:49 AM by user517202

    Need help in XML generation

    user517202

      I tried but struck with incomplete xml output. I got the majority of the part but the closing header tag is missing in my output. Rest of the xml part is all fine. Below are my requirements. Please help me

       

       

      ********************************************* TABLE STRUCTURES WITH VALUES *****************************************************

      CREATE TABLE JE_HEADER(

      ID                                    NUMBER,

      ENTRY_CREATOR        VARCHAR2(100),

      UNIQUE_IDENTIFIER      VARCHAR2(100),

      DOCUMENT_TYPE           VARCHAR2(100),

      POSTING_DATE                DATE,

      CURRENCY_CODE           VARCHAR2(100),

      CREATE_DATE                  DATE,

      PROCESS_STATUS          VARCHAR2(10),

      PUBLISH_DATE                 DATE,

      IZ_BUSINESS_ID            NUMBER(10))

      /

      ALTER TABLE JE_HEADER ADD CONSTRAINT PK_JEHEADER PRIMARY KEY (ID)

      /

      INSERT INTO JE_HEADER VALUES(12345,'DEU FSS Sales','S201806180012404055','ZR',SYSDATE,'EUR',SYSDATE)

      /

      CREATE TABLE JE_DETAILS(

      ID                                    NUMBER,

      DIVISION_CODE           VARCHAR2(100),

      TOUCHPOINT_CODE VARCHAR2(100),

      LINE_NO                          VARCHAR2(100),

      LINE_DESCRIPTION      VARCHAR2(100),

      LINE_CODE                     VARCHAR2(100),

      QUANTITY                     VARCHAR2(100),

      PRODUCT_CODE           VARCHAR2(100),

      UNIT_OF_MEASURE      VARCHAR2(100),

      TAXBASE_AMOUNT      VARCHAR2(100),

      CREDIT_CARD_TYPE      VARCHAR2(100),

      AMOUNT_VALUE VARCHAR2(100),

      TAXCODE VARCHAR2(100))

      /

      ALTER TABLE JE_DETAILS ADD CONSTRAINT fk_jedetails FOREIGN KEY (id) REFERENCES JE_HEADER(id)

      /

      INSERT INTO JE_DETAILS VALUES(12345,'12','0012404055','1','Sales',null,'16','MK41010000','EA',null,null,'-524.37','1')

      /

      INSERT INTO JE_DETAILS VALUES(12345,'13','0012404055','1','Sales',null,'3','MXN1010000','EA',null,null,'-57.98','1')

      /

       

      *************************SAMPLE OUTPUT I NEED (selected only 1 column from each table to keep it short )************************************

       

      <?xml version="1.0" encoding="UTF-8"?>

      <JournalEntry>

           <Header Action="Change">

                <EntryCreator>DEU FSS Sales</EntryCreator>

           </Header>

           <Details>

                <JournalDetail>

                     <DivisionCode>12</DivisionCode>

                </JournalDetail>

                <JournalDetail>

                     <DivisionCode>13</DivisionCode>

                </JournalDetail>

           </Details>

      </JournalEntry>

       

      *********************************************SELECT I TRIED **********************************************************************

       

      declare

          v_xml_clob CLOB;

      begin  

      select

               to_clob('<?xml version="1.0" encoding="UTF-8"?>') ||chr(10)||

              xmlserialize(document

               xmlelement("JournalEntry"

               , xmlelement("Header", xmlattributes('change' as "Action"))

                  , xmlelement("EntryCreator", jh.Entry_Creator)

               , xmlelement("Details"

                 , xmlagg(

                     xmlelement("JournalDetail"

                      , xmlelement("DivisionCode", jd.Division_Code)

                      )

                   )

                 )

                 )

                  indent --< for display purpose

                 )

                as ConsumerTX

                    into v_xml_clob

        from je_header jh

             join je_details jd on jh.id = jd.id

        group by jh.entry_creator;

       

      dbms_output.put_line(v_xml_clob);

      END;

      *********************************************OUTPUT I AM GETTING **********************************************************************

       

      <?xml version="1.0" encoding="UTF-8"?>

      <JournalEntry>

        <Header Action="change"/>

        <EntryCreator>DEU FSS Sales</EntryCreator> -------------> closing header tag is missing after this point. need as per the sample above

        <Details>

          <JournalDetail>

            <DivisionCode>12</DivisionCode>

          </JournalDetail>

          <JournalDetail>

            <DivisionCode>13</DivisionCode>

          </JournalDetail>

        </Details>

      </JournalEntry>

       

      PL/SQL procedure successfully completed.

        • 1. Re: Need help in XML generation
          odie_63

          You already know how to nest an element inside another, e.g. JournalDetail > DivisionCode

          So what problem do you have in doing the same for Header > EntryCreator ?

           

          SQL> select xmlserialize(document
            2           xmlelement("JournalEntry"
            3           , xmlelement("Header", xmlattributes('change' as "Action")
            4             , xmlelement("EntryCreator", jh.Entry_Creator)
            5             )
            6           , xmlelement("Details"
            7             , xmlagg(
            8                 xmlelement("JournalDetail"
            9                 , xmlelement("DivisionCode", jd.Division_Code)
           10                 )
           11               )
           12             )
           13           )
           14           indent --< for display purpose
           15         )
           16         as ConsumerTX
           17  from je_header jh
           18       join je_details jd on jh.id = jd.id
           19  group by jh.entry_creator;
          
          CONSUMERTX
          --------------------------------------------------------------------------------
          <JournalEntry>
            <Header Action="change">
              <EntryCreator>DEU FSS Sales</EntryCreator>
            </Header>
            <Details>
              <JournalDetail>
                <DivisionCode>12</DivisionCode>
              </JournalDetail>
              <JournalDetail>
                <DivisionCode>13</DivisionCode>
              </JournalDetail>
            </Details>
          </JournalEntry>
          
          • 2. Re: Need help in XML generation
            user517202

            Thank you. Had Made a mistake in placing right parenthesis. I will implement this and want to check for multiple records in a loop.

            Once again Thanks a lot

            • 3. Re: Need help in XML generation
              user517202

              Hello odie_63,

               

              I worked on the above all was fine. Now I took a different set of records and tried to store output in a file, Below is the code and the error i received,

              Please can you help me.

               

              NOTE: Code to generate xml and into clob is all exactly same as earlier but only working with different data set.

               

               

              CREATE OR REPLACE DIRECTORY my_dir AS  'c:\je'

               

              declare

                  v_xml_clob CLOB;

                  v_dir   VARCHAR2 (1000) := 'MY_DIR'; -- directory object, not path, in upper case

              begin   

              select

                       to_clob('<?xml version="1.0" encoding="UTF-8"?>') ||chr(10)||

                      xmlserialize(document 

                       xmlelement("JournalEntry" 

                       , xmlelement("Header", xmlattributes('change' as "Action") 

              , xmlelement("EntryCreator", jh.Entry_Creator)

                       , xmlelement("UniqueIdentifier", jh.Unique_Identifier)

                       , xmlelement("DocumentType", jh.Document_Type)

                       , xmlelement("PostingDate", jh.Posting_Date)

                       , xmlelement("CurrencyCode", jh.Currency_Code)

                       , xmlelement("CreateDate", jh.Create_Date)

                       )

                       , xmlelement("Details" 

                         --, XMLAttributes(count(*) as "rec_count")

                          ,xmlagg( 

                             xmlelement("JournalDetail" 

                              , xmlelement("DivisionCode", jd.Division_Code)

                              , xmlelement("TouchPointCode", jd.TouchPoint_Code)

                              , xmlelement("LineNo", jd.Line_No)

                              , xmlelement("LineDescription", jd.Line_Description)

                              , xmlelement("LineCode", jd.Line_Code)

                              , xmlelement("Quantity", jd.Quantity)

                              , xmlelement("ProductCode", jd.Product_Code)

                              , xmlelement("UnitofMeasure", jd.Unit_of_Measure)

                              , xmlelement("TaxBaseAmount", jd.TaxBase_Amount)

                              , xmlelement("CreditCardType", jd.Credit_Card_Type)

                              , xmlelement("AmountValue", jd.Amount_Value)

                              , xmlelement("TaxCode", jd.TaxCode)

                              )

                              order by jd.Line_No

                           ) 

                         )            

                         )          

                          indent --< for display purpose 

                         )          

                        as JournalEntry 

                            into v_xml_clob

                from je_header jh 

                     join je_details jd on jh.id = jd.id

                group by  jh.entry_creator

                          , jh.Unique_Identifier

                          , jh.Document_Type

                          , jh.Posting_Date

                          , jh.Currency_Code

                          , jh.Create_Date; 

               

              DBMS_LOB.CREATETEMPORARY (v_xml_clob, TRUE);

              DBMS_XSLPROCESSOR.CLOB2FILE (v_xml_clob, v_dir, 'latestxml.txt');

              DBMS_LOB.FREETEMPORARY (v_xml_clob);

              END;

               

               

              Error report -

              ORA-21560: argument 3 is null, invalid, or out of range

              ORA-06512: at "SYS.DBMS_LOB", line 991

              ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 324

              ORA-06512: at line 57

              21560. 00000 -  "argument %s is null, invalid, or out of range"

              *Cause:    The argument is expecting a non-null, valid value but the

                         argument value passed in is null, invalid, or out of range.

                         Examples include when the LOB/FILE positional or size

                         argument has a value outside the range 1 through (4GB - 1),

                         or when an invalid open mode is used to open a file, etc.

              *Action:   Check your program and correct the caller of the routine

                         to not pass a null, invalid or out-of-range argument value.

              • 4. Re: Need help in XML generation
                odie_63

                Those calls (in red) must be removed :

                DBMS_LOB.CREATETEMPORARY (v_xml_clob, TRUE);

                DBMS_XSLPROCESSOR.CLOB2FILE (v_xml_clob, v_dir, 'latestxml.txt');

                DBMS_LOB.FREETEMPORARY (v_xml_clob);

                The SELECT INTO query is already creating a temporary LOB (an abstract LOB to be precise) in variable v_xml_clob.

                So your call to CREATETEMPORARY is just zeroing the LOB pointer, hence the error.

                 

                FREETEMPORARY is not needed either, the LOB will be freed automatically at the end of the PL/SQL execution.

                1 person found this helpful
                • 5. Re: Need help in XML generation
                  user517202

                  Okay, Thank you so much.

                  Another procedure which is in the database already has the following code which is causing the error, As you said I think, we should eliminate the lines which is in red color. Right? Somebody else wrote this and have no permission to change it and not sure why have they used it. I have check with DBA. Not sure where it impacts if I remove these lines. Will check and let you know

                   

                  _______________________________ procedure in the database___________________________________

                   

                  DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);     *********** line to removed********************

                   

                  v_Loop_Ceil:=v_CLOB_Len/v_Buffer;

                   

                  FOR i IN 1..CEIL(v_Loop_Ceil)

                  LOOP

                  v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));

                  DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);   

                  v_Start := v_Start + v_Buffer;

                  v_Buffer := v_clob_len - v_Start + 1 ;

                      IF (v_Buffer > 32767)

                  THEN

                  v_Buffer := 32767;

                          END IF;

                  END LOOP;

                   

                  v_Lob_Locator := v_BLOB;

                  DBMS_LOB.FREETEMPORARY(v_BLOB);               *********** line to removed********************

                  RETURN v_Lob_Locator;

                  • 6. Re: Need help in XML generation
                    odie_63

                    user517202 wrote:

                     

                    Another procedure which is in the database already has the following code which is causing the error, As you said I think, we should eliminate the lines which is in red color. Right? Somebody else wrote this and have no permission to change it and not sure why have they used it. I have check with DBA. Not sure where it impacts if I remove these lines. Will check and let you know

                     

                    _______________________________ procedure in the database___________________________________

                     

                    DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE); *********** line to removed********************

                     

                    v_Loop_Ceil:=v_CLOB_Len/v_Buffer;

                     

                    FOR i IN 1..CEIL(v_Loop_Ceil)

                    LOOP

                    v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));

                    DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);

                    v_Start := v_Start + v_Buffer;

                    v_Buffer := v_clob_len - v_Start + 1 ;

                    IF (v_Buffer > 32767)

                    THEN

                    v_Buffer := 32767;

                    END IF;

                    END LOOP;

                     

                    v_Lob_Locator := v_BLOB;

                    DBMS_LOB.FREETEMPORARY(v_BLOB); *********** line to removed********************

                    RETURN v_Lob_Locator;

                    This is totally different procedure.

                    Does it raise the same exception?

                     

                    I can't tell if those lines need to be removed without seeing the complete error stack, and the whole procedure (or function?) code.

                    • 7. Re: Need help in XML generation
                      user517202

                      Yes, its a different procedure

                      Yes, it throws the same exception

                       

                      Below is that procedure in FULL. This is the procedure which is picking my v_xml_clob output as CLOB_TO_BLOB(v_xml_clob);

                       

                      **************************************************************************************************************************************************************************

                      FUNCTION CLOB_To_BLOB (p_CLOB IN CLOB) RETURN BLOB

                      AS

                      v_BLOB BLOB;

                      v_RAW RAW(32767);

                      v_Start PLS_INTEGER := 1;

                      v_Buffer PLS_INTEGER := 32767;

                      v_CLOB_Len NUMBER;

                      v_Lob_Locator BLOB := EMPTY_BLOB();

                      v_Loop_Ceil NUMBER(10,4);

                      BEGIN

                      ----------------------------------------------------------------------------------

                      ---Arun 06/15/06 Following code commented. Krishna's code below was use to solve 8i issue

                      --DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);

                      --FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_CLOB) / v_Buffer)

                      --LOOP

                      -- v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));

                        -- DBMS_LOB.WRITEAPPEND(v_BLOB, LENGTH(v_RAW), v_RAW);

                        -- v_Start := v_Start + v_Buffer;

                      --END LOOP;

                      ----------------------------------------------------------------------------------

                      --Krishna's Code Below works fine with 8i.

                      ----------------------------------------------------------------------------------

                       

                      --If the Length of the CLOB is 0( NULL Clob then return)

                      v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);

                      IF NVL(v_CLOB_len,0) = 0

                      THEN

                      DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);

                      v_Lob_Locator := v_BLOB ;

                      RETURN v_Lob_Locator;

                      END IF;

                      ----------------------------------------------------------------------------------

                       

                      --If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth

                      IF v_CLOB_Len < 32767

                      THEN

                      v_Buffer := v_CLOB_len;

                      ELSE

                      v_Buffer := 32767;

                      END IF;

                      ----------------------------------------------------------------------------------

                       

                      DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);

                       

                      v_Loop_Ceil:=v_CLOB_Len/v_Buffer;

                       

                      FOR i IN 1..CEIL(v_Loop_Ceil)

                      LOOP

                      v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));

                      DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);

                      v_Start := v_Start + v_Buffer;

                      v_Buffer := v_clob_len - v_Start + 1 ;

                          IF (v_Buffer > 32767)

                      THEN

                      v_Buffer := 32767;

                              END IF;

                      END LOOP;

                       

                      v_Lob_Locator := v_BLOB;

                      DBMS_LOB.FREETEMPORARY(v_BLOB);

                      RETURN v_Lob_Locator;

                       

                      **************************************************************************************************************************************************************************

                      • 8. Re: Need help in XML generation
                        odie_63

                        There's a built-in function to convert CLOB to BLOB : DBMS_LOB.CONVERTTOBLOB

                        You can even use XMLSerialize function to directly output a BLOB from the query, no need to manipulate all those LOBs.

                         

                        Those procedures probably have some history I'm not aware of, but for sure they may be greatly simplified now.

                        1 person found this helpful
                        • 9. Re: Need help in XML generation
                          user517202

                          I used DBMS_LOB.CONVERTTOBLOB and it works now

                           

                          Thanks a lot. I really appreciate your help. You saved me.

                          • 10. Re: Need help in XML generation
                            user517202

                            I used "DBMS_LOB.CONVERTTOBLOB" and published it, no errors but they could not see the xml at their end. It was blank. Earlier I had done the same thing with their code successfully published xml without any error. BUT now with a data for a different date, its giving errors. So I went back and used their code and see whats happening with messages in between.

                             

                            Not sure what is wrong, Can you help me, Sorry for the trouble, I am really not getting this.

                             

                            I get the below error when I comment the line "DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);"

                             

                            ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

                            ORA-06512: at "SYS.DBMS_LOB", line 1139

                            ORA-06512: at "ELCREVEL.CLOB_TO_BLOB1", line 50

                            ORA-06512: at "ELCREVEL.JE_PUBLISH", line 158

                            ORA-06512: at line 1

                            06502. 00000 -  "PL/SQL: numeric or value error%s"

                             

                            I get below error when I open up the "DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);"

                             

                            ORA-21560: argument 2 is null, invalid, or out of range

                            ORA-06512: at "SYS.DBMS_LOB", line 1139

                            ORA-06512: at "ELCREVEL.CLOB_TO_BLOB1", line 50

                            ORA-06512: at "ELCREVEL.JE_PUBLISH", line 158

                            ORA-06512: at line 1

                            21560. 00000 -  "argument %s is null, invalid, or out of range"

                             

                            -----------------Below is the code for function "FUNCTION CLOB_To_BLOB1"--------------------------------

                             

                            create or replace FUNCTION CLOB_To_BLOB1 (p_CLOB IN CLOB) RETURN BLOB

                            AS

                            v_BLOB BLOB;

                            v_RAW RAW(32767);

                            v_Start PLS_INTEGER := 1;

                            v_Buffer PLS_INTEGER := 32767;

                            v_CLOB_Len NUMBER;

                            v_Lob_Locator BLOB := EMPTY_BLOB();

                            v_Loop_Ceil NUMBER(10,4);

                            xmllength                   long;

                            BEGIN

                            --If the Length of the CLOB is 0( NULL Clob then return)   

                                xmllength := length(p_CLOB);

                                dbms_output.put_line('p_CLOB length = '||xmllength);   

                            v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);

                               

                                dbms_output.put_line('Package p_CLOB length = '||v_CLOB_Len);

                               

                            IF NVL(v_CLOB_len,0) = 0

                            THEN

                            DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);

                            v_Lob_Locator := v_BLOB ;

                            RETURN v_Lob_Locator;

                            END IF;

                            ----------------------------------------------------------------------------------

                            --If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth

                            IF v_CLOB_Len < 32767

                            THEN

                            v_Buffer := v_CLOB_len;

                            ELSE

                            v_Buffer := 32767;

                            END IF;

                               

                                dbms_output.put_line('Package v_Buffer1 length = '||v_Buffer);

                            ----------------------------------------------------------------------------------

                            --DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);

                            v_Loop_Ceil:=v_CLOB_Len/v_Buffer;   

                                dbms_output.put_line('Package v_Loop_Ceil length = '||v_Loop_Ceil);

                             

                             

                            FOR i IN 1..CEIL(v_Loop_Ceil)

                            LOOP

                                dbms_output.put_line('inside loop1');

                            v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));

                                    dbms_output.put_line('inside loop2');

                            DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);

                                    dbms_output.put_line('inside loop3');

                            v_Start := v_Start + v_Buffer;

                                    dbms_output.put_line('inside loop4');

                            v_Buffer := v_clob_len - v_Start + 1 ;

                                    dbms_output.put_line('Package v_Buffer2 length = '||v_Buffer);

                                IF (v_Buffer > 32767)

                                        THEN

                                        dbms_output.put_line('Package v_Buffer3 length = '||v_Buffer);

                            v_Buffer := 32767;           

                                    END IF;

                            END LOOP;

                             

                             

                            v_Lob_Locator := v_BLOB;

                            DBMS_LOB.FREETEMPORARY(v_BLOB);

                            RETURN v_Lob_Locator;

                            END CLOB_To_BLOB1;

                            • 11. Re: Need help in XML generation
                              Jason_(A_Non)

                              None of us are certain what line 50 is in this CLOB_TO_BLOB1 function.  Copying our your source code shows it to be

                              v_Buffer := v_clob_len - v_Start + 1 ;

                              which of course does not agree with your error stack.  I would guess the value for v_Buffer is 0 or negative but you didn't show the output you got so we have no way to know.  We can only assist using the information you provide so do your best to provide us a test case that we can use to reproduce the error.  We can generate a clob if it happens with any clob but if you are having issues with a specific clob, that is something we'd need to see.

                               

                              Also, what did your code for DBMS_LOB.CONVERTTOBLOB look like?  That is easier code to write/maintain and eliminates the loop, which is probably where your issue is.  Maybe we can see the issue there that prevented the users from seeing the result.

                              • 12. Re: Need help in XML generation
                                user517202

                                Hello Mr. Odie_63,

                                Need help. I have code below to reproduce the error. It has below 3 parts.

                                 

                                1) 1st part -- To create the concerned tables and insert records

                                2) 2nd part ---- Function to convert clob to blob

                                3) 3rd part ----- Procedure which generates an xml and converts into clob, and then calls above function to convert to blob

                                 

                                which is in when i am getting the following error

                                 

                                p_CLOB length = 65702

                                Package p_CLOB length = 65702

                                Package v_Buffer1 length = 32767

                                Package v_Loop_Ceil length = 2.0051

                                inside loop1

                                inside loop2

                                 

                                Error starting at line : 1 in command -

                                BEGIN je_publish; END;

                                Error report -

                                ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

                                ORA-06512: at "SYS.DBMS_LOB", line 1139

                                ORA-06512: at "ELCREVEL.CLOB_TO_BLOB", line 45

                                ORA-06512: at "ELCREVEL.JE_PUBLISH", line 53

                                ORA-06512: at line 1

                                06502. 00000 - "PL/SQL: numeric or value error%s"

                                 

                                ____________________________________________________CODE PART1____________________________________________________________________________

                                 

                                CREATE TABLE JE_HEADER(

                                ID                NUMBER,       

                                ENTRY_CREATOR              VARCHAR2(100),

                                UNIQUE_IDENTIFIER          VARCHAR2(100),

                                DOCUMENT_TYPE              VARCHAR2(100),

                                POSTING_DATE               VARCHAR2(10), 

                                CURRENCY_CODE              VARCHAR2(100),

                                CREATE_DATE                VARCHAR2(10), 

                                PROCESS_STATUS             VARCHAR2(10), 

                                PUBLISH_DATE               DATE,         

                                IZ_BUSINESS_ID             VARCHAR2(100))

                                /

                                ALTER TABLE JE_HEADER ADD CONSTRAINT PK_JEHEADER PRIMARY KEY (ID)

                                /

                                INSERT INTO JE_HEADER VALUES(20,'USFSS Sales','S201801201936202033','ZR','20180120','USD',to_char(current_date, 'YYYYMMDD'),null,null,null)

                                /

                                CREATE TABLE JE_DETAILS(

                                ID NUMBER,

                                DIVISION_CODE           VARCHAR2(100),

                                TOUCHPOINT_CODE VARCHAR2(100),

                                LINE_NO VARCHAR2(100),

                                LINE_DESCRIPTION VARCHAR2(100),

                                LINE_CODE VARCHAR2(100),

                                QUANTITY VARCHAR2(100),

                                PRODUCT_CODE VARCHAR2(100),

                                UNIT_OF_MEASURE VARCHAR2(100),

                                TAXBASE_AMOUNT VARCHAR2(100),

                                CREDIT_CARD_TYPE VARCHAR2(100),

                                AMOUNT_VALUE VARCHAR2(100),

                                TAXCODE VARCHAR2(100))

                                /

                                ALTER TABLE JE_DETAILS ADD CONSTRAINT fk_jedetails FOREIGN KEY (id) REFERENCES JE_HEADER(id)

                                /

                                SET DEFINE OFF

                                 

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', 1, 'Sales', NULL, '4', '015PS33100', 'EA', '312', NULL, '-312', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', '050PN36100', 'EA', '360', NULL, '-360', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', '050PP24100', 'EA', '360', NULL, '-360', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '6', '050PS33100', 'EA', '1080', NULL, '-1080', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', '050PS33200', 'EA', '288', NULL, '-288', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '4', '050PT29100', 'EA', '720', NULL, '-720', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '050PT40100', 'EA', '300', NULL, '-300', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PF27100', 'EA', '265', NULL, '-265', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PP24100', 'EA', '265', NULL, '-265', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '4', '100PS33100', 'EA', '1060', NULL, '-1060', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PS33200', 'EA', '212', NULL, '-212', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PT29100', 'EA', '265', NULL, '-265', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', '100PT40100', 'EA', '460', NULL, '-460', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'BODLB22100', 'EA', '68', NULL, '-68', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'BODLJ17100', 'EA', '68', NULL, '-68', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'BODLR31100', 'EA', '68', NULL, '-68', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'CNDCC17100', 'EA', '75', NULL, '-75', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'CNDCC26100', 'EA', '170', NULL, '-170', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'CNDCF15100', 'EA', '150', NULL, '-150', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'CNDCP12100', 'EA', '150', NULL, '-150', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '8', 'CNDCS26100', 'EA', '600', NULL, '-600', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'CNDVF15100', 'EA', '65', NULL, '-65', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '8', 'DS05000500', 'EA', '240', NULL, '-240', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'DS16100500', 'EA', '285', NULL, '-285', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G000000500', 'EA', '16', NULL, '-16', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G002010100', 'EA', '0', NULL, '0', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G003010100', 'EA', '0', NULL, '0', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G004006100', 'EA', '0', NULL, '0', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'G004060100', 'EA', '76', NULL, '-76', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G005006100', 'EA', '16', NULL, '-16', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'G006060100', 'EA', '30', NULL, '-30', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBB22100', 'EA', '90', NULL, '-90', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBJ17100', 'EA', '90', NULL, '-90', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBS33100', 'EA', '90', NULL, '-90', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'LIQBT29100', 'EA', '90', NULL, '-90', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P001500100', 'EA', '38', NULL, '-38', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P003250100', 'EA', '35', NULL, '-35', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P004150100', 'EA', '45', NULL, '-45', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '3', 'P005250100', 'EA', '81', NULL, '-81', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P006075100', 'EA', '11', NULL, '-11', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P006250100', 'EA', '54', NULL, '-54', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P008250100', 'EA', '40', NULL, '-40', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'P010250100', 'EA', '24', NULL, '-24', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P013015100', 'EA', '28', NULL, '-28', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P015075100', 'EA', '50', NULL, '-50', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '4', 'P016075100', 'EA', '40', NULL, '-40', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'P016250100', 'EA', '48', NULL, '-48', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMA09100', 'EA', '12', NULL, '-12', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '9', 'PSAMA13100', 'EA', '54', NULL, '-54', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '3', 'PSAMB22100', 'EA', '18', NULL, '-18', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAMF27100', 'EA', '6', NULL, '-6', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMJ17100', 'EA', '12', NULL, '-12', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAML18100', 'EA', '6', NULL, '-6', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAML41100', 'EA', '6', NULL, '-6', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAML41400', 'EA', '0', NULL, '0', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMN36100', 'EA', '12', NULL, '-12', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAMO27100', 'EA', '6', NULL, '-6', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMP24100', 'EA', '12', NULL, '-12', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'PSAMR31100', 'EA', '6', NULL, '-6', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '15', 'PSAMS33100', 'EA', '90', NULL, '-90', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '11', 'PSAMT29100', 'EA', '66', NULL, '-66', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'PSAMV46100', 'EA', '12', NULL, '-12', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGA13100', 'EA', '52', NULL, '-52', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGR31100', 'EA', '52', NULL, '-52', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGS33100', 'EA', '52', NULL, '-52', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SHOGT29100', 'EA', '52', NULL, '-52', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'SOAPS33100', 'EA', '48', NULL, '-48', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'TRTC000100', 'EA', '308', NULL, '-308', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '2', 'TRTUR31200', 'EA', '102.67', NULL, '-102.67', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '3', 'TRTUT29200', 'EA', '154', NULL, '-154', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Sales', NULL, '1', 'TRTUY49200', 'EA', '51.33', NULL, '-51.33', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'VAT', NULL, NULL, NULL, 'EA', NULL, NULL, '-785.24', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'CASH', '550.17', '');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'AMERICANEXPRESS', '3035.49', '');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'VISA', '4824', '');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'DEBIT_VISA', '84.92', '');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'MASTERCARD', '649.63', '');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Clearing', NULL, NULL, NULL, 'EA', NULL, 'DEBIT_MASTERCARD', '587.93', '');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', NULL, '1', 'PSAMA13100', 'EA', NULL, NULL, '2.1', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', NULL, '1', 'PSAMB22100', 'EA', NULL, NULL, '2.1', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', NULL, '1', 'PSAMO27100', 'EA', NULL, NULL, '2.1', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', 'Discovery Set 100ml', '1', '100PP24100', 'EA', NULL, NULL, '265', '62');

                                 

                                INSERT INTO JE_DETAILS (ID, DIVISION_CODE, TOUCHPOINT_CODE, LINE_NO, LINE_DESCRIPTION, LINE_CODE, QUANTITY, PRODUCT_CODE, UNIT_OF_MEASURE, TAXBASE_AMOUNT, CREDIT_CARD_TYPE, AMOUNT_VALUE, TAXCODE)

                                VALUES (20, '55', '20', ((select max(nvl(line_no,0))+1 from je_details)), 'Discount', 'Discovery Set 100ml', '1', '100PF27100', 'EA', NULL, NULL, '265', '62');

                                 

                                commit

                                ____________________________________________________________________PART2__________________________________________________________________________________

                                 

                                create or replace FUNCTION CLOB_To_BLOB (p_CLOB IN CLOB) RETURN BLOB

                                AS

                                v_BLOB BLOB;

                                v_RAW RAW(32767);

                                v_Start PLS_INTEGER := 1;

                                v_Buffer PLS_INTEGER := 32767;

                                v_CLOB_Len NUMBER;

                                v_Lob_Locator BLOB := EMPTY_BLOB();

                                v_Loop_Ceil NUMBER(10,4);

                                xmllength                   long;

                                BEGIN

                                --If the Length of the CLOB is 0( NULL Clob then return)   

                                    xmllength := length(p_CLOB);

                                    dbms_output.put_line('p_CLOB length = '||xmllength);   

                                v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);

                                   

                                    dbms_output.put_line('Package p_CLOB length = '||v_CLOB_Len);

                                   

                                IF NVL(v_CLOB_len,0) = 0

                                THEN

                                DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);

                                v_Lob_Locator := v_BLOB ;

                                RETURN v_Lob_Locator;

                                END IF;

                                ----------------------------------------------------------------------------------

                                --If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth

                                IF v_CLOB_Len < 32767

                                THEN

                                v_Buffer := v_CLOB_len;

                                ELSE

                                v_Buffer := 32767;

                                END IF;

                                   

                                    dbms_output.put_line('Package v_Buffer1 length = '||v_Buffer);

                                ----------------------------------------------------------------------------------

                                --DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);

                                v_Loop_Ceil:=v_CLOB_Len/v_Buffer;   

                                    dbms_output.put_line('Package v_Loop_Ceil length = '||v_Loop_Ceil);

                                 

                                 

                                FOR i IN 1..CEIL(v_Loop_Ceil)

                                LOOP

                                    dbms_output.put_line('inside loop1');

                                v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));

                                        dbms_output.put_line('inside loop2');

                                DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);

                                        dbms_output.put_line('inside loop3');

                                v_Start := v_Start + v_Buffer;

                                        dbms_output.put_line('inside loop4');

                                v_Buffer := v_clob_len - v_Start + 1 ;

                                        dbms_output.put_line('Package v_Buffer2 length = '||v_Buffer);

                                    IF (v_Buffer > 32767)

                                            THEN

                                            dbms_output.put_line('Package v_Buffer3 length = '||v_Buffer);

                                v_Buffer := 32767;           

                                        END IF;

                                END LOOP;

                                 

                                 

                                v_Lob_Locator := v_BLOB;

                                DBMS_LOB.FREETEMPORARY(v_BLOB);

                                RETURN v_Lob_Locator;

                                END CLOB_To_BLOB;

                                 

                                ____________________________________________________________________PART3__________________________________________________________________________________

                                 

                                CREATE OR REPLACE PROCEDURE je_publish

                                AS

                                      v_xml_clob              CLOB;

                                      v_xml_blob                BLOB;

                                BEGIN       

                                select

                                         to_clob('<?xml version="1.0" encoding="UTF-8"?>') ||chr(10)||

                                        xmlserialize(document 

                                         xmlelement("JournalEntry" 

                                         , xmlelement("Header", xmlattributes('change' as "Action") 

                                , xmlelement("EntryCreator", jh.Entry_Creator)

                                         , xmlelement("UniqueIdentifier", jh.Unique_Identifier)

                                         , xmlelement("DocumentType", jh.Document_Type)

                                         , xmlelement("PostingDate", jh.Posting_Date)

                                         , xmlelement("CurrencyCode", jh.Currency_Code)

                                         , xmlelement("CreateDate", jh.Create_Date)

                                         )

                                         , xmlelement("Details" 

                                          --, XMLAttributes(count(*) as "rec_count")

                                           , xmlagg( 

                                               xmlelement("JournalDetail" 

                                                , xmlelement("DivisionCode", jd.Division_Code)

                                                , xmlelement("TouchPointCode", jd.TouchPoint_Code)

                                                , xmlelement("LineNo", jd.Line_No)

                                                , xmlelement("LineDescription", jd.Line_Description)

                                                , xmlelement("LineCode", jd.Line_Code)

                                                , xmlelement("Quantity", jd.Quantity)

                                                , xmlelement("ProductCode", jd.Product_Code)

                                                , xmlelement("UnitofMeasure", jd.Unit_of_Measure)

                                                , xmlelement("TaxBaseAmount", jd.TaxBase_Amount)

                                                , xmlelement("CreditCardType", jd.Credit_Card_Type)

                                                , xmlelement("AmountValue", jd.Amount_Value)

                                                , xmlelement("TaxCode", jd.TaxCode)

                                                )

                                                order by jd.Line_No

                                             ) 

                                           )            

                                           )          

                                            indent --< for display purpose 

                                           )          

                                          as JournalEntry 

                                              into v_xml_clob

                                  from je_header jh 

                                       join je_details jd on jh.id = jd.id

                                  group by  jh.entry_creator

                                            , jh.Unique_Identifier

                                            , jh.Document_Type

                                            , jh.Posting_Date

                                            , jh.Currency_Code

                                            , jh.Create_Date;

                                 

                                            v_xml_blob := CLOB_TO_BLOB(v_xml_clob);          

                                 

                                 

                                END JE_publish;

                                • 13. Re: Need help in XML generation
                                  user517202

                                  Below is the function code which uses dbms_lob.convertToBlob. I have also added the error I receive when i use this function

                                   

                                  create or replace function CLOB_TO_BLOB2(p_CLOB IN CLOB) return BLOB is

                                    Result BLOB;

                                  l_blob        blob:= null;

                                    l_amt         integer := dbms_lob.lobmaxsize;

                                    l_dest_offset integer := 1;

                                    l_src_offset  integer := 1;

                                    l_csid        integer := dbms_lob.default_csid;

                                    l_ctx         integer := dbms_lob.default_lang_ctx;

                                    l_warn        integer:= null;

                                  begin

                                      dbms_lob.convertToBlob(l_blob,

                                                             p_CLOB,

                                                             l_amt,

                                                             l_dest_offset,

                                                             l_src_offset,

                                                             l_csid,

                                                             l_ctx,

                                                             l_warn );

                                    return(Result);

                                  end CLOB_TO_BLOB2;

                                  /   

                                   

                                  ERROR

                                   

                                  v_xml_clob length = 65702

                                   

                                  Error starting at line : 4 in command -

                                  BEGIN je_publish; END;

                                  Error report -

                                  ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

                                  ORA-06512: at "SYS.DBMS_LOB", line 991

                                  ORA-06512: at "ELCREVEL.CLOB_TO_BLOB2", line 11

                                  ORA-06512: at "ELCREVEL.JE_PUBLISH", line 158

                                  ORA-06512: at line 1

                                  06502. 00000 -  "PL/SQL: numeric or value error%s"

                                  • 14. Re: Need help in XML generation
                                    Jason_(A_Non)

                                    Why not just read up on XMLSerialize as Odie_63 suggested and handle the BLOB conversion there instead of adding all this extra code?

                                     

                                    For example,

                                    SELECT XMLSERIALIZE(DOCUMENT xmlelement("JournalEntry", 'rest here') AS BLOB ENCODING 'UTF-8' VERSION '1.0' NO INDENT) v1 FROM dual;
                                    

                                     

                                    XMLSerialize will do the conversion for you and add the prolog so use it and save yourself the hassle, given you are struggling with LOBs.

                                    1 2 Previous Next