asked on

load oracle table from flat file

We will be getting data from flat file every morning. This data needs to be loaded to oracle table. I cannot create external table or create control file to load data to the table as this data needs to be loaded every day that has a sequence number.
LT_CERNER_ID is the sequence that gets created for every line during insert. What suggestions for me to get the data loaded?

CREATE TABLE LT_CERNER_INTER
(
  LT_CERNER_ID            NUMBER                NOT NULL,
  SOURCE_SYSTEM           VARCHAR2(1 BYTE),
  SHIP_TO_ID              VARCHAR2(24 BYTE),
  BILL_TO_ID              VARCHAR2(24 BYTE),
  SERVICE_CODE            VARCHAR2(5 BYTE),
  DEPT_NUMBER             VARCHAR2(4 BYTE),
  UNIT_NUMBER             VARCHAR2(15 BYTE),
  QTY                     NUMBER(17,2),
  PRICE                   NUMBER(17,2),
  EXTENDED_AMOUNT         NUMBER(17,2),
  DOCUMENT_TYPE           VARCHAR2(2 BYTE),
  SERVICE_DESCRIPTION     VARCHAR2(50 BYTE),
  PO_NUMBER               VARCHAR2(30 BYTE),
  BLOOD_ID                VARCHAR2(15 BYTE),
  PRODUCT_CODE            VARCHAR2(8 BYTE),
  ALIQUOT                 VARCHAR2(2 BYTE),
  PRODUCT_ID              VARCHAR2(25 BYTE),
  PRODUCT_SHORT_NAME      VARCHAR2(10 BYTE),
  INVENTORY_PRODUCT_TYPE  VARCHAR2(6 BYTE),
  ABO_TYPE                VARCHAR2(3 BYTE),
  BRANCH_ID               VARCHAR2(2 BYTE),
  CUSTOMER_TYPE           VARCHAR2(2 BYTE),
  DOCUMENT_DATE           DATE,
  DOCUMENT_NO             VARCHAR2(14 BYTE),
  ORDER_DATE              DATE,
  ORDER_NO                VARCHAR2(10 BYTE),
  BIX_ID                  VARCHAR2(10 BYTE),
  ORIGINAL_DOCUMENT_DATE  DATE,
  ORIGINAL_DOCUMENT_NO    VARCHAR2(14 BYTE),
  PATIENT_NAME            VARCHAR2(30 BYTE),
  DOCTOR_NAME             VARCHAR2(30 BYTE),
  VOLUME_LITERS           NUMBER(17,3),
  TRANSACTION_TYPE        VARCHAR2(2 BYTE),
  SORT_ORDER              VARCHAR2(2 BYTE),
  LOT_NUMBER              VARCHAR2(30 BYTE),
  LOT_NUMBER_EXP_DATE     DATE,
  IU                      NUMBER(17),
  TOTAL_IU                NUMBER(17),
  NDC_CODE                VARCHAR2(12 BYTE),
  PHS_FLAG                VARCHAR2(1 BYTE),
  DERIVATIVE_ITEM_CODE    VARCHAR2(12 BYTE),
  REFERENCE_DATE          DATE,
  PATIENT_TYPE            VARCHAR2(10 BYTE),
  ADMIT_DOC_NAME_FIRST    VARCHAR2(20 BYTE),
  COLLECTION_DATE         DATE,
  COMMENTS                VARCHAR2(80 BYTE),
  CPT4_1                  VARCHAR2(80 BYTE),
  CPT4_2                  VARCHAR2(80 BYTE),
  DATE_OF_SERVICE         DATE,
  DONOR_NAME_FIRST        VARCHAR2(20 BYTE),
  DONOR_NAME_LAST         VARCHAR2(20 BYTE),
  FIN_CLASS               VARCHAR2(10 BYTE),
  MM                      VARCHAR2(10 BYTE),
  OPI                     VARCHAR2(10 BYTE),
  PATIENT_DOB             DATE,
  PATIENT_NAME_FIRST      VARCHAR2(20 BYTE),
  PATIENT_NAME_LAST       VARCHAR2(20 BYTE),
  PATIENT_NAME_MI         VARCHAR2(20 BYTE),
  SSN                     VARCHAR2(9 BYTE),
  CLIENT_CODE             VARCHAR2(6 BYTE),
  PATIENT_FIN_NOR         VARCHAR2(10 BYTE),
  REQ_NO                  VARCHAR2(10 BYTE),
  TEST_ALIAS              VARCHAR2(10 BYTE),
  TRANSPLANT_TYPE         VARCHAR2(10 BYTE),
  ICD9_CODES              VARCHAR2(80 BYTE),
  CREDIT_COMMENT_1        VARCHAR2(80 BYTE),
  CREDIT_COMMENT_2        VARCHAR2(80 BYTE),
  USER_DEFINED_FIELD_1    VARCHAR2(40 BYTE),
  USER_DEFINED_FIELD_2    VARCHAR2(40 BYTE),
  USER_DEFINED_FIELD_3    VARCHAR2(40 BYTE),
  USER_DEFINED_FIELD_4    VARCHAR2(40 BYTE),
  CREATED_BY              VARCHAR2(30 BYTE),
  CREATE_TIME             DATE,
  UPDATED_BY              VARCHAR2(30 BYTE),
  UPDATE_TIME             DATE
)



