设置SQLPLUS环境: COLUMN INTFTG FORMAT A7 COLUMN EXC FORMAT A4执行下面的主文件,会生成一个表名.cvs的文件文件 执行过程中会提示你输入表名。--主文件:ExportTable.sql accept TableName prompt 'Table to export:'set concat ~prompt Data file - &TableName~.csvspool &TableName~.sqlstart GetLoaderData &TableNamespool &TableName.csvstart &TableNamespool offhost del &TableName~.sqlrem host rm &TableName~.sqlset termout on --文件:GetLoaderData.sql --执行者: ExportTable.sql set pause off set newpage none set heading off set concat ~ set feedback off set verify off set linesize 1000 set trimspool on set trimout on set termout off column ord noprint select 0 ord, 'select',null,null,'rtrim('||column_name ||')' from user_tab_columns where table_name = upper('&&TableName') and column_id=1 union select column_id ord, '||' , ''';''' , '||' ,'rtrim('||column_name ||')' from user_tab_columns where table_name = upper('&TableName') and column_id > 1 union select 1000 ord, '||' , ''';''' , null, 'from &TableName order by 1;' from dual order by ord;
sql>spool test.txt sql>select * from tablename; sql>spool off
set pagesize 50000; set echo off;spool c:\test.txt; select rpad(intftg,7,' '),rpad(exc,4,' ') from tablename; spool off;
to wangybyangxr(王永斌): 你的ExportTable.sql文件写在什么位置啊? 多谢!
to wangybyangxr(王永斌): 你的ExportTable.sql文件写在什么位置啊? 多谢!你到时候指一下全路径就好了
COLUMN INTFTG FORMAT A7
COLUMN EXC FORMAT A4执行下面的主文件,会生成一个表名.cvs的文件文件
执行过程中会提示你输入表名。--主文件:ExportTable.sql
accept TableName prompt 'Table to export:'set concat ~prompt Data file - &TableName~.csvspool &TableName~.sqlstart GetLoaderData &TableNamespool &TableName.csvstart &TableNamespool offhost del &TableName~.sqlrem host rm &TableName~.sqlset termout on
--文件:GetLoaderData.sql
--执行者: ExportTable.sql
set pause off
set newpage none
set heading off
set concat ~
set feedback off
set verify off
set linesize 1000
set trimspool on
set trimout on
set termout off
column ord noprint
select 0 ord, 'select',null,null,'rtrim('||column_name ||')'
from user_tab_columns
where table_name = upper('&&TableName')
and column_id=1
union
select column_id ord, '||' , ''';''' , '||' ,'rtrim('||column_name ||')'
from user_tab_columns
where table_name = upper('&TableName')
and column_id > 1
union
select 1000 ord, '||' , ''';''' , null, 'from &TableName order by 1;'
from dual
order by ord;
sql>select * from tablename;
sql>spool off
set echo off;spool c:\test.txt;
select rpad(intftg,7,' '),rpad(exc,4,' ') from tablename;
spool off;
你的ExportTable.sql文件写在什么位置啊?
多谢!
你的ExportTable.sql文件写在什么位置啊?
多谢!你到时候指一下全路径就好了
或者,启动SQLPLUS后
执行:!CD 你的路径
OK!