Generate Spool for multiple db objects / How to generate Spool for multiple DB objects in oracle

SET PAGES 0

SET TIMING   OFF
SET ECHO     OFF
SET FEEDBACK OFF

SET TERMOUT  OFF

SPOOL C:\Emirates\Assignments\views\manyspools_8.sql


SELECT 'SPOOL C:\Emirates\Assignments\objects\' || object_name || '.sql' || CHR (10) ||
       'SELECT text from dba_source WHERE name = ''' || object_name || ''';' || CHR (10) ||
       'SPOOL OFF'
  from xxtemp_hr_stg where object_type in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION');

SPOOL OFF

@ C:\Emirates\Assignments\objects\manyspools_8.sql

SET TERMOUT ON

--Note:

-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of table "dba_source", need to use dba_views for views

--My stage table: where my list of objects stored in stage table

--xxtemp_hr_stg

--Table Script:

create table xxtemp_hr_stg

(

 SNO NUMBER,

 OWNER VARCHAR2(10),

 OBJECT_NAME VARCHAR2(100),

 OBJECT_TYPE VARCHAR2(20),

 REMARKS VARCHAR2(2000),

 MODIFY_YN VARCHAR2(2000),

 ADDITIONAL_COMMENTS VARCHAR2(4000)

);