test upload video

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Links

• Subscribe to this feed

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: sql, tip

Links

• Subscribe to this feed

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

• Subscribe to this feed

MySql may soon have Oracle's external table

It is called mysql-filesystem-engine.

Links

• Subscribe to this feed

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

• Subscribe to this feed

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

• Subscribe to this feed

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:oracle, sql

Blogged with Flock

Links

• Subscribe to this feed