Category Archives: Oracle

Oracle SQL PL/SQL SQL+ tips and tricks.

ODBC TNSNAMES Dropdown Gobbledygook and windings

On Windows 7 ODBC Administrator, if you add a new Oracle connection in System DSN, and the TNS Selector shows a list of gobbledygook or Windings, then check your %TNS_ADMIN% System Environment Variable.

Chances are it is not pointing to where the Network/Admin directory is (ie where your tnsname.ora is located).

Simply update with the correct path and you’re sorted.

(right click computer, Properties->Advanced System Settings->Environment Variables->System Variables)

Debugging Views that don’t compile

If you’re running SQLDeveloper and you compile a View, it will show a little red X next to it if there are issues.

However it won’t actually tell you what the problem is!

Go into sqlplus and enter this command:

 show errors view my_schema.kc_tradedetails

You should then see something like this

 Errors for VIEW MY_SCHEMA.KC_TRADEDETAILS:
 LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-00904: "TRADEFIELDS"."COMPANY_SECTOR_CODE": invalid identifier

Which is now a straightforward debugging task

SQL Plus Spool as CSV

To get a CSV output from SQL Plus you might be inclined to concatenate the SELECT columns together with commas:

SPOOL MyOutputFile.csv

SELECT col1 ||’,’|| col2 ||’,’|| col3 from table;

SPOOL OFF;

This can get quite messy and doesn’t read well.

But you don’t have to do this, if you set COLSEP to β€œ,” you will get a csv file with tab style spacing and you can keep your SQL formatted with a regular SELECT statement.

SET COLSEP ,

You can then easily suck out the whitespace afterwards with a Perl one liner

                perl -pi.bak -e “s/ *,/,/g;s/, */,/g” MyOutputFile.csv

This makes a backup of the file and does in inline replacement, taking out all spaces following or preceeding a comma.

This will ignore spaces embedded in a string, say “xxx,Mr Joe Bloggs,xxxx”.

SQL Developer cannot find JVM

I installed the Oracle 11gR2 client (32 bit) on to a Win 2008 R2 64bit server, as we have 32 bit apps to support.

When I ‘point’ SQLDeveloper at the Java 6 SE JVM, I get “Unable to find Java Virtual Machine” message.

This is because I have Java 6 SE 64 bit version installed.

You need to install the 32 bit JDK , so that it lives in “C:\Programs Files (x86)”.

Then repoint the variable “SetJavaHome” that is in sqldeveloper.conf, and restart.

Using Perl DBI to test ODBC Connections

If you have the DBI perl module, and the Win32 ODBC pm (optional), this little script can be used to check your connections and give a database DSN a quick test.

 

   #!perl -w   use DBI;   use Win32::ODBC;      print "ODBC Drivers \n";   my @drivers = DBI->available_drivers;   print join(", ", @drivers), "\n";      print "-------------------------------------------------------------------------\n";      my $d = join("", @drivers);   print "DBD::ODBC";   print "not" if ($d !~ /ODBC/);   print "installed\n";      print "-------------------------------------------------------------------------\n";      my @dsns = DBI->data_sources('ODBC');   foreach my $d (@dsns)   {   print "$d\n";   }      print "-------------------------------------------------------------------------\n";      my $dbh = DBI->connect('dbi:ODBC:DBASE02', 'browser', 'browser');   $dbh->disconnect() if ($dbh);      print "-------------------------------------------------------------------------\n";      my $dbh32 = DBI->connect('dbi:Win32-ODBC:DBASE02', 'browser', 'browser');   $dbh32->disconnect() if ($dbh32); 

 

 

System DSN on Windows 2008r2 64 bit

Setting up a 32bit VB/Oracle app on a Win2008r2 64bit OS/Server can prove to be problematic.

Heres a link to my discussionfrom the Oracle OTN forum.

https://forums.oracle.com/forums/thread.jspa?messageID=10346952#10346952

The key is using the windows 32 bit panel here c:\windows\sysWOW64\odbcad32.exe to create your connections using the Oracle 32 bit client/driver.

In addition theres a gotcha where you may have to configure, rather than add, an existing System DSN connection to get it to work.

 

Updated 8 May 2013:

ODBC connections can be very finnicky regarding white space. When creating a DSN make sure there is no whitespace at the end of the DSN Alias! It sounds stupid, and it is yes, but this can take a while to figure out πŸ™

TNS ping

So what does this do?

Given you have your database and server details put in TNSNAMES.ORA, this should will check that your listener is up on the database server

Microsoft Windows [Version 5.2.3790] (C) Copyright 1985-2003 Microsoft Corp.

C:\>tnsping mydb01

TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 – Production on 01-FEB-2 012 15:31:18

Copyright (c) 1997, 2008, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1234)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb01)))

OK (20 msec)

C:\>