Category Archives: Uncategorized

execute immediate quote escape oracle pl/sql

To run this via execute immediate: CREATE TABLE t2 AS SELECT * FROM t1 WHERE col1 = ‘1’ ; Either use new style quote format: EXECUTE IMMEDIATE q'[CREATE TABLE t2 AS SELECT * FROM t1 WHERE col1 = ‘1’]’ ; … Continue reading

Posted in Uncategorized | Leave a comment

Partition an existing oracle table example

I used this SQL to convert an existing non-partitioned table to be partitioned. Table was mostly queried on snapshot_id=’Current’, which had the most up to date data, but had much more historic data that was rarely queried against, all flagged … Continue reading

Posted in Uncategorized | Leave a comment

SQL to display date duration as number of years and months

Convert date duration (here customer age at time of first application) in years and months, e.g. ‘3408’ for 34 years and 8 months old: WITH customers AS ( SELECT SYSDATE AS appl_date , TO_DATE ( ’01-AUG-1980′ , ‘DD-MON-YYYY’ ) AS … Continue reading

Posted in Uncategorized | Leave a comment

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 … Continue reading

Posted in Uncategorized | 1 Comment

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: … Continue reading

Posted in Uncategorized | Leave a comment

Run Windows interactive scheduled task even if user not logged in

http://superuser.com/questions/616206/run-interactive-task-even-if-user-is-not-logged-on-windows?rq=1 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 … Continue reading

Posted in Uncategorized | Leave a comment

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 … Continue reading

Posted in Uncategorized | Leave a comment

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 … Continue reading

Posted in PL/SQL, Uncategorized | Leave a comment

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