2 Replies Latest reply: Jul 3, 2013 5:45 AM by DK2010 Branched from an earlier discussion. RSS

    Re: How to pass a parameter to SQL from shell script loops

    kumar73

      Hello Purushothaman / all,

       

      I have a requirement where I had to call a sql file from unix shell script , but the sql file is going to take user accepted parameters .,., how I can provide the paramters [ one or multiple ] to a  sql file through shell script . I tired like the followig .sh file I created but got error

       

      Enter value for oemodiworkschema:

      SP2-0546: User requested Interrupt or EOF detected.

       

       

       

       

       

       

       

      #!/bin/bash

      source  /usr/local/bin/setora-11g
      SQL=$ORACLE_HOME/bin/sqlplus
      LogDirectory='/tmp/logs'

      SCHEMA_NAME=MY_SCHEMA
      LOCATION=/app/sql/install.sql
      echo "Enter UserName to connect to DATABASE :\n"
      read username
      DBUSER=$username
      echo "Enter Password :\n"
      read pwd
      DBPWD=$pwd
      echo " Enter Host String : \n"
      read host
      MYDB=$host
      echo $DBUSER
      if test  $DBUSER != $DBPWD
      then
      echo " Invalid username or password "
      exit
      fi
      $SQL -s  $DBUSER/$DBPWD@$MYDB << EOF
      set linesize 32767
      set feedback on
      set serveroutput on
      select * from dual ;
      @$LOCATION $SCHEMA_NAME
      EOF

       

      When executed ,  am gettting following error ..

       

       

      Enter value for oemodiworkschema:

      SP2-0546: User requested Interrupt or EOF detected.

       

      Pls help ..

       

      This is the statment from sql file asking the user paramter to enter "Enter value for oemodiworkschema: " I got to provide the paramter to this using shell script this is what my query is ..

      thanks