SQL & PL/SQL

Hi ...

This is my first post in this forum...and straightaway I critically need a help...

I have a stored procedure (Wrapper) which calls around 20 other procedures one by one.

I need only one single file to be generated using UTL_FILE to record all the stuff from all these procedures.

For this, each of these procedures have one input file handle varibale of the type UTL_FILE.file_type, through which they write into the one single file.

This file handler I set in the wrapper procedure and opens the file and closes the same in the wrapper only.

Now, when I try to invoke these child procedures from within the wrapper procedure using EXXECUTE IMMEDIATE as follows:

......
......
EXECUTE IMMEDIATE 'Begin sp_child1 (:A1, :A2, :A3) End;'
USING value1, value2, Value3; ----(here, value3 is of the type utl_file.file_type)

.......
.....

Here I'm facing a compilation error saying "Non SQL Type encountered".

I know that Dynamic SQL statements support only SQL types and this is why the error is there.

But, my requirement is such that I have to do it in this way only... i.e. using Dynamic SQL only...

So, can anybody please let me know if there is some workaround for this to be done using dynamic SQL/PL sql...

Thanks in advance..