I have a PLSQL Package with several stored procedures inside. This package is used to create specific reports.
I call the procedures like that :
package.init(parameter1 => 'Blablabla');
package.do_something(parameter1 => 'More Blabla');
package.close;
package.init(parameter1 => 'Blabla');
package.close;
v_report_data := package.return_data;
So basically this package accepts text and other data which are coming from my SQL queries.
The Data is modified and appended to a CLOB for further usage inside the package and is finally returned to the caller with the return_data function.
However sometimes I need to have more than one iteration at a time open to produce different reports (which then should be joined together to one output).
For example: I have a query like this:
package.init(parameter1 => 'dataset1');
for i in (select value1, value2, value3 ,value4 from table_xyz)
loop
package.do_something(parameter1 => value1||value2);
end loop
package.close;
v_report_data := package.return_data;
This works without a problem. However if I want to do this:
package.init(parameter1 => 'dataset1');
package.init(parameter2 => 'dataset2'); -- this does not work
for i in (select value1, value2, value3 ,value4 from table_xyz)
loop
package.do_something(parameter1 => value1||value2);
package.do_something(parameter1 => value3||value4); -- this is data i want to put in the second iteration
end loop
package.close;
v_report_data := package.return_data;
That is not working because I did not close the CLOB (which I cannot do since I have to do things with it).
I am by far no coding expert but I wonder if there is a way to somehow add an ID or something like that to a procedure and if it does not match with the first ID spawn a second iteration of the procedures?
