Executing SQL from Shell Script

  • This topic is empty.
Viewing 2 reply threads
  • 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>

Viewing 2 reply threads
  • You must be logged in to reply to this topic.