Calling Oracle Stored Procedure with Parameters that return a RefCursor

Calling Oracle Stored Procedure with Parameters that return a RefCursor

Posted by: edmandlooi on 14 September 2017, 10:19 am EST

• 

  edmandlooi

  • Post Options:
  • Link
    Copy

  Posted 14 September 2017, 10:19 am EST

  [activereports_archive]Hi,

  I am using ActiveReports 11 and need to call an Oracle stored procedure with parameters and return result in a RefCursor. I have search the forum and did not find any clear instruction on what is needed to call an Oracle stored procedure. I have create a simple stored procedure and tested it with Oracle SQL developer tool. Could someone give me instruction on how to setup ActiveReport under the DataSource and DataSet section that is equivalent to the call below that I have test that works in Oracle SQL Developer?

  Thanks for any help you may provide.

  var c refcursor;
  
  execute MyStoredProc( sysdate()-1,sysdate(), :c)
  
  [/activereports_archive]

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 14 September 2017, 10:19 am EST

  [activereports_archive]Hello,

  I have reported this issue to the concerned team. I will let you know as soon as I hear anything on this.
  
  Tracking id for this issue is:242351

  Regards,
  
  Deepak Sharma[/activereports_archive]

• 

  akoenig

  • Post Options:
  • Link
    Copy

  Posted 1 October 2020, 1:34 am EST

  I’m having the same issue in ActiveReports v14. Has there been any progress on this issue?

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 4 October 2020, 10:20 pm EST

  Hello,

  Currently we do not have a working sample application to demonstrate this. I would like to inform you that Oracle Client Provider in RDL and PageReports has a limitation, it does not support the stored procedures with ‘SYS_REFCURSOR’ but you can try to use OleDB client(MSDAORA) for Oracle, so if stored procedure has one input parameter and refcursor as output, e.g.:

  PROCEDURE PROC2
  
  (
  
  PARAM1 IN VARCHAR2
  
  , T2 OUT SYS_REFCURSOR
  
  )…

  then it can be called in the page report like PROC2(?,)

  Thanks,
  
  Mohit