Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Blob image to XML file

yoshiishiSep 19 2018 — edited Sep 20 2018

Hi,

Can someone let me know how I can extract out a blob image into an xml file?

The xml file contains other text data and I would like to include the blob image(s) as well.

The xml  file is intended to be used to transfer data from one system to another, so I would like to include text as well as blob images.

My coding is current like below and would like to incorporate the blob into it.

CREATE OR REPLACE PROCEDURE CLAIMS.Claims_Xml (XML_ORDERID NUMBER) IS

Begin

DECLARE

  claims_xml1  clob;

  v_po_no     varchar2(1000);

BEGIN

         dbms_output.put_line('1');

  -- Create XML context.

         select xmlserialize(document 

         xmlelement("Claims"

         ,xmlelement("ClientID", '138146')

         ,xmlagg(claim_xml))

       --  as clob indent

        ) into claims_xml1

        from (

         select xmlelement("Claim"

         , xmlelement("ClaimId", a.claimid) 

         , xmlelement("AssetId", a.assetid) 

         , xmlelement("ClaimNo", a.claimno)

         , xmlelement("StatusId", c.descr)

       --  , xmlelement("ClaimDept", deocde(deptcode),'C','Communication','D','Deck','E','Electrical','H','Furniture Hotel','T','Technical Hotel','M','Machinery','V','HVAC')) 

         , xmlelement("Subject", a.subject) 

         , xmlelement("CreatedBy", l.name)

         , xmlelement("DamageDate", a.damagedate)

         , xmlelement("DamageSentDate", a.damagesentdate)

         , xmlelement("DamageApproveDate", a.damageapprovedate)

         , xmlelement("MakerName", a.makername)

         , xmlelement("VendorName", a.vendorname)

         , xmlelement("CompType", a.comptype)

         , xmlelement("SerialNo", a.serialno)

         , xmlelement("DeckFZone", a.deckfzone)

         , xmlelement("YardMarking", a.yardmarking)

         , xmlelement("DefectDesc", a.defectdesc)

         , xmlelement("CurrencyCode",a.currencycode)

         , xmlelement("TotalCost", a.totalcost)        

           , xmlelement("Repair"

             , xmlelement("RepairDesc", a.repairdesc)

             , xmlelement("RepairDate", a.repairdate)

             , xmlelement("RepairApproveDate", a.repairapprovedate)

             , xmlelement("RepairSentDate", a.repairsentdate)

             , xmlelement("ClosedDate", a.closeddate)

             , xmlelement("DamageApprovedBy", m.name)

             , xmlelement("RepairApprovedBy", n.name)

                        )

           , xmlelement("Comment"

             , xmlelement("Comments", a.comments)

                       ) 

           , xmlelement("HourlyExpenses"

             ,(

                select xmlagg(

                    xmlelement("ExpenseType",j.description

                     , xmlforest( i.workdate as "WorkDate"

                                , j.description as "ExpenseType"

                                , i.hours as "Hours"

                                , j.hourlyrate as "HourlyRate"

                                , i.currencycode as "CurrencyCode"

                                , i.cost as "Cost"

                                )

                               )

                              )

                from claimwork i, claimworkgroup j

                where a.claimid=i.claimid(+)

                and i.workgroupid=J.WORKGROUPID(+)

              )  

            ) 

            , xmlelement("Spares"

             ,(

                select xmlagg(

                     xmlelement("Spare"

                     , xmlforest( k.partname as "PartName"

                                , k.makerref as "MakerRef"

                                , k.quantity as "Quantity"

                                , k.price as "Price"

                                , k.currencycode as "CurrencyCode"

                                , K.INCLUDEINCLAIMCOST as "IncludeInClaimCost"

                                )

                               )

                              )

                from claimstockitem k

                where a.claimid=k.claimid(+)

               )  

            ) 

           , xmlelement("Yard"

             , xmlelement("YardApprovedBy", o.name)

             , xmlelement("YardNotes", a.yardnotes)

             , xmlelement("YardApprovedDate", a.yardapproveddate)

             , xmlelement("YardStatus", a.yardstatus)

             , xmlelement("YardRejectedDate", a.yardrejecteddate)

             , xmlelement("YardRejectedBy", p.name)

                        )

       ) as claim_xml 

      from claim a, claimdept b, claimstatus c, claimtype e, amosorderform f, amosworkorder g, asset h, amos.amosuser l,amos.amosuser m

      , amos.amosuser n, amos.amosuser o, amos.amosuser p

      where a.claimid=xml_orderid

      and a.assetid=h.assetid(+)

      and a.statusid=c.statusid(+)

      and a.deptcode=b.deptcode(+)

      and a.claimid=f.claimid(+)

      and a.claimid=g.claimid(+)

      and a.createdby=l.userid(+)

      and a.damageapprovedby=m.userid(+)

      and a.repairapprovedby=n.userid(+)

      and a.yardapprovedby=o.userid(+)

      and a.yardrejectedby=p.userid(+)

       );

  select claimno into v_po_no from claim

  where claimid=  XML_ORDERID;

  v_po_no:= 'CLAIM-'||v_po_no||'.xml';     

  --dbms_output.put_line('2');

  --dbms_output.put_line('3');

  --dbms_output.put_line('4');

  --dbms_output.put_line('order_xml'||order_xml1);

  dbms_output.put_line('6');

  DBMS_XSLPROCESSOR.clob2file('<?xml version="1.0" encoding="UTF-8" standalone="no"?>'||chr(10)||claims_xml1,'e:\amos-sap',v_po_no);

  dbms_output.put_line('7');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));

    --UTL_FILE.fclose(v_file);

END;

End;

/

Thanks in advance.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 18 2018
Added on Sep 19 2018
3 comments
3,437 views