Author Archives: Andrew Fraser

oracle substrb function to chop a string down to its first 4000 bytes

The substrb 1 , 4000 function will chop a string down to its first 4000 bytes, e.g.: SELECT SUBSTR(’1€3′,2,1) , SUBSTRB(’1€3′,1,3) , LENGTH(’1€3′) , LENGTHB(’1€3′) FROM dual ; Needed if using the (non default) option of ‘char’ in column lengths: … Continue reading

Posted in Uncategorized | Leave a comment

Format sql files with sed: tab to space, pad commas with spaces, uppercase match

Shell script to make more readable all sql files in a directory – pads commas and brackets with whitespace, converts tabs to spaces, makes SQL keywords uppercase. # FUNCTIONS pad () { sed -i “s/\([^ ]\)$1/\1 $1/g” *sql sed -i … Continue reading

Posted in Uncategorized | Leave a comment

Laravel Symfony CodeIgniter

Laravel is the new CodeIgniter, a coding framework on top of PHP for simplified/standardised syntax. Complex PHP applications can become un-supportable without a coding framework like those in place. To install and demo Laravel on WAMP: (1) follow the steps … Continue reading

Posted in php | Leave a comment

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

srvctl start listener -n nodename

Preferred method to stop and start oracle listener in RAC enivronment: srvctl config listener -n nodename srvctl stop listener -n nodename srvctl start listener -n nodename

Posted in RAC | 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

Create output XML data files from oracle with dbms_xmlgen

The below code will generate XML format output, just plug in any SQL and it will work without further development. This is based on three articles by other authors: Tim Hall – but Tim’s code has a bug that stops … Continue reading

Posted in XML | Leave a comment

Replace all tnsnames.ora files with a single master file using TNS_ADMIN

Maintaining large numbers of client tnsnames.ora files is a pain.  Oracle Internet Directory is the recommended solution to this, but it is not an easy exercise to set up or migrate to. An easier alternative is to set TNS_ADMIN in all … Continue reading

Posted in SQL*Net, Windows | Leave a comment