2

im trying to save the output of a query in a variable. I followed some answers here in stackoverflow but no luck. Currently i'm using this 

billerrors=$(sqlplus -s $username/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
SELECT ERROR from temp_table
exit;
EOF
)

echo $billerrors

The output i always get is

SQLPlus: Release 11.2.0.1.0 Production Copyright (c) 1982, 2009, Oracle. All rights reserved. Use SQLPlus to execute SQL, PL/SQL and SQL*Plus statements.

The output of the above query SELECT ERROR from temp_table will be in the below format

Bill payment errors in the last 30 minutes: XX : 70. YY : 20.

Incase of no bill errors, the output will be

Bill payment errors in the last 30 minutes: 0.

I would really appreciate if someone could help

Improve this question
12
  • what do you get if you run the command on a terminal? Commented Jan 24, 2018 at 15:48
  • In general it should work well. Maybe try just SELECT ERROR from temp_table; (without exit;) Commented Jan 24, 2018 at 15:58
  • It should not matter, however try capital -S Commented Jan 24, 2018 at 16:01
  • @LuisMuñoz I'm getting the correct output when i run the same query in toad. Commented Jan 24, 2018 at 16:27
  • @WernfriedDomscheit Same result when i try with capital S also Commented Jan 24, 2018 at 16:29

1 Answer 1

Reset to default
2

You use the wrong names in the script of the variable username and password. If you use the correct names in the script for the variables username and password, then your script must work.

Exapmple 1. Correct names in the script for the variables $username and $password.

  #!/bin/sh

username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd

echo username:  $username
echo password:  $password
echo tns:       $SID

