Oracle Database Explorer

Posted by toddw on 2010-05-13 16:52 | Staff Pick!
1.0
Eric Promislow
Tue, 2012-01-31 11:28
  • : 6.0.0-b1 - 7.*

Adds support for Oracle databases to Komodo's Database Explorer component.

Requirements:
You'll need to be using the latest Komodo IDE nightly build (6.0.0-beta1 or higher).

You'll need to install the Oracle Instant Client libraries (versions required per platform are below). The Oracle Instant Client library is provided as a free downloadable file from the Oracle web site:
http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

  • Oracle Instant Client 11.2 for Windows and Linux
  • Oracle Instant Client 10.2 for Mac OS X (10.5 and higher only)

dafi | Fri, 2010-05-14 00:10

=== Ubuntu 9.10 (my workstation at work)

I needed to install libaio

sudo apt-get install libaio1

I suppose this is valid only for fewer users

=== Connection params

The connection dialog prompts for 'hostname' but this can generate confusion to dumb users (like me) because you should specify the service name as defined in tnsnames.ora file.

if you receive an ORA-12504 error probably you need to specify service name

=== Full table is slow

When I open the 'View ABC table' menu the 'full table' operation can be slow if table contains thousands of records, maybe a different approach in necessary.
This is expecially true if table contains blob/clob columns.

=== Show only tables

I think you already know because the Oracle extension is far from be complete but I can see only TABLE object types.
For example no VIEW/TRIGGERS/SEQUENCES are shown in db tree view.

Obviously I can query views writing sql code (eg 'SELECT * FROM VW_ABC')

BTW The extension is very stable

--
dafi
Enhance KomodoEdit with MoreKomodo

cvioletti | Thu, 2010-07-15 11:47

I'm running multiple versions of Oracle on Windows and I'm getting TNS errors. How is Komodo looking for the driver? Is it looking in %ORACLE_HOME%, in %PATH% or some other way? It would be nice if we could tell Komodo which ORACLE_HOME to use.

toddw
ActiveState Staff
Thu, 2010-07-15 12:58

I believe Komodo is looking for the Oracle Client libraries from the PATH environment variable, as they get loaded dynamically at runtime.

The extensions uses the cxOracle Python module under the hood.

Cheers,
Todd

ericp
ActiveState Staff
Fri, 2010-07-16 12:35

As for the speed issue, it's due to an internal problem I have
with Oracle. We don't get all the rows from the database,
only enough rows to display in the view. This is very straightforward
with every database I've used, except Oracle, as it doesn't
support the LIMIT and OFFSET qualifiers in select statements. I'll
have to try a different approach.

- Eric

dafi | Fri, 2010-07-16 22:01

In Oracle to limit returned rows yu must to use subselects and the pseudo column ROWNUM

For example to obtain only the first 20 records from table mytbl you can write

SELECT * FROM
    (SELECT * FROM mytbl WHERE cond < x ORDER BY id DESC)
WHERE ROWNUM < 20;

It is very ugly but it common used for example to paginate results
The syntax should be easily made dynamic for DB Explorer

--
dafi
Enhance KomodoEdit with MoreKomodo
Consider a little donation

ericp
ActiveState Staff
Mon, 2010-07-19 11:03

Hi Dafi,

I also found that if you want to find rows that start after the
first, you need to create a temporary variable:

select * from
  select rownum ko$rnum, col1, col2, ... from ...
where ko$rnum between startRow and endRow

If startRow is greater-than 1, the test without a temporary
variable will fail.

I don't think this is the reason for the slowness, but I
don't see it with the other databases.

- Eric

gniquil | Mon, 2010-11-08 13:00

It always gives me ORA-24315: illegal attribute type

raketemensch | Fri, 2012-01-13 14:36

Every time I try to add an Oracle instance, I get an error message telling me to set the DYLD_LIBRARY_PATH environment variable.

1) This var is set system-wide, and points to /usr/local/oracle/instantclient_10_2
2) I also set the var within Komodo IDE, but still get the issue

toddw
ActiveState Staff
Mon, 2012-01-23 14:29

Which Mac OS version are you running? As we'll try to reproduce the problems with the same OS.

john.clark | Tue, 2012-02-07 08:50

When I try to use this extension I get the following error message:

  The database adapter Oracle currently isn't available: Couldn't load database adapter cx_Oracle: DLL load failed: %1 is not a valid Win32 application...  Please set LIB environment variable to the Oracle Instant Client 11.2 directory, and restart Windows

I'm running Windows 7 (x64), Komodo IDE, version 7.0.0, build 68957, platform win32-x86, Python 2.7.1 (r271:86832, Feb 7 2011, 11:30:38) [MSC v.1500 32 bit (Intel)] on win32, Oracle 11g (11.2).

My LIB environment variable is set to: LIB=C:\oracle\oracle_1120\product\11.2.0\dbhome_1\instantclient\;

Thanks in advance for any assistance...

john.clark | Tue, 2012-02-07 14:42

Okay - this has to do with making sure that the "correct" oracle instant client is loaded - I have the x64 version of the Oracle software installed on my machine so the extension was attempting to load the x64 version of the dll. If I install the x32 version of the instant client, and I add the directory for the x32 version to my path it loads the DLL correctly. Of course now I am getting the TNS names resolution problem.

It appears that the LIB environment variable does not resolve this problem - I'm hoping someone can comment on why that is the suggested solution.

dhorne | Tue, 2012-02-21 18:26

Hi there

Thanks for the extension ... it's very useful.

I'd like to request one enahncement. As a developer, I don't get to log in to the schema that owns the tables. I would like the ability to filter on a schema owner whose tables I have access to when I create the connection. So instead of having a connection identified by something like

Oracle:tnsname/myuser

So I would anticipate the connection being something like

Oracle:tnsname/myuser:schemaowner

Thanks

va_fyreheart | Mon, 2013-12-09 14:31

Is this add-on going to upgraded for v8.5 and Oracle 12c?

Thanks.

-John