ISNUMBER

If you have a text field that you want to convert to a number that has a non numeric value in it, then the conversion will fail with an invalid number error. What you need is a function that determines if the string is a number, so that you can decide whether to convert or not. Sadly Oracle SQL doesn't have one.

All is not lost however, with a cunning use of translate you can suss out if a string is a number or not.

Let's say you wanted to convert JOBCODE in JOB into a number when it is numeric, and zero when it is not. The following SQL will do this for you quite nicely.

select distinct JOBCODE,
                ( case
                    when translate(JOBCODE, '_0123456789', '_') is null then to_number(JOBCODE)
                    else                                                     0
                 end )
  from PS_JOB

Rounding

If you want to round up or down in Oracle use one of the following: -

select 3.14159,
       ROUND(3.14159,2)       as ROUNDED,
       CEIL(3.14159*100)/100  as ROUNDED_UP,
       FLOOR(3.14159*100)/100 as ROUNDED_DOWN
 from DUAL

Replacing 2 in the first example with the number of decimal places you want to round to, and 100 in the other examples with 10 to the power of the  number of decimal places you want to round up or down to.

Get Table Details

Sometimes when you're documenting you need to get a list of fields, fieldnames, types and their length. This little piece of SQL marries an Oracle system table and two PeopleSoft tables to furnish this information.

select ATC.COLUMN_ID,
       ATC.COLUMN_NAME,
       LAB.LONGNAME,
       CASE
         WHEN FLD.FIELDTYPE = 0 THEN 'Character'
         WHEN FLD.FIELDTYPE = 1 THEN 'Long Character'
         WHEN FLD.FIELDTYPE = 2 THEN 'Number'
         WHEN FLD.FIELDTYPE = 3 THEN 'Signed Number'
         WHEN FLD.FIELDTYPE = 4 THEN 'Date'
         WHEN FLD.FIELDTYPE = 5 THEN 'Time'
         WHEN FLD.FIELDTYPE = 6 THEN 'Date Time'
         WHEN FLD.FIELDTYPE = 8 THEN 'Image or Attachment'
         WHEN FLD.FIELDTYPE = 9 THEN 'ImageReference'
         ELSE                        'Unknown'
       END as TYPE,
       FLD.LENGTH
  from ALL_TAB_COLUMNS ATC,
       PSDBFLDLABL     LAB,
       PSDBFIELD       FLD
 where ATC.TABLE_NAME    = 'PS_JOB'
   and ATC.COLUMN_NAME   = LAB.FIELDNAME
   and LAB.DEFAULT_LABEL = 1
   and LAB.FIELDNAME     = FLD.FIELDNAME
 order by ATC.COLUMN_ID Asc

Getting Oracle Table Information

The following SQL gets useful information about tables in an Oracle Database, such as the tablespace used.


select TABLE_NAME,
OWNER,
INITIAL_EXTENT,
PCT_USED,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
TABLESPACE_NAME
from dba_tables
where TABLE_NAME in ( 'PS_TY_TL_RAP_H_HST',
'PS_TY_TL_RAP_T_HST',
'PS_TY_TL_RPT_T_HST',
'PS_TY_TL_PAY_T_HST',
'PS_TY_SCH_AD_D_HST',
'PS_TY_SCH_MN_S_HST',
'PS_TY_TL_TR_CO_HST' )

Fun with Time and Labor Dates

There's a useful little table in PeopleSoft Time and Labor called PS_TL_DATES_TBL. This holds a variety of date information, such as day number, julian date , day of week and month, that can save a bit of coding to derive these values yourself. The following example uses this table to get the start and end dates of the previous, current and next years, months and weeks.

select DAT.THE_DATE,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as START_OF_PREVIOUS_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as END_OF_PREVIOUS_YEAR,
       TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as START_OF_CURRENT_YEAR,
       TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as END_OF_CURRENT_YEAR,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as START_OF_NEXT_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as END_OF_NEXT_YEAR,
       ADD_MONTHS((TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1),-1)                    as START_OF_PREVIOUS_MONTH,
       DAT.THE_DATE - DAT.DAYOFMONTH                                                as END_OF_PREVIOUS_MONTH,
       TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1                                     as START_OF_CURRENT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),1)                                as END_OF_CURRENT_MONTH,
       ADD_MONTHS(TRUNC((DAT.THE_DATE - DAT.DAYOFMONTH) + 1),1)                     as START_OF_NEXT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),2)                                as END_OF_NEXT_MONTH,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 13
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 8
                         when DAT.DAYOFWEEK = 4 then 9
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 11
                         when DAT.DAYOFWEEK = 7 then 12
                       end                                                          as START_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 1
                         when DAT.DAYOFWEEK = 3 then 2
                         when DAT.DAYOFWEEK = 4 then 3
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 5
                         when DAT.DAYOFWEEK = 7 then 6
                       end                                                          as END_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 6
                         when DAT.DAYOFWEEK = 2 then 0
                         when DAT.DAYOFWEEK = 3 then 1
                         when DAT.DAYOFWEEK = 4 then 2
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 4
                         when DAT.DAYOFWEEK = 7 then 5
                       end                                                          as START_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 0
                         when DAT.DAYOFWEEK = 2 then 6
                         when DAT.DAYOFWEEK = 3 then 5
                         when DAT.DAYOFWEEK = 4 then 4
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 2
                         when DAT.DAYOFWEEK = 7 then 1
                       end                                                          as END_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 1
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 6
                         when DAT.DAYOFWEEK = 4 then 5
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 3
                         when DAT.DAYOFWEEK = 7 then 2
                       end                                                          as START_OF_NEXT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 13
                         when DAT.DAYOFWEEK = 3 then 12
                         when DAT.DAYOFWEEK = 4 then 11
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 9
                         when DAT.DAYOFWEEK = 7 then 8
                       end                                                          as END_OF_NEXT_WEEK
  from PS_TL_DATES_TBL DAT
 where DAT.THE_DATE = TRUNC(SYSDATE)
 order by DAT.THE_DATE desc

