CODEWITHSUNDEEP
databaseoracle-databaseunixsqlplusoracle12c
Nikhil
Nikhil

Reputation: 109

Eliminating unwanted messages from a sqlplus query executed from command line

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

Upvotes: 0

Views: 3734

Answers (4)

Tejas S
Tejas S

Reputation: 11

Adding -S flag worked for me.

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

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191245

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.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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.

Upvotes: 0

APC
APC

Reputation: 146209

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.

Upvotes: 1

Related Questions