Author Archives: Andrew Fraser

oracle update select merge

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

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

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

Posted in php | Leave a comment

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

Posted in Uncategorized | Leave a comment

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

Posted in Uncategorized | Leave a comment

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

Posted in RAC | Leave a comment