SQLSyntaxErrorException with JRI but not in JasperSoft Studio

daust / JasperReportsIntegration

JasperReportsIntegration provides an interface to use the JasperReports reporting engine in an Oracle database application, specifically with Oracle Application Express (Oracle APEX).

BSD 3-Clause "New" or "Revised" License

55 stars 23 forks source link

SQLSyntaxErrorException with JRI but not in JasperSoft Studio #143

Closed abaldoni closed 2 months ago

abaldoni commented 2 months ago

I'm designing a complex report which, in the main SELECT SQL query, calls a function from a package in a specific tablespace: URFAPEX.SWPROJ.get_settore_username(di."ID") AS settore In JasperSoft Studio the reports runs just fine. In JRI I get the following exception: 18:54:39 [ERROR] oc.utils.Utils.throwRuntimeException - java.sql.SQLSyntaxErrorException: ORA-00904: "URFAPEX"."SWPROJ"."GET_SETTORE_USERNAME": invalid identifier

Any hint?

Regards, Alessandro

daust commented 2 months ago

Seems to be a privilege error. Do you connect to the database with the same user?

Also, what does the function return? The tablespace shouldn't be of interest. But for sure if you are using different database users. Just connect to the database with the user URFAPEX , I assume. You can use SQLDeveloper or sqlplus or whatever. Then execute the query. Does that work?

abaldoni commented 2 months ago

And indeed a GRANT was needed!

Thank you so much @daust !