LONG to VARCHAR2 - See text from a LONG field

See text from a LONG field

desc USER_MVIEWS

Name                   Type                                                                    

---------------------- ------------------------------------- 

OWNER                  VARCHAR2(30) 

MVIEW_NAME             VARCHAR2(30) 

CONTAINER_NAME         VARCHAR2(30) 

QUERY                  LONG         

CREATE TABLE TEMP_SNAPSHOTS AS 

SELECT mview_name, TO_LOB(query) query 

FROM USER_MVIEWS;

To see the SQL:

SELECT to_CHAR(query) FROM TEMP_SNAPSHOTS WHERE ROWNUM = 1;

Now can see the sql text

set termout off

set verify off

set newpage none

set echo off

set heading off

set pagesize 1000

set linesize 400

set feedback off

set trimspool on

COLUMN sql_str FORMAT A300 WORD_WRAP

SPOOL create_snapshots.sql

SELECT 'DROP MATERIALIZED VIEW '||mview_name||';'||CHR(10)||

       CHR(10)||

       'CREATE MATERIALIZED VIEW '||mview_name||' REFRESH FAST ON DEMAND AS'||CHR(10)||

       DBMS_LOB.SUBSTR(query,4000,1)

       ||';'||CHR(10)||CHR(10) as sql_str

FROM TEMP_SNAPSHOTS

WHERE mview_name <> 'GATEWAY_SS';

spool off

SPOOL create_snapshot_gateway_ss.sql

SELECT 'DROP MATERIALIZED VIEW '||mview_name||';'||CHR(10)||

       CHR(10)||

       'CREATE MATERIALIZED VIEW '||mview_name||' REFRESH FAST ON DEMAND AS'||CHR(10)||

       DBMS_LOB.SUBSTR(query,3000,1)

       ||CHR(10) as sql_str

FROM TEMP_SNAPSHOTS

WHERE mview_name = 'GATEWAY_SS';

SELECT 'DROP MATERIALIZED VIEW '||mview_name||';'||CHR(10)||

       CHR(10)||

       'CREATE MATERIALIZED VIEW '||mview_name||' REFRESH FAST ON DEMAND AS'||CHR(10)||

       DBMS_LOB.SUBSTR(query,6000,3001)

       ||';'||CHR(10)||CHR(10) as sql_str

FROM TEMP_SNAPSHOTS

WHERE mview_name = 'GATEWAY_SS';

spool off

SPOOL create_synonyms.sql

SELECT 'CREATE OR REPLACE SYNONYM '||synonym_name||' FOR '||table_name||';'||CHR(10)

       as sql_str

 FROM USER_SYNONYMS

WHERE table_name IN (SELECT mview_name FROM TEMP_SNAPSHOTS);

spool off

create_snapshots.sql - will have all the snapshot SQLs

create_snapshot_gateway_ss.sql - special case, where sql is too long.

create_synonyms - will have all thew synonyms for the snapshots