Skip to Main Content
  • Questions
  • I have to convert whole file in UTF-8 character

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, NITESH.

Asked: September 17, 2020 - 6:38 am UTC

Last updated: September 21, 2020 - 1:52 am UTC

Version: 12C

Viewed 1000+ times

You Asked

CREATE OR REPLACE PACKAGE BODY INTLECM.PRODUCT_UTF_CHECK AS
G_FILE              UTL_FILE.FILE_TYPE;
  DEBUG_PROCESS_ID    LOG_PARAMETERS.PROCESS_ID%TYPE;
  PROCEDURE NL IS
BEGIN

  UTL_FILE.NEW_LINE(G_FILE);

END NL;
FUNCTION ITE (B BOOLEAN, T NVARCHAR2, F NVARCHAR2) RETURN NVARCHAR2 IS
BEGIN
  IF B THEN
    RETURN T;
  ELSE
    RETURN F;
  END IF;
END ITE;

PROCEDURE P (P_TEXT IN VARCHAR2) IS

BEGIN
   
 --UTL_FILE.PUT_LINE_NCHAR(G_FILE, P_TEXT);

  UTL_FILE.PUT_LINE_NCHAR(G_FILE, CONVERT(TO_NCHAR(P_TEXT), 'WE8MSWIN1252', 'AL16UTF16'));

END P;

FUNCTION TAG (P_TAG VARCHAR2, P_TEXT NVARCHAR2 DEFAULT NULL) RETURN NVARCHAR2 IS
BEGIN

  RETURN '<' ||TRIM (P_TAG) || '>' || ITE(TRIM(P_TEXT) IS NOT NULL, TRIM(HTF.ESCAPE_SC(P_TEXT)), NULL) || '</' || TRIM (P_TAG) || '>';

END TAG;
PROCEDURE PRODUCT (P_REGION IN NVARCHAR2 DEFAULT NULL) IS

    CURSOR CURSOR01 IS
    SELECT EP.*,
           DECODE(P1.ISBN, NULL, 0, 1) SUBSTITUTION_VALID_PDH, 
           DECODE(P2.ISBN, NULL, 0, 1) PRIOR_EDITION_VALID_PDH
      FROM TMP_UTF8_PRODUCT EP,
           INTLDBO.PRODUCT IP,
           INTLDBO.PRODUCT P1,
           INTLDBO.PRODUCT P2,
           INTLDBO.SELLING_RIGHTS SR
     WHERE EP.MHID = IP.ISBN(+)
       AND EP.SUBSTITUTION_MHID = P1.ISBN(+)
       AND EP.PRIOR_EDITION_MHID = P2.ISBN(+)
       AND EP.SELLING_RIGHTS_CODE = SR.SELLING_RIGHTS_CODE(+)
      AND REGION = DECODE(P_REGION, NULL, REGION, P_REGION) 
      AND (TRUNC(EP.DATE_ADDED) = TRUNC(SYSDATE) OR TRUNC(EP.DATE_MODIFIED) = TRUNC (TO_DATE( '01-05-2020', 'DD-MM-YYYY' ), 'DD'))
       AND EP.MHID NOT IN (SELECT MHID FROM ECM_NOLOAD)
       AND ((EP.AUTHOR IS NOT NULL AND EP.EDITION IS NOT NULL AND EP.COPYRIGHT_DATE IS NOT NULL AND IP.ISBN IS NULL 
       AND (EP.SELLING_RIGHTS_CODE IS NULL OR SR.SELLING_RIGHTS_CODE IS NOT NULL)
       ) OR IP.ISBN IS NOT NULL)
       AND ((IS_ISBN10(EP.MHID) = 0 AND EP.ISBN IS NOT NULL) OR IS_ISBN10(EP.MHID) = 1)
     ORDER BY EP.REGION, EP.ISBN;
BEGIN

  G_FILE := UTL_FILE.FOPEN_NCHAR ('INTLECM_OUTBOUND', 'NITESHproduct' || TRIM(UPPER(P_REGION)) || '.xml', 'w', 32767);
