/
/ $Header: sdo/admin/LoadClobs.txt /main/1 2008/10/09 08:17:11 mhorhamm Exp $
/
/ LoadClobs.txt
/
/ Copyright (c) 2008, Oracle. All Rights Reserved.
/
/   NAME
/     LoadClobs.txt - <one-line expansion of the name>
/
/   DESCRIPTION
/     <short description of component this file declares/defines>
/
/   NOTES
/     <other useful comments, qualifications, etc.>
/
/   MODIFIED   (MM/DD/YY)
/   mhorhamm    10/09/08 - Creation
/

If the database is created in a non-default character set,
this may cause the NADCON coordinate transformation to fail,
due to the character set of some NADCON CLOBs in the
SDO_COORD_OP_PARAM_VALS table.

This document describes how to reload these CLOBs:

 - Create a temporary table, in the MDSYS schema:

     CREATE TABLE lob_table (
       file_name_official VARCHAR2(30),
       file_name_actual   VARCHAR2(30),
       lob_data           CLOB);



 - Locate the folder with the NADCON CLOBs (*.laa, *.loa):

     <ORACLE_HOME>\md\admin



 - Load the CLOBs into that table, using SQL*Loader
   (make sure that *.laa, *.loa, LoadClobs.ctl are available,
    use appropriate pwd for MDSYS):

     sqlldr userid=mdsys/MDSYSSSSS control=LoadClobs.ctl log=LoadClobs.log



 - Copy the CLOBs into SDO_COORD_OP_PARAM_VALS, in the MDSYS schema

     update
       sdo_coord_op_param_vals vals
     set
       vals.param_value_file =
         (select
            lob_data from lob_table tmp
          where
            tmp.file_name_official = upper(vals.param_value_file_ref))
     where
       parameter_id in (8657, 8658);

     commit;



 - Delete the temporary table, in the MDSYS schema

     drop table lob_table;