Hi there
i an trying to load data from a text file into 3 different tables. This would be pretty staight forward except for that on the main table there is a Primary key. the Primary key is a sequence with a check character which is worked out using a modulus.
i can't get the call to the procedure that contains the function to work from the control file.
can anybody HELP me ???
this is the control file:
load data
infile practice_R2.TXT
APPEND
into table tdco_customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(CUST_ID "select pkco_convert_to_code.FN_CUST_ID from dual"
,CUST_SEARCH_NAME
,CUST_SET_UP_DATE SYSDATE
,CUST_CHECK_CHAR CONSTANT ''
,CUST_PAYEE_NAME CONSTANT ''
,CUST_SUF_CODE CONSTANT ''
,CUST_CSTY_CODE CONSTANT ''
,CUST_CTYP_CODE CONSTANT ''
,CUST_CCAT_CODE CONSTANT ''
,CUST_FRST_CODE CONSTANT ''
,CUST_LANG_CODE CONSTANT ''
,CUST_GEN_CODE CONSTANT ''
,CUST_TTL_CODE CONSTANT ''
,CUST_CTRY_CODE_NAT CONSTANT ''
,CUST_TRADING_NAME
,CUST_CEASED_TRADING_DATE CONSTANT ''
,CUST_COMP_NAME
,CUST_CONTACT_NAME CONSTANT ''
,CUST_FORENAME CONSTANT ''
,CUST_MIDD_NAME CONSTANT ''
,CUST_SURNAME CONSTANT ''
,CUST_NAME_KNOWN_BY CONSTANT ''
,CUST_M_BTH_SURNAME CONSTANT ''
,CUST_BIRTH_DATE CONSTANT ''
,CUST_DEATH_DATE CONSTANT ''
,CUST_VERS_NUM CONSTANT ''
,CUST_AUDIT_USER CONSTANT ''
,CUST_AUDIT_DATE SYSDATE
,CUST_AUDIT_ACTION CONSTANT ''
,CUST_AUDIT_LOCATION CONSTANT '')
This the Package
Spec
CREATE OR REPLACE PACKAGE pkco_convert_to_code AS
-- ***********************************************************************************************
-- * *
-- * Author : Finbar Kelleher *
-- * Created: July '02 *
-- * Purpose: This package is for inserting data, using SQL Loader, *
-- * into the Dynamic tables. *
-- * It decodes the description from the text(.TXT) file holding the data *
-- * and inserts the code into the table. * *
-- * The packge HAS to be called from the Control(.CTR) file. * *
-- * *
-- ***********************************************************************************************
Function FN_CONVERT_TO_CNTY_CODE( p_cnty_desc VARCHAR2) RETURN NUMBER;
Function FN_CUST_ID RETURN TDCO_CUSTOMERS.cust_id%type;
end pkco_convert_to_code;
Body:
CREATE OR REPLACE PACKAGE BODY pkco_convert_to_code AS
-- ***********************************************************************************************
-- * *
-- * Author : Finbar Kelleher *
-- * Created: July '02 *
-- * Purpose: This package is for inserting data, using SQL Loader, *
-- * into the Dynamic tables. *
-- * It decodes the description from the .TXT file and inserts the *
-- * code into the table. *
-- * The packge HAS to be called from the .CTR file. *
-- * *
-- ***********************************************************************************************
-- ********************************************************************************
-- * *
-- * Procedure: FN_CUST_ID *
-- * *
-- * Purpose: To get the next Cust_id and add the check character *
-- * *
-- ********************************************************************************
FUNCTION FN_CUST_ID RETURN tdco_customers.cust_id%type AS
v_cust_id tdco_customers.cust_id%type;
BEGIN
SELECT Pkco_App_Tdco_Customers.FN_ADD_CHK_DIG(Pkco_App_Tdco_Customers.getNextSequence())
INTO v_cust_id
FROM DUAL;
RETURN v_cust_id;
END;
-- ********************************************************************************
-- * *
-- * Procedure: FN_CONVERT_TO_CNTY_CODE *
-- * *
-- * Purpose: Converts County Description to Cnty_code *
-- * *
-- ********************************************************************************
FUNCTION FN_CONVERT_TO_CNTY_CODE (p_cnty_desc VARCHAR2) RETURN NUMBER AS
v_cnty_code NUMBER(3);
BEGIN
select cnty_code
into v_cnty_code
from tsco_counties
where cnty_full_name = p_cnty_desc;
RETURN v_cnty_code;
END ;
end PKCO_CONVERT_TO_CODE;
i an trying to load data from a text file into 3 different tables. This would be pretty staight forward except for that on the main table there is a Primary key. the Primary key is a sequence with a check character which is worked out using a modulus.
i can't get the call to the procedure that contains the function to work from the control file.
can anybody HELP me ???
this is the control file:
load data
infile practice_R2.TXT
APPEND
into table tdco_customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(CUST_ID "select pkco_convert_to_code.FN_CUST_ID from dual"
,CUST_SEARCH_NAME
,CUST_SET_UP_DATE SYSDATE
,CUST_CHECK_CHAR CONSTANT ''
,CUST_PAYEE_NAME CONSTANT ''
,CUST_SUF_CODE CONSTANT ''
,CUST_CSTY_CODE CONSTANT ''
,CUST_CTYP_CODE CONSTANT ''
,CUST_CCAT_CODE CONSTANT ''
,CUST_FRST_CODE CONSTANT ''
,CUST_LANG_CODE CONSTANT ''
,CUST_GEN_CODE CONSTANT ''
,CUST_TTL_CODE CONSTANT ''
,CUST_CTRY_CODE_NAT CONSTANT ''
,CUST_TRADING_NAME
,CUST_CEASED_TRADING_DATE CONSTANT ''
,CUST_COMP_NAME
,CUST_CONTACT_NAME CONSTANT ''
,CUST_FORENAME CONSTANT ''
,CUST_MIDD_NAME CONSTANT ''
,CUST_SURNAME CONSTANT ''
,CUST_NAME_KNOWN_BY CONSTANT ''
,CUST_M_BTH_SURNAME CONSTANT ''
,CUST_BIRTH_DATE CONSTANT ''
,CUST_DEATH_DATE CONSTANT ''
,CUST_VERS_NUM CONSTANT ''
,CUST_AUDIT_USER CONSTANT ''
,CUST_AUDIT_DATE SYSDATE
,CUST_AUDIT_ACTION CONSTANT ''
,CUST_AUDIT_LOCATION CONSTANT '')
This the Package
Spec
CREATE OR REPLACE PACKAGE pkco_convert_to_code AS
-- ***********************************************************************************************
-- * *
-- * Author : Finbar Kelleher *
-- * Created: July '02 *
-- * Purpose: This package is for inserting data, using SQL Loader, *
-- * into the Dynamic tables. *
-- * It decodes the description from the text(.TXT) file holding the data *
-- * and inserts the code into the table. * *
-- * The packge HAS to be called from the Control(.CTR) file. * *
-- * *
-- ***********************************************************************************************
Function FN_CONVERT_TO_CNTY_CODE( p_cnty_desc VARCHAR2) RETURN NUMBER;
Function FN_CUST_ID RETURN TDCO_CUSTOMERS.cust_id%type;
end pkco_convert_to_code;
Body:
CREATE OR REPLACE PACKAGE BODY pkco_convert_to_code AS
-- ***********************************************************************************************
-- * *
-- * Author : Finbar Kelleher *
-- * Created: July '02 *
-- * Purpose: This package is for inserting data, using SQL Loader, *
-- * into the Dynamic tables. *
-- * It decodes the description from the .TXT file and inserts the *
-- * code into the table. *
-- * The packge HAS to be called from the .CTR file. *
-- * *
-- ***********************************************************************************************
-- ********************************************************************************
-- * *
-- * Procedure: FN_CUST_ID *
-- * *
-- * Purpose: To get the next Cust_id and add the check character *
-- * *
-- ********************************************************************************
FUNCTION FN_CUST_ID RETURN tdco_customers.cust_id%type AS
v_cust_id tdco_customers.cust_id%type;
BEGIN
SELECT Pkco_App_Tdco_Customers.FN_ADD_CHK_DIG(Pkco_App_Tdco_Customers.getNextSequence())
INTO v_cust_id
FROM DUAL;
RETURN v_cust_id;
END;
-- ********************************************************************************
-- * *
-- * Procedure: FN_CONVERT_TO_CNTY_CODE *
-- * *
-- * Purpose: Converts County Description to Cnty_code *
-- * *
-- ********************************************************************************
FUNCTION FN_CONVERT_TO_CNTY_CODE (p_cnty_desc VARCHAR2) RETURN NUMBER AS
v_cnty_code NUMBER(3);
BEGIN
select cnty_code
into v_cnty_code
from tsco_counties
where cnty_full_name = p_cnty_desc;
RETURN v_cnty_code;
END ;
end PKCO_CONVERT_TO_CODE;
