Oracle sqldeveloper Export data is lack when I get mass of metadata

Question

Data lack is happen when I execute following query and export tsv from wizard.

--query

Select 
 view_name
 , replace(replace(dbms_metadata.get_ddl(‘VIEW’, view_name), ‘“‘,’’), concat(user, ‘.’),’’) as ddl
From user_views
order by view_name;

--result

"VIEW_NAME" | "DDL"
"V_EQUITY" |
" CREATE OR REPLACE FORECE EDITABLE VIEW...
…"
"V_MATERIAL" |
" CREATE OR REPLACE FORECE EDITABLE VIEW V_MATERIAL (XXXX,XXXXX,XXXXX,...
XXXX,....) AS
 SELECT
  XXXX
  ,XXXX
  ,XXXX
  ,XXXX
 FROM
  (SELECT * FROM XXXX) MT
  LEFT O"

-> Actually query is continued but some view is lacked. Maybe it is too long but I don't know the limit.

It is confusing it is no problem when I try following query and export tsv file.

--query select
view_name
, replace(replace(dbms_metadata.get_ddl(‘VIEW’, view_name), ‘“‘,’’), concat(user, ‘.’),’’) as ddl
from user_views where view_name = 'V_MATERIAL'
order by view_name;

user_1156452 · Answer

That returns a LOB, so you need to SET LONG such that the text doesn't get truncated.

Also, your code has SMART QUOTES in it..that won't work. They are less than helpful, thanks Microsoft.

clear screen
set long 1000

Select
view_name
, replace(replace(dbms_metadata.get_ddl('VIEW', view_name), '"',''), concat(user, '.'),'') as ddl
From user_views
where view_name = 'EMP_DETAILS_VIEW'
order by view_name;

And the output -

VIEW_NAME                                                                                                                        DDL                                                                             
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------
EMP_DETAILS_VIEW                                                                                                                                                                                                 
                                                                                                                                   CREATE OR REPLACE FORCE EDITIONABLE VIEW EMP_DETAILS_VIEW (EMPLOYEE_ID, JOB_ID
                                                                                                                                 , MANAGER_ID, DEPARTMENT_ID, LOCATION_ID, COUNTRY_ID, FIRST_NAME, LAST_NAME, SAL
                                                                                                                                 ARY, COMMISSION_PCT, DEPARTMENT_NAME, JOB_TITLE, CITY, STATE_PROVINCE, COUNTRY_N
                                                                                                                                 AME, REGION_NAME) DEFAULT COLLATION USING_NLS_COMP  AS                          
                                                                                                                                   SELECT                                                                        
                                                                                                                                   e.employee_id,                                                                
                                                                                                                                   e.job_id,                                                                     
                                                                                                                                   e.manager_id,                                                                 
                                                                                                                                   e.department_id,                                                              
                                                                                                                                   d.location_id,                                                                
                                                                                                                                   l.country_id,                                                                 
                                                                                                                                   e.first_name,                                                                 
                                                                                                                                   e.last_name,                                                                  
                                                                                                                                   e.salary,                                                                     
                                                                                                                                   e.commission_pct,                                                             
                                                                                                                                   d.department_name,                                                            
                                                                                                                                   j.job_title,                                                                  
                                                                                                                                   l.city,                                                                       
                                                                                                                                   l.state_province,                                                             
                                                                                                                                   c.country_name,                                                               
                                                                                                                                   r.region_name                                                                 
                                                                                                                                 FROM                                                                            
                                                                                                                                   employees e,                                                                  
                                                                                                                                   departments d,                                                                
                                                                                                                                   jobs j,                                                                       
                                                                                                                                   locations l,                                                                  
                                                                                                                                   countries c,                                                                  
                                                                                                                                   regions r                                                                     
                                                                                                                                 WHERE e.department_id = d.department_id                                         
                                                                                                                                   AND d.location_id = l.location_id                                             
                                                                                                                                   AND l.country_id = c.country_id                                               
                                                                                                                                   AND c.region_id = r.region_id                                                 
                                                                                                                                   AND j.job_id = e.job_id                                                       
                                                                                                                                 WITH READ ONLY

Or a picture :)

Oracle sqldeveloper Export data is lack when I get mass of metadata

1 Answers