--  UTL_FILE.PUT_LINE_NCHAR(G_FILE, convert('NITESHproduct.xml', 'WE8MSWIN1252', 'AL16UTF16'));
  P('<?xml version="1.0" encoding="UTF-8"?>');
  NL;

  P('<e:LoadItemListEBM');
  P('xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"');
  P('xmlns="http://xmlns.mheducation.com/EnterpriseObjects/Core/CommonEBO/V1"');
  P('xmlns:a="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2"');
  P('xmlns:d="http://xmlns.mheducation.com/EnterpriseObjects/Core/Common/V1"');
  P('xmlns:e="http://xmlns.mheducation.com/EnterpriseObjects/Core/EBO/Item/V1"');
  P('xmlns:f="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2"');
  P('xmlns:g="http://xmlns.oracle.com/EnterpriseObjects/Core/CommonEBO/V1"');
  P('xsi:schemaLocation="http://xmlns.mheducation.com/EnterpriseObjects/Core/EBO/Item/V1 EOL/mheducation/EnterpriseObjectLibrary/Core/EBO/Item/V1/ItemEBM.xsd');
  P('                   http://xmlns.mheducation.com/EnterpriseObjects/Core/EBO/Item/V1 EOL/mheducation/EnterpriseObjectLibrary/Core/EBO/Item/V1/ItemEBO.xsd');
  P('                   http://xmlns.mheducation.com/EnterpriseObjects/Core/CommonEBO/V1 EOL/mheducation/EnterpriseObjectLibrary/Core/CommonEBO/V1/Item.xsd');
  P('                   http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2 EOL/oracle/EnterpriseObjectLibrary/Infrastructure/V1/Meta.xsd ">');
  NL;
 
  P('<a:EBMHeader xmlns="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2">');
  P(TAG('CreationDateTime', TO_CHAR(CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')));
  
  P('<Sender>');
  P('  <ID>INTLECM</ID>');
  P('  <Description>International Product load</Description>');
  P('</Sender>');
  P('</a:EBMHeader>');
  NL;
  
  P('<e:DataArea>');
  P('<e:LoadItemList>');
  NL;
    
  FOR C1 IN CURSOR01 LOOP 
    P('<OrganizationItem Name="' || C1.REGION || '">');
    
--Name
    IF IS_ISBN10(C1.MHID) = 1 THEN
      P(TAG('Name', C1.MHID)); 
    END IF;
--    P('<f:Status>');
--    P(TAG('f:Code', C1.OPIM_PUB_STATUS_CODE));
--    IF C1.PUB_STATUS_DATE IS NOT NULL THEN
--      P(TAG('f:EffectiveDateTime', TO_CHAR(C1.PUB_STATUS_DATE, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')));
--    END IF;
--    P('</f:Status>');
   
    IF C1.ISBN IS NOT NULL THEN
      P('<f:ItemIdentification><f:AlternateObjectKey><f:ID schemeID="ISBN13">' || C1.ISBN || '</f:ID></f:AlternateObjectKey></f:ItemIdentification>');
    END IF; 

--Description
--corecom:Status
--TypeCode
--BaseUOMCode
--corecom:ItemIdentification
--AuthorID
--ProjectNumber
--ProgramISBN
--LegacyStatSponsorCode

    IF C1.STAT_SPONSOR IS NOT NULL THEN
      P(TAG('LegacyStatSponsorCode', C1.STAT_SPONSOR));
    END IF;

--LegacyProductGroupCode

    IF C1.PRODUCT_GROUP IS NOT NULL THEN
      P(TAG('LegacyProductGroupCode', C1.PRODUCT_GROUP));
    END IF;

--ItemDetails

    P('<ItemDetails>');
    P(TAG('ISBNRequiredFlag', ITE(C1.ISBN IS NOT NULL, 'Y', 'N')));
    P(TAG('EBSFullTitle', SUBSTRB(TO_CHAR(C1.LONG_TITLE), 1, 80)));
    P(TAG('EBSShortTitle', SUBSTRB(TO_CHAR(C1.SHORT_TITLE), 1, 30)));
    
    IF C1.EDITION IS NOT NULL THEN
      P(TAG('Edition', C1.EDITION));
    END IF;
    
    IF C1.AUTHOR IS NOT NULL THEN
     P(TAG('LeadContributor', SUBSTRB(TO_CHAR(C1.AUTHOR), 1, 15)));
   END IF;
    
    P(TAG('CopyrightYear', C1.COPYRIGHT_DATE));
    
    P('<IntlTitleInformation>');
    P(TAG('IntlTitle', C1.INTERNATIONAL_TITLE));
   -- IF C1.INTERNATIONAL_LEAD_CONTRIBUTOR IS NOT NULL THEN
    --  P(TAG('IntlLeadContributor', SUBSTRB(TO_CHAR(C1.INTERNATIONAL_LEAD_CONTRIBUTOR), 1, 40)));
  --  END IF;
    P('</IntlTitleInformation>');
    P('</ItemDetails>');

--ItemProgramCharacteristics

    IF C1.TITLE_TYPE IS NOT NULL THEN
      P('<ItemProgramCharacteristics>');
      P(TAG('TitleType', C1.TITLE_TYPE));
      P('</ItemProgramCharacteristics>');
    END IF;

--ItemSourceSystemInformation

    IF IS_ISBN10(C1.MHID) = 0 THEN
      P('<ItemSourceSystemInformation>');
      P(TAG('SourceId', C1.MHID));
      P('</ItemSourceSystemInformation>');
    END IF;

--ItemPhysicalCharacteristics

    P('<ItemPhysicalCharacteristics>');
    P('<HeightMeasureSI unitCode="cm">' ||  TRIM(TO_CHAR(C1.PRODUCT_HEIGHT, '99990.9999')) || '</HeightMeasureSI>');
    P('<LengthMeasureSI unitCode="cm">' ||  TRIM(TO_CHAR(C1.PRODUCT_LENGTH, '99990.9999')) || '</LengthMeasureSI>');
    P('<WidthMeasureSI unitCode="cm">' ||  TRIM(TO_CHAR(C1.PRODUCT_WIDTH, '99990.9999')) || '</WidthMeasureSI>');
    P('<WeightMeasureSI unitCode="gm">' || TRIM(TO_CHAR(C1.PRODUCT_WEIGHT, '99990.9999')) || '</WeightMeasureSI>');
    P('<ItemTextCharacteristics><NumberOfPages>' || C1.NUMBER_OF_PAGES || '</NumberOfPages></ItemTextCharacteristics>');
    P('</ItemPhysicalCharacteristics>');

--InventoryCharacteristics

    IF C1.AVAILABLE_QUANTITY IS NOT NULL THEN
      P('<InventoryCharacteristics>');
      P('<SupplyAttributes>');
      P(TAG('AvailableQty', C1.AVAILABLE_QUANTITY));
      P('</SupplyAttributes>');
      P('</InventoryCharacteristics>');
    END IF;
    
--ItemPurchasingCharacteristics
--ItemPlanningCharacteristics
--ItemManufacturingCharacteristics
--ItemContainerCharacteristics

    IF C1.CARTON_QUANTITY IS NOT NULL THEN
      P('<ItemContainerCharacteristics>');
      P(TAG('CartonQuantity', C1.CARTON_QUANTITY));
      P('</ItemContainerCharacteristics>');
    END IF;

--ItemOrderManagementCharacteristics

    P('<ItemOrderManagementCharacteristics>');
    P('<ProcessingCapabilitiess>');
    IF C1.PUBLICATION_DATE IS NOT NULL THEN
      P(TAG('PublicationDate', TO_CHAR(C1.PUBLICATION_DATE, 'YYYY-MM-DD')));
    END IF;
    IF C1.INSTRUCTOR_MANUAL_INDICATOR IS NOT NULL THEN 
      P(TAG('InstructorMaterialIndicator', ITE(C1.INSTRUCTOR_MANUAL_INDICATOR = 'Y', 'true', 'false')));
    END IF;
    IF C1.SELLING_RIGHTS_CODE IS NOT NULL THEN
      P(TAG('SellingRights', C1.SELLING_RIGHTS_CODE));
    END IF;
    P('</ProcessingCapabilitiess>');
    IF C1.CONTRACT_ONLY_INDICATOR IS NOT NULL THEN 
      P('<FulfillmentSystemResctrictions>');
      P(TAG('ContractCustomersOnlyIndicator', ITE(C1.CONTRACT_ONLY_INDICATOR = 'Y', 'true', 'false')));
      P('</FulfillmentSystemResctrictions>');
    END IF;
    P('</ItemOrderManagementCharacteristics>');

--ItemServiceCharacteristics
--InventoryLocation
--ItemOwningCharaceteristics

    P('<ItemOwningCharaceteristics>');
    P(TAG('Subdivision', C1.SUBDIVISION_CODE));
    IF C1.AREA IS NOT NULL THEN
      P(TAG('Area', C1.AREA));
    END IF;
    IF C1.DISCIPLINE IS NOT NULL THEN
      P(TAG('Discipline', C1.DISCIPLINE));
    END IF;
    P('</ItemOwningCharaceteristics>');

--ItemPricingCharacteristics
--ItemRegionalPricingCharacteristics
--ItemMarketingCharacteristics

    IF C1.MARKETING_TITLE IS NOT NULL THEN
      P('<ItemMarketingCharacteristics>');
      P('<MarketingTitleInfo>');
      P(TAG('MarketingTitle', C1.MARKETING_TITLE));
      P('</MarketingTitleInfo>');
      P('</ItemMarketingCharacteristics>');
    END IF;

--ItemPublishingIndicators

    IF C1.PUBNET_INDICATOR IS NOT NULL THEN 
      P('<ItemPublishingIndicators>');
      P(TAG('PubnetPublishIndicator', ITE(C1.PUBNET_INDICATOR = 'Y', 'true', 'false')));
      P('</ItemPublishingIndicators>');
    END IF;

--ItemPublisherDetails
--ItemPublishingStatus

    P('<ItemPublishingStatus>');
    P(TAG('PublicationStatus', C1.OPIM_PUB_STATUS_CODE));
    IF C1.PUB_STATUS_DATE IS NOT NULL THEN
      P(TAG('StatusDate', TO_CHAR(C1.PUB_STATUS_DATE, 'YYYY-MM-DD')));
    END IF;
    P('</ItemPublishingStatus>');

--ItemAccountingCharacteristics
--corecomEBO:ItemClassification
--corecomEBO:RelatedItem

  IF C1.PRIOR_EDITION_MHID IS NOT NULL AND C1.PRIOR_EDITION_VALID = 1 THEN
    P('<g:RelatedItem>');
    P('<g:RelationshipCode>Prior Edition</g:RelationshipCode>');
    P('<a:ItemReference>');
    P('<a:ItemIdentification>');
    P('<a:ID schemeAgencyID="MHE" schemeID="MHID">' || C1.PRIOR_EDITION_MHID || '</a:ID>');
    P('</a:ItemIdentification>');
    P('</a:ItemReference>');
    P('</g:RelatedItem>');
  END IF;
  
--ItemInvestmentCharacteristics
--ItemUsageCharacteristics
--ItemRoyaltyAndPermissionsCharacteristics
--corecomEBO:ItemCatalog
--corecommhe:CrossReference
--ItemContributors
--ItemTaxonomy
--CRMFlags
--EditorialInfoOnlineSites
--ProductContacts
--MediaReviews
--SupplementProductLinkages
--InternalCompetitionProducts
--ExternalCompetitionProducts
--SubstitutionInformation

  IF C1.SUBSTITUTION_MHID IS NOT NULL AND C1.SUBSTITUTION_VALID = 1 THEN    
    P('<SubstitutionInformation>');
    P('<SubstituteToItem>' || C1.SUBSTITUTION_MHID || '</SubstituteToItem>');
    P('<SubstitutionTypeCode>W</SubstitutionTypeCode>');
    P('</SubstitutionInformation>'); 
  END IF;

--MilestoneDates
--InternationalPublishRestrictions

    P('<InternationalPublishRestrictions>');
    IF C1.ESALESENABLED IS NOT NULL THEN 
      P(TAG('eSalesEnabled', ITE(C1.ESALESENABLED = 1, 'true', 'false')));
    END IF;
    IF C1.SAMPLEFROMCRMENABLED IS NOT NULL THEN 
      P(TAG('SampleFromCRMEnabled', ITE(C1.SAMPLEFROMCRMENABLED = 1, 'true', 'false')));
    END IF;
    IF C1.OPENUPENABLED IS NOT NULL THEN 
      P(TAG('OpenUniversityPressEnabled', ITE(C1.OPENUPENABLED = 1, 'true', 'false')));
    END IF;
    IF C1.ECOMMERCEENABLED IS NOT NULL THEN 
      P(TAG('eCommerceEnabled', ITE(C1.ECOMMERCEENABLED = 1, 'true', 'false')));
    END IF;
    P('</InternationalPublishRestrictions>');

--LastOrderReceived
--ComponentItems
--Features
--CopyMirrorAttributes
--CustomProductDevelopment 

IF C1.DURATION IS NOT NULL THEN 
      P('<SubscriptionSpecifications>');
      P(TAG('Duration', C1.DURATION));
      P(TAG('DurationPeriod', C1.DURATION_PERIOD));
      P('</SubscriptionSpecifications>');
    END IF;
 --Adding SubscriptionSpecifications

    P('</OrganizationItem>');
    NL;
  END LOOP;

  P('</e:LoadItemList>');
  P('</e:DataArea>');
  P('</e:LoadItemListEBM>');
  
  UTL_FILE.FFLUSH (G_FILE);
  UTL_FILE.FCLOSE (G_FILE);
  
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(G_FILE) THEN
      UTL_FILE.FFLUSH(G_FILE);      
      UTL_FILE.FCLOSE(G_FILE);     
    END IF;

    UTL_DEBUG.LOG_MESSAGE (
      PROCESS_ID   => DEBUG_PROCESS_ID,
      OBJECT_NAME  => 'PRODUCT_UTF_CHECK.PRODUCT',
      MESSAGE_TYPE => 'E',
      LOG_MESSAGE  => SQLERRM || UTL_TCP.CRLF || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
      SQLCODE      => SQLCODE,
      SQLERRM      => SQLERRM);   
  
END PRODUCT;
BEGIN

  DEBUG_PROCESS_ID := UTL_DEBUG.INITIALIZE_DEBUG('NITESH');

END;
/

and Connor said...

And there is a question here ?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Didn't get Ans of my question

NITESH MALVIYA, September 18, 2020 - 5:15 am UTC

I have to convert whole file in UTF-8 but when i am opening a file it's coming with special characters
Connor McDonald
September 21, 2020 - 1:52 am UTC

You didnt ASK a question.

You just pasted a chunk of code with no example of:

- what it is doing
- what is it meant to do
- where it is going wrong
- sample data

etc....

You have to help us to help you

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library