Hi Experts,
I am trying to extract view definitions of views for which I have managed the script below so far
#!/bin/bash
cat "VIEW_LIST.txt" | while read ObjectRec
do
filename="/home/oracle/VIEWS/$ObjectRec.sql"
echo $filename
sqlplus -s userid/passwrd << ENDSQL 1>$filename 2>&1
set sqlprompt ''
set sqlnumber off
set serveroutput on size 1000000
SET FEEDBACK OFF
set linesize 32767 WRAP ON
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 200
SET long 50000
set lines 3174
col txt for a3174
select dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,1) piece1,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,4001) piece2,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,8001) piece3,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,12001) piece4,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,16001) piece5,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,20001) piece6,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,24001) piece7,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,28001) piece8,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,32001) piece9,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,36001) piece10,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,40001) piece11,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,44001) piece12,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,48001) piece13,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,52001) piece14,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,56001) piece15,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,60001) piece16,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),4000,64001) piece17,
dbms_lob.substr(DBMS_METADATA.GET_DDL('VIEW', view_name, owner),3999,68001) ||'/' DDL from dba_views
where view_name = '$ObjectRec';
ENDSQL
done
The problem with the script above is for some of the view still I am getting partial definition even for views which when I run DBMS_LOB.GETLENGTH function I get the output as 21k.
when I just run query below I am getting error "ORA-24813: cannot send or receive an unsupported LOB" so I have to use dbms_lob.substr function.
select dbms_metadata.get_ddl('VIEW','view_name','owner') from dual;
Limitation I have is - I can access database only through sqlplus session in putty.