-
Hi
I have the following shell script
#####################################
#!/bin/sh
user =${1}
pass =${2}
sid =${3}
colmatch =${4}
deltable =${5}
childtbl =${6}
childcol =${7}
parenttbl =${8}
parentcol =${9}
part =${10}
${ORACLE_HOME}/bin/sqlplus ${user}@${sid}/${pass} << __eof__
set head off;
commit;
set transaction use rollback segment rei_data_load;
declare
acc_num ${deltable}.${colmatch}%type;
cursor c1 is
select ${colmatch} from ${deltable};
begin
open c1;
loop
fetch c1 into acc_num ;
exit when c1%notfound;
Delete from ${childtbl} partition ${part}
where ${childcol} = acc_num;
Delete from ${parenttbl} partition ${part}
where ${parentcol} = acc_num;
end loop;
close c1;
end;
#######################################
How do I run it from unix and test it out. The program name is del.sh
Please advise
Badrinath
-
Running it as
/bin/sh your_script_name
or
./your_script_name
Either of then would work on borne shell/korne shell
if your are in c shell
% $ <-- this would take you to korne shell
$ /bin/sh your_script_name
make sure that read and execute permissions had been set.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Then how do I pass the parameters. Can I just say
./del.sh par1 par2 par3....
Badrinath
-
Sorry,
I also want to call this shell program through another
calling program.
Please help me.
Badrinath
-
you are correct, the syntax is:
./del.sh par1 par2 par3....
-
In the above example, I am trying to pass the username,passwd and sid as follows.(For timebeing I have suppressed the other parameters)
./del1.sh rei nas rel (user=rei, pass=nas , sid =rel)
and
./del1.sh rei rel nas.
In both the cases, I am not getting connected.
Badrianth
-
If you are planning to execute the shell script directly, then it would be
./del.sh par1 par2 ...
If you are calling it from another shell script, you can call it as
/fullpath/del.sh par1 par2 par3
IF you are planning to call it from another script then, it would be good to rename your del.sh to dell_data or delpartition or something like this order with no extension.
/fullpath/del par1 par2 par3
If you are passing the parameters
par1 = xyd
par2 = 623
par3= 234
/fullpath/del $par1 $par2 $par3 ...
Do not use the
/fullpath/del par1 par2 par3 for the above example, because you would just end up passing names par1 par2 and par3 and not the values that you assigned to them.
Hope this would help you.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Originally posted by badrinathn
Hi
I have the following shell script
#####################################
#!/bin/sh
user =${1}
pass =${2}
sid =${3}
colmatch =${4}
deltable =${5}
childtbl =${6}
childcol =${7}
parenttbl =${8}
parentcol =${9}
part =${10}
${ORACLE_HOME}/bin/sqlplus ${user}@${sid}/${pass} << __eof__
set head off;
commit;
set transaction use rollback segment rei_data_load;
declare
acc_num ${deltable}.${colmatch}%type;
cursor c1 is
select ${colmatch} from ${deltable};
begin
open c1;
loop
fetch c1 into acc_num ;
exit when c1%notfound;
Delete from ${childtbl} partition ${part}
where ${childcol} = acc_num;
Delete from ${parenttbl} partition ${part}
where ${parentcol} = acc_num;
end loop;
close c1;
end;
#######################################
How do I run it from unix and test it out. The program name is del.sh
Please advise
Badrinath
IT was because that you are not passing the temination point for your script
i.e __eof__ .
add the following to the end of your posted script
/
__eof__
Also change the following
${ORACLE_HOME}/bin/sqlplus ${user}@${sid}/${pass}
to
${ORACLE_HOME}/bin/sqlplus ${user}/${pass}@${sid}
Hope this would fix the problem.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Sambavan, It was very clear, but still it doesn't work for me.
<> *********************************
#!/bin/sh
user =${1}
pass =${2}
sid =${3}
${ORACLE_HOME}/bin/sqlplus ${user}@${sid}/${pass} << __eof__
set head off;
commit;
set serveroutput on;
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
<>>>*******************************
/export/home/oracle/rei/daily/del1.sh $rei $nas $rel
***************************************************
When I say ./del2.sh I get the following err
oracle@ocs1> ./del2.sh
/export/home/oracle/rei/scripts/daily/del1.sh: user: not found
/export/home/oracle/rei/scripts/daily/del1.sh: pass: not found
/export/home/oracle/rei/scripts/daily/del1.sh: sid: not found
SQL*Plus: Release 8.1.6.0.0 - Production on Mon Mar 26 15:47:12 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SP2-0310: unable to open file "/.sql"
Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where ::= [/][@] | /
Enter user-name: Enter password:
Enter password:
ERROR:
ORA-01005: null password given; logon denied
*****************************************************
so, How do I specify this parameters.
Badrinath
-
I have simplified the code,, only to accept the user name.
Please let me know I do I pass only one parameter
<>>>>
#!/bin/sh
user = ${1}
${ORACLE_HOME}/bin/sqlplus ${user}/nas@rel << __eof__
set head off;
commit;
set serveroutput on;
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
__eof__
*********************************************
running it as ./del3.sh rei fails
and say
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|