Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Unanswered: error on execution of sql script that has substitution variables

    Hello Friends,

    I am am running a sql script through unix script file . The sql script file has substitution parameters where the user has to provide values at run time .. when I am trying to use that script in .sh inorder to run it , its giving me error

    Enter value for workschemaname:
    SP2-0546: User requested Interrupt or EOF detected.
    Enter value for oemsbscsblschema:
    SP2-0546: User requested Interrupt or EOF detected.


    Here my .sh file

    #!/bin/bash
    source /usr/local/bin/setora-11g
    SQL=$ORACLE_HOME/bin/sqlplus
    LogDirectory='/tmp/logs'
    PATH=/app/sql/fresh_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
    $SQL -s $DBUSER/$DBPWD@$MYDB << EOF
    set linesize 32767
    set feedback on
    set serveroutput on
    @$PATH

    EOF

    fresh_install.sql has a &&workschemaname

    How to accept the user paramter into the sql file ?

    thanks

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Setting the program name to PATH is probably not a good idea. Change it to PGM or PROG.
    Are there any meta characters in User password or host. It may be necessary to place it in quotation marks.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by MIKELALA View Post
    . . . E t c . . .
    fresh_install.sql has a &&workschemaname

    How to accept the user paramter into the sql file ?
    You may need to define in top level or pass as parameter:
    Code:
     #-- like this:
    $SQL -s $DBUSER/$DBPWD@$MYDB << EOF
    set lin 32767 feed on trims off
    set serveroutput on;
    
    def workschemaname='MYSCHEMA'
    
    @$PGM
    
    EOF
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •