Home » RDBMS Server » Server Utilities » sql loader (Oracle, 10g, Windows Server 2003)
sql loader [message #468923] Mon, 02 August 2010 08:55 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member
Hi,
Check table creation script

 CREATE TABLE "SCOTT"."TEST_USER" 
   (	"TX_SID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"TX_FIRST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"TX_LAST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"TX_MIDDLE_NAME" VARCHAR2(30 BYTE), 
	"TX_HOME_PHONE_NUMBER" VARCHAR2(20 BYTE), 
	"TX_WORK_PHONE_NUMBER" VARCHAR2(20 BYTE), 
	"TX_CELL_PHONE_NUMBER" VARCHAR2(20 BYTE), 
	"TX_PHONE_CALL_FLAG" VARCHAR2(1 BYTE), 
	"TX_CALL_TREE" VARCHAR2(30 BYTE), 
	"TX_EMAIL_ADDR" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"TX_USER_REGION" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"TX_USER_COUNTRY" VARCHAR2(30 BYTE), 
	"TX_USER_CITY" VARCHAR2(30 BYTE), 
	"TX_USER_ADDR" VARCHAR2(100 BYTE), 
	"TX_IS_ACTIVE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"TX_CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DT_CREATE_DATE" DATE NOT NULL ENABLE, 
	"TX_UPDATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DT_UPDATE_DATE" DATE NOT NULL ENABLE
   );

  CREATE TABLE "SCOTT"."TEST_TITLE" 
   (	"TX_SID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"ID_TITLE" NUMBER(38,0) NOT NULL ENABLE, 
	"TX_CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DT_CREATE_DATE" DATE NOT NULL ENABLE, 
	"TX_UPDATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DT_UPDATE_DATE" DATE NOT NULL ENABLE, 
	"TITLE_NAME" VARCHAR2(100 BYTE)
   );

  CREATE TABLE "SCOTT"."TEST_ROLE" 
   (	"TX_SID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"ID_ROLE" NUMBER(38,0) NOT NULL ENABLE, 
	"TX_CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DT_CREATE_DATE" DATE NOT NULL ENABLE, 
	"TX_UPDATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DT_UPDATE_DATE" DATE NOT NULL ENABLE, 
	"ROLE_NAME" VARCHAR2(20 BYTE)
   );

Here i'm loading data into these three tables through sql loader.

Here is the control file

OPTIONS (SKIP=1,ROWS=5)
LOAD DATA
INFILE 'C:\SQL LOADER DEMO\testuser_data_lat.csv'
INTO TABLE TEST_USER
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
TX_SID,
TX_FIRST_NAME,
TX_LAST_NAME,
TX_MIDDLE_NAME,
TX_HOME_PHONE_NUMBER,
TX_WORK_PHONE_NUMBER,
TX_CELL_PHONE_NUMBER,
TX_PHONE_CALL_FLAG CONSTANT '',
TX_CALL_TREE CONSTANT '',
TX_EMAIL_ADDR,
TX_USER_REGION,
TX_USER_COUNTRY,
TX_USER_CITY,
TX_USER_ADDR,
TX_IS_ACTIVE CONSTANT "Y",
TX_CREATED_BY CONSTANT "SYSTEM",
DT_CREATE_DATE SYSDATE,
TX_UPDATED_BY CONSTANT "SYSTEM",
DT_UPDATE_DATE SYSDATE)
INTO TABLE TEST_ROLE(
TX_SID POSITION(1:3) CHAR,
ID_ROLE "get_role_id(:ROLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",
DT_CREATE_DATE SYSDATE ,
TX_UPDATED_BY CONSTANT "SYSTEM",
DT_UPDATE_DATE SYSDATE,
ROLE_NAME POSITION(122:127) CHAR
)
INTO TABLE TEST_TITLE(
TX_SID POSITION(1:3) CHAR,
ID_TITLE "get_title_id(:TITLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",
DT_CREATE_DATE SYSDATE ,
TX_UPDATED_BY CONSTANT "SYSTEM",
DT_UPDATE_DATE SYSDATE,
TITLE_NAME POSITION(129:149) CHAR
)


Here are the two function which i'm calling from sql loader control file

