Oracle External Table error for Subquery

Oracle 10g 10.2.0.4 and below (BUG 5600425 at metalink)

Oracle says that the bug is fixed in 10.2.0.4 but it is not.

So, if you are trying to access an external table using a subquery and a NOT IN clause, the query may error out due to field formatting errors. Even if you use SKIP 1 for the external table definition and your external file has a header row, the following query may return a SQL Loader error.

      DELETE FROM cfgview
        WHERE cfgviewid NOT IN (SELECT view_id
                                FROM vw_ext_acc_configview);

An alternative is to use

      DELETE FROM cfgview cv
        WHERE NOT EXISTS (SELECT NULL
                 FROM vw_ext_acc_configview vecv
                WHERE vecv.view_id = CV.cfgviewid); 

Oracle Subquery bug - Test each subquery individually

Here arises the importance of unit testing each subquery individually. Consider the scenario below:

I have this code:

 DELETE FROM cfgview
        WHERE cfgviewid NOT IN (SELECT cfgviewid
                                FROM vw_ext_acc_configview);

The inner subquery if executed alone, returns an error because the columnname cfgviewid doesn't exist.

But if you execute the full query, it returns successfully and deletes 0 rows.

So, this implies that you need to unit test each query before adding it as a subquery.

Oracle Enterprise Manager 10g Job Scheduler Configuration Problem Resolution for Solaris

Oracle Enterprise Manager 10g is a powerful tool that comes with many administrative features which make database management much more simpler.
It has an inbuilt job scheduler that lets you schedule jobs. Its not a very feature rich scheduler but still does quite a lot considering the fact that it is free with Oracle's License.

I am outlining a configuration problem that may save some time to diagnose:
If a shell script or a Java program is executed using OEM under the credentials of a user who doesnt belong to the Oracle user group on Solaris, it fails with the following error:

Shell script :

/bin/sh: cannot determine current directory
or

shell-init: could not get current directory: getcwd: cannot access parent directories: Permission denied

Java Proram :

Error occurred during initialization of VM
java.lang.Error: Properties init: Could not determine current working directory

This is due to permission lacking on the directory where the OEM agent is installed
/u01/app/oracle/product/agent10g/sysman/emd

Check the permissions on this directory.
drwxr-x--- 6 oracle dba 512 May 12 13:40 emd

Change the permissions to
drwxr-xr-x 6 oracle dba 512 May 12 13:40 emd

I believe that OEM's working directory is the above location and since the underlying user doesnt have access to this directory, hence all the programs error out.

My favorite firefox Addons

My favorite firefox plugins

Download Them All
https://addons.mozilla.org/en-US/firefox/addon/201

Users of DAP (Download Accelerator Plus) will find this quite useful. It helps download huge files in upto 10 chunks maximizing your available bandwidth usage.

ScrapBook
https://addons.mozilla.org/en-US/firefox/addon/427

Ever thought that you had the ability to just store a block of paragraph from a webpage. Well, here is the addon that lets you to copy scraps out of webpage and lets you organize them in a hierarchical structure.

Ad Block Plus
https://addons.mozilla.org/en-US/firefox/addon/1865

Want to get rid of the annoying ads filling up the web page.

FireFTP
https://addons.mozilla.org/en-US/firefox/addon/684

An integrated FTP solution. It becomes slower if there are thousands of files in the directory that you are trying to traverse.

Fox Clocks
https://addons.mozilla.org/en-US/firefox/addon/1117

Working in an office spread over several timezones. This tool can help you track times in all part of the world in the status bar of your browser.

Tab Splitter (Experimental)
https://addons.mozilla.org/en-US/firefox/addon/7327

Amazon Price Drop tracker (Experimental)
https://addons.mozilla.org/en-US/firefox/addon/7264

Oracle Transparent Gateway SQL Server Encoding issues resolution

This article for all those who are involved in migration of data from Oracle to SQL server or vice versa.
It highlights a number of encoding issues that you might be facing.


You cannot represent the left and right quotation marks in Latin 8859-1.
They are represented as ‘(â\200\230) and ’ (\200\231).
Only when you set the format to UTF-8 (Unicode Transport Format), you can see them as ( ‘ ) and ( ’ ). These symbols are not found on the Windows keyboards but they are generated by tools like Microsoft Word.

When inserted inside the database, they may get messed up.

There is another problem while inserting these characters in SQL Server database using TG:
SS uses Collation Latin1_General_CI_AS by default. And UCS-2 is the UTF8 equivalent of Oracle in SS. But by no means, they are compatible.

So, a character needs to be converted to ANSI before moving over to SS.

Another problem with UTF8 is with the German characters involving Umlauts. If that is the last character in an insert statement, then 'ORA-01756: quoted string not properly terminated' is encountered.
eg: insert into tmp_enc@dblinkToSS values ('Umlauts ä ö ü ß Ä Ö Ü ');
would result in an error.

Adding a space before the terminating quote character will result in Success.
insert into tmp_enc@dblinkToSS values ('Umlauts ä ö ü ß Ä Ö Ü ');


This is being discussed at the following thread too:

http://www.orafaq.com/forum/t/89484/0/

SQL Server create table as Syntax Oracle

SQL server syntax for creating a table from a query

select * into new_table from
(select col1, col2 from old_tab1,old_tab2 where 1=1) a


Keep in mind that the alias needs to be specified. SQL Server is not that forgiving.


I find the Oracle syntax much intuitive:
create table new_table
as
select * from ....

Make the maximum from a Skype IN account

Using Skype IN -

The Skype In number alone costs $60 per annum.
If you buy it as part of a package with Skype Pro, you will pay $36 for skype Pro + $24 for Skype In number.

Now, how to get it even cheaper.

A little planning is needed to get the maximum worth of your money with skype.

Buy a Skype Pro account for $36 for one year.
Now you are given a credit of $12 as talk time. They also call it Skype credit. This credit can be used as cash to purchase most services on skype.
Ask a friend to use his her skype account to send you paypal money of $12.

Pay him/her back by any means that you like or even using the same paypal service available with skype.

Now you have 12+12 dollars in your account. And you can buy a Skype In number with these $24.

So, one year of unlimited calling + Skype In number + more services cost you just $36 + $12 = $48.