Month: April 2015

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’]’ ;

Posted in Uncategorized

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

Posted in Uncategorized

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

Posted in Uncategorized