 Help me ...spent a long time on this issue [message #128049] |
Thu, 14 July 2005 16:19  |
sanjay191
Messages: 1
Registered: July 2005
|
Junior Member |
|
|
Guru's
I am new to industry..is my first project. i am weak in unix.
The project is am loading 2flat files to oracle 9i (weekly loading).
Sql loader---staging table-- actual table.
for every weekly load i have to update or insert...
I am using the MERGE statement and everything is fine on my machine when i test it with TOAD. Now i have to call from unix..
Now i am calling 2 scripts from unix
1st -- to load the data from flat file to sstaging table using sqlldr in shell script, i wrote the shell script with all the valuable informatioin in these forum.
Now i am trying to execute the 2nd Script in unix whic calls the MERGE statement and i could not fix the errors( from 2 days.-- ).
i tried these 2 scripts below.. Help me where i am goin wrong..plzzzzz
# final script #
#!/usr/bin/ksh
sqlplus -s dev/dev@connect <b><<</b>EOF | read RETVAL
set echo off heading off feedback off pagesize 0
WHENEVER SQLERROR EXIT 1
merge into final_test1 f
using (select * from test1) t
on (f.deptno=t.deptno)
when matched then
update
set f.dept_name=t.dept_name
when not matched then
insert ( f.deptno,f.dept_name ) values ( t.deptno,t.dept_name);
/
exit;
EOF;
echo "retval is $RETVAL";
Error-- It says syntax error at line 3 ``' unexpected..
2 nd
#Script test.sh#
#!/usr/bin/ksh
sqlplus -s dev/dev@connectstring
merge into final_test1 f
using (select * from test1) t
on (f.deptno=t.deptno)
when matched then
update
set f.dept_name=t.dept_name
when not matched then
insert ( f.deptno,f.dept_name ) values ( t.deptno,t.dept_name);
return=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader exectuion exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac
this will go to Infinite loop, then i have to kill the process.
Help me ...
|
|
|
|
| Re: how to load multiple files into 1 table using unix script [message #149279 is a reply to message #128049] |
Wed, 30 November 2005 23:30  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
From your second script...
| Quote: |
sqlplus -s dev/dev@connectstring
|
where is the <<EOF in this script.
your script should be something like
| Quote: |
sqlplus -s dev/dev@connectstring << EOF
merge into final_test1 f
using (select * from test1) t
on (f.deptno=t.deptno)
when matched then
update
set f.dept_name=t.dept_name
when not matched then
insert ( f.deptno,f.dept_name ) values ( t.deptno,t.dept_name);
exit;
EOF
return=`echo $?`
case "$retcode" in -- i think both are same
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader exectuion exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac
|
And i just noticed look at bold text above.
Look here also.
regards,
tarun
[Updated on: Wed, 30 November 2005 23:46] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
