-
Hide sqlldr Oracle Password in PS Command
Our internal audit team has raised an issue on some scripts exposing database user, password and tns. You see, all you need is an access to unix shell and run ps command and you’ll be able to get the user id and password of the database. On the otherhand, there’s not a lot of people…
-
UTF_FILE and Directories
In case you are maintaining a pl/sql program developed by someone else and that program writes into a file, then basically that file is written to a folder/directory. And since the directories are saved in codes, you have to find the meaning (or location) of those codes. The code below will help you display the…
-
Oracle Power Function
There are some pl/sql functions that you rarely use and we always forget the syntax.And here’s another example — the power function. You know, that’s the n to the power of x (n^x). Again, I feel a bit ashamed for forgetting this one. Here’s the syntax.
-
Oracle MERGE command
Oracle MERGE command was added in 9i but I only learned about it lately. This command is sometimes referred to as UPSERT because in one statement, you can both update and insert records into a target. Based on the matching condition, MERGE can update existing records if a match is found or insert new record…
-
Convert Rows to One Column
Converting rows to column is a breeze if you are using Microsoft Access or reporting tools like Oracle Reports Builder. However, there are times that you’ll also need it for something else. I mean like displaying rows to one column in a SQL*Plus report. So how leh? (imagine that saying with a Singaporean accent)
-
Date Functions in MySQL
Here’s few of the usual equivalent Oracle data functions in MySQL. DATE ADD (SYSDATE +/-1) SELECT DATE_ADD(SYSDATE(),INTERVAL 1 DAY); SELECT DATE_ADD(SYSDATE(),INTERVAL -10 DAY);
-
Oracle NVL Equivalent in MySQL
Here’s the MySQL equivalent of Oracle’s NVL and MSSQL/Sybase’s ISNULL functions. Syntax IFNULL(expr1,expr2) Usage If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. Take note that IFNULL() returns a numeric or string value, depending on the context in which it is used. I haven’t used it for other data types yet but…
-
MySQL SEQUENCE Equivalent
Most Oracle guys (and I heard PostgreSQL, too) will be missing sequence in MySQL. The nearest equivalent that MySQL has for this feature is the AUTO INCREMENT field. However, auto increment is sometimes not sufficient and time will come that you will be missing it. Case in point is the alpha-numeric auto-generated code. In Oracle,…
-
Oracle WITH Clause (Subquery Refactoring)
WITH clause was introduced in Oracle 9i to provide powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries. WITH Clause Syntax WITH alias_name — alias for the aggregate_query AS (aggregate_query_here) SELECT… — Beginning of the query main body It should be noted that multiple aggregate queries…
-
Oracle CASE WHEN Statement
Another oracle sql statement that you may need in order to simplify and optimize your code is the use of CASE statement. Here’s how it is being used. Syntax #1 CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 … WHEN condition_n THEN result_n ELSE result END Example SELECT last_name, commission_pct, (CASE…
Recent Comments