| SQL*loader problem (merged 3 threads) [message #164314] |
Wed, 22 March 2006 21:34  |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
Hi Team,
I ve some few doubts regarding SQL*Loader.Please if any of u can help me out.
Issue-1
---------
i 've a table as follows:
create table t1
(id number,
name varchar2(20),
sal number(5),
sex char(3)
);
Now i've to load some data into this table using external textfile using sqlldr.one more thing is that id must be automatically be generated from the Backend itself so no data for "id" column is provided in the Textfile.ok .
The textfile is like this:
data.txt
---------
manu|5999|m
anu|7000|f
sam|3000|m
Now i wrote a controlfile but all the records are going to bad file. I am getting the problem with order of the columns when i checked the logfile.
case2.ctl
------------
load data infile 'data.txt'
apppend into table t1
fields terminated by "|"
trailing nullcols
(id "s1.nextval",
name,
sal,
sex
);
before this i've created one sequence in the database as follows
>create sequence s1
start with 1
increment by 1;
and i invoked the sqlldr command as follws
c:\>sqlldr uname/pwd@service_name control=case2.ctl log=case2.log bad=case2.bad
But i am not able load the data. Can anyone please provide me a proper solution for this.
------------------------------------------------------------
Issue no-2
-----------
Assume that there is one column in my table
as
create table t1
(id number,
name varchar2(20),
created_by varchar2(20),
created_date date
);
now i'll provide only id,name values in my text file but i must
able to fill the values for created_by as the user who have loggedin and for created_date as sysdate by default. how can i do it.I just want how does my controlfile look like.
here is my datafile
data.txt
---------
101|manu
102|anu
103|sam
--------------------------------------------------------------
Issue-3
--------
i've date columns with different format and id columns must be
automatically generated thru sequence and for sex columns i should able to give a default value .how would my controlfile look like,
create table t1
(id number,
name varchar2(20),
dob date,
doj date,
sex char(3)
);
create sequence s1
start with 1
increment by 1;
date-formmat is mm/dd/yyyy
data.txt
---------
manu|12/1/1980|10/22/2004 12:30:11 am
anu|11/2/1974|2/15/2000 10:20:44 pm
Then how would my controlfile look like.
This is an urgent requirement for me. Looking forward for sincere reply.
Thanks and Regards.
|
|
|
|
| SQL*loader problem [message #164316 is a reply to message #164314] |
Wed, 22 March 2006 21:51  |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
Hi Team,
I 've enconterd the following problem when i tried to login to Oracle database using password file authentication.
I've only one single database. and i just wanted to test the OS authentication for sys user if incase he forget his password.
steps done by me.
------------------
step1
-----
shut the database graclefully
>shut immediate
step2
------
got to init.ora
init.ora
..
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
..
step-3
------
then i created one passwordfile in
%ORACLE_HOME%/DATABASE dir
orapwd file=PWD<oracle_sid>.ora password=sys
step-4
-------
now i tried as follows
sqlplus/nolog
SQL>conn sys/sys@servicename as sysdba
It immediately respond with an error msg saying
Insufficient privileges.
I cant even start my database now.
what could be reason and what action can i do now.
This was tested on a development database and i was unsuccessfull.Finally i ended up again creating one more test database.
|
|
|
|
| sqlldr problem urgent... [message #164330 is a reply to message #164314] |
Thu, 23 March 2006 00:10 |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
Hi all,
I've one small problem.I am waiting for urgent reply
There is one table as follows:
create table t4
( id number,
name varchar2(20),
dob date,
doj date,
sex char(1)
);
I need to fill the ID column using a sequence.So i created one sequence.
create sequence s2
start with 1
increment by 1;
The datafile is as follows that has to be loaded into "name" and
"doj" columns. Here the datafile look like.
data4.txt
---------
manu|12/1/2000 12:20:12 am
anu|12/1/2000 12:20:12 am
ramesh|12/1/2000 12:20:12 am
The controlfile which i've created as follows . the rows are getting inserted but i am unable to expected result.So any one try to give suggestions on this below controlfile.
case2,ctl
-------------
load data infile 'data4.txt'
append into table t4
fields terminated by "|"
trailing nullcols
(
id "s2.nextval",
name position(1:6),
dob sysdate,
doj position(6:27) date 'mm/dd/yyyy hh:mi:ss am',
sex constant 'm'
)
sample output when i run the sqlldr
SQL> /
ID NAME DOB DOJ S
--------- -------------------- --------- --------- -
27 manu|1 23-MAR-06 01-DEC-00 m
28 anu|12 23-MAR-06 01-FEB-00 m
This was not the expected output.
One recored was rejected i.e the third record and need the what
kind of modifications that could be done for the "name" column
and the same time i should get the time other fields must not be disturned.
Waiting for reply.
Thanks in advance.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
