Tag Archives: oracle

ORA-28000: the account is locked – PeopleSoft

Hey everyone,

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:

1
ALTER USER sysadm ACCOUNT UNLOCK;

If you don’t have access you may need to get a DBA to run it.

Installing SQL Developer on Ubuntu – make-sqldeveloper-package chmod: missing operand after `755′

Hey everyone,

I was following a guide while trying to install SQL Developer on Ubuntu, however I ran into the following error:

make-sqldeveloper-package chmod: missing operand after `755′

 

To fix it, simply change line number #381 in ‘/usr/bin /make-sqldeveloper-package’ as follows:

Original

${FIND} "${OPTDIR}" ! \( -type d -o -name "*.jar" \) |${XARGS} ${XARGS_OPTS} ${FILE} ${FILE_OPTS} |${GREP} ${GREP_OPTS} "shell script text executable" |${CUT} ${CUT_OPTS_FUNC_CLEAN} |${XARGS} ${XARGS_OPTS} ${CHMOD} ${CHMOD_OPTS}

Modified

${FIND} "${OPTDIR}" ! \( -type d -o -name "*.jar" \) |${XARGS} ${XARGS_OPTS} ${FILE} ${FILE_OPTS} |${GREP} ${GREP_OPTS} "shell script" | ${GREP} ${GREP_OPTS} "text executable" |${CUT} ${CUT_OPTS_FUNC_CLEAN} |${XARGS} ${XARGS_OPTS} ${CHMOD} ${CHMOD_OPTS}

Thanks to this blog post for the solution, just note that it is Polish.

Let me know if you have any issues!

View Last Query Executed – Oracle

Hey all,

Just a quick post on how to view recent queries run into an Oracle database:

 
SELECT sql_text, sql_fulltext
  FROM v$sql
ORDER BY last_load_time DESC

Cheers

Oracle – Number of Weekdays Between Two Dates

Hey everyone,

Just a quick post on how to select the number of weekdays between two dates:

SELECT temp_tbl.days
--SELECT COUNT(temp_tbl.days)
FROM (          
        SELECT (TRUNC(TO_DATE ('01/08/2012', 'DD/MM/YYYY'), 'dd') + LEVEL - 1) days
        FROM DUAL
        CONNECT BY LEVEL <= TO_CHAR(TO_DATE('10/08/2012', 'DD/MM/YYYY') - TO_DATE('01/08/2012', 'DD/MM/YYYY'))
     ) temp_tbl
WHERE TO_CHAR(temp_tbl.days, 'D') >= 2
      AND TO_CHAR(temp_tbl.days, 'D') <= 6

Sending Email – PL/SQL

A quick post on how to send an email with PL/SQL.

There’s a LOT of documentation available at the following link:
http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
PROCEDURE EMAIL_ERROR_REPORT IS
 
       /* Create vars */
       v_From        VARCHAR2 (80) := 'test@test.com';
       v_Recipient   VARCHAR2 (80) := 'me@me.com';--REPORT_RECEIVER;
       v_Subject     VARCHAR2 (80) := 'test subject';
       v_Mail_Host   VARCHAR2 (30) := 'smtp.test.com';
       v_Mail_Conn   UTL_SMTP.Connection;
       crlf          VARCHAR2 (2) := CHR (13) || CHR (10);
    BEGIN
 
       /* Define connection */
       v_Mail_Conn := UTL_SMTP.Open_Connection (v_Mail_Host, 25);
       UTL_SMTP.Helo (v_Mail_Conn, v_Mail_Host);
       UTL_SMTP.Mail (v_Mail_Conn, v_From);
       UTL_SMTP.Rcpt (v_Mail_Conn, v_Recipient);
       UTL_SMTP.Data (
          v_Mail_Conn,
             'Date: '
          || TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
          || crlf
          || 'From: '
          || v_From
          || crlf
          || 'Subject: '
          || v_Subject
          || crlf
          || 'To: '
          || v_Recipient
          || crlf
          || crlf
          || 'some message text'
          || crlf
          ||                                                       -- Message body
            'more message text'
          || crlf);
       UTL_SMTP.Quit (v_mail_conn);
    EXCEPTION
 
       /* Catch exceptions */
       --WHEN OTHERS THEN         
    END;

Reserved Words – Oracle

Just a quick post on how to check if a word is reserved using the v$reserved_words:

SELECT *
FROM 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:

SELECT *
FROM v$reserved_words
WHERE UPPER(keyword) LIKE '%ONLINE%'

Sleep Function Within PeopleCode – PeopleSoft

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);