Home » RDBMS Server » Enterprise Manager » Failed script (OEM 12c job - script)
Failed script [message #644075] Tue, 27 October 2015 16:23 Go to next message
gbernas
Messages: 4
Registered: October 2015
Junior Member
Why knows why this script fail when I try to execute it through an OEM job. I"m trying to determine which users will expire within the next 14 days.

If anyone knows how I can get this information through BI publisher - Management Repository Views - that would be event better.




column name format a30
set linesize 300
select
name,
TO_CHAR(ctime,'DD-MM-YYYY HH:MI') Create_Date,
TO_CHAR(ptime,'DD-MM-YYYY HH:MI') Last_Changed,
TO_CHAR(EXPTIME,'DD-MM-YYYY HH:MI') Expire_Date
from
sys.user$
where
EXPTIME <= sysdate + 14
and
Name not in ('RCU_INSTALL')
and
ASTATUS = 0
order by name
Re: Failed script [message #644080 is a reply to message #644075] Wed, 28 October 2015 01:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you call "it fails"? Did you get any error, or what?

Though, I'd modify the condition from
where exptime <= sysdate + 14

to
where exptime between sysdate and sysdate + 14

because yours returns users who have already expired (which can be a valid state, I presume).
Re: Failed script [message #644106 is a reply to message #644075] Wed, 28 October 2015 03:33 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Also,
Name not in ('RCU_INSTALL')

Could obviously be
Name != 'RCU_INSTALL'
Re: Failed script [message #644138 is a reply to message #644106] Wed, 28 October 2015 08:32 Go to previous messageGo to next message
gbernas
Messages: 4
Registered: October 2015
Junior Member
sorry when I mean failed, when I past the above SQL into an OEM job script, I get an error say


SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 28 13:31:27 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 exit
*
ERROR at line 15:
ORA-00933: SQL command not properly ended
Re: Failed script [message #644139 is a reply to message #644080] Wed, 28 October 2015 08:33 Go to previous messageGo to next message
gbernas
Messages: 4
Registered: October 2015
Junior Member
thank you; this is a good idea.
Re: Failed script [message #644141 is a reply to message #644106] Wed, 28 October 2015 08:55 Go to previous messageGo to next message
gbernas
Messages: 4
Registered: October 2015
Junior Member
for the Name not in ('RCU_INSTALL')

I"m going to have a few more users added to this list.

Re: Failed script [message #644143 is a reply to message #644141] Wed, 28 October 2015 08:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no terminator at the end of your script. Try to put a semi-colon (or a slash) there, such as
order by name;
or
order by name
/


(Note that I don't use OEM so this is a pure SQL*Plus advice).

[Updated on: Wed, 28 October 2015 08:57]

Report message to a moderator

Re: Failed script [message #644145 is a reply to message #644075] Wed, 28 October 2015 09:07 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
This works for me:
SQL> column name format a30
SQL> set linesize 300
SQL> select
  2  name,
  3  TO_CHAR(ctime,'DD-MM-YYYY HH:MI') Create_Date,
  4  TO_CHAR(ptime,'DD-MM-YYYY HH:MI') Last_Changed,
  5  TO_CHAR(EXPTIME,'DD-MM-YYYY HH:MI') Expire_Date
  6  from
  7  sys.user$
  8  where
  9  EXPTIME <= sysdate + 14
 10  and
 11  Name not in ('RCU_INSTALL')
 12  and
 13  ASTATUS = 0
 14  order by name
 15  /

NAME                           CREATE_DATE      LAST_CHANGED     EXPIRE_DATE
------------------------------ ---------------- ---------------- ----------------
USER01                         27-02-2015 11:05 16-09-2015 12:59 11-09-2015 03:20
USER02                         29-05-2014 01:10 16-09-2015 01:02 11-09-2015 12:31

SQL>

Post your entire SQL*Plus session, like I did.
Previous Topic: E-mail alerts in 12c
Next Topic: Data Guard (OEM)
Goto Forum:
  


Current Time: Thu Mar 28 06:01:19 CDT 2024