Archive for the ‘Oracle’ Category

A moldy-oldy harvested from my old blog:

SELECT

...
SUM(COL_1) AS COL1,
SUM(COL_2) AS COL2,
SUM(COL_3) AS COL3,
SUM(COL_4) AS COL4,
NVL (SUM(COL_1), 0) + NVL (SUM(COL_2), 0) + NVL (SUM(COL_3), 0) + NVL (SUM(COL_4), 0) AS TOTAL_COMPLETE
...

FROM

...

To view the status of User Scheduled Jobs in Oracle, try the following:

SELECT JOB_NAME,LOG_DATE,OWNER,STATUS
FROM USER_SCHEDULER_JOB_LOG

To view more details:

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS

To view the version of Oracle to which you are connected, enter the following from a query window:

select * from v$version
where banner like 'Oracle%';

You should see something like this:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Viewing APEX Mail Log and Queue

Posted: September 28, 2012 in Oracle APEX
Tags: ,
Run these from APEX SQL Workshop:

SELECT * from APEX_MAIL_LOG ORDER BY LAST_UPDATED_ON DESC;

The next one will normally be empty, unless just prior to a queue purge (every 15 min?) or an error happened, then it will be retried 10X before finally being purged:

SELECT * from APEX_MAIL_QUEUE ORDER BY LAST_UPDATED_ON DESC;

PL/SQL Compiler awesomeness in action

Posted: September 28, 2012 in PL/SQL
Tags: , ,

The PL/SQL package compiler, being awesome, will happily compile the following line of code in your package spec:

MY_CONST CONSTANT VARCHAR2(5):='More than 5 characters';

despite the fact that the constant value is significantly longer than 5 characters in length. (more…)

Oracle APEX, being much smarter than you (and me, and the rest of the software development community), won’t allow you to do something crazy like exporting components, i.e. one or more pages, from one workspace, let’s call it DEVELOPMENT, to another called, for instance, TEST. (more…)

APEX JavaScript Hide/Show

Posted: September 28, 2012 in Oracle APEX
Tags: , ,
These functions will allow you to hide and show DOM objects like regions or buttons, as opposed to simply toggling their visibility, which may not collapse the empty space. Be sure to give the objects static IDs

$x_Hide('<DOM object name>');

$x_Show('<DOM object name>');