What day is it son?

If you need to know what day of the week it is you could get the day number then go on the principle that 1 is a sunday, 2 is a Monday etc, to work it out. The problem with this however, is that this number can vary depending on how the Oracle environment is configured, which makes this unreliable. A better way of finding out the day is to use the 'DAY' parameter of TO_CHAR. This returns the day as MONDAY, TUESDAY etc. The only thing to watch here is that the returned value has trailing spaces, so if you're comparing it then remember to trim it first. For example: -

select DAT.THE_DATE, to_char( DAT.THE_DATE, 'DAY' )
  from PS_TL_DATES_TBL DAT
where trim(to_char( DAT.THE_DATE, 'DAY' )) in ('SATURDAY','SUNDAY')
  and DAT.THE_DATE between '01-DEC-2011' and '31-DEC-2011'
order by DAT.THE_DATE Asc

Enjoy.

Analyze This

In the good old days you analyzed a table using a statement along the lines of: -

ANALYZE TABLE tablename COMPUTE STATISTICS;

With the advent of Oracle 8i the DBMS_STATS utility was introduced to gather object statistics. This is Oracles preffered method of gathering the information.

Using it the equivalent of the above analyze statement would be: -

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE);

This should be executed after the table you need to analyze has been populated.

To cut back on the time it takes to execute, you can limit the number of rows it uses in its stats gathering by adding estimate_percent => nn (where nn is a number between 1 and 100) this will only use the given percentage of the total rows in the table when performing its anaysis.

e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE, estimate_percent => 20);

Will only use 20% of the tables rows. The higher the percentage, the better the analysis, but the longer it will take. When used Oracle randomly selects the rows it uses from the table to make them more representative.

N.B. This statement doesn't work from Oracle Developer (I've not tried it from Toad). As such execute it from SQL Plus instead.

Listing a tables Indexes

select index_name
from dba_indexes
where table_name=<table_name>

Query Tree Security SQL

Query Trees are assigned to a permission list in PeopleSoft under the Query tab of: -

PeopleTools > Security > Permissions and Roles > Permission Lists

Working out who's got access to what query trees can be a bit of a nightmare manually searching through each permission list in turn. To that end, the following piece of SQL is quite useful in establishing which roles have access to a particular query tree or trees.

select distinct ROL.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME in (Query Tree List)

To see which query trees a particular user has access to, use: -

select distinct USR.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLEUSER_VW USR,
PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where USR.OPRID = 'Operator ID'
and USR.ROLENAME = ROL.ROLENAME
and ROL.CLASSID = SAG.CLASSID

To see which users have access to a particular query tree: -

select distinct ROL.ROLENAME,
OPR.OPRDEFNDESC
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG,
PSROLEUSER_VW USR,
PSOPRDEFN OPR
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME = 'QRY_TMUK_ALL'
and ROL.ROLENAME = USR.ROLENAME
and USR.OPRID = OPR.OPRID
order by ROLENAME, OPR.OPRDEFNDESC

Spooling Your Output

When writing SQL scripts in a production environment it's always a good idea to spool the fruits of your labor to a log file when it is run. It also gives you the opportunity to wear your "I Told You So" teeshirt when someone denies you've run something.

Selecting the database name from sys.v_$database also gives you the warm feeling that you've run your deletion script in a development environment and not accidentally against production when you review your log file. Alternatively it could give you a more localised warm feeling down your trouser leg if you find out it's the other way round !

The set pagesize 0 command formats your SQL output better so you get one continuous page rather than giving you reams of headers.

The set timing on statement gives you timings for the SQL being run. This gives future runners of the script an idea of how long the SQL takes, which can bu useful, especially when running it in production.

whenever SQLERROR exit failure;

spool "/filename"

set echo on;
set feedback on;
set verify on;
set pagesize 0;
set termout on;
set timing on;

select name from sys.v_$database;

/************************/
/* Insert your SQL here */
/************************/

spool off;