CREATE UNIQUE INDEX LT_CERNER_INTER_PK ON LT_CERNER_INTER
(LT_CERNER_ID)
LOGGING
TABLESPACE PHBC_IDX1
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER LT_CERNER_INTER_BI 
  before insert on LT_CERNER_INTER
  for each row
declare 
  -- local variables here 
begin 
  :new.LT_CERNER_ID := hbc_sequences_pkg.nextvalue('LT_CERNER_ID');
  :new.create_time := Sysdate; 
  :new.created_by  := User; 
end LT_CERNER_INTER_BI;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER LT_CERNER_INTER_BU 
  before update on LT_CERNER_INTER
  for each row
declare 
  -- local variables here 
begin 
  :new.update_time := Sysdate; 
  :new.updated_by  := User; 
end LT_CERNER_INTER_BU;
/
SHOW ERRORS;



CREATE PUBLIC SYNONYM LT_CERNER_INTER FOR LT_CERNER_INTER;


ALTER TABLE LT_CERNER_INTER ADD (
  CONSTRAINT LT_CERNER_INTER_PK
 PRIMARY KEY
 (LT_CERNER_ID)
    USING INDEX 
    TABLESPACE PHBC_IDX1
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  LT_CERNER_INTER TO HBC_USER;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  LT_CERNER_INTER TO PUBLIC;

Open in new window

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Sample date that I created. The company is yet to send me. I may get it by the end of the day, but I just created one.

1000, 'C', 'A234B456', 'C123D456', 'A123', 
    '17', 10, 45, 450, 'CR', 
    'HH', 'PO1234', 'W039715009279-0', 'E3086V00', 'A', 
    'W039715009279-0E3086V00A', 'E3086', 'LRBC', 'AB+', 'AU', 
    'RG', TO_DATE('03/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ABC1234', TO_DATE('03/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DEF1234', 
    'BOX1234', TO_DATE('03/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'ABC1234', 'SUSAN FIELDS', 'MICHEAL', 
    10, 'B', '02', 'AX123', TO_DATE('03/31/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    12, 43, 'CDE', 'Y', 'W45679000', 
    TO_DATE('03/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'RG', 'MICHEAL', TO_DATE('03/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'COMMENTS AS OF', 
    'ADB', 'DCF', TO_DATE('03/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'JAMES', 'FRANK', 
    'INS', '01', 'D12', TO_DATE('03/22/1962 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LINDA', 
    'KELLY', 'D', '123456789', 'C0123', 'A0923', 
    'R01234', 'REG', 'NA', '0123456', 'NO COMMENTS', 
    'NO COMMENTS', NULL, NULL, NULL, NULL, 
    'HBC_DATA', TO_DATE('03/10/2015 09:02:40', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL

Open in new window

-- Create directory 
create or replace directory LT_CERNER_INTER_FILES
  as '/home/hbc_data/data';
  
-- Create table
create table LT_CERNER_INTER_EXT
(
  LT_CERNER_ID           NUMBER  NOT NULL,
  SOURCE_SYSTEM          VARCHAR2(1 BYTE),
  SHIP_TO_ID             VARCHAR2(24 BYTE),
  BILL_TO_ID             VARCHAR2(24 BYTE),
  SERVICE_CODE           VARCHAR2(5 BYTE),
  DEPT_NUMBER            VARCHAR2(4 BYTE),
  UNIT_NUMBER            VARCHAR2(15 BYTE),
  QTY                    NUMBER(17,2),
  PRICE                  NUMBER(17,2),
  EXTENDED_AMOUNT        NUMBER(17,2),
  DOCUMENT_TYPE          VARCHAR2(2 BYTE),
  SERVICE_DESCRIPTION    VARCHAR2(50 BYTE),
  PO_NUMBER              VARCHAR2(30 BYTE),
  BLOOD_ID               VARCHAR2(15 BYTE),
  PRODUCT_CODE           VARCHAR2(8 BYTE),
  ALIQUOT                VARCHAR2(2 BYTE),
  PRODUCT_ID             VARCHAR2(25 BYTE),
  PRODUCT_SHORT_NAME     VARCHAR2(10 BYTE),
  INVENTORY_PRODUCT_TYPE VARCHAR2(6 BYTE),
  ABO_TYPE               VARCHAR2(3 BYTE),
  BRANCH_ID              VARCHAR2(2 BYTE),
  CUSTOMER_TYPE          VARCHAR2(2 BYTE),
  DOCUMENT_DATE          DATE,
  DOCUMENT_NO            VARCHAR2(14 BYTE),
  ORDER_DATE             DATE,
  ORDER_NO               VARCHAR2(10 BYTE),
  BIX_ID                 VARCHAR2(10 BYTE),
  ORIGINAL_DOCUMENT_DATE DATE,
  ORIGINAL_DOCUMENT_NO   VARCHAR2(14 BYTE),
  PATIENT_NAME           VARCHAR2(30 BYTE),
  DOCTOR_NAME            VARCHAR2(30 BYTE),
  VOLUME_LITERS          NUMBER(17,3),
  TRANSACTION_TYPE       VARCHAR2(2 BYTE),
  SORT_ORDER             VARCHAR2(2 BYTE),
  LOT_NUMBER             VARCHAR2(30 BYTE),
  LOT_NUMBER_EXP_DATE    DATE,
  IU                     NUMBER(17),
  TOTAL_IU               NUMBER(17),
  NDC_CODE               VARCHAR2(12 BYTE),
  PHS_FLAG               VARCHAR2(1 BYTE),
  DERIVATIVE_ITEM_CODE   VARCHAR2(12 BYTE),
  REFERENCE_DATE         DATE,
  PATIENT_TYPE           VARCHAR2(10 BYTE),
  ADMIT_DOC_NAME_FIRST   VARCHAR2(20 BYTE),
  COLLECTION_DATE        DATE,
  COMMENTS               VARCHAR2(80 BYTE),
  CPT4_1                 VARCHAR2(80 BYTE),
  CPT4_2                 VARCHAR2(80 BYTE),
  DATE_OF_SERVICE        DATE,
  DONOR_NAME_FIRST       VARCHAR2(20 BYTE),
  DONOR_NAME_LAST        VARCHAR2(20 BYTE),
  FIN_CLASS              VARCHAR2(10 BYTE),
  MM                     VARCHAR2(10 BYTE),
  OPI                    VARCHAR2(10 BYTE),
  PATIENT_DOB            DATE,
  PATIENT_NAME_FIRST     VARCHAR2(20 BYTE),
  PATIENT_NAME_LAST      VARCHAR2(20 BYTE),
  PATIENT_NAME_MI        VARCHAR2(20 BYTE),
  SSN                    VARCHAR2(9 BYTE),
  CLIENT_CODE            VARCHAR2(6 BYTE),
  PATIENT_FIN_NOR        VARCHAR2(10 BYTE),
  REQ_NO                 VARCHAR2(10 BYTE),
  TEST_ALIAS             VARCHAR2(10 BYTE),
  TRANSPLANT_TYPE        VARCHAR2(10 BYTE),
  ICD9_CODES             VARCHAR2(80 BYTE),
  CREDIT_COMMENT_1       VARCHAR2(80 BYTE),
  CREDIT_COMMENT_2       VARCHAR2(80 BYTE),
  USER_DEFINED_FIELD_1   VARCHAR2(40 BYTE),
  USER_DEFINED_FIELD_2   VARCHAR2(40 BYTE),
  USER_DEFINED_FIELD_3   VARCHAR2(40 BYTE),
  USER_DEFINED_FIELD_4   VARCHAR2(40 BYTE)
)
organization external
(
  type ORACLE_LOADER
  default directory LT_CERNER_INTER_FILES
  access parameters 
  (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1
    fields terminated by "," optionally enclosed by '"' RTRIM
    (lt_cerner_id,
  	 source_system ,
     ship_to_id,
     bill_to_id,
     service_code,
     dept_number,
     unit_number,
     qty,
     price,
     extended_amount,
     document_type,
     service_description,
     po_number,
     blood_id,
     product_code,
     aliquot,
     product_id,
     product_short_name,
     inventory_product_type,
     abo_type,
     branch_id,
     customer_type,
     document_date,
     document_no,
     order_date,
     order_no,
     bix_id,
     original_document_date,
     original_document_no,
     patient_name,
     doctor_name,
     volume_liters,
     transaction_type,
     sort_order,
     lot_number,
     lot_number_exp_date,
     iu,
     total_iu,
     ndc_code,
     phs_flag,
     derivative_item_code,
     reference_date,
     patient_type,
     admit_doc_name_first,
     collection_date,
     comments,
     cpt4_1,
     cpt4_2,
     date_of_service,
     donor_name_first,
     donor_name_last,
     fin_class,
     mm,
     opi,
     patient_dob,
     patient_name_first,
     patient_name_last,
     patient_name_mi,
     ssn,
     client_code,
     patient_fin_nor,
     req_no,
     test_alias,
     transplant_type,
     icd9_codes,
     credit_comment_1,
     credit_comment_2,
     user_defined_field_1,
     user_defined_field_2,
     user_defined_field_3,
     user_defined_field_4))
  )
)
reject limit 50;

-- Grant/Revoke object privileges 
grant select on LT_CERNER_INTER_EXT to HBC_PROC;

grant select on LT_CERNER_INTER_EXT to HBC_USER;

Open in new window

johnsone

A couple of questions come to mind.

Is that one record? If so, will the real data be on 15 lines like that and not one?

There are TO_DATE functions in the data. Will the real data be like that?

Looking at the data, you cannot use a comma as a delimiter as there are function calls that have commas in them that aren't enclosed by double quotes.

If the sample record is what the actual sample data will look like, I would use editor tricks to turn it into insert statements. It really just needs the insert on the front and the closing parenthesis and semicolon on the end.

anumoses

ASKER

Now that I have created external table, is there a way to add audit columns?

like create_time, created_by, update_time and updated_by? How do we also let the sequence add the next number to LT_CERNER_ID ?

I am still waiting for a sample file from the other company.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

thanks and that was what I thought.