Andrew Fraser DBA – Page 10 – Oracle and SQL Server DBA

Convert java dates to oracle sql dates (esp. odm_publicobject createdate)

Java dates are recorded in milliseconds after 01-Jan-1970 00:00:00 GMT. To convert these to oracle dates, use sql like: SELECT TO_DATE(’01-JAN-1970 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) + createdate/(1000*60*60*24) createdate , creator FROM odm_publicobject WHERE TO_DATE(’01-JAN-1970 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) + createdate/(1000*60*60*24) > sysdate – 30 ORDER BY 1 , 2 ; Note that the above does not correct for any […]

October 26, 2012
Posted in Scripts

QR Code Tag create

http://createqrcode.appspot.com/ is good for creating QR Codes. Don’t use www.qrtag.net, because they redirect the URL to point at their own website.

October 24, 2012
Posted in Uncategorized
One Comment

Atira Pure Research – List Users by last activity

List out users by last activity in Atira’s Pure Research Information System with: SELECT a.username , u.name_first_name forename , u.name_last_name surname , TRUNC ( MAX ( a.entry_date ) ) last_audit_entry FROM audit_entry a , users u WHERE a.username = u.username(+) GROUP BY a.username , u.name_first_name , u.name_last_name ORDER BY 4 , 3 , 2 , […]

October 24, 2012
Posted in Uncategorized

Oracle regexp_replace remove non alpha ASCII

Replace everything that isn’t a standard ASCII alpha character with a space: SELECT REGEXP_REPLACE ( ‘xxXX € Ááé 123 %; test text æ¸¬è© ¦ “xmxmx” number²’ , ‘[^a-zA-Z]’ , ‘ ‘ ) FROM DUAL h/t Kok Yan Lo and jadarnel27 Update : an alternative method from Paul Bradley: select ‘ÄÊÍÕØÓÑ’ str, substr(upper(utl_raw.cast_to_varchar2((nlssort(‘ÄÊÍÕØÓÑ’, ‘nls_sort=binary_ai’)))),1,10) str2 from […]

October 2, 2012
Posted in Scripts

Global search and replace with sed and find

sed -e ‘s/old1/new1/g s/old2/new2/g s/old3/new3/g’ –i $(find / -name ‘*.php’ -o -name ‘*.include’ -o -name ‘*.inc’ -o -name ‘*.class’ -o -name ‘ht.access’) ‘locate’ would be an alternative to ‘find’.

July 12, 2012
Posted in Linux, Scripts
One Comment

Display four digit year as tax financial academic year

This SQL works for all years from AD 9 onwards: SELECT ‘2011’ , ‘2011’ || ‘/’ || SUBSTR ( 1 + TO_NUMBER ( ‘2011’ ) , -2 ) formatted FROM DUAL ; Results: ‘201 Formatted —- ——— 2011 2011/12

June 29, 2012
Posted in Uncategorized

Oracle User Group Scotland Conference 2012

My notes from the 2012 Oracle User Group Scotland Conference:

June 14, 2012
Posted in Uncategorized

Database Password Changes from users web page

This is a Pl/sql wrapper around “alter user” to allow front end interfaces (Servicedesk, Javascript or PHP web pages, etc.) to safely change passwords. CREATE OR REPLACE PROCEDURE sys.php_reset_passwords /*************************** || Name : sys.php_reset_passwords || Author : Andrew Fraser || Date : 05-Apr-2012 || Purpose : Allow users to reset their database passwords from a […]

April 5, 2012
Posted in Scripts, Security

Documentation and Comments

Steve Feuerstein here: There are two forms of code documentation: external and internal. External documentation is descriptive information about a program which is written and stored separately from the program itself. Internal documentation, also known as inline documentation or comments, is placed within the program itself, either at the program level or the statement level.

March 6, 2012
Posted in Uncategorized

Change many oracle database parameters using a temporary pfile/init.ora from spfile

Spfiles are better than pfiles (init.ora’s) for most purposes, since they allow parameters to be changed with “alter system set parameter” SQL commands. However if you want to change a whole load of parameters in one go, it is easier to temporarily switch back to the old pfile/init.ora method to allow you to edit all […]

February 17, 2012
Posted in Spfile