Regexp_replace

*** Please note I have stumbled across a better, i think most correct way, of accomplishing the below, but using REGEXP_SUBSTR here.

Just a reminder when using PL/SQL regexp_replace -

Your search string can be used as a 'filter' to get exactly what you want out of the string and nothing more.

Consider the following: your string is the following: BUTTE 211 - Color (BSS) or BUTTE 211 - B&W (BSS) or in some cases BUTTE 211 - random words.

You need to extract *only* the words between 'BUTTE 211 - ' and the parenthesized items, if they exist '(BSS)'. Sometimes, on rare occasions, words exist other than Color & B&W in the string between these two expressions.

You could write multiple regexp functions nested, or even do an case when instr('Color') > 0 but

the following does the same thing, IN ONE STATEMENT/Function call:

regexp_replace(q.queuename, '(.* - | \(.*\))', '\2')

Basically what the above expression is doing is telling the regexp_replace function to get either .* - or (.*) into backreference \1. Since we don't want those characters and specifically anything but those expressions, we ask for \2...anything *not* those strings.

Viola: you get your answer, Color and B&W (and in some cases, 'random word')

generate time/date table Oracle

/* BEST way to generate date list as 1 column of dates between inputs */
WITH DATES AS
 (SELECT TO_DATE(NVL('&begin_dt'
                    ,SYSDATE - 365)
                ,'DD-MON-YY') BEGIN_DT
        ,TO_DATE(NVL('&end_dt'
                    ,SYSDATE)
                ,'DD-MON-YY') END_DT
  FROM DUAL)

SELECT BEGIN_DT + (LEVEL - 1) DT FROM DATES CONNECT BY LEVEL <= (END_DT + 1) - BEGIN_DT


/*two other ways: */
select to_char(x.lvl, 'HH24') || ':00:00'
from ( SELECT sysdate - (level/24) lvl
FROM dual
CONNECT BY LEVEL <= 24 ) x
order by 1

select to_char(x.lvl, 'YYYY-MM-DD')
from ( SELECT sysdate - level lvl
FROM dual
CONNECT BY LEVEL <= 30) x

/*Create a time dim for every second of the day in a few easy steps: */

create table DWPRD.Time_Dim as

WITH timegen AS ((SELECT to_char(trunc(sysdate) + (level/86400),'HH24:MI:SS') lvl_sort, trunc(sysdate) + (level/86400) lvl FROM dual CONNECT BY LEVEL <= 86400) order by 1)

SELECT ROWNUM TIME_KEY, b.* FROM (
SELECT to_char(lvl, 'HH12:MI:SS AM') TIME_STANDARD, to_char(lvl, 'HH24:MI:SS') TIME_MILITARY, to_char(lvl, 'HH12') HOUR_STANDARD, to_char(lvl, 'HH24') HOUR_MILITARY, to_char(lvl, 'MI') MINUTE, to_char(lvl, 'SS') SECOND,
to_char(lvl,'AM') as AM_PM,
case WHEN to_char(lvl, 'HH24') BETWEEN '08' AND '16' THEN 'Y' ELSE 'N' END AS IN_GENERAL_BUSINESS_HOURS_FLAG

FROM TIMEgen ) b

Oracle seconds to hours minutes seconds

This code converts seconds to hours, minutes, and seconds.

select to_char(trunc(/60/60),'09') ||
to_char(trunc(mod(,3600)/60),'09') ||
to_char(mod(mod(,3600),60),'09')
from dual;
I use SQL*Plus to show how to use it:

SQL> variable n number

SQL> exec :n := 3000;

PL/SQL procedure successfully completed.

SQL> select to_char(trunc(:n/60/60),'09') ||
2 to_char(trunc(mod(:n,3600)/60),'09') ||
3 to_char(mod(mod(:n,3600),60),'09')
4 from dual;

TO_CHAR(T
---------
00 50 00

SQL> exec :n := 45

PL/SQL procedure successfully completed.

SQL> /

TO_CHAR(T
---------
00 00 45

SQL> exec :n := 3770;

PL/SQL procedure successfully completed.

SQL> /

TO_CHAR(T
---------
01 02 50

Install SQL Server 2005 Business Intelligence Tools

Getting an error about SQL Native Client?

Uninstall existing SQL Native Clients from add/remove programs and attempt to reinstall.

SQL Server 2000 and SQL Server Manager Nightmare

Never install SQL Server 2000 after installing SQL Server Manager (in this case 2005). Don't.

Just don't.

Instant Client and Visual Studio

1) Download Instant Client
2) Unzip to C:/Oracle
3) Set user variable "path" to C:\Oracle <- notice no ; is used here if setting user variables

-- The last portion here wasnt necessary with my latest Visual Studio Install.
4) Get the latest TNS_Names from our network tools server \tools\oracle\network\admin\tnsnames.ora
5) set TNS_Names

ORA 12705: Cannot access NLS data files or invalid environment specified

After some GPO updates over the network today, Holly couldnt connect to RDS with Golden. Evidently an Oracle Registry value was 'reset' - I removed it once.

For ORA-12705 errors caused by invalid NLS_LANG settings, you need to verify that it is "unset" at the system-level:

Windows - The NLS_LANG must be unset in the Windows registry (re-named is best). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it.

Oracle Long Nightmare

Today I spent two hours converting an Oracle Long datatype in one of our older tables to CLOB.

Only to find out shortly thereafter that .asp can't sql using CLOB.

Consequently, CLOB was quickly converted to Varchar2.