I am using Oracle 10g R2. I want to write a script to generate a .sql file containing all stored procedures and functions present in my database. The purpose is to take backup of these objects.
i am trying to use the following code:
BEGIN
for c in (select OBJECT_NAME,OBJECT_TYPE from user_objects o where o.object_type IN ( 'PROCEDURE','FUNCTION'))
loop
execute immediate ( 'SELECT dbms_metadata.get_ddl(''' || c.object_type || ''',''' || OBJECT_NAME || ''') FROM dual;');
end loop;
END;
But i want to know how can i export the definition of objects in .sql file using this script.