how to assign a numeric value gererated from sequence in a control file [message #72951] |
Mon, 22 December 2003 11:22 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
Hello
I have a control file like following:
LOAD DATA
INFILE *
INTO TABLE member
REPLACE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(
control_id,
name,
address
)
problem is that control_id value is not in data file and I have to assign to each row the same value generated from sequence or from unix variable.
For example, after I run sqlldr, I have to have records in the table like following:
control_id name address
---------- ---- -------
1847 Charlie 250 yonge st
1847 Peter 5 Brookbanks dr
1847 Ben 123 King st
.
.
.
How do I do that?
|
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #72954 is a reply to message #72951] |
Tue, 23 December 2003 05:14 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
how about creating a control file dynamically?
You can use any method you want like
shell script to create a controlfile
or
sqlplus to create a control file.
I have used the second method.
i am just passing a value to the control file..if you are looking for a sequence number from database...we'll have to think about it.
----------------------------------------------------------------------
first lets truncate the table (since i am inserting data)
----------------------------------------------------------------------
mag@mutation_mutation > truncate table member;
Table truncated.
mag@mutation_mutation > exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
----------------------------------------------------------------------
lets run the scriopt
it reads the value entered and generates the control file..and invokes sqlldr session to load the data
----------------------------------------------------------------------
bash-2.03$ loadmember
Enter the constant to be loaded
1847
Generating the controlfile member.ctl as
load data
infile 'member.dat'
insert into table member
fields terminated by ','
(
control_id constant '1847',
name,
address
)
SQL*Loader: Release 9.2.0.1.0 - Production on Tue Dec 23 10:07:48 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
----------------------------------------------------------------------
lets check wehter it has done the job
----------------------------------------------------------------------
bash-2.03$ sqlplus mag/mag
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 23 10:07:52 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
mag@mutation_mutation > select * from member;
CONTROL_ID NAME ADDRESS
---------- ------------ ------------------------------
1847 Charlie 250 yonge st
1847 Peter 5 Brookbanks dr
1847 Ben 123 King st
mag@mutation_mutation > exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
----------------------------------------------------------------------
this is script
----------------------------------------------------------------------
bash-2.03$ cat loadmember
#!/usr/bin/bash
echo Enter the constant to be loaded
read const
sqlplus -s mag/mag <<EOF
set feed off
set head off
set termout off
prompt
prompt Generating the controlfile member.ctl as
prompt
spool member.ctl
select 'load data'||chr(10)||
'infile ''member.dat'''||chr(10)||
'insert into table member'||chr(10)||
'fields terminated by '','''||chr(10)||
'('||chr(10)||
'control_id constant ''$const'','||chr(10)||
'name,'||chr(10)||
'address'||chr(10)||
')'
from dual;
spool off;
exit;
EOF
sqlldr userid=mag/mag control=member.ctl
[b]
and data file i used
[/b]
bash-2.03$ cat member.dat
Charlie, 250 yonge st
Peter, 5 Brookbanks dr
Ben, 123 King st
[Updated on: Sun, 23 April 2006 21:11] Report message to a moderator
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #72955 is a reply to message #72951] |
Tue, 23 December 2003 05:20 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
ahhh!~ the script is been reformetted when displayed (this is thml forum )
note the bolded line..this is the correct one .. may be you need to remove the extra space..after <<
#!/usr/bin/bash
echo Enter the constant to be loaded
read const
[b]sqlplus -s mag/mag << EOF[/b]
set feed off
set head off
set termout off
prompt
prompt Generating the controlfile member.ctl as
prompt
spool member.ctl
select 'load data'||chr(10)||
'infile ''member.dat'''||chr(10)||
'insert into table member'||chr(10)||
'fields terminated by '','''||chr(10)||
'('||chr(10)||
'control_id constant ''$const'','||chr(10)||
'name,'||chr(10)||
'address'||chr(10)||
')'
from dual;
spool off;
exit;
EOF
sqlldr userid=mag/mag control=member.ctl
[Updated on: Sun, 23 April 2006 21:11] Report message to a moderator
|
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #72958 is a reply to message #72957] |
Tue, 23 December 2003 07:00 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
lets consider the same script with line numbers.
1 #!/usr/bin/bash
2 echo Enter the constant to be loaded
3 read const
4 sqlplus -s mag/mag << EOF
5 set feed off
6 set head off
7 set termout off
8 prompt
9 prompt Generating the controlfile member.ctl as
10 prompt
11 spool member.ctl
12 select 'load data'||chr(10)||
13 'infile ''member.dat'''||chr(10)||
14 'insert into table member'||chr(10)||
15 'fields terminated by '','''||chr(10)||
16 '('||chr(10)||
17 'control_id constant ''$const'','||chr(10)||
18 'name,'||chr(10)||
19 'address'||chr(10)||
20 ')'
21 from dual;
22 spool off;
23 exit;
24 EOF
25 sqlldr userid=mag/mag control=member.ctl
----------------------------------------------------------------------
idea is to generate the contents of ctl file ( using Unix utilties like sed is more simpler, whole script will be 2-3 lines only.....but needs more understanding of unix tools and regular expressions).
i used sqlplus..to do the same..
i am just spooling the "typical" contents of the control file
like
load data
infile filename
insert into table_xx
...
...
..
meanwhile, i embed the value of the paramters ( whcih the user supplies)
and use this script to be used in sqlldr.
lines 5,6,7 -> deals with formatting so that no heading and feedback are returned from sqlplus
lines 8,9,10 -> just displaying some user friendly messages. equivalent to echo in os scripting
line 11 -> initiating the spool process. anything results after this line is writte to the file
lines 12 to 21-> just a simple query that displays things from dual. I am addding use supplied value in line 17
chr(10) will make the result to be displayed in the next new line.
line 22 -> terminating the spool process
rest lines -> exit and invoke sqlldr.
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #72959 is a reply to message #72951] |
Tue, 23 December 2003 08:11 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
here is a much simpler version using sed
----------------------------------------------------------------------
demo
----------------------------------------------------------------------
bash-2.03$ lmem
Enter the constant to to loaded
7876
The control file to be used to load is
load data
infile 'member.dat'
insert into table member
fields terminated by ','
(
control_id constant '7876',
name,
address
)
invoking the sql*loader
SQL*Loader: Release 9.2.0.1.0 - Production on Tue Dec 23 13:12:04 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
bash-2.03$ sqlplus mag/mag
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 23 13:12:08 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
mag@mutation_mutation > select * from member;
CONTROL_ID NAME ADDRESS
---------- ------------ ------------------------------
7876 Charlie 250 yonge st
7876 Peter 5 Brookbanks dr
7876 Ben 123 King st
mag@mutation_mutation > exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
----------------------------------------------------------------------
the script , control file and dat files used
----------------------------------------------------------------------
bash-2.03$ cat lmem
#!/usr/bin/bash
echo
echo Enter the constant to to loaded
read const
cp member.ctl temp.ctl
sed -e "s/$const/$const/g" temp.ctl > temp1.ctl
echo
echo The control file to be used to load is
cat temp1.ctl
echo
echo invoking the sql*loader
sqlldr userid=mag/mag control=temp1.ctl
rm temp.ctl
rm temp1.ctl
bash-2.03$ cat member.ctl
load data
infile 'member.dat'
insert into table member
fields terminated by ','
(
control_id constant '$const',
name,
address
)
bash-2.03$ cat member.dat
Charlie, 250 yonge st
Peter, 5 Brookbanks dr
Ben, 123 King st
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #72966 is a reply to message #72961] |
Wed, 24 December 2003 05:33 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
what do you mean by [i]some other stuff in UNIX?[/i] ??
i am using the sequence inside the oracle db.
for every sqlloader session or every time u execute this script, the sequence value is returned as CONTROL_ID
----------------------------------------------------------------------
demo
First I display the current records in table
and truncate them
then i run the script.
then i check the records again.
----------------------------------------------------------------------
bash-2.03$ sqlplus -s mag/mag @za
CONTROL_ID NAME ADDRESS
---------- ------------ ------------------------------
79 Charlie 250 yonge st
79 Peter 5 Brookbanks dr
79 Ben 123 King st
Table truncated.
bash-2.03$ loadm
the generated sequential value is 80
The control file to be used to load is
load data
infile 'member.dat'
insert into table member
fields terminated by ','
(
control_id constant '80',
name,
address
)
invoking the sql*loader
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Dec 24 10:21:38 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
bash-2.03$ sqlplus -s mag/mag @za
CONTROL_ID NAME ADDRESS
---------- ------------ ------------------------------
80 Charlie 250 yonge st
80 Peter 5 Brookbanks dr
80 Ben 123 King st
Table truncated.
----------------------------------------------------------------------
script
im using a sequence from the db.
----------------------------------------------------------------------
bash-2.03$ cat loadm
#!/usr/bin/bash
const=$(sqlplus -s "mag/mag"<< EOF | perl -ne 'chomp;s/s//g;print;'
set feed off
set head off
select memberseq.nextval from dual;
exit;
EOF)
echo the generated sequential value is $const
cp member.ctl temp.ctl
sed -e "s/$const/$const/g" temp.ctl > temp1.ctl
echo
echo The control file to be used to load is
cat temp1.ctl
echo
echo invoking the sql*loader
sqlldr userid=mag/mag control=temp1.ctl
bash-2.03$ cat member.ctl
load data
infile 'member.dat'
insert into table member
fields terminated by ','
(
control_id constant '$const',
name,
address
)
[Updated on: Sun, 23 April 2006 21:16] Report message to a moderator
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #72967 is a reply to message #72966] |
Wed, 24 December 2003 06:37 |
Charlie
Messages: 35 Registered: March 2001
|
Member |
|
|
Forget about some stuff in Unix.
I meant that I was wondering if Unix can generate sequence number. Of course I know we can make such a thing as sequence in DB.
I just run your script. it seems to work fine printing out a sequence number and writing files.
However, the table got populated with null value at control_id column. When I look at the control file,
constant value was not placed there.
What seems to be the problem?
|
|
|
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #463087 is a reply to message #72951] |
Tue, 29 June 2010 11:59 |
mleninbabugmailcom
Messages: 8 Registered: June 2010
|
Junior Member |
|
|
Hi,
Thanks for your quick response i posted in forum also.iam trying to pass parameter that should be insert in to the column level.
this is my control file
LOAD DATA
infile '$file_directory'
INTO TABLE gcca.GCCA_GERS_HDR
Append
WHEN (01) = 'HDR'
fields terminated by '^' optionally enclosed by '"'
TRAILING NULLCOLS
( record_type POSITION(1)CHAR TERMINATED BY '^',
key_id CHAR TERMINATED BY '^' "ltrim(rtrim(:key_id))",
employee_id CHAR TERMINATED BY '^' "ltrim(rtrim(:employee_id))",
source '$file_source');
data file:
HDR^TEA0010366^34342
HDR^TEA0010367^34340
HDR^TEA0010368^34341
In data file i dont have value for the source i need to insert at run time.
For the above control file i need to give file directory and source as parameters.
Thanks,
Lenin
|
|
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #463149 is a reply to message #72951] |
Tue, 29 June 2010 17:36 |
mleninbabugmailcom
Messages: 8 Registered: June 2010
|
Junior Member |
|
|
Hi GURU's,
I tried like this
# !/bin/ksh
file_source=$5
file_directory=$6
export file_source file_directory
env | grep file
cat > /$GCCA_TOP/bin/gcca_gers_load.ctl << EOF
LOAD DATA
infile '$file_directory'
INTO TABLE gcca.GCCA_GERS_HDR
Append
WHEN (01) = 'HDR'
fields terminated by '^' optionally enclosed by '"'
TRAILING NULLCOLS
( record_type POSITION(1)CHAR TERMINATED BY '^',
key_id CHAR TERMINATED BY '^' "ltrim(rtrim(:key_id))",
employee_id CHAR TERMINATED BY '^' "ltrim(rtrim(:employee_id))",
source '$file_source'
);
EOF
/$GCCA_TOP/bin/gcca_gers_load.ctl
echo invoking the sql*loader
sqlldr userid=apps/dev1apps control=gcca_gers_load.ctl
i registerd with two parameters and i ran it from application, it got error out and it didn't create log also.
Please help me out where i did wrong in the above script
|
|
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #463156 is a reply to message #463151] |
Tue, 29 June 2010 19:12 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
--
-- sample load. first parameter $1 is directoryName and second parameter $2 is filename.
-- during input, you need to escape the slashes (/)
oracle@kaapi#./somescript "\/temp" somedat.dat
Control used to load is
load data
truncate into table t2
fields terminated by ','
(
c1,
c2 constant '/temp'
)
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jun 29 19:53:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
C1 C2
---------- ------------------------------
va1 /temp
va2 /temp
--
-- Second run. Just changing the name for the directory. Anyvalue can go in
oracle@kaapi#./somescript "\/u01\/mydir\/otherDir" somedat.dat
Control used to load is
load data
truncate into table t2
fields terminated by ','
(
c1,
c2 constant '/u01/mydir/otherDir'
)
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jun 29 19:53:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
C1 C2
---------- ------------------------------
va1 /u01/mydir/otherDir
va2 /u01/mydir/otherDir
--
-- This is the script
--
-- somectl.ctl is the template control file.
-- For every load, a new temp.ctl is created and used to load
--
oracle@kaapi#cat somescript
cat somectl.ctl | sed -e "s/dirName/$1/g" > temp.ctl
echo "Control used to load is "
cat temp.ctl
sqlldr dbadmin/xxx control=temp.ctl data=$2
sqlplus -s dbadmin/xxx <<EOF
select * from t2;
Exit;
EOF
oracle@kaapi#cat somectl.ctl
load data
truncate into table t2
fields terminated by ','
(
c1,
c2 constant 'dirName'
)
oracle@kaapi#cat somedat.dat
va1
va2
Hope this helps.
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #463314 is a reply to message #463156] |
Wed, 30 June 2010 12:16 |
mleninbabugmailcom
Messages: 8 Registered: June 2010
|
Junior Member |
|
|
Hi folks,
Still i am facing the problem.Please find the attached file which contains my code.
I tried like the attached file.
I am getting error message like this in the log file.
/opt/apps/products/11_5/ERPDEV/erpdevappl/GCCA/11.5.0/bin/gcca_gers_load_data_prog[4]: ^M: not found.
in file
/us_test_datanew.out
/opt/apps/products/11_5/ERPDEV/erpdevappl/GCCA/11.5.0/bin/gcca_gers_load_data_prog[8]: ^M: not found.
/opt/apps/products/11_5/ERPDEV/erpdevappl/GCCA/11.5.0/bin/gcca_gers_load_data_prog[10]: ^M: not found.
infile '
/us_test_datanew.out
'
/opt/apps/products/11_5/ERPDEV/erpdevappl/GCCA/11.5.0/bin/gcca_gers_load_data_prog[23]: Syntax error at line 108 : `'' is not matched.
/opt/apps/products/11_5/ERPDEV/erpdevappl/GCCA/11.5.0/bin/gcca_gers_load_data_prog
Program exited with status 2
Thanks,
Lenin
|
|
|
|
|
Re: how to assign a numeric value gererated from sequence in a control file [message #464391 is a reply to message #463317] |
Wed, 07 July 2010 13:50 |
lenin_babu55
Messages: 12 Registered: August 2007
|
Junior Member |
|
|
Hi folks,
Thanks for your help and all now iam able to insert the dynamic value at column level.
I have another issue in the same program like i registered it in oracle apps and iam running from application it always shows completed normal eventhough the script fails .
So i need to do like if script fails it completes with error .
I need to do any change in the shell script to acheive this?
Please help me out
Thanks,
Lenin
|
|
|
|