比如我们准备把人员数据导出到excel;
1.建立一个目录
create directory utlfile_dir as 'd:\oracle\';
grant read,write on directory utlfile_dir to hr;
2.创建存储过程
create or replace procedure out_excel(dir in varchar2,
filename in varchar2) is
file utl_file.file_type;
cursor empc is
select last_name, salary, department_id
from employees
order by department_id;
begin
file := utl_file.fopen(dir, filename, 'w');
utl_file.put_line(file, 'report: generated on ' || sysdate);
utl_file.new_line(file);
utl_file.put_line(file,
'department_id' || chr(9) || 'name' || chr(9) ||
'salary');
FOR emp_rec IN empc LOOP
UTL_FILE.PUT_LINE(file,
nvl(emp_rec.department_id, '') || chr(9) ||
emp_rec.last_name || chr(9) || emp_rec.salary);
END LOOP;
UTL_FILE.PUT_LINE(file, '*** END OF REPORT ***');
UTL_FILE.FCLOSE(file);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid File.');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20002, 'Unable to write to file');
when utl_file.invalid_operation then
RAISE_APPLICATION_ERROR(-20003, 'file operate invalid');
END out_excel;
3.执行过程
execute sal_status(dir =>'DIR_FILE',filename => 'outexcel.xls');
之后,我们就可以看到outexcel.xls文件,直接使用excel打开即可.
总结:
问题的关键,就是使用 tab键让两个列分开.
OracleConnection OC1 = new OracleConnection(Easy2008.QuickDevelop.Configuration.ConfigurationManager.AppSettings["DataBaseConnectionString"].ToString());
try
{
string filename = "人员信息" + DateTime.Now.Ticks.ToString() + ".xls";//_rc_tbl_cjrxx_sp.Report("", _pr_tbl_maintable_sys_id, "tbl_cjrxx_ryxx", Page);
//自动删除文件
_rc_tbl_cjrxx_sp.AutoDeleteFile(this.Page);
OC1.Open();
OracleCommand orclCMD = new OracleCommand(); //初始化一个命令对象
orclCMD.Connection = OC1;
orclCMD.CommandText = "out_excel"; //存储过程名
orclCMD.CommandType = CommandType.StoredProcedure;//表面是存储过程
//如果创建存储过程是有变量,所以还要声明变量
OracleParameter IdIn1 = orclCMD.Parameters.Add("dir", OracleType.VarChar,200); //输入参数
OracleParameter IdIn2 = orclCMD.Parameters.Add("filename", OracleType.VarChar,200); //输入参数
OracleParameter IdIn3 = orclCMD.Parameters.Add("tbl_maintable_sys_id", OracleType.VarChar,200); //输入参数
IdIn1.Direction = ParameterDirection.Input;
IdIn2.Direction = ParameterDirection.Input;
IdIn3.Direction = ParameterDirection.Input;
IdIn1.Value = "UTLFILE_DIR";
IdIn2.Value = filename;
IdIn3.Value = _pr_tbl_maintable_sys_id;
//测试时,可直接写成 IdIn.Value = 2 这样的形式
//如果创建存储过程是还定义了输出变量
//OracleParameter NumTitles = orclCMD.Parameters.Add("file", OracleType.VarChar);//输出参数
//NumTitles.Direction = ParameterDirection.Output;
//测试执行存储过程影响的行数
orclCMD.ExecuteNonQuery();
orclCMD.Dispose();
OC1.Close();
// 注意获得参数的方式: testCMD.Parameters["sname"] ;这个是在存储过程中有输出的变量需要这样子,如果没有输出变量,可以不写。
//MessageBox.Show(testCMD.Parameters["sname"].Value.ToString());