Home » RDBMS Server » Server Administration » Capture DDL using DBMS_METADATA.GET_DDL
Capture DDL using DBMS_METADATA.GET_DDL [message #225050] Fri, 16 March 2007 15:01 Go to next message
swhaley
Messages: 1
Registered: March 2007
Junior Member
I am trying to capture the DDL for a procedure from a SQL*Plus job. It works however it removes any whitespace on the left.
making this:
IF SOMETHING
   DO THIS;
ELSE
   DO THAT;
END IF;


look like this:
IF SOMETHING
DO THIS;
ELSE
DO THAT;
END IF;

Does anyone know how to fix this?
_________________________________________________________________
ACCEPT Source_Schema CHAR PROMPT 'Source Schema: ' 
ACCEPT Object_Name CHAR PROMPT 'Object Name: ' 

SET PAGES 0
SET TRIMS ON
SET LONG 2000000
SET VERIFY OFF
SET LINESIZE 1000
SET TERMOUT OFF
SET FEEDBACK OFF

column ddl format a200 word_wrapped

connect username/password@SID

spool &&Object_Name..sql

SELECT DBMS_METADATA.GET_DDL('PROCEDURE',UPPER('&&Object_Name'), UPPER('&&Source_Schema')) || '/' ddl from dual;

SPOOL OFF
Re: Capture DDL using DBMS_METADATA.GET_DDL [message #225054 is a reply to message #225050] Fri, 16 March 2007 15:50 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It works as-is.
So it has to do with your formatting.
scott@9i > create or replace procedure foobar
  2  as
  3  begin
  4     null;
  5     if something > nothing
  6             then 
  7                     dbms_output.put_line('Hello World!');
  8     end if;
  9  end;
 10  /

Warning: Procedure created with compilation errors.

scott@9i > set long 5000
scott@9i > column xxx format a90 
scott@9i > SELECT DBMS_METADATA.GET_DDL ('PROCEDURE','FOOBAR')||';' as xxx from dual;

XXX
------------------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "SCOTT"."FOOBAR"
as
begin
        null;
        if something > nothing
                then
                        dbms_output.put_line('Hello World!');
        end if;
end;
 ;

[Updated on: Fri, 16 March 2007 15:52]

Report message to a moderator

Previous Topic: Oracle licenses
Next Topic: can we encrypt the RMAN backup with netbackup
Goto Forum:
  


Current Time: Fri Sep 20 04:02:01 CDT 2024