Dariusz on Software

Today I installed (again) Oracle XE on Debian (Lenny) and got the following error:

ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
SVR4 Error: 13: Permission denied

I suspected problem was caused by errant ORACLE_HOME / ORACLE_SID values bug got the answer:

chmod 6751 $ORACLE_HOME/bin/oracle

That gives "-rwsr-s--x". Why permissions were not set properly on installation - have no idea. Any clues?

Eclipse SQL Explorer is simple but powerfull tool to inspect Your database state. You can:

• exec SQL commands
• browse database objects in Eclipse (including existing indices)
• open multiple databases at once

Installation is pretty simple:

1. Download preferred version (select any sqlexplorer_plugin-*.zip file)
2. Unpack this ZIP to your Eclipse directory: unzip sqlexplorer_plugin-*.zip
3. Restart Eclipse

Usage: switch to SQLExplorer perspective and define new connection:

If needed driver is not on the list you have to configure new driver by selecting JDBC jar file from Your disk.

By default the following views are present in SQL Explorer perspective:

• Connection list
• SQL commands history
• SQL editor window
• Database details (table structure, indices are here)
• Database structure (list of tables, views etc.)

Enjoy!

By default Oracle is cryptic about details of an error. Directive SHOW ERRORS is very useful during debugging of stored procedures for instance.

When you are joining to an existing project you don't have good knowledge of internal project structures. Existing documentation may be inaccurate or just missing. In this case you can rely only on clean source code structure and source code comments. Based on them you can generate API docs using Javadoc or Pydoc for instance. But what with persistent data model stored in database? SchemaSpy comes with help here!

SchemaSpy is small (~200kB) Java application that is able to render schema retrieved from JDBC connection into set of "hyperlinked" HTML pages with Java Script and (if GraphViz installed) diagrams. Below you see one page of example output of this tool:

Installation is very simple. I'll describe integration process with Eclipse. First: download jar with application. Then create new Run configuration in Eclipse:

Add downloaded jar to classpath:

Then fill execution program arguments. My preferred list is as follows:

-t orathin -db XE -u <db-user> -p <db-password> -o /opt/c2p/schema-docs -dp /opt/lib/ojdbc14.jar -host localhost -port 1521 -schema <db-schema> -noimplied

"orathin" option is database type and ojdbc14.jar contains JDBC driver for this connection type. "-noimplied" option will make documentation generation faster and saves some disk space for big projects.

The only missing feature for me is to disable rendering of attributes on diagrams (some association-rich tables gives very big images that could kill Firefox browser). It's an open source so I hope to fix this soon and of course release a path :-)

I got the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

on line:

<property name="customer" column="CustomerId" />

the fix for above error was to use many-to-many tag instead of plain <property>:

<many-to-one name="customer" column="CustomerId" />

Note that class is not required here - it's computed from return type of getCustomer() getter by reflection.

"ORA-01722: invalid number" is raised when a number was passed a query parameter, but another type (string) was expected.

The error message isn't very helpful, is it?

Interesting error found on Oracle XE installed for one of projects. After a run of test case that uses JDBC to perform some operations on database all tests started to fail with this exception:

Caused by: java.sql.SQLException: ORA-00600: internal error code, arguments:\
 [kdsgrp1], [], [], [], [], [], [], []
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)

Restart of Oracle database did not work. Seems table space was broken. I rebuit the database (DROP, then CREATE) and error dissapeared.

Interesting ...

I bet everyone knows how to enable SQL logging for Hibernate. If you add this parametr to Hibernate configuration:

<property name="hibernate.show_sql">true</property>

you will see queries like this in log file:

select roles0_.EntityKey as Entity9_1_, roles0_.ENTITYKEY as ENTITY9_168_0_
from USERROLE roles0_
where roles0_.EntityKey=?

but wait: what value is passed as parameter by Hibernate? (parameters are marked as "?" in JDBC) You have configure TRACE level for some log categories, here's example for simplelog.properties:

org.apache.commons.logging.simplelog.log.org.hibernate.SQL=trace
org.apache.commons.logging.simplelog.log.org.hibernate.engine.query=trace
org.apache.commons.logging.simplelog.log.org.hibernate.type=trace
org.apache.commons.logging.simplelog.log.org.hibernate.jdbc=trace

For your convenience here's log4j configuration:

log4j.logger.org.hibernate.SQL=trace
log4j.logger.org.hibernate.engine.query=trace
log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.jdbc=trace

Then you will see all passed parameters and results in logs:

[DEBUG] (generated SQL here with parameter placeholders)
[TRACE] preparing statement
[TRACE] binding '1002' to parameter: 1
[TRACE] binding '1002' to parameter: 2
[TRACE] binding '1002' to parameter: 3
[DEBUG] about to open ResultSet (open ResultSets: 0, globally: 0)
[DEBUG] about to close ResultSet (open ResultSets: 1, globally: 1)
[DEBUG] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[TRACE] closing statement

Nothing hidden here. Happy debugging!

WebSphere uses SQL databases for internal managment of MQ queues (Derby database engine under the covers). Sometimes you need to reset their state. Here's the script that erase and recreate BPEDB database state (tested under WS 6.1.2):

rm -rf $WID_HOME/pf/wps/databases/BPEDB
echo "CONNECT 'jdbc:derby:$WID_HOME/pf/wps/databases/BPEDB;create=true' AS BPEDB;"|\
    $WID_HOME/runtimes/bi_v61/derby/bin/embedded/ij.sh /dev/stdin

Recently I've got the following error during SQL named query execution:

SQL Error: 17006, SQLState: null Invalid column name

In order to track the problem down I enabled SQL logging and collected SQL query issued, then run it under SQL monitor and ... it was working without error! I was surprised.

In order to debug what's going on I enabled detailed logging in Hibernate. What was happened then?

[INFO] could not read column value from result set: entityKey215_0_; Invalid column name

This message showed me that the problem was not related to generated SQL query but to column names expected by Hibernate. Generated recordset didn't has entityKey215_0_ column. I added:

entityKey AS {list.entityKey}

to named SQL query and error dissapeared.

This error is not-very-obvious kind of error because Oracle hides column / table names in error messages returned. Why? I don't get this cryptic error messages idea. In order to track an error under Oracle I had to enable verbose logging (error message standalone din't give anything useful). Much simpler database MySQL has better error reporting than "fat" Oracle.