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: SELECT q5.event_category , q5.num , MIN ( q5.week ) AS start_week , MAX ( q5.week […]
Read More Oracle data transform using analytic lag row_number caseIf you want to update one table based on the values in another table, use ‘UPDATE = (SELECT) WHERE (SELECT)’ or ‘MERGE WHERE’, as in examples (3) and (3a) below. Sample data: DROP TABLE a ; CREATE TABLE a ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ; INSERT INTO a ( id […]
Read More oracle update select mergehttp://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 myself yet, don’t know if there are any unpleasant side effects from it or not.
Read More Run Windows interactive scheduled task even if user not logged inTo 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 mysqldumpLet’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 loopThe 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 bytesShell 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 matchLaravel 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 CodeIgniterPL/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 unionStandard 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