--------------------------------------------------------------------------------
--
-- File name:   ddl.sql
-- Purpose:     Extracts DDL statements for specified objects
--
--
-- Usage:       @ddl [schema.]<object_name_pattern>
--          @ddl mytable
--          @ddl system.table
--              @ddl sys%.%tab%
--
--------------------------------------------------------------------------------


EXEC dbms_metadata.set_transform_param( dbms_metadata.session_transform,'SQLTERMINATOR', TRUE);

SELECT
    dbms_metadata.get_ddl( decode(object_type
        , 'PACKAGE BODY', 'PACKAGE_BODY'
        , 'DATABASE LINK', 'DB_LINK'
        , 'JOB', 'PROCOBJ'
        , object_type), object_name, owner ) DDL
FROM
    all_objects
WHERE
    UPPER(object_name) LIKE
                UPPER(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     )
AND owner LIKE
        CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            USER
        END
/