CREATE OR REPLACE FUNCTION get_role_id(p_role_name VARCHAR2)
RETURN NUMBER IS
 lv_role_id NUMBER;
BEGIN
 SELECT id_role
 INTO lv_role_id
 FROM rd_role_master
 WHERE tx_role_name = p_role_name;

 RETURN lv_role_id;

END get_role_id;
/

Function Created

CREATE OR REPLACE FUNCTION get_title_id(p_title_name VARCHAR2)
RETURN NUMBER IS
 lv_title_id NUMBER;
BEGIN
 SELECT id_title
 INTO lv_title_id
 FROM rd_title_master
 WHERE tx_title_name = p_title_name;

 RETURN lv_title_id;

END get_title_id;
/
Function Created


i'hv attached the testuser_data_lat.csv file, which is the data file.

Command line 

C:\SQL LOADER DEMO>SQLLDR scott/sc CONTROL=rd_users_control.ctl


Now let me tell u what is happening

Whem i'm running the above sqlldr, log is generating saying

Record 1: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 2: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 3: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 4: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 5: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 6: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 7: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 8: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 9: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

Record 10: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")


Table TEST_USER:
0 Rows successfully loaded.
10 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Table TEST_ROLE:
0 Rows successfully loaded.
10 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Table TEST_TITLE:
0 Rows successfully loaded.
10 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

But when i remove

INTO TABLE TEST_ROLE(
TX_SID POSITION(1:3) CHAR,
ID_ROLE "get_role_id(:ROLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",
DT_CREATE_DATE SYSDATE ,
TX_UPDATED_BY CONSTANT "SYSTEM",
DT_UPDATE_DATE SYSDATE,
ROLE_NAME POSITION(122:127) CHAR
)
from Control file, data is getting popupalated in TEST_USER and TEST_TITLE similarly if remove

INTO TABLE TEST_TITLE(
TX_SID POSITION(1:3) CHAR,
ID_TITLE "get_title_id(:TITLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",
DT_CREATE_DATE SYSDATE ,
TX_UPDATED_BY CONSTANT "SYSTEM",
DT_UPDATE_DATE SYSDATE,
TITLE_NAME POSITION(129:149) CHAR
)

from Control file, TEST_USER and TEST_ROLE is getting populated.

Here RD_ROLE_MASTER script

  CREATE TABLE RD_ROLE_MASTER (
	"ID_ROLE" NUMBER(38,0) NOT NULL ENABLE, 
	"TX_ROLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE
                                    );

Insert into RD_ROLE_MASTER (ID_ROLE,TX_ROLE_NAME) 
values (1,'EDITOR');
Insert into RD_ROLE_MASTER (ID_ROLE,TX_ROLE_NAME) 
values (2,'VIEWER');
Insert into RD_ROLE_MASTER (ID_ROLE,TX_ROLE_NAME) 
values (3,'SUPER_ADMIN');
Insert into RD_ROLE_MASTER (ID_ROLE,TX_ROLE_NAME) 
values (4,'RESPONDER');
Insert into RD_ROLE_MASTER (ID_ROLE,TX_ROLE_NAME) 
values (5,'LOB_ADMIN');


Here is RD_TITLE_MASTER script

  CREATE TABLE RD_TITLE_MASTER(	
   "ID_TITLE" NUMBER(38,0) NOT NULL ENABLE, 
   "TX_TITLE_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE);

Insert into RD_TITLE_MASTER (ID_TITLE,TX_TITLE_NAME) 
values (7,'RED_LOB_ESCALATION_L1');

Kindly tell me what is the problem?

Regards,
Ritesh

[Updated on: Mon, 02 August 2010 09:18]

Report message to a moderator

Re: sql loader [message #468953 is a reply to message #468923] Mon, 02 August 2010 11:04 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid that you didn't count column positions well. This is what you said:
ID_ROLE "get_role_id(:ROLE_NAME)",
ROLE_NAME POSITION(122:127) CHAR

but ROLE_NAME is in positions (121:126). So - modify the control file so that ROLE_NAME line looks like 
ROLE_NAME POSITION(121:126) CHAR
and try again.

Report message to a moderator

Previous Topic: need help with sql loader
Next Topic: importing dump
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 14:02:39 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.