bcp out for Oracle

Thu May 5, 2005 by Brett Kaiser (x002548) in oracle

As of Oracle 8i (which I believe is no longer supported) there was no utility to unload data. I guess Oracle felt that once the got the data, there was no reason to relinquesh it. In any case, for myself, and my rusty Oracle, here a sample of code on how to code in PLSQL as well as how to perform an extract. I know it'll be hard from a sql server perspective, but this actually flies.

-- @Q:\Packages\Ben_Extract_Package.pkg;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_PLAN_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_DPND_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_DPND_BENEF_sp;

-- ***********************************************************************
-- *         Prudential Benefits Annual Enrollment System                *
-- *          *
-- * Description: Extract Feed to PeopleSoft                             *
-- *              Specifications used:                                   *
-- *              Web-IVR_To_PeopleSoft_Files_Layout.doc                 *
-- * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
-- * Modifications Log:                                                  *
-- *          *
-- * User     Date       Description                                     *
-- * -------- ---------- ------------------------------------------------*
-- * x002548 07/19/2000 Initial Version                                 *
-- * x002548 10/12/2000 Modify PESP Extract to include format changes, *
-- *                     Change the amount fields to 2 byte integers     *
-- * x002548 09/25/2000 Modify PESP Extract to include format changes, *
-- *                     truncate the National_Id to 9 bytes from 15, and*
-- *                     add a trailer record                            *
-- * - - - - - - - - - - - - - 2002 Changes - - - - - - - - - - - - - - -*
-- * x091589 08/22/2001 Add SCHED_ID to Ex_ENR_PARTIC_sp   *
-- *      Ex_ENR_PARTIC_PLAN_sp   *
-- *      Ex_ENR_PARTIC_DPND_sp   *
-- * x091589 08/22/2001 Add STUDENT_CERTIFY to Ex_ENR_DPND_BENEF_sp *
-- * x091589 08/22/2001 Remove Ex_PESP_sp     *
-- * x091589 10/03/2001 Increase the size of EMPL_CONTRBUTN_AMT from 8-9*
-- *    When Null      *
-- *          *
-- ***********************************************************************
-- ********** S T A R T - G L O B A L - D E C L A R A T I O N S **********
-- ***********************************************************************

CREATE OR REPLACE PACKAGE Ben_Extract_Package AS

I_FileType utl_file.file_type;
I_FilePath VarChar2(200);
I_FileName VarChar2(50);
O_SqlCode Number;
O_SqlErrM VarChar2(254);
O_RowCount Number;

-- ***********************************************************************
-- * Procedure Calls *
-- ***********************************************************************

    PROCEDURE Ex_ENR_PARTIC_sp;
    PROCEDURE Ex_ENR_PARTIC_PLAN_sp;
    PROCEDURE Ex_ENR_PARTIC_DPND_sp;
    PROCEDURE Ex_ENR_DPND_BENEF_sp;

END Ben_Extract_Package;

CREATE OR REPLACE PACKAGE BODY Ben_Extract_Package
AS

-- **********************************************************************
-- *** P R O C E D U R E (Ex_ENR_PARTIC_sp) D E C L A R A T I O N S *****
-- **********************************************************************
PROCEDURE Ex_ENR_PARTIC_sp AS
v_row_count number := 0;

Begin

-- Declare Cursor to extract the ENR_PARTIC table as a 1 Column fixed width 124 bytes
-- called Unload_String

