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;

Advertisements

Howto: simply concatenate multiple rows of data into a variable in SQL Server 2005

Problem: I have a select statement that returns more than one rows of result, and I need to concatenate all rows into one. For example, if I have a table called ‘Game’ and it returns the following result when you select all and you just want to get a list of games’ names:

gameId Name
1 Rayman: Raving Rabbids
2 Naruto: Rise of a Ninja
3 Guitar Hero III: Legends of Rock
4 Dynasty Warriors 6

Solution: usually I would thought of to loop through it or in more complex cases, to use a cursor. However, I found a really easy way today (you don’t need to declare the variable asNVARCHAR(MAX), just in case if you have heaps of games and happen to use SQL Server 2005 or above).

DECLARE @games NVARCHAR(MAX)
SELECT @games=@games + Name +', ' AS GameList FROM Games
SELECT @games

Result?

GameList
Rayman: Raving Rabbids, Naruto: Rise of a Ninja,Guitar Hero III: Legends of Rock,Dynasty Warriors 6,…

Of course you’ll need to get rid of the last ‘,’ from the variable at the end. How simple, and how could I never knew this before!