Home » Applications » Oracle Fusion Apps & E-Business Suite » SQL*Loader-929: Error (Oracle apps 11.5.10.2)  () 1 Vote
SQL*Loader-929: Error [message #300397] Fri, 15 February 2008 05:22 Go to next message
vishalaksha
Messages: 16
Registered: September 2007
Junior Member
Hi,
I am getting following error while running SQL*Loader.

SQL*Loader-930: Error parsing insert statement for column XXEEG.XXCONV_NOR_OKS_CON_HEADERS.ORACLE_CONTRACT_NUMBER.
ORA-00904: "XXCONV_NORDIC_CONTRACT_PKG"."GET_ORCL_KNO": invalid identifier.

-------------------------------
Below are my control file and log file

Control file:

OPTIONS (SKIP=1)
load data
INFILE '/home/C9976680/xxconv_nordic_oks_header.csv'
TRUNCATE
into table xxeeg.XXCONV_NOR_OKS_CON_HEADERS
fields terminated by "," optionally enclosed by '"' trailing nullcols
(
ID "xxconv_nordic_contract_pkg.get_seq_val('HDR')",
BATCH_NUMBER "xxconv_nordic_contract_pkg.get_batch_no(to_date(:START_DATE,'MM/DD/YYYY'),to_date(:END_DATE,'MM/DD/YYYY'))",
CONTRACT_NUMBER,
CONTRACT_VERSION,
ORACLE_CONTRACT_NUMBER "xxconv_nordic_contract_pkg.get_orcl_kno(:CONTRACT_NUMBER,CONTRACT_VERSION))",
START_DATE "to_date(:START_DATE,'MM/DD/YYYY')",
END_DATE "to_date(:END_DATE,'MM/DD/YYYY')",
STATUS,
PARTY_ID,
BILL_TO_ID,
SHIP_TO_ID,
ACCOUNTING_RULE_TYPE,
INVOICE_RULE_TYPE,
PAYMENT_TERMS,
INT_SALESREP_NAME,
EXT_SALESREP_NAME,
RENEWAL_CONTACT_NAME,
ISR_ZONE,
ORBITAL_PROFILE_ID,
CCHOLDER_NAME,
CC_ZIP,
CUST_PO,
CC_NO,
CC_EXPIRY_DATE,
ERROR_MESSAGE,
INTERFACED_STATUS_FLAG CONSTANT "N",
ERROR_STACK
)
---------------------------------------
Log file

SQL*Loader: Release 8.0.6.3.0 - Production on Thu Feb 14 03:24:01 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Control File: /opt/egapmdev/ebmdappl/xxeeg/bin/xxconv_nordic_oks_header.ctl
Data File: /home/C9976680/xxconv_nordic_oks_header.csv
Bad File: /opt/egapmdev/ebmdappl/xxeeg/bin/xxconv_nordic_oks_header.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table XXEEG.XXCONV_NOR_OKS_CON_HEADERS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , O(") CHARACTER
BATCH_NUMBER NEXT * , O(") CHARACTER
CONTRACT_NUMBER NEXT * , O(") CHARACTER
CONTRACT_VERSION NEXT * , O(") CHARACTER
ORACLE_CONTRACT_NUMBER NEXT * , O(") CHARACTER
START_DATE NEXT * , O(") CHARACTER
END_DATE NEXT * , O(") CHARACTER
STATUS NEXT * , O(") CHARACTER
PARTY_ID NEXT * , O(") CHARACTER
BILL_TO_ID NEXT * , O(") CHARACTER
SHIP_TO_ID NEXT * , O(") CHARACTER
ACCOUNTING_RULE_TYPE NEXT * , O(") CHARACTER
INVOICE_RULE_TYPE NEXT * , O(") CHARACTER
PAYMENT_TERMS NEXT * , O(") CHARACTER
INT_SALESREP_NAME NEXT * , O(") CHARACTER
EXT_SALESREP_NAME NEXT * , O(") CHARACTER
RENEWAL_CONTACT_NAME NEXT * , O(") CHARACTER
ISR_ZONE NEXT * , O(") CHARACTER
ORBITAL_PROFILE_ID NEXT * , O(") CHARACTER
CCHOLDER_NAME NEXT * , O(") CHARACTER
CC_ZIP NEXT * , O(") CHARACTER
CUST_PO NEXT * , O(") CHARACTER
CC_NO NEXT * , O(") CHARACTER
CC_EXPIRY_DATE NEXT * , O(") CHARACTER
ERROR_MESSAGE NEXT * , O(") CHARACTER
ERROR_STACK NEXT * , O(") CHARACTER

INTERFACED_STATUS_FLAG CONSTANT 'N'

Column ID had SQL string
"xxconv_nordic_contract_pkg.get_seq_val('HDR')"
applied to it.
Column BATCH_NUMBER had SQL string
"xxconv_nordic_contract_pkg.get_batch_no(to_date(:START_DATE,'MM/DD/YYYY'),to_date(:END_DATE,'MM/DD/YYYY'))"
applied to it.
Column ORACLE_CONTRACT_NUMBER had SQL string
"xxconv_nordic_contract_pkg.get_orcl_kno(:CONTRACT_NUMBER,:CONTRACT_VERSION)"
applied to it.
Column START_DATE had SQL string
"to_date(:START_DATE,'MM/DD/YYYY')"
applied to it.
Column END_DATE had SQL string
"to_date(:END_DATE,'MM/DD/YYYY')"
applied to it.

SQL*Loader-930: Error parsing insert statement for column XXEEG.XXCONV_NOR_OKS_CON_HEADERS.ORACLE_CONTRACT_NUMBER.
ORA-00904: "XXCONV_NORDIC_CONTRACT_PKG"."GET_ORCL_KNO": invalid identifier
---------------------------------------------------
---------------------------------------------------

Function used above are

FUNCTION get_batch_no(p_start_date DATE, p_end_date DATE) RETURN NUMBER IS
BEGIN
RETURN 1;
END get_batch_no;
-----------------------------

FUNCTION get_orcl_kno(p_contract_number VARCHAR2, p_contract_version NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN 'M'||p_contract_number||'v'||p_contract_version;
END get_orcl_kno;
------------------------------

FUNCTION get_seq_val (p_seqtype VARCHAR2) RETURN NUMBER IS
v_seqno NUMBER;
BEGIN
IF UPPER(p_seqtype) = 'HDR' THEN
SELECT XXCONV_NOR_HDR_S.NEXTVAL
INTO v_seqno
FROM dual;

RETURN v_seqno;
END IF;

IF UPPER(p_seqtype) = 'LINE' THEN
SELECT XXCONV_NOR_LINE_S.NEXTVAL
INTO v_seqno
FROM dual;

RETURN v_seqno;
END IF;

IF UPPER(p_seqtype) = 'SUBLINE' THEN
SELECT XXCONV_NOR_SUBLINE_S.NEXTVAL
INTO v_seqno
FROM dual;

RETURN v_seqno;
END IF;

IF UPPER(p_seqtype) = 'BILL_SCH' THEN
SELECT XXCONV_NOR_BILL_SCH_S.NEXTVAL
INTO v_seqno
FROM dual;

RETURN v_seqno;
END IF;

IF UPPER(p_seqtype) = 'PMS' THEN
SELECT XXCONV_NOR_PMS_S.NEXTVAL
INTO v_seqno
FROM dual;

RETURN v_seqno;
END IF;

IF UPPER(p_seqtype) = 'TEST' THEN
SELECT XXCONV_NOR_WARRANTY_S.NEXTVAL
INTO v_seqno
FROM dual;

RETURN v_seqno;
END IF;

END get_seq_val;
---------------------------------------

Please give me some suggestion on it.
Re: SQL*Loader-929: Error [message #300437 is a reply to message #300397] Fri, 15 February 2008 07:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems it is a custom package.
Get the package specification with the following sql
SELECT text
  FROM user_source
 WHERE name = 'XXCONV_NORDIC_CONTRACT_PKG'
   AND type = 'PACKAGE'
 ORDER BY line;
Cross verify your calling code with the definition of the procedure get_orcl_kno.

By the way is it get_orcl_kno or get_orcl_no?

By
Vamsi
Re: SQL*Loader-929: Error [message #463402 is a reply to message #300437] Thu, 01 July 2010 03:23 Go to previous message
arun_sh
Messages: 1
Registered: July 2010
Location: INDIA
Junior Member

ORACLE_CONTRACT_NUMBER "xxconv_nordic_contract_pkg.get_orcl_kno(:CONTRACT_NUMBER,CONTRACT_VERSION))",

look into that control file syntax. there are one extra enclosing bracket. remove that.

[Updated on: Thu, 01 July 2010 03:23]

Report message to a moderator

Previous Topic: Oracle EBS APIs and best approach to import and export data/objets from EBS 12.1.1 ?
Next Topic: Migration Help from 32-bit to 64 bit EBS
Goto Forum:
  


Current Time: Fri Dec 09 03:56:17 CST 2016

Total time taken to generate the page: 0.21144 seconds