ReportServer Forum

aguiral

Member

Registered: 2017-11-30

Oracle cursor

Hello,

is it possible using a dynamic list to run an Oracle function which returns a cursor ?

Something like this :

create or replace FUNCTION FN_CURSOR
RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT attribute1 FROM table;
  RETURN my_cursor;
END fn_cursor;

and be able to output the value of attribute1 on ReportServer using a dynamic list (perhaps something like select * from (select fn_cursor from dual)) in dynamic list body

Thanks

Alfredo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Oracle cursor

Hi aguiral,

do you get any error messages when you try this ? It should work since your query is for reportserver a standard query ( select * from (select fn_cursor from dual)) ) and this query is just passed to oracle by wrapping it.

In your db, you will get for example the wrapped query:
SELECT TOP 0 * FROM (SELECT * FROM (select * from (select fn_cursor from dual) ) wrappedQry) limitQry /* user: 6 */ /* report: 3753316 */ /* token: ca79ff7b-e1ab-4d68-b907-c3b6bd5cf15a */ /* currentuser: 6 */

Again, you should try it and I would be interesting to know your results.

Regards,
Eduardo

aguiral

Member

Registered: 2017-11-30

Re: Oracle cursor

Thanks indeed Eduardo for your quick answer.

See below the error message ...

28-Jun-2018 16:10:47.446 FINE [http-nio-8080-exec-8] net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement SQL query string: SELECT * FROM (SELECT * FROM (select fn_cursor() c_att from dual
) wrappedQry) limitQry WHERE ROWNUM < 0   /* user: 6 */   /* report: 350529 */   /* token: b7a903f5-1556-4be3-b337-2cbbbe3d7342 */   /* currentuser: 6 */
28-Jun-2018 16:10:47.454 WARNING [http-nio-8080-exec-8] net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute Could not map -10 to a java class
java.lang.IllegalArgumentException: Could not map -10 to a java class
    at net.datenwerke.rs.base.service.dbhelper.DatabaseHelper.mapSQLTypeToJava(DatabaseHelper.java:380)
    at net.datenwerke.rs.base.service.reportengines.table.output.object.TableDefinition.fromResultSetMetaData(TableDefinition.java:107)
    at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:273)
    at net.datenwerke.rs.base.service.reportengines.table.TableReportEngine.createReport(TableReportEngine.java:258)

and the Oracle PLSQL function whichs works properly using Sqldeveloper tool for instance

create or replace FUNCTION FN_CURSOR
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT identificativo FROM dat_terminal;
  RETURN my_cursor;
END fn_cursor;

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Oracle cursor

Hi Alfredo,

thanks for testing. I raised ticket RS-2954 for this. At the moment, cursors are not supported.

Regards,
Eduardo

aguiral

Member

Registered: 2017-11-30

Re: Oracle cursor

Okay thanks a lot Eduardo