Home » Infrastructure » Unix » Help me ...spent a long time on this issue
icon5.gif  Help me ...spent a long time on this issue [message #128049] Thu, 14 July 2005 16:19 Go to next message
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 Go to previous message
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

Previous Topic: Urgent !!! not prompting user to change password
Next Topic: database not shutting down
Goto Forum:
  


Current Time: Thu Mar 28 04:16:00 CDT 2024