ORACLE导出数据为文本格式和EXCEL格式怎么导啊？

Question

[Q]怎么样把查询内容输出到文本
[A]用spool如
如SQL>set heading off
SQL>set feedback off
SQL>spool temp.txt
SQL>select * from tab;
SQL>spool off

Answer 1

先谢谢 hevin(没有什么是不可能的) 的热情回答，temp.txt的路径是什么呢？如果我想导出到客户机上行吗？如果不行那么temp.txt在服务器上的什么路径，我能制定它的路径吗？

Answer 2

用PL/SQL Developer
很方便的

Answer 3

我还写过一个程序
你有兴趣可以用用#!/usr/bin/perl -w
#function:            select records from table and print to Excel file
#author:              atgc
#version:             1.0
#date of compilation  September 6,2004#you can change the following parameters
my $conn_str = '连接名';
my $user = 'user';
my $pass = 'password';
my $excel_file = 'e:\output.xls';
my $sql  = "select * from table";use strict;
use DBI;
use Win32::OLE;
my($dbh,$sth,$row,$col,@field,$ele,$c_times,$residual,$cols,$cell_end);
unlink $excel_file if (-e $excel_file);
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->add;
my $Sheet = $Book->Worksheets(1);
my @array_cols=("","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
$dbh = DBI->connect("DBI:Oracle:$conn_str",$user,$pass);
$sth = $dbh->prepare($sql);
$sth->execute();
$row=1;
$cols=0;
if (@field = $sth->fetchrow)
{
$cols=scalar(@field);
$c_times=int($cols/26);
$residual=$cols%26;
if ($cols<27)
{
$cell_end = $array_cols[$cols];
}
else
{
if ($residual == 0)
{
$cell_end = $array_cols[$c_times-1]."Z";
}
else
{
$cell_end = $array_cols[$c_times].$array_cols[$residual];
}
}
$Sheet->Range("A1:$cell_end$row")->{Value} = [@field];
while(@field = $sth->fetchrow)
{
$row++;
$Sheet->Range("A$row:$cell_end$row")->{Value} = [@field];
}
$sth->finish();
$dbh->disconnect();
$Book->SaveAs($excel_file);
}
undef($Sheet);
undef($Book);
undef($Excel);

Answer 4

ATGC(想到南极去看看飞翔的海鸥) 用的C++.net吧，我习惯用C#.net请问怎么做呢？

Answer 5

你用PL/SQL Developer号了
很方便的
new -> REPORT WINDOW -> 执行SQL 语句->SAVE AS EXCEL 或者 TSV(文本)等

Answer 6

http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html

Answer 7

我想问一下用java怎么在oracle中读取excel中的内容呢

Answer 8

temp.txt可以带路径，默认的应该是你运行sqlplus时所在的路径。如果你想输出到另一台机器（客户端）上，你可以把另一台机器的文件夹共享，然后在本机上作映射，你试试能不能输出到映射盘上，Sql server是不行的，我不知道oracle支不支持这样做，我现在没环境试。
另外，关于输出到Excel,你可以看看下面这篇文章，个人觉得效果不错： 
http://www.soft6.com/know/detail.asp?id=BBDAIB

Answer 9

在sqlplus里用spool并不能得到真正的EXCEL文件
java读取EXCEL可以用POI包
我写了一个例子，供参考//Function Read data from Excel
//Author ATGC
//Date of compilation Oct 29,2004import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow; 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import java.io.FileInputStream;
public class  xls2table
{
public  static  String  fileToBeRead="e://test.xls";
public  static  void  main(String  argv[])
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
HSSFSheet    sheet    = workbook.getSheet("Sheet1");
int rows  =  sheet.getPhysicalNumberOfRows();
                     for (int r = 0; r < rows; r++)
                     {
HSSFRow row = sheet.getRow(r);
if (row != null)
{
int cells = row.getPhysicalNumberOfCells();
                                String   value = "";
                                System.out.println(cells);
for (short c = 0; c < cells; c++) 
                             {
                                 HSSFCell cell  = row.getCell(c);
                                 if (cell != null)
                                 {
switch (cell.getCellType())
                        {
                                         case HSSFCell.CELL_TYPE_FORMULA :
                                               //strCell = String.valueOf(aCell.getNumericCellValue());
                          //returnstr+=strCell+" ";
                                                break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
      value += (long)cell.getNumericCellValue()+"\t";
      break;
                                case HSSFCell.CELL_TYPE_STRING:
                                value += cell.getStringCellValue()+"\t";
                                break;
                   case HSSFCell.CELL_TYPE_BLANK://blank
                     //strCell = aCell.getStringCellValue();
                     //returnstr+=strCell+" ";
                     break;                        default:
                        value +="\t";
                                      }
                                      }
                                 }
                                 //下面可以将查找到的行内容用SQL语句INSERT到oracle
                                 System.out.println(value);
                                 //
                         }
                 }
}catch(Exception  e)
{System.out.println(e);}
}
}