DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to run shell script?

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    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


  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Then how do I pass the parameters. Can I just say

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

    Badrinath

  4. #4
    Join Date
    Jan 2001
    Posts
    642
    Sorry,
    I also want to call this shell program through another
    calling program.

    Please help me.
    Badrinath

  5. #5
    Join Date
    Nov 2000
    Posts
    245

    you are correct, the syntax is:

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


  6. #6
    Join Date
    Jan 2001
    Posts
    642
    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

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  9. #9
    Join Date
    Jan 2001
    Posts
    642
    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






  10. #10
    Join Date
    Jan 2001
    Posts
    642
    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
  •  


Click Here to Expand Forum to Full Width