Declare Cursor
CSR_ENR_PARTIC IS
    SELECT
    RPAD(A.EMPLID,11)
  ||NVL((RPAD(A.BENEFIT_PROGRAM,3)), '   ')
  ||NVL((RPAD(A.EVENT_CLASS,3)), '   ')
  ||NVL((RPAD(A.EVENT_STATUS,1)), ' ')
  ||NVL((RPAD(A.BAS_PROCESS_STATUS,2)), ' ')
  ||NVL((TO_CHAR(A.EVENT_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((RPAD(A.ELECT_SUMM,1)), ' ')
  ||NVL((TO_CHAR(A.ELECT_SUMM_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((RPAD(A.SURVEY_COMPLETE,1)), ' ')
  ||NVL((TO_CHAR(A.SURVEY_COMPLETE_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((TO_CHAR(A.DATAASOF_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((TO_CHAR(A.DATAIN_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((TO_CHAR(A.DATAOUT_DT, 'YYYY-MM-DD')),'          ')
  ||TO_CHAR(A.LASTUPDDTTM, 'yyyy-mm-dd-hh24.mi.ss')||'.000000'
  ||NVL((RPAD(A.LASTUPDUSER,15)),'               ')
  ||NVL((RPAD(A.LASTUPDVEH,1)), ' ')
  ||RPAD(NVL(C.SCHED_ID,' '),6) AS Unload_String
      FROM   ENR_PARTIC A
     ,ENR_EMPLOY_DATA B
     ,ENR_USER_SCRTY C
     WHERE A.EMPLID    = B.EMPLID
       AND B.NATIONAL_ID = C.NATIONAL_ID;

csr_Columns CSR_ENR_PARTIC%ROWTYPE;

Begin

Ben_Extract_Package.O_SqlCode := 0;
Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
Ben_Extract_Package.I_FileName := 'ENR_PARTIC.txt';
Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

I_FileType := utl_file.fopen (I_FilePath,I_FileName,'W');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records. The Cursor Open, Fetch and Close is dynamic
For csr_Columns IN CSR_ENR_PARTIC
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose (Ben_Extract_Package.I_FileType);

     dbms_output.put_line('ENR_PARTIC EXTRACT COUNT:'||to_char(v_row_count));
   EXCEPTION
WHEN utl_file.invalid_path THEN
  dbms_output.put_line(' FAILS Extract - Invalid path');
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid File Path';
WHEN utl_file.invalid_operation THEN
  dbms_output.put_line(' FAILS Extract - Invalid operation');
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Operation';
WHEN utl_file.invalid_mode THEN
  dbms_output.put_line(' FAILS Extract - Invalid mode');
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Mode';
WHEN OTHERS THEN
  dbms_output.put_line('SqlError:='||SQLERRM||' SQLCODE:='||SQLCODE);
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := SqlErrM(SqlCode);
End;
   END Ex_ENR_PARTIC_sp;

-- ***************************************************************************
-- *** P R O C E D U R E (Ex_ENR_PARTIC_PLAN_sp) D E C L A R A T I O N S *****
-- ***************************************************************************
PROCEDURE Ex_ENR_PARTIC_PLAN_sp AS
v_row_count number := 0;

Begin
-- Declare Cursor to extract the ENR_PARTIC_PLAN table as fixed width 124 bytes

Declare Cursor
CSR_ENR_PARTIC_PLAN IS

    SELECT
     RPAD(A.EMPLID,11)
  ||RPAD(A.PLAN_TYPE,2)
  ||RPAD(NVL(A.BENEFIT_PROGRAM,' '),3)
  ||RPAD(NVL(TO_CHAR(A.EVENT_DT, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.DISPLAY_PLN_SEQ,' '),2)
  ||RPAD(NVL(A.ELECTION_MADE,' '),1)
  ||RPAD(NVL(A.PROOF_RCVD,' '),1)
  ||RPAD(NVL(A.OPTION_CD,' '),3)
  ||RPAD(NVL(A.ALT_OPTION_CD,' '),3)
  ||NVL(LPAD(TO_CHAR(A.PCT_GROSS, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.PCT_GROSS_ATAX, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.CUR_PCT_GROSS, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.CUR_PCT_GROSS_ATAX, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.EMPL_CONTRBUTN_AMT, +99990.99),9,0),'     0.00')
  ||NVL(LPAD(TO_CHAR(A.ANNUAL_PLEDGE, +99990.99),9,0),'     0.00')
  ||RPAD(NVL(A.CUR_HLTH_PROVIDER,' '),1)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID1,' '),30)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID2,' '),30)
  ||RPAD(NVL(A.HIGHLY_COMP_EE,' '),1)
  ||RPAD(NVL((TO_CHAR(A.LASTUPDDTTM, 'yyyy-mm-dd-hh24.mi.ss')||'.000000'),' '),26)
  ||RPAD(NVL(A.LASTUPDUSER,' '),15)
  ||RPAD(NVL(A.LASTUPDVEH,' '),1)
  ||RPAD(NVL(C.SCHED_ID,' '),6) AS Unload_String
      FROM   ENR_PARTIC_PLAN A
     ,ENR_EMPLOY_DATA B
     ,ENR_USER_SCRTY C
     WHERE A.EMPLID    = B.EMPLID
       AND B.NATIONAL_ID = C.NATIONAL_ID;

csr_Columns CSR_ENR_PARTIC_PLAN%ROWTYPE;

Begin

Ben_Extract_Package.O_SqlCode := 0;
Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
Ben_Extract_Package.I_FileName := 'ENR_PARTIC_PLAN.txt';
Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

-- Open File
Ben_Extract_Package.I_FileType := utl_file.fopen (Ben_Extract_Package.I_FilePath,Ben_Extract_Package.I_FileName,'w');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records. The Cursor Open, Fetch and Close is dynamic

For csr_Columns IN CSR_ENR_PARTIC_PLAN
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose (Ben_Extract_Package.I_FileType);

dbms_output.put_line('ENR_PARTIC_PLAN EXTRACT COUNT:'||to_char(v_row_count));

   EXCEPTION
WHEN utl_file.invalid_path THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid File Path';
WHEN utl_file.invalid_operation THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Operation';
WHEN utl_file.invalid_mode THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Mode';
WHEN OTHERS THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := SqlErrM(SqlCode);
End;
   END Ex_ENR_PARTIC_PLAN_sp;

-- ***************************************************************************
-- *** P R O C E D U R E (Ex_ENR_PARTIC_DPND_sp) D E C L A R A T I O N S *****
-- ***************************************************************************
PROCEDURE Ex_ENR_PARTIC_DPND_sp AS
v_row_count number := 0;

Begin
-- Declare Cursor to extract the ENR_PARTIC_DPND table as fixed width n bytes

Declare Cursor
CSR_ENR_PARTIC_DPND IS

SELECT
     RPAD(A.EMPLID,11)
  ||RPAD(A.PLAN_TYPE,2)
  ||RPAD(NVL(A.DEPENDENT_BENEF,' '),2)
  ||RPAD(NVL(A.BENEFIT_PROGRAM,' '),3)
  ||RPAD(NVL(TO_CHAR(A.EVENT_DT, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.DISPLAY_PLN_SEQ,' '),2)
  ||LPAD(TO_CHAR(NVL(A.BENEF_PCT,0)),3,'0')
  ||RPAD(NVL(A.CONTINGENT,' '),1)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID1,' '),30)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID2,' '),30)
  ||RPAD(NVL(A.CUR_HLTH_PROVIDER,' '),1)
  ||RPAD(NVL(C.SCHED_ID,' '),6) AS Unload_String
      FROM   ENR_PARTIC_DPND A
     ,ENR_EMPLOY_DATA B
     ,ENR_USER_SCRTY C
     WHERE A.EMPLID    = B.EMPLID
       AND B.NATIONAL_ID = C.NATIONAL_ID;

csr_Columns CSR_ENR_PARTIC_DPND%ROWTYPE;

Begin

Ben_Extract_Package.O_SqlCode := 0;
Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
Ben_Extract_Package.I_FileName := 'ENR_PARTIC_DPND.txt';
Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

-- Open File
Ben_Extract_Package.I_FileType := utl_file.fopen (Ben_Extract_Package.I_FilePath,Ben_Extract_Package.I_FileName,'w');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records. The Cursor Open, Fetch and Close is dynamic

For csr_Columns IN CSR_ENR_PARTIC_DPND
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose (Ben_Extract_Package.I_FileType);

dbms_output.put_line('ENR_PARTIC_DPND EXTRACT COUNT:'||to_char(v_row_count));

-- **************************************************************************
-- *** P R O C E D U R E (Ex_ENR_DPND_BENEF_sp) D E C L A R A T I O N S *****
-- **************************************************************************
PROCEDURE Ex_ENR_DPND_BENEF_sp AS
v_row_count number := 0;

Begin
-- Declare Cursor to extract the ENR_DPND_BENEF table as fixed width n bytes

Declare Cursor
CSR_ENR_DPND_BENEF IS
        SELECT
     RPAD(A.EMPLID,11)
  ||RPAD(A.DEPENDENT_BENEF,2)
  ||RPAD(NVL(A.NAME,' '),50)
  ||RPAD(NVL(A.SAME_ADDRESS_EMPL,' '),1)
  ||RPAD(NVL(A.ADDRESS1,' '),35)
  ||RPAD(NVL(A.ADDRESS2,' '),35)
  ||RPAD(NVL(A.ADDRESS3,' '),35)
  ||RPAD(NVL(A.ADDRESS4,' '),35)
  ||RPAD(NVL(A.CITY,' '),30)
  ||RPAD(NVL(A.STATE,' '),6)
  ||RPAD(NVL(A.POSTAL,' '),12)
  ||RPAD(NVL(A.RELATIONSHIP,' '),2)
  ||RPAD(NVL(A.DEP_BENEF_TYPE,' '),1)
  ||RPAD(NVL(A.MAR_STATUS,' '),1)
  ||RPAD(NVL(TO_CHAR(A.MAR_STATUS_DT, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.SEX,' '),1)
  ||RPAD(NVL(TO_CHAR(A.BIRTHDATE, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.STUDENT,' '),1)
  ||RPAD(NVL(A.DISABLED,' '),1)
  ||RPAD(NVL(A.NATIONAL_ID,' '),15)
  ||RPAD(NVL(A.DPND_CERTIFY,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP1,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP2,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP3,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP4,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP5,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP6,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP7,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF1,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF2,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF3,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF4,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR1,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR2,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR3,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR4,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR5,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR6,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR7,' '),1)
  ||RPAD(NVL(A.NEW_RCD,' '),1)
  ||RPAD(NVL((TO_CHAR(A.LASTUPDDTTM, 'yyyy-mm-dd-hh24.mi.ss')||'.000000'),' '),26)
  ||RPAD(NVL(A.LASTUPDUSER,' '),15)
  ||RPAD(NVL(A.LASTUPDVEH,' '),1)
  ||RPAD(NVL(A.STUDENT_CERTIFY,' '),1) AS Unload_String
      FROM   ENR_DPND_BENEF A;

csr_Columns CSR_ENR_DPND_BENEF%ROWTYPE;

Begin

Ben_Extract_Package.O_SqlCode := 0;
Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
Ben_Extract_Package.I_FileName := 'ENR_DPND_BENEF.txt';
Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

-- Open File
Ben_Extract_Package.I_FileType := utl_file.fopen (Ben_Extract_Package.I_FilePath,Ben_Extract_Package.I_FileName,'w');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records. The Cursor Open, Fetch and Close is dynamic

For csr_Columns IN CSR_ENR_DPND_BENEF
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose (Ben_Extract_Package.I_FileType);

dbms_output.put_line('ENR_DPND_BENEF EXTRACT COUNT:'||to_char(v_row_count));

END Ben_Extract_Package;

Legacy Comments


breakdance 2005-05-06	re: bcp out for Oracle thanks

eyechart 2005-05-18	re: bcp out for Oracle this seems overly complicated. why not use something like this: set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on spool oradata.txt select col1 \|\| ',' \|\| col2 \|\| ',' \|\| col3 from tab1 where col2 = 'XYZ'; spool off -ec

Brett (Not just a Number...huh?) 2005-05-18	re: bcp out for Oracle Thanks eyechart...but how do you move that to a production environment so it can run completely hands off. Also, don't forget that there is a potential that you will blow out the buffer depending on the volume of the data. Unless the options feed off head off trimspool on handle that? Also can that be run in a package? And does anyone have a prefered method as proposed by Oracle? To me it's akin to the DTS vs. bcp debate (is there really a debate) on which to use. Moving DTS to a production environment is not easy. Or can be fully tested. Does anyone know if Oracle developed a utility to perform this? They've had SQL Loader around since 8i. Why wouldn't they have provided a SQL unloader?