Hi Experts,
I am trying to take backup of few the packages in the database , I've written the below script to do this task.
The dbms_output.put_line prints the entire package however in the file , i am not getting the complete package text till END <packagebody>.
I was hoping if you could help me to understand and resolve why dbms_output.put_line is printing the entire package in view output screen and UTL_FILE is not writing the same in a file, it would be really appreciated
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_clob CLOB;
l_offset INT := 1;
BEGIN
SELECT
dbms_metadata.get_ddl('PACKAGE', 'PACKAGE_NAME_PKG', 'APPS')
INTO l_clob
FROM
dual;
l_file := UTL_FILE.fopen('CLOBS', 'PACKAGE_NAME_PKG.txt', 'w',32767);
loop
exit when l_offset > dbms_lob.getlength(l_clob);
dbms_output.put_line( dbms_lob.substr( l_clob, 255, l_offset ) );
UTL_FILE.put(l_file, dbms_lob.substr( l_clob, 255, l_offset ));
l_offset := l_offset + 255;
end loop;
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Expected end.
dbms_output.put_line (dbms_utility.format_error_backtrace);
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
WHEN OTHERS THEN
dbms_output.put_line (dbms_utility.format_error_backtrace);
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
Regards