Friday, April 5, 2019

Queries to get Source code from Database DBMS_METADATA.GET_DDL, DBA_SOURCE, DBA_VIEWS













             In this post, we try to give queries useful for any developer. We always encounter the situation to download the source code from production database for enhancement or debugging purpose. Here is the list of queries useful for you. 



Packages, Procedures, Functions: 

SELECT
    replace(
        replace(
            TRIM(CHR(10) FROM text),
            CHR(9),
            '    '
        ),
        'PACKAGE',
        'CREATE OR REPLACE PACKAGE'
    )
FROM
    dba_source
WHERE
    name = 'XXSH_PR_INTF_PKG'
ORDER BY type,line;

 Views:

SELECT 'CREATE OR REPLACE VIEW '||view_name||CHR(10)||' AS', text 
  FROM all_views 
 WHERE view_name = 'XXPO_PR_DATA_V';


 For Any Database Object: 

SELECT DBMS_METADATA.get_ddl ('PACKAGE', 'XX_BPEL_EBS11I10ATPCHECK')  
  FROM sys.dual;

SELECT DBMS_METADATA.get_ddl ('<type>', '<object_name>')  
  FROM sys.dual;


Hope this Helps!!!
 

0 Responses to “Queries to get Source code from Database DBMS_METADATA.GET_DDL, DBA_SOURCE, DBA_VIEWS”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search