2 Replies Latest reply: Feb 25, 2014 10:29 AM by L-MachineGun RSS

    need a script or  utility to convert Oracle control files over to bcp format files

    ivw

      Hello,

       

       

      I am currently in the process of converting an Oracle data warehouse

      over to SQL Server. The warehouse is loaded through a bunch of text

      files all through SQL Loader, using various control files. I need to

      convert this over to use BCP with format files, which has proven to be

      a very tedious task.

       

       

      Has anyone written, or seen, a utility to convert Oracle control files

      over to format files?

       

       

      Thanks,

        • 1. Re: need a script or  utility to convert Oracle control files over to bcp format files
          L-MachineGun

          You could write one yourself by querying  the {ALL|DBA}_TAB_COLUMNS view.

           

          • 2. Re: need a script or  utility to convert Oracle control files over to bcp format files
            L-MachineGun

            Has anyone written, or seen, a utility to convert Oracle control files

            over to format files?

            You are very, very lucky, I found a script that I wrote some time ago that may help you:

             

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

            -- gen_bcp_fmt.sql -- Generate a bcp non-xml format file

            --

            -- Parameters:

            --              1.- Schema name

            --              2.- Table_Name

            --

            DEF schema=&1

            DEF tbl_name=&2

            SET HEAD OFF PAGES 0 LIN 120 TRIMS on FEED OFF VER OFF RECSEP OFF EMBED ON

            SET TERM OFF

            COL sq NOPRINT

            COL c1 FOR A15

            COL c2 FOR 99999

            COL c3 FOR A06

            COL c4 FOR 999

            COL c5 FOR A34

            COL c6 FOR A34

            COL maxid NEW_VALUE _Maxid NOPRINT

            SELECT TRIM(MAX ( Column_Id)) Maxid

              FROM Dba_Tab_Columns

            WHERE Owner = UPPER('&&schema.')

               AND Table_Name = UPPER('&&tbl_name.');

            SPO &&schema._&&tbl_name..bcp

            SELECT '9.0'  c1 FROM DUAL;

            SELECT '&&_maxid' c1 FROM DUAL;

            SELECT   TO_CHAR ( Column_Id, 'fm000') || LPAD ( 'SQLCHAR 0', 10) C1

                   , CASE WHEN Data_Type = 'NUMBER' THEN (Data_Precision + Data_Scale + 2)

                          WHEN Data_Type = 'DATE' THEN (14)

                          ELSE Data_Length END AS C2

                   , DECODE (Column_Id, &&_Maxid, '"\r\n"', '","') C3

                   , Column_Id C4

                   , Column_Name C5

                   , DECODE (Column_Id, 1, '""', 'SQL_Latin1_General_CP1_CI_AS') C6

                FROM Dba_Tab_Columns

               WHERE Owner = UPPER('&&schema.')

                 AND Table_Name = UPPER('&&tbl_name.')

            ORDER BY Column_id;

            SPO OFF

             

            Enjoy!