column names misformat at exporting view to text file 1

[Calator]

I support a system that has a v.large schema with thousands of views. Occasionally I have to apply patches to the views delivered by the software's vendor. Some of the patches will prove incorrect and will need to be rolled back. I "export" the schema prior to change, however I don't have the database priviledges to restore from that backup. I need to log a job to an outsourced service for their dba to perform the restore, and that may take a couple of days. I am looking for a quick alternative,m that I can perform myself.

As an alternive I attempted the sqlplus code (as listed below) to extract the views code to a text file, from where I could re-load back to the schema, by executing the text file.
The problem I have is that in case of large views, the extracted text has some of column names truncated or split on subsequent lines of the file, and when re-loaded, they get loaded with incorrect columns names for eg "employee" is loaded as:
"emp loyee". I cannot visually check and fix issues, as there are hundreds on views in the backup.

Any ideas about a fix for this code, to guarantee that no matter how large the source code for the view, it will be extracted correctly? Note that I played with the "set" parameters in line one, to extend the limits, but still did not manage to get it right for all views.

set long 100000 longc 1000 lines 1000 feedback off heading off termout off trims on
spool extract_backup_ddl_views.log
prompt set echo on
prompt connect <schema>/<password>
prompt whenever sqlerror exit
prompt spool restore_backup_ddl_views.log
prompt prompt Begin backup ddl
prompt set sqlblanklines on feedback on

select dbms_metadata.get_ddl(object_type, object_name, owner)||';' || chr(10) ||'show errors ; ' ddl_txt from all_objects
where object_type = 'VIEW' and owner = 'myschema'
/
prompt prompt End backup ddl
prompt spool off
spool off

 

[LKBrwnDBA]

Add this to your code:

set long 100000 longc [b][red]120[/red][/b] lines [b][red]120 pages 0[/red][/b] feed off head off term off trims on
spool extract_backup_ddl_views.log
-- Etc ...
[b][red]COL ddl_txt WOR[/red][/b]
SELECT    DBMS_METADATA.get_ddl (object_type, object_name, owner)
       || ';' || CHR (10)|| 'show errors ; '     ddl_txt
  FROM all_objects
 WHERE object_type = 'VIEW' AND owner = 'myschema'
/
-- Etc ...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Calator]

tHAT SOLVED MY PROBLEM - FANTASTIC - MANY THANKS