How to run shell script?

[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

[sambavan]

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

[badrinathn]

Then how do I pass the parameters. Can I just say

./del.sh par1 par2 par3....

Badrinath

[badrinathn]

Sorry,
I also want to call this shell program through another
calling program.

Please help me.
Badrinath

[jm]

you are correct, the syntax is:

./del.sh par1 par2 par3....

[badrinathn]

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

[sambavan]

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

[sambavan]

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 <b>${user}/${pass}@${sid}</b>

Hope this would fix the problem.

Sam

[badrinathn]

Sambavan, It was very clear, but still it doesn't work for me.


<<del1.sh>> *********************************
#!/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;



<<del2.sh>>>>*******************************
/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 <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name: Enter password:
Enter password:
ERROR:
ORA-01005: null password given; logon denied

*****************************************************
so, How do I specify this parameters.

Badrinath

[badrinathn]

I have simplified the code,, only to accept the user name.

Please let me know I do I pass only one parameter

<<del3.sh>>>>>
#!/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

[sambavan]

Here is a sample script for your test to undestand things

********************************
#!/bin/sh

user=${1}
pass=${2}
sid=${3}

$ORACLE_HOME/bin/sqlplus $user/$pass@$sid << EOF
select sysdate from dual;
EOF

exit
***********************************


Copy it into a file, example test.sh and change permissions of test.sh

chmod 755 test.sh

execute it as

./test.sh scott tiger SID_name_here



According to your code, it should look as follows

#!/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;
__eof__


exit <--- if you want to exit after the execution of this script.

Good luck,
Sam

[badrinathn]

Finally It worked.

Thank you for your patience, in guiding me.

Thanks again
Badrinath