Issue
Basically Im trying to get the output of 'DESC HR.EMPLOYEES' via command line.
I have created a file called 'file.sql'
DESC hr.employees;
exit;
Then I execute this on unix command line:
sqlplus username/password @file.sql
My output looks like this, however I want to eliminate all extra messages and want to see only the relevant query result. Any way this is possible? Basically someway to do a silent login / logoff.
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 15 19:04:53 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Last Successful login time: Mon Sep 15 2014 19:04:06 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
I used the -s option with sqlplus. and that eliminated most of the unwanted messages. But I still get ERROR: ORA-28002: the password will expire within 7 days
Solution
You do clearly need to change the password, but if you have a scenario where you need to be able to run a particular script in the meantime, it is possible to hide that message; but you have to move the credentials into the file. (Supplying them on the command line is insecure anyway). So file.sql would become:
set termout off
connect username/password
set termout on
desc hr.employees;
exit;
And you'd run it as:
sqlplus -s /nolog @file
The /nolog means it won't automatically attempt to connect, and when it does so from within the script the output from the connection command is hidden.
Of course, this would hide any other messages related to the account or database availability which would make understanding a failure hard; and you probably really want to be told about the pending expiry so you can change the password - otherwise you'll come to run this a week later and find the account is actually expired, which isn't something you can fix yourself. (Since your example is from the 15th, your account may already have expired, or had its password reset, of course).
Another minor wrinkle with this is that any SQL commands in your login.sql or glogin.sql will show an SP2-0640 error as they will try to run before you are connected.
Just because something is possible doesn't mean it's a good idea, and the potential issues almost certainly outweigh any advantages. So really, when you see the warning, change the password.
Answered By - Alex Poole Answer Checked By - David Goodson (WPSolving Volunteer)