Oracle Timezone Conversions – GMT to localtime (and back)
Posted by watashii | Filed under Oracle, Programming
GMT to Local Time
Convert a datetime value (which has no timezone info, but assumed as GMT/UTC) to a local datetime (based on timezone parameter as set by SESSIONTIMEZONE, eg ‘+11:00′):
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'GMT')
AT LOCAL) AS DATE) "Local Time"
FROM DUAL;
Local Time
--------------------------------------------------
1/12/1999 10:00:00 PMTags: gmt, localtime, oracle, timezone, utc
BusinessObjects Universe Designer – Sysdate Value Object
Posted by watashii | Filed under BusinessObjects, Oracle, Software
Creating SYSDATE value objects in a Universe (via Oracle connection) can simply be done as shown above. I have simply created a single-value dimension object, which can be used anywhere within a query (result/condition/sort). Having sysdate values allows the flexibility to create dynamic time-based reports. For example you can schedule a weekly report to extract data from the past 7 days starting from the current time the report was run.
Note, when parsing the object against the database (Oracle), an error will appear. ORA-00903: Invalid table name.
You can just ignore this error and press ok.
Tags: BusinessObjects, oracle, sysdate, universe
Oracle Index Null Values
Posted by watashii | Filed under Oracle, Programming
When creating Oracle indexes, Oracle ignores the NULL values. For example, the following index was created over “emp_name” column.
create index emp_name_idx on employees (emp_name);
Querying for a NULL value on the indexed column would yield a full table scan.
select emp_name from employees where emp_name is NULL;
So how can we make Oracle perform a fast indexed search?
The solution is to index NULL values with a function-based index. The following example replaces all the NULL values with a string named ‘null’ (note, this can be any arbitrary string). This allows the NULL to be included as if it was a real value.
create index emp_name_idx on employees (nvl(emp_name,'null'));
Tags: index, oracle, performance
Oracle SQL Loader – Importing CSV files to a table
Posted by watashii | Filed under Oracle, Programming
This post shows how to easily insert data from a CSV file into a database table, using the Oracle SQL Loader tool. We will only concentrate on importing CSV formatted data to the Oracle table with a 1-to-1 column mapping, otherwise I will need to write a book. Users can easily reuse my code, and just customise the columns to their needs.
Read the rest of this entry »
Tags: control file, import csv, oracle, sql loader
PL/SQL – Pipelined Table Function
Posted by watashii | Filed under Oracle, Programming
Normally when writing PL/SQL functions, they return a single value result. But what if you want to return a collection type instead? The easiest way to do this is via a pipelined function, with the PIPELINED keyword, in which rows are returned by the function iteratively. It can be created at the schema level, or inside a package. I prefer the latter since its more portable, and easier to manage.
Inside the function, you return individual elements of the collection type, instead of returning the entire collection type when function ends. This also has performance benefits, depending on your application.
Tags: function, oracle, performance, pipelined
