x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

bcp out for 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));

   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_DPND_sp;

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

   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_DPND_BENEF_sp;

END Ben_Extract_Package;

/
   

 


Print | posted on Thursday, May 05, 2005 12:33 PM | Filed Under [ Oracle ]

Feedback

Gravatar

# re: bcp out for Oracle

thanks
5/6/2005 6:18 PM | breakdance
Gravatar

# 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
5/18/2005 2:19 AM | eyechart
Gravatar

# 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?
5/18/2005 8:53 AM | Brett (Not just a Number...huh?)
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET