Oracle Apps Technical

  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.

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     ...