SPOOL IN STORED PROCEDURE ORACLE

[infdeveloper]

Hello every one,

Could any one of you tell me how do I spool the data from a stored procedure to file

My requirement is I have to generate a merge_into script on the fly.
I have to generate a script like. I was able to get the the column names and constraints from the data dictionary. I was able to do that using a shell script. but when it comes to stored proc, I was stuck in how do we spool the data in stored procedure

"MERGE INTO Table A
USING Table B
ON (a.y= b.y)
WHEN MATCHED THEN
UPDATE SET a.x = b.x
WHEN NOT MATCHED THEN
INSERT (a.x)
VALUES (b.x);
"

Thanks,

 

[carp]

You can't spool from a stored procedure, per se. However, you CAN use dbms_output to generate your output on screen (which could be captured by spooling):

set serveroutput on size 1000000
spool myfile
exec my_stored_procedure;
spool off

Alternatively, you can use the utl_file package to generate your file for you.