Home » RDBMS Server » Server Utilities » how to assign a numeric value gererated from sequence in a control file
how to assign a numeric value gererated from sequence in a control file [message #72951] Mon, 22 December 2003 11:22 Go to next message
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 #72952 is a reply to message #72951] Mon, 22 December 2003 22:44 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

See the SQL*Loader FAQ - http://www.orafaq.com/faqloadr.htm#MODIFY

Best regards.

Frank
Re: how to assign a numeric value gererated from sequence in a control file [message #72953 is a reply to message #72952] Tue, 23 December 2003 04:15 Go to previous messageGo to next message
Charlie
Messages: 35
Registered: March 2001
Member
I am already familiar with typical examples.
This one is very challenging because a same seq number has to be assigned to all rows coming from a same input file.
seq.nextval has to be run only once somewhere and I don't think you can do it in the control file.

Thank you for your concern anyway,
Charlie
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 #72956 is a reply to message #72955] Tue, 23 December 2003 06:13 Go to previous messageGo to next message
Charlie
Messages: 35
Registered: March 2001
Member
It works perfect.
Thank you !!!
Re: how to assign a numeric value gererated from sequence in a control file [message #72957 is a reply to message #72956] Tue, 23 December 2003 06:26 Go to previous messageGo to next message
Charlie
Messages: 35
Registered: March 2001
Member
I think I should be able to understand what your script tries to do except the two EOFs you put.
If I'm asking too much, Can you at least tell me where to go to find out more?
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 #72961 is a reply to message #72958] Tue, 23 December 2003 08:31 Go to previous messageGo to next message
Charlie
Messages: 35
Registered: March 2001
Member
I was trying to modify your script so that I don't have to type control_id myself.
Can your script generate one itself by using sequence object in DB or some other stuff in UNIX?
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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 #72968 is a reply to message #72967] Wed, 24 December 2003 07:58 Go to previous messageGo to next message
Charlie
Messages: 35
Registered: March 2001
Member
Never mind the previous msg.
It works now.
Thank you and have a great holiday.
Re: how to assign a numeric value gererated from sequence in a control file [message #73144 is a reply to message #72967] Sun, 15 February 2004 13:07 Go to previous messageGo to next message
Lois Donovan
Messages: 1
Registered: February 2004
Junior Member
How do I write"numeric value--in words or numbers
Re: how to assign a numeric value gererated from sequence in a control file [message #463070 is a reply to message #72966] Tue, 29 June 2010 11:09 Go to previous messageGo to next message
mleninbabugmailcom
Messages: 8
Registered: June 2010
Junior Member
Hi folks,

I also have the same requirement to pass the value dynamically.i saw this chain of conversations i understand the concept but iam totally new to unix and shellscript so could you please send the working code because iam getting syntax errors and also i would like to pass filedirectory along with the constant.

Thanks in advance

Lenin
Re: how to assign a numeric value gererated from sequence in a control file [message #463077 is a reply to message #463070] Tue, 29 June 2010 11:29 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about you post what you've got so far and we'll show you where you're going wrong.
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 Go to previous messageGo to next message
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 #463100 is a reply to message #463087] Tue, 29 June 2010 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
bcm@bcm-laptop:~/old$ cat example.sh
file_source='sample.dat'
file_directory="/tmp"
export file_source file_directory
env | grep file
cat > /tmp/output.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
cat /tmp/output.ctl
bcm@bcm-laptop:~/old$ ./example.sh
file_directory=/tmp
file_source=sample.dat
LOAD DATA
infile '/tmp'
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 'sample.dat');
bcm@bcm-laptop:~/old$
Re: how to assign a numeric value gererated from sequence in a control file [message #463110 is a reply to message #463100] Tue, 29 June 2010 13:27 Go to previous messageGo to next message
mleninbabugmailcom
Messages: 8
Registered: June 2010
Junior Member
Hi thanks for your reply.But i confused like you mentioned two different ways or how.

One more doubt like in the above script no need to call sqloader to execute?

Thanks,
Lenin
Re: how to assign a numeric value gererated from sequence in a control file [message #463130 is a reply to message #463110] Tue, 29 June 2010 15:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Above example by BlackSwan demonstrates on creating the controlfile.
You need to call this control file in your sqlldr command line options.
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 Go to previous messageGo to next message
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 #463150 is a reply to message #463149] Tue, 29 June 2010 17:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i registerd with two parameters and i ran it from application,
I don't know the line means or what you did.

>it got error out and it didn't create log also.
ERROR? What Error? I don't see any error.

It is a challenge to fix errors that can't not be seen.

is COPY & PASTE broken for you?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 29 June 2010 17:50]

Report message to a moderator

Re: how to assign a numeric value gererated from sequence in a control file [message #463151 is a reply to message #72951] Tue, 29 June 2010 18:03 Go to previous messageGo to next message
mleninbabugmailcom
Messages: 8
Registered: June 2010
Junior Member
Hi,

it just error out,it didn't create neither error file nor logfile.

thanks,
lenin
Re: how to assign a numeric value gererated from sequence in a control file [message #463152 is a reply to message #463151] Tue, 29 June 2010 18:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since you choose to not do as requested, I choose to not waste more time asking for needed details or trying to assist you.

Enjoy your mystery.

You're On Your Own (YOYO)!
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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 #463316 is a reply to message #463314] Wed, 30 June 2010 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>/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.
Commonly occurs when moving text files between Windoze & *NIX
edit to correct

>/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.
self explanatory
Re: how to assign a numeric value gererated from sequence in a control file [message #463317 is a reply to message #463314] Wed, 30 June 2010 12:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I would do it one-step-at-a-time.
First see if you can dynamically generate the control file the way you want it (with substituted values and all).
Try to load using this control file.
If everything works, script it out.
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 Go to previous messageGo to next message
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
Re: how to assign a numeric value gererated from sequence in a control file [message #464393 is a reply to message #464391] Wed, 07 July 2010 13:54 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can get the sqlldr exit codes and check it
http://www.cs.umbc.edu/portal/help/oracle8/server.815/a67792/ch06.htm#1956
Previous Topic: ORA-02270 during Import
Next Topic: EXPDP to remote Windows directory
Goto Forum:
  


Current Time: Fri Apr 19 14:23:15 CDT 2024