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 to that are: Shishir Tekade http://www.shishirtekade.com/2014/01/connect-oracle-to-mysql-server-through.html Pythian http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/ My Oracle Support 1320645.1 ‘Detailed Overview of […]

Read More Data from MySQL to Oracle with dg4odbc database link or mysqldump
November 18, 2014
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 11m rows in one go. The first method will fire the child select 10m times […]

Read More Analytics row_number to identify first parent row in oracle pl/sql cursor loop
October 22, 2014
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: CREATE TABLE mytable ( col1 VARCHAR2(4000 CHAR) ) ; You can change the default setting […]

Read More oracle substrb function to chop a string down to its first 4000 bytes
August 25, 2014
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 “s/$1\([^ ]\)/$1 \1/g” *sql } upper () { sed -i “s/\(\\)/\U\1/g” *sql } # MAIN […]

Read More Format sql files with sed: tab to space, pad commas with spaces, uppercase match
June 2, 2014
Posted in Uncategorized

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 in http://www.darwinbiler.com/how-to-install-laravel-on-wamp-for-beginners/ (note, uses a different WAMP php.ini file from normal). (2) File c:\wamp\www\laraveltest\public\.htaccess then […]

Read More Laravel Symfony CodeIgniter
May 2, 2014
Posted in php

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 ALL SELECT dname AS name FROM dept ) SELECT name BULK COLLECT INTO l_big FROM […]

Read More PL/SQL bulk collect union
April 17, 2014
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 16 instead of returning hexadecimal ’10’. Why did I want that? I needed to squeeze […]

Read More Extended Hexadecimal in Oracle
March 7, 2014
Posted in Uncategorized

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

Read More srvctl start listener -n nodename
March 6, 2014
Posted in RAC

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 granted_roles FROM dba_role_privs GROUP BY grantee ORDER BY 1 ; H/t Adrian Billington.

Read More Denormalise listagg children on single row comma separated
February 24, 2014
Posted in Uncategorized

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 , LISTAGG ( child.aos_code || child.stud_pass ) WITHIN GROUP ( ORDER BY child.aos_code || child.stud_pass […]

Read More Oracle remove duplicates parent child with listagg
February 10, 2014
Posted in Uncategorized