Calling a pl/sql procedure then doing select

[tchen_1]

Is there a way for me to call a procedure that populates a temporary table and then grab data from that table using a select statement?

I tried:

<queryString language="plsql">
  <![CDATA[{call my_procedure(some_input)} select * from temp_table]]>
 </queryString>

but it doesn't work.

I also tried calling the procedure in the main report and using a subreport to do the select statement, but that doesn't work either.

I know that I can pass the ORACLE_REF_CURSOR to my_procedure and have it populate the temp table and return a resultset with my data in it, but the question I have about this is: do I need to close the cursor myself or does pl/sql or something else take care of this for me automatically? I'm concerned about this because I read about people encountering the "maximum open cursors exceeded" error.

Thanks!

[mlopez_1]

I make a trick and works very well.

Use in Dataset and Query Dialog

<queryString language="plsql">
  <![CDATA[{call my_procedure(some_input)}]]>
 </queryString>

With this we force to execute the stored procedure for filling temporary table first of all when opening the Report Unit.

Make sure that 'When no data type' report property is set to "No Data Section'.

After this, insert a table component in 'No Data' section. Table must have a Dataset, where you will put your temporary table's sql query and recreate its fields in this component.

Regards,

 

Mariano