Category Archives: Uncategorized

PL/SQL bulk collect union

PL/SQL bulk collect will work with UNION ALL when written with an inline view: DECLARE TYPE l_type_assoc IS TABLE OF VARCHAR2(14) INDEX BY BINARY_INTEGER ; l_big l_type_assoc ; BEGIN WITH q AS ( SELECT ename AS name FROM emp UNION … Continue reading

Posted in Uncategorized | Leave a comment

Extended Hexadecimal in Oracle

Standard decimal > hexadecimal is done with: select to_char ( 10 , ‘x’ ) from dual ; But I wanted a kind of extended hexadecimal that would keep single letters going up beyond 15/f, so would return ‘g ‘for decimal … Continue reading

Posted in Uncategorized | Leave a comment

Denormalise listagg children on single row comma separated

You can denormalise data using the listagg function, so to display users with their roles on a single line comma separated: SELECT grantee , LISTAGG ( granted_role , ‘ , ‘ ) WITHIN GROUP ( ORDER BY granted_role ) AS … Continue reading

Posted in Uncategorized | Leave a comment

Oracle remove duplicates parent child with listagg

De-duplicating parent – child table sets of data here using the listagg function to merge all the child rows into a single denormalised column. set serverout on BEGIN FOR d1 IN ( WITH denorm AS ( SELECT parent.aos_code , parent.pre_group_id … Continue reading

Posted in Uncategorized | Leave a comment

Copy mirror files with robocopy rsync lftp

The best utilities to use when copying/mirroring/backing up files are: robocopy – Windows rsync – Linux lftp – Linux without setting up password-less ssh Scripts can supply a password to sftp using lftp like this: lftp -u user:password sftp://server.domain.com << … Continue reading

Posted in Uncategorized | Leave a comment

bash check count processes

Simple bash command to check if processes are running: if [ $(pgrep -f pmon) ] then echo One or more databases running else echo No databases running fi Syntax gets more complex if you want to check for specific counts … Continue reading

Posted in Uncategorized | Leave a comment

Unlock oracle accounts without alter user system privilege

I wanted to give non-DBA users (Servicedesk, Operations) the ability to unlock database user accounts. The ‘alter user’ system privilege allows that, but it also gives out a lot more privileges (change password, kill session, alter DBA accounts) than I … Continue reading

Posted in Uncategorized | Leave a comment

Oracle 12c new features

Oracle 12c was released yesterday (25-Jun-2013). I’m a little bit underwhelmed by a first look at the new features guide – if that’s all there is, Oracle is going to continue to struggle against Microsoft SQL Server. Main change with … Continue reading

Posted in Uncategorized | Leave a comment

Drop and create database links at materialized view refresh time

Materialized views work ok even when their dependent database link is dropped from under them. This can be used as a security improvement if you don’t want database links lying around during the day outside the refresh window. Script used … Continue reading

Posted in Uncategorized | Leave a comment

Oracle single quote marks for excel text datatype

Add a quote in front of a field (useful for excel to mark cell as text datatype) with any of: SELECT CHR(39) || address1 FROM customers ; SELECT q’[']‘ || address1 FROM customers ; SELECT ”” || address1 FROM customers … Continue reading

Posted in Uncategorized | Leave a comment