Oracle 我必须转换整个文件在UTF-8字符

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) || '';

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('');
  NL;

  P('');
  NL;
 
  P('');
  P(TAG('CreationDateTime', TO_CHAR(CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')));
  
  P('');
  P('  INTLECM');
  P('  International Product load');
  P('');
  P('');
  NL;
  
  P('');
  P('');
  NL;
    
  FOR C1 IN CURSOR01 LOOP 
    P('');
    
--Name
    IF IS_ISBN10(C1.MHID) = 1 THEN
      P(TAG('Name', C1.MHID)); 
    END IF;
--    P('');
--    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('');
   
    IF C1.ISBN IS NOT NULL THEN
      P('' || C1.ISBN || '');
    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('');
    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('');
    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('');
    P('');

--ItemProgramCharacteristics

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

--ItemSourceSystemInformation

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

--ItemPhysicalCharacteristics

    P('');
    P('' ||  TRIM(TO_CHAR(C1.PRODUCT_HEIGHT, '99990.9999')) || '');
    P('' ||  TRIM(TO_CHAR(C1.PRODUCT_LENGTH, '99990.9999')) || '');
    P('' ||  TRIM(TO_CHAR(C1.PRODUCT_WIDTH, '99990.9999')) || '');
    P('' || TRIM(TO_CHAR(C1.PRODUCT_WEIGHT, '99990.9999')) || '');
    P('' || C1.NUMBER_OF_PAGES || '');
    P('');

--InventoryCharacteristics

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

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

--ItemOrderManagementCharacteristics

    P('');
    P('');
    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('');
    IF C1.CONTRACT_ONLY_INDICATOR IS NOT NULL THEN 
      P('');
      P(TAG('ContractCustomersOnlyIndicator', ITE(C1.CONTRACT_ONLY_INDICATOR = 'Y', 'true', 'false')));
      P('');
    END IF;
    P('');

--ItemServiceCharacteristics
--InventoryLocation
--ItemOwningCharaceteristics

    P('');
    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('');

--ItemPricingCharacteristics
--ItemRegionalPricingCharacteristics
--ItemMarketingCharacteristics

    IF C1.MARKETING_TITLE IS NOT NULL THEN
      P('');
      P('');
      P(TAG('MarketingTitle', C1.MARKETING_TITLE));
      P('');
      P('');
    END IF;

--ItemPublishingIndicators

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

--ItemPublisherDetails
--ItemPublishingStatus

    P('');
    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('');

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

  IF C1.PRIOR_EDITION_MHID IS NOT NULL AND C1.PRIOR_EDITION_VALID = 1 THEN
    P('');
    P('Prior Edition');
    P('');
    P('');
    P('' || C1.PRIOR_EDITION_MHID || '');
    P('');
    P('');
    P('');
  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('');
    P('' || C1.SUBSTITUTION_MHID || '');
    P('W');
    P(''); 
  END IF;

--MilestoneDates
--InternationalPublishRestrictions

    P('');
    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('');

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

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

    P('');
    NL;
  END LOOP;

  P('');
  P('');
  P('');
  
  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;
/