I am exporting packages using dbms_metadata.get_ddl . For example:
select dbms_metadata.get_ddl('PACKAGE_BODY','NOMBRE_PAQ','SCHEMA_NAME') from dual;
But when generating the spool with the package code, a blank line is generated before CREATE OR REPLACE... .
How can I delete the blank line in the extraction?
I tried setting:
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true)
... but it does not work.
Indeed, it's annoying, but dbms_metadata.get_ddl always returns a result that begins with the character for a new line (ascii 10).
If you want to remove it, you can use SUBSTR to to return everything except the first character:
select substr(dbms_metadata.get_ddl('PACKAGE_BODY','NOMBRE_PAQ','SCHEMA_NAME'), 2)
from dual;
Or you can try TRIM :
select trim(leading chr(10) from dbms_metadata.get_ddl('PACKAGE_BODY','NOMBRE_PAQ','SCHEMA_NAME'))
from dual;
Maybe using the conditional%% is better, in case there is some circumstance where the result will not start with a new line.
Demo .