Daily Oracle

short Oracle take-away tips that you can put to use in under an hour.

Saturday, June 14, 2008

Ensuring that a sql query returns all or nothing

I have a little project which automatically returns the result from a sql query to an Excel worksheet.

I run this job recently and the user complained that the Excel output was unusual.

I did some investigation and found that the query did not run to completion. It bombed out midway because there was a division by zero error.

Because the job was automatic and it was run in Excel, I did not see the error message.

That got me thinking - if the output was empty, I would realize immediately that the something was wrong. When it returned an impartial set, only a user who analyzed can tell whether it was wrong or not.

So, I wanted the query to return all or nothing.

And that is quite easily done, just add an order by clause at the end of the query.

The order by clause will retrieve all the rows and then sort them. If it could not retrieve all the rows because some of the rows has, for example, a division by zero error, nothing will return. The query will then return all or nothing at all.

Technorati tags: ,

Links

Wednesday, May 23, 2007

Using ifile to centralise tnsnames.ora entries for multiple oracle homes

Tom Kyte has a great tip for centralising tns entries in one tnsnames.ora for multiple oracle homes.

The summary - maintain all tns entries in 1 tnsnames.ora.

Let's say this file is at:

c:/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

In all the other tnsnames.ora, delete every thing else and type in this:

ifile=c:/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

Take note that the ifile parameter is not documented, according to Tom.

The other option is to set the TNS_ADMIN environment variable but this can cause a lot of  confusion.

Imagine a situation that you did not know the TNS_ADMIN variable is set, you will be looking at all the tnsnames.ora file in all the oracle homes and wondering why all the homes use one tnsnames.ora.

The ifile option makes this explicit in the place where everyone will usually look.

Links

Wednesday, May 02, 2007

MySql may soon have Oracle's external table

It is called mysql-filesystem-engine.

Links

Monday, October 09, 2006

from sqlldr (sql loader) to external table

Prior to Oracle 9i, to import data from a text file, you will have to run sqlldr (sql loader) to load the data into a table.

Starting from Oracel 9i, you can use external table to read text file data from within sqlplus and you can use the sqlloader control file to help you.

Just run something like this:

sqlldr scott/tiger control=mycontrol.ctl EXTERNAL_TABLE=GENERATE_ONLY.

This will create a log file with :

1. CREATE DIRECTORY statement

2. CREATE EXTERNAL TABLE statement

3. sample INSERT statement

4. sample DROP TABLE statement

It's a quick way to move from sql loader to external table.

External tables have these benefits:

1. you do not have to import the data into a table to read it. You can just read from the text file as if it were a table

2. since external table can be used with SELECT, you can write a load program with either SQL or PL/SQL. Prior to Oracle 9i, it was often asked whether it was possible to invoke sql loader from within PL/SQL. With external table, you do not have to.

Links

Monday, July 10, 2006

find gap in sequence with analytical function

SQL> WITH aquery AS
2 (SELECT measurement_id after_gap,
3 LAG(measurement_id,1,0) OVER (ORDER BY measurement_id) before_gap
4 FROM sensor_data)
5 SELECT
6 before_gap, after_gap
7 FROM
8 aquery
9 WHERE
10 before_gap != 0
11 AND
12 after_gap - before_gap > 1
13 ORDER BY
14 before_gap;
BEFORE_GAP AFTER_GAP
---------- ----------
2 4
5 7

http://techrepublic.com.com/5110-9592-6088140.html

Links

Monday, June 19, 2006

script to delete duplicates in a table

DELETE FROM emp
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROWID,
ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
FROM emp2)
WHERE rn > 1);

source:techrepublic

This is another script to delete duplicates in a table, using analytical functions.

The table is emp and the criteria for whether the row is duplicate is the empno.

If your version of Oracle does not have analytical functions, you can use this instead

DELETE FROM emp e1
WHERE ROWID !=
(SELECT min(rowid)
FROM emp e2
where e2.empno = e1.empno);
From the tests I ran, the second script is faster.

technorati tags:,

Blogged with Flock

Links

Tuesday, March 28, 2006

using function and constant in sql loader

After a long break from Oracle work, I recently had to load some data from Excel into an Oracle database.

I googled for a ready made solution but the Oracle expert says the easiest is to convert the Excel file into a csv and use sql loader to load the file.

In my solution, I wanted to be able to load data files that :
1. contain ','
2. contain date columns in this format '31-12-06' (31st Dec 06)

To fulfil 1., my control file cannot use the standard fields terminated by ',' quoted in most examples.
Instead my fields will be terminated by tab.
There were no examples on the internet that I could find.
I experimented with '\t' and it worked.

Secondly, I wanted sql loader to convert '31-12-06' to 31 Dec 06 before loading, otherwise I get the invalid date error.
I needed to use a function to process the field first.

GL_DATE "to_date(:gl_date, 'dd-mm-yy')" - worked

Thirdly, I populated a column with a constant value (C)

STATEMENT_BASIS constant 'C'


My eventual control fileĀ  is like this:

load data
infile 'C:/1/sql/t.txt'
into table MY_TABLE
append
fields terminated by '\t'
trailing nullcols
(CSL_UID,
COMPANY,
RC_UID,
REPORT_CENTER_NAME,
BILL_CODE,
GROSS_WORKING_INTEREST,
PARTY_UID,
PARTY_NAME,
AMOUNT,
GL_DATE "to_date(:gl_date, 'dd-mm-yy')",
MAJOR_ACCOUNT,
LINK_INFO,
STATEMENT_BASIS constant 'C')

SQL Loader is a very useful and powerful tool.


Links