spool generar archivo plano

CREATE OR REPLACE PROCEDURE export_tables (p_schema IN VARCHAR2)

IS

  v_sql VARCHAR2(4000);

BEGIN

  FOR r_table IN (SELECT table_name FROM all_tables WHERE owner = p_schema) LOOP

    spool '/home/oracle/' || r_table.table_name || '.txt';

    v_sql := 'SELECT * FROM ' || p_schema || '.' || r_table.table_name;

    EXECUTE IMMEDIATE v_sql;

    spool off;

  END LOOP;

END;

/

set linesize 1000

set pagesize 0

set colsep '|'

set trimspool on

set feedback off

set heading off

DECLARE

  v_sql VARCHAR2(4000);

BEGIN

  FOR r_table IN (SELECT table_name FROM all_tables WHERE owner = 'HR') LOOP -- Reemplazar 'HR' con el nombre del esquema deseado

    spool '/home/oracle/' || r_table.table_name || '.txt';

    v_sql := 'SELECT * FROM ' || r_table.table_name;

    EXECUTE IMMEDIATE v_sql;

    spool off;

  END LOOP;

END;

/

set heading on

set feedback on

exit;

//////////////////////////////////////

set line 100

set heading off

set feedback off

spool /home/oracle/empleados.txt

select * from hr.employees

/

spool off

set heading on

set feedback on

exit

alter session set current_schema=HR;







set echo off

set verify off

set heading off

set feedback off

set pagesize 0

spool /home/oracle/create_table.sql

select 'CREATE TABLE ' || TABLE_NAME || '_TEST ' || 'AS SELECT * FROM ' || TABLE_NAME || ' WHERE 1=2;' 

from user_tables

/

spool off

set echo off

set verify off

set feedback off

set pagesize 0

spool /home/oracle/drop_table.sql

select 'DROP TABLE ' || TABLE_NAME || ';'

from user_tables

where table_name like '%_TEST'

/

spool off

set heading on

set feedback on

exit