nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using active objects alias causes error ORA-00904 invalid identifier

Jens Kisters __SeibertSolutions

Hello,

on a customers system our plugin (version 0.5 to 1.1.1) and jira (?? to 7.5.0) were upgraded.

Since then the plugin started throwing exceptions when we access a Active Objects table like this:

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "issueShare")
.where("issueShare.ISSUE_ID = ?", issue.getId())
.order("issueShare.ID DESC")
);

The error message is

java.sql.SQLSyntaxErrorException: ORA-00904: "issueShare"."ID": invalid identifier

It looks to me like the alias is not resolved when the sql is built, but why?

The same piece of code worked on the old system before the update and on other systems with the same Plugin Version that was installed during the upgrade.

Two fields were added to the IssueShare table between version 0.5 and 1.1.1, thats everything that has changed.

Thanks in Advance!

Jens

Accepted answers

Jens Kisters __SeibertSolutions

Changing the alias to uppercase seems to fix the problem for oracle 12.

The lowercase alias causes the same problems.

We will do some regression testing to see if this causes trouble for MySQL, PSQL or HSQL.

All comments

Eduard M

Hello Jens,

The ORA-00904 invalid identifier means that Oracle didn't like the column name. Since it's printed in the error as a quoted identifier, I would examine how Oracle is reporting the table/column name and possibly if it changed recently. Also you may try to trace the invalid SQL to look for anything unexpected. Hope you can find something interesting.

Jens Kisters __SeibertSolutions

Hi Eduard,

no the ID column did not change.

How can the SQL be traced?

Eduard M

Hi Jens,

OK, what I was wondering is if Oracle's dictionary is storing the tablename in uppercase, that would clash with the quoted identifier usage in the code. You can check with DESCRIBE command in sqlplus if that seems to be the case. 'SHOW DESCRIBE' will give you the current settings and 'DESCRIBE issueShare' will give info about that table.

There are some examples of tracefiles on the net although an Oracle dba might be needed if they are not enabled by default. The example I found was pointing to an existing trace file:

SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
---------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc

SQL> host tail -10 /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
-------------------------------------------------------------
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : CHA.EMP
Policy name    : EMP_POLICY
Policy function: CHA.EMP_RESTRICT
RLS view  :
SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP"   "EMP" WHERE ('SCOTT' = enamee)
ORA-00904: "ENAMEE": invalid identifier
-------------------------------------------------------------

The pre-upgrade JVM version and current Oracle version and odbc driver version might be relevant info as well.

Jens Kisters __SeibertSolutions

Hi Eduard,

we checked the traces and found this example of a not working query:

It seems like the problem is caused by the double quotes in the order by clause.

How can we get rid of those?

The current Oracle Version is Oracle 12.1.0.2.0 und the driver filename is ojdbc7.jar

The pre-update system is not accessible any more as the machine is now running the upgraded version of Jira.

The oracle version was not changed.

best regards

Jens

Jens Kisters __SeibertSolutions

Changing the alias to uppercase seems to fix the problem for oracle 12.

The lowercase alias causes the same problems.

We will do some regression testing to see if this causes trouble for MySQL, PSQL or HSQL.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-development

active-objects