Just a small PeopleSoft issue I ran into while running an SQR:
(SQR 5528) ORACLE OCISessionBegin(RDBMS) error 28000 in cursor 0:
ORA-28000: the account is locked
(SQR 4701) Cannot logon to the database.
SQR for PeopleSoft: Program Aborting.
For some reason the sysadm account had been locked, thankfully there’s a fairly easy fix:
ALTER USER sysadm ACCOUNT UNLOCK;
If you don’t have access you may need to get a DBA to run it.
Just a quick post on how to view recent queries run into an Oracle database:
SELECT sql_text, sql_fulltext
ORDER BY last_load_time DESC
Just a quick post on how to check if a word is reserved using the v$reserved_words:
WHERE reserved = 'Y'
Alernatively you can specify the word, i.e. to check if online is a reserved word, run the following:
WHERE UPPER(keyword) LIKE '%ONLINE%'
I came across a requirement today where I needed to find out when a user had last logged into PeopleSoft. The record PSACCESSLOG came in handy here, it stores all of the following:
OPRID: The users operator ID
LOGIPADDRESS: The users IP address
LOGINDTTM: A timestamp showing when the user logged in
LOGOUTDTTM: A timestamp showing when the user logged out
PT_SIGNON_TYPE: The signon type
Find Last Login Date for User:
WHERE oprid = ''
ORDER BY effdt DESC;
Find all logins since 1st March 2012:
WHERE TO_DATE(CAST(logindttm AS DATE), 'dd/mm/yy') >= TO_DATE('01/03/2012', 'dd/mm/yy');
PSACCESSLOG - Login History in PeopleSoft
Leave a comment below if you have any questions.
Editing a message catalog definition in PeopleSoft is pretty straight forward, simply browse to the following menu path:
PeopleTools > Utilities > Administration > Message Catalog
Enter your message set number into the prompt. Note that if you do not know your message set number you can use the following to find it:
WHERE MESSAGE_SET_NBR = '12345'
AND MESSAGE_NBR = '12345';
WHERE MESSAGE_TEXT LIKE '%what your message is%';
Once you have your message simply edit the text and description appropriately then save!
While at work today I discovered that there doesn’t appear to be any built in functionality to allow for a delay to be implemented within PeopleCode. Thankfully there are a couple of roundabout way you can go about doing this:
#1: Works if you are sitting on an Oracle DB
/* The following code creates a 5 second delay using DBMS_LOCK.SLEEP */
&s = 5;
SQLExec("exec DBMS_LOCK.SLEEP(:1)", &s);
#2: Note that this method is not as efficient as DMBS_LOCK.SLEEP()
/* The following code creates a 5 second delay using the java sleep method */
&s = 5;
GetJavaClass("java.lang.Thread").sleep(1000 * &s);