billerrors=$(sqlplus -s $username/$password@$SID  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors

    oracle@esmd:~> ./test.sh
    username: SCOTT
    password: Tiger
    tns: esmd
    USER is "SCOTT" Date: 25-01-2018 08:32 The test is passed

Example 2. I make an error in the name of the variable $UserName, when I call sqlplus

#!/bin/sh

username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd

echo username:  $username
echo password:  $password
echo UserName:  $UserName
echo tns:       $SID

billerrors=$(sqlplus -s $UserName/$password@$SID  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors

        oracle@esmd:~> ./test.sh
        username: SCOTT
        password: Tiger
        UserName:
        tns: esmd

            SQL*Plus: Release 11.2.0.3.0 Production Copyright (c) 1982, 2011, Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus 
        statements. Usage 1: sqlplus -H | -V -H Displays the SQL*Plus version and the 
        usage help. -V Displays the SQL*Plus version. Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ] <option> is: [-C <version>] [-L] [-M "<options>"] 
        [-R <level>] [-S] -C <version> Sets the compatibility of affected commands to the version specified by <version>. The version has the form "x.y[.z]". 
For example, -C 10.2.0 -L Attempts to log on just once, instead of reprompting 
on error. -M "<options>" Sets automatic HTML markup of output. The options have the form: HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] 
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}] -R <level> Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 
1, 2 or 3. The most restrictive is -R 3 which disables all user commands 
interacting with the file system. -S Sets silent mode which suppresses the 
display of the SQL*Plus banner, prompts, and echoing of commands. <logon> is: 
{<username>[/<password>][@<connect_identifier>] | / } [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value] Specifies the database account username, password and connect identifier for the database connection. Without a connect identifier, SQL*Plus connects to the default database. The AS SYSDBA, AS SYSOPER and AS SYSASM options are database administration privileges. <connect_identifier> can be in the form of Net Service Name or Easy Connect. @[<net_service_name> | [//]Host[:Port]/<service_name>] <net_service_name> is a simple name for a service that resolves to a connect descriptor. Example: Connect to database using Net Service Name and the database net service name is ORCL. sqlplus myusername/mypassword@ORCL Host specifies the host name or IP address of the database server computer. Port specifies the listening port on the database server. <service_name> specifies the service name of the database you want to access. Example: Connect to database using Easy Connect and the Service name is ORCL. sqlplus myusername/mypassword@Host/ORCL The /NOLOG option starts SQL*Plus without connecting to a database. The EDITION specifies the value for Session Edition. <start> is: @<URL>|<filename>[.<ext>] [<parameter> ...] Runs the specified SQL*Plus script from a web server (URL) or the local file system (filename.ext) with specified parameters that will be assigned to substitution 
    variables in the script. When SQL*Plus starts, and after CONNECT commands, the 
    site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile 
    (e.g. login.sql in the working directory) are run. The files may contain 
    SQL*Plus commands. Refer to the SQL*Plus User's Guide and Reference for more 
    information.

Example 3. I make an error in the name of the variable $Password, when I call sqlplus

#!/bin/sh

username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd

echo username:  $username
echo password:  $password
echo UserName:  $UserName
echo Password:  $Password
echo tns:       $SID

billerrors=$(sqlplus -s $username/$Password@$SID  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors



       oracle@esmd:~> ./test.sh
        username: SCOTT
        password: Tiger
        UserName:
        Password:
        tns: esmd
        SQL*Plus: Release 11.2.0.3.0 Production Copyright (c) 1982, 2011, 
Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus 
statements. Usage 1: sqlplus -H | -V -H Displays the SQL*Plus version and the

Example 4 I make an error in the name of the variable $Sid, when I call sqlplus

#!/bin/sh

username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd

echo username:  $username
echo password:  $password
echo SID:       $SID
echo Sid:       $Sid

billerrors=$(sqlplus -s $username/$password@$Sid  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors


oracle@esmd:~> ./test.sh
username: SCOTT
password: Tiger
SID: esmd
Sid:
SQL*Plus: Release 11.2.0.3.0 Production Copyright (c) 1982, 2011, Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus

Example 5 I make an error in the value of the variable $password=tiger, when I call the sqlplus program

#!/bin/sh

username=SCOTT
## True password is Tiger
password=tiger

## esmd is TNS alias in tnsnames.ora
SID=esmd

echo username:  $username
echo password:  $password
echo SID:       $SID

billerrors=$(sqlplus -s $username/$password@$SID  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors




 oracle@esmd:~> ./test.sh
    username: SCOTT
    password: tiger
    SID: esmd
    ERROR: ORA-01017: invalid username/password; logon denied SP2-0306: Invalid
 option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] 
[edition=value]] where <logon> ::= <username>[/<password>]
[@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>]
[@<connect_identifier>] SP2-0306: Invalid option. Usage: CONN[ECT] 
ORACLE after 3 attempts, exiting SQL*Plus

Example 6 I make an error in the value of the variable $SID=esm.

#!/bin/sh

username=SCOTT
password=tiger
## esmd is TNS alias in tnsnames.ora
## True is esmd
SID=esm

echo username:  $username
echo password:  $password
echo SID:       $SID

billerrors=$(sqlplus -s $username/$password@$SID  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors

    oracle@esmd:~> ./test.sh
    username: SCOTT
    password: tiger
    SID: esm
    ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
 SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS 
YSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[<username>]
[/<password>][@<connect_identifi>] SP2-0157: unable to CONNECT to ORACLE after 3 
attempts, exiting SQL*Plus

Example 7 If the Username is case sensitive.

#!/bin/sh

username=\"Scott\"
password=TigeR
## esmd is TNS alias in tnsnames.ora
SID=esmd

echo username:  $username
echo password:  $password
echo SID:       $SID

billerrors=$(sqlplus -s $username/$password@$SID  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)

echo $billerrors


oracle@esmd:~> ./test.sh
username: "Scott"
password: TigeR
SID: esmd
USER is "Scott" Date: 25-01-2018 09:20 The test is passed
oracle@esmd:~> ./test.sh
username: "Scott"
password: TigeR
SID: esmd
USER is "Scott" Date: 25-01-2018 09:23 The test is passed
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks for the detailed explanation. I tweaked the query and the data inserted to the temp table a little bit and just tried getting the COUNT. It is working now :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.