Return cursor from Oracle global temporary tables, error ORA-01410: invalid ROWID

I have a stored procedure which does the following things:

  • insert input records into a global temporary table
  • create a cursor that selects record from another table that inner joins the temporary table
  • return the cursor

e.g.
OPEN p_cursor FOR
SELECT *
FROM emp
INNER JOIN temp_table
ON temp_table.department = emp.department;

Then I tried to execute the stored procedure from java code and I get ORA-01410: invalid ROWID error. If you google it, you’ll see the following explanation.

Cause: A ROWID was entered incorrectly. ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F. A typical ROWID format is ‘000001F8.0001.0006’.
Action: Check the format, then enter the ROWID using the correct format. ROWID format: block ID, row in block, file ID

This doesn’t help me to solve the problem, but I found a thread on Oracle forum that mentioned about preserve rows (Global Temporary tables for returning resultsets to ODP.NET client app), so in my case, this error occurs because when creating the temporary table, it is set to ON COMMIT DELETE ROWS. To fix this just change it to ON COMMIT PRESERVE ROWS, so your records will still exist while you trying to get the resultset. e.g:

CREATE GLOBAL TEMPORARY TABLE temp_table (
department     VARCHAR2(50))
ON COMMIT PRESERVE ROWS;

[junit] java. lang.ClassNotFoundException while running ant script

This error is usually caused by having problem with classpath

You could get this for example while running the script for javac, or junit test. Make sure to add the lib folders you need, and the classes folder into the classpath. e.g. I have a main project with a model project (with hibernate), and before compile the main project, I’ve already built a jar for the model project and put it under the lib folder of the main one. It’ll look something like the following. Note: don’t forget to include the hibernate.cfg.xml file into classpath if you are using hibernate.

  <property name="build.main.dir"    location="/project/mainProject" />
<property name="build.model.dir"    location="/project/modelProject" />

<property name="class"    location="/project/build/classes" />
<path id="junit.classpath">
        <pathelement location="${build.main.dir}/classes"/>
        <pathelement location="${build.model.dir}/classes"/>
        <fileset dir="${build.model.dir}/classes">
            <include name="**/*.xml"/>
        </fileset>
</path>
<target  name="build">
<javac srcdir="${build.main.dir}/src" destdir="${classes}"   debug="on" >
 <include name="**/*.java"/>
<classpath refid="main.classpath" />
</javac>
<javac srcdir="${build.main.dir}/test" destdir="${classes}"   debug="on" >
 <include name="**/*.java"/>
<classpath refid="main.classpath" />
</javac>
</target>

For junit case, there’re more stuffs to pay attention to:

1) make sure you referenced to the junit libs, they are NOT included in the ant distribution, reference to this junit test manual from ant. Basically you can put junit.jar and ant-junit.jar under ant’s lib folder, or reference that two classes in your classpath variable.

2) remember to build the test classes as well, like the example above included the test folder. 

3) add the above test classes to the classpath.

4) if your junit testing include calls to database through hibernate, do not use the jdbc connection that’s setup in the sun application server, so in hibernate.cfg.xml it should be something like this

<property name="connection.url">jdbc:oracle:thin:@123.456.78:test</property>
<property name="connection.username">username</property>
<property name="connection.password">password</property>

instead of 
<property name="connection.datasource">jdbc/testProject</property>