How to get the View definition without losing their alias names

 

If we use 

SELECT text FROM all_views WHERE view_name='ORG_ORGANIZATION_DEFINITIONS';

This will not have the alias names given while creating the view. TO avoid this, use the below statement to get the Original DDL statement 


SELECT dbms_metadata.get_ddl( 'VIEW', 'ORG_ORGANIZATION_DEFINITIONS', 'APPS') FROM dual;


Refer: DBMS_METADATA for more details on how to use the API and more options.


Comments

Post a Comment

Popular posts from this blog

Oracle WEB ADI Success And Error Counts Not Displayed Correctly

Image
Green Smiley Face Missing / Success Status If There Is An Error On One Of The Records Of Custom Integrators   I am working on Oracle Web ADI which is PL/SQL based.  When all the rows are successfully processed, Web ADI is showing green for all success records. When all rows are failed, Web ADI is showing red with error message for all the records. But If few rows are processed successfully and few are failed, there is no success icon for successfully processed records. To Solve this problem, Oracle has suggested a patch. Refer the Oracle doc ID:  1643524.1 After this patch is applied, I had to add a parameter bne:commitrows in custom integrator definition with two options -> All Rows and Each Row After adding this, issue is solved. 
Read more

Split CLOB content to lines (new line character as split character)

Below procedure is to split CLOB data into array of Lines separated by new line character.  DECLARE l_clob_data      CLOB; i                             NUMBER      :=0; BEGIN -- To get the view definition into clob SELECT DBMS_METADATA.get_ddl ('VIEW', 'ORG_ORGANIZATION_DEFINITIONS')  into l_clob_data from dual; FOR rec IN (WITH clob_table(c) as (SELECT l_clob_data c FROM DUAL),                    recurse(text,line) as (SELECT regexp_substr(c, '.+', 1, 1) text,1 line                                             FROM clob_table                                            UNION ALL     ...
Read more

How to fix the error "signer information does not match signer information of other classes in the same package"

In R12.2.0, when I try to compile java classes and generated customall.jar using adcgnjar utility. But swas getting  the below error.   java.lang.SecurityException: class xxx.oracle.apps.ibe.classname signer information does not match signer information of other classes in the same package Rootcause for this issue: There was a folder created under $OA_HTML/WEB-INF/classes/xxx.  Solution: Remove the folder xxx under   $OA_HTML/WEB-INF/classes and regenrate customall.jar. Restart oacore servers from weblogic server console. Application will be accessed without any issues.
Read more