Category: Uncategorized

Rebuild table indexes lobs in oracle

Rebuilds are beneficial in some rare circumstances such as after a mass delete operation. The below method is safer and faster than export/import. Example table: CREATE TABLE tab1 ( col1 VARCHAR2(1) , col2 CLOB ) TABLESPACE ts1 ; CREATE INDEX

Posted in Uncategorized

Oracle data transform using analytic lag row_number case

So I needed to transform this source data: Into this output format: Seems simple, but required a combination of CASE, LAG, and ROW_NUMBER – as Tom Kyte steps through in detail in this article from 2004. Here is the solution:

Posted in Uncategorized

Run Windows interactive scheduled task even if user not logged in Problem arises when a windows scheduled task needs to fire up a GUI – if there is no session logged in then it fails to run because has nowhere to display the GUI. Caveat: I haven’t tried this solution

Posted in Uncategorized

Data from MySQL to Oracle with dg4odbc database link or mysqldump

To transfer data from MySQL into Oracle 1) Best method is to use a database link. That way the MySQL table is available in the Oracle database for use in queries, joins, DML, PL/SQL. Simple guides on how to do

Posted in Uncategorized

Analytics row_number to identify first parent row in oracle pl/sql cursor loop

Let’s say you have 10m parent rows and 11m child rows and need to process them row-at-a-time in pl/sql. Two methods of doing that are below. The second method will fire the combined select just the once, picking up all

Posted in PL/SQL, Uncategorized

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:

Posted in Uncategorized

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

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

Posted in Uncategorized

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

Posted in Uncategorized

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

Posted in Uncategorized