Eliminating unwanted messages from a sqlplus query executed from command line

Question

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

Ok 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 — Nikhil
– Nikhil, Commented Sep 23, 2014 at 6:39
Don't put additional information in comment. Instead, you should edit your question to make it better, clearer. — APC
– APC, Commented Sep 23, 2014 at 7:56
Similar question here stackoverflow.com/questions/1095871/… — Lalit Kumar B
– Lalit Kumar B, Commented Sep 23, 2014 at 7:59

Alex Poole · Accepted Answer · 2014-09-23 09:01:29Z

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.

APC · Accepted Answer · 2014-09-23 07:53:59Z

1

This error message ...

 ORA-28002: the password will expire within 7 days

...means what it says. The account's password is set to expire and that user will not be able to login within the next week.

You want to get rid of the message? Simple: change the password.

If that solution is undesirable for any reason (I don't know how you use the account) you will need to persuade the DBA to unexpire it. That is probably less simple.

answered Sep 23, 2014 at 7:53

APC

147k20 gold badges180 silver badges289 bronze badges

Comments

Lalit Kumar B · Accepted Answer · 2014-09-23 07:56:03Z

0

If you want to get rid off the PASSWORD EXPIRY message, then you can set the PASSWORD_LIFE_TIME limit to unlimited. Follow these steps :

SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME='SCOTT'
  2  /

PROFILE
------------------------------
DEFAULT

SQL>
SQL> SELECT RESOURCE_NAME,
  2    LIMIT
  3  FROM DBA_PROFILES
  4  WHERE PROFILE    ='DEFAULT'
  5  AND RESOURCE_NAME='PASSWORD_LIFE_TIME'
  6  /

RESOURCE_NAME                  LIMIT
------------------------------ ------------------------------
PASSWORD_LIFE_TIME             180

For your profile, execute :

alter profile <profile_name> limit password_life_time UNLIMITED;

You shouldn't be getting the message again.

answered Sep 23, 2014 at 7:56

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

2 Comments

APC Over a year ago

This solution may or may not be applicable, depending on the organization's security policies.

Lalit Kumar B Over a year ago

Agreed. The answer can be improvised depending on further information by OP.

Tejas S · Accepted Answer · 2023-02-21 14:16:26Z

0

Adding -S flag worked for me.

sqlplus -S username/pass@server @sample.sql

answered Feb 21, 2023 at 14:16

Tejas S

115 bronze badges

Collectives™ on Stack Overflow

Eliminating unwanted messages from a sqlplus query executed from command line

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related