2

I have the result of several CLOB, (about 100 Clob). I want to to extract all the data in the CLOB in one one script. I searched for spool but for now I didnt how to use it .

Example: select info from ex_employee where data <15

INFO
< CLOB >
< CLOB >
< CLOB >
< CLOB >
< CLOB >


those CLOB contain informations how can I spool them into a script example.sql?

My PROBLEM IS : How to export the CLOB data into .sql ?

I tried this way
Spool on
set heading off
Spool c:\spooltext.txt
select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables
/
Spool off

Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • @a_horse_with_no_name PL/SQL developer. I tried this way ------- Spool on set heading off Spool c:\spooltext.txt select dbms_metadata.get_ddl('TABLE', table_name) from user_tables / Spool off but its giving me errors – Moudiz Jul 03 '13 at 12:28
  • :Can you please update your question with what you tried? – Gaurav Soni Jul 03 '13 at 12:43
  • So what is the error? We are not mind readers. – OldProgrammer Jul 03 '13 at 12:50
  • @OldProgrammer you can re- read my question – Moudiz Jul 03 '13 at 12:53
  • @Moudiz :Please post the error when you execute the script – Gaurav Soni Jul 03 '13 at 12:56
  • @GauravSoni When I run it in sql windown it throw me an error: Invalid sql statement. IF I run it in command window, the query works fine but it display the half query ( create table abc ( v nmbr, .. and it stop here ) so I am searching for a way that I can extract the data – Moudiz Jul 03 '13 at 13:02

1 Answers1

2

Try this ,because it work for me in toad 

SET HEADING OFF
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF
SPOOL P:\other\file_name.sql

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name) || ';' FROM user_tables;

SPOOL OFF

And this works in command prompt too

Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72