Executing SQL from Shell Script

This topic contains 2 replies, has 0 voices, and was last updated by  [email protected] 9 years, 9 months ago.

  • Author
    Posts
  • #1908

    [email protected]
    Participant

    Hi,

    I have a two node RAC Cluster Setup (10R2) and I want to execute sql queries from a shell script, to be run as root.

    I have the foll test.sh script

    #! /bin/bash

    echo “In BASH”

    sqlplus / <<+ENDOFSQL+

    select name, path from v$asm_disk;

    exit

    +ENDOFSQL+

    echo “Back to Bash”

    exit

    and I get the foll O/P

    In BASH

    SQL*Plus: Release 10.2.0.1.0 – Production on Wed Dec 23 07:25:32 2009

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    ERROR:

    ORA-12546: TNS:permission denied

    Enter user-name: SP2-0306: Invalid option.

    Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]

    where <logon> ::= <username>[/<password>][@<connect_identifier>] | /

    Enter user-name: Enter password:

    ERROR:

    ORA-12546: TNS:permission denied

    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

    Back to Bash

    What permissions should for this?

    2) Also I need to know the node names of the nodes that form the clustre. I add the foll lines to my script

    NODE_NAMES=olsnodes

    echo $NODE_NAMES

    ad I get O/P

    linux-1 linux-2

    Is it possible to store these space separated node names in diff variables that can then be used. (the number of nodes are variable)

    Any suggestion/advise is appreciated. Thanks

  • #2083

    Amit Bansal
    Keymaster

    Deepika,

    Please find response below

    1) Your first script has mistake. Should be like

    export ORACLE_SID=+ASM1

    sqlplus / as sysdba <<EOF

    select name, path from v$asm_disk;

    exit

    EOF

    2)

    node_list=olsnodes

    node1=echo $node_list|awk '{print $1}'

    node2=echo $node_list|awk '{print $2}'

    echo “Node 1 is ” $node1

    echo “Node 2 is ” $node2

    Hope this helps

  • #2084

    [email protected]
    Participant

    Thanks Amit.

    I was able to execute the SQL using the command

    sqlplus <username>/<password>@<orcl_sid>

You must be logged in to reply to this topic.