Skip to main content

Oracle: source text for the view, package and other objects


The best method is to use dbms_metadata package:

SELECT dbms_metadata.get_ddl(UPPER('&OBJ_TYPE'),UPPER('&OBJ_NAME'),UPPER('&OWNER'))
FROM dual;
Additional methods could be also used:
Source text of the package
SELECT text
FROM dba_source
WHERE UPPER(name) LIKE UPPER('&which_object')
ORDER BY line ;
Use $ORACLE_HOME/bin/wrap utility to encrypt the package (there is no unwrap)
(Well, actually, there is unwrap – just look in the search machine for the words unwrap10 or rewrap…)
Source text of the views
SET LONG 5000
col text 
FOR a80

SELECT text FROM dba_views WHERE view_name = UPPER('&which_view');
SELECT query FROM DBA_mviews WHERE mview_name = UPPER('&which_view');
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='&which_view';
Source text of the synonym
SELECT
TABLE_OWNER || 
'.' || TABLE_NAME
|| 
DECODE ( db_link , NULL , '' , '@' || db_link ) SYNONYM_OBJECT
FROM dba_synonyms
WHERE SYNONYM_NAME = UPPER('&which_synonym');
Source text of the trigger

SELECT
 
'create or replace trigger "' || trigger_name || '"'
  || 
CHR(10)|| DECODESUBSTR( trigger_type, 11 ), 'A''AFTER''B''BEFORE''I''INSTEAD OF' )
  || 
CHR(10) || triggering_event || CHR(10) || 'ON "' || table_owner || '"."' || table_name
  || 
'"' || CHR(10) || DECODEINSTR( trigger_type, 'EACH ROW' ), 0NULL'FOR EACH ROW' )
  || 
CHR(10) ,
  trigger_body
FROM dba_triggers
WHERE trigger_name = UPPER('&which_trigger')  AND owner=UPPER('&trigger_owner');

Comments

Popular posts from this blog

SQL Query to extract Oracle Purchase Order Information

SELECT   poh.po_header_id,    poh.type_lookup_code PO_TYPE,   poh.authorization_status PO_STATUS,   poh.segment1 PO_NUMBER,   pov.vendor_name SUPPLIER_NAME,   povs.vendor_site_code Location,   hrls.location_code Ship_To,   hrlb.location_code Bill_to,   pol.line_num ,   msib.segment1 Item,   pol.unit_price,   pol.quantity,   pod.amount_billed Amount,   pod.destination_subinventory,   ppf.full_name Buyer_Name,   poh.closed_Code  FROM   PO_HEADERS_ALL poh,   PO_LINES_ALL pol,   mtl_system_items_b msib,   PO_LINE_LOCATIONS_ALL poll,   PO_DISTRIBUTIONS_ALL pod,   po_vendors pov,   po_vendor_sites_All povs,   hr_locations_all hrls,   hr_locations_all hrlb,   per_all_people_f ppf,   po_line_types polt WHERE   1                         =1 AND polt.line...

Query to find Operating Unit, Business Group and Legal Entity Information

SELECT   DISTINCT   hrl . country ,                  hroutl_bg . name              bg ,                  hroutl_bg . organization_id ,                  lep . legal_entity_id ,                  lep . name                    legal_entity ,                  hroutl_ou . name              ou_name ,               ...

How To Enable / Disable Forms Personalization Option

Forms Personalization gives great flexibility to execute custom business logic without performing so much of technical work. To start forms personalization navigate to Help -> Diagnostics -> Custom Code -> Personalize But many time when we click on personalize it give below error  " Function is not available for this respnosibility. Change responsibilities or contact your System Administrator " To Enable access to forms personalization function we need to set below profile option.  -  Utilities:Diagnostics -> Yes / No It determines the diagnostics option is enabled for a user / responsibility or site, depending on the level profile option is set. Navigate to System Administrator -> Profile -> System Query for your user / responsibility for which you want to provide access. Set the value to 'Yes' , If you want allow access to forms personalization Since we change the profile option please change the respons...