怎么在oracle查询后生成文本文件
发布时间:2010-05-20 14:01:29 文章来源:www.iduyao.cn 采编人员:星星草
如何在oracle查询后生成文本文件
一个查询之后怎么可以生成一个文本文件啊?
听说用shell脚本可以做到?那要怎么做呢?
------解决方案--------------------
用spool 就可以了。
把这个写到脚本里就可以了。 如:
spool D:test.xls
SQL 语句
spool off
------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
------解决方案--------------------
是将输出结果输出为文本,还是将查询语句输出为文本。
------解决方案--------------------
试试这个,需要看懂了修改
/*文件写*/
CREATE OR REPLACE PROCEDURE PrintTranscript (
/* Outputs a transcript to the indicated file for the indicated
student. The transcript will consist of the classes for which
the student is currently registered and the grade received
for each class. At the end of the transcript, the student's
GPA is output. */
p_StudentID IN students.ID%TYPE,
p_FileDir IN VARCHAR2,
p_FileName IN VARCHAR2) AS
v_StudentGPA NUMBER;
v_StudentRecord students%ROWTYPE;
v_FileHandle UTL_FILE.FILE_TYPE;
v_NumCredits NUMBER;
CURSOR c_CurrentClasses IS
SELECT *
FROM registered_students
WHERE student_id = p_StudentID;
BEGIN
-- Open the output file in append mode.
v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, 'a');
SELECT *
INTO v_StudentRecord
FROM students
WHERE ID = p_StudentID;
-- Output header information. This consists of the current
-- date and time, and information about this student.
UTL_FILE.PUTF(v_FileHandle, 'Student ID: %sn',
v_StudentRecord.ID);
UTL_FILE.PUTF(v_FileHandle, 'Student Name: %s %sn',
v_StudentRecord.first_name, v_StudentRecord.last_name);
UTL_FILE.PUTF(v_FileHandle, 'Major: %sn',
v_StudentRecord.major);
UTL_FILE.PUTF(v_FileHandle, 'Transcript Printed on: %snnn',
TO_CHAR(SYSDATE, 'Mon DD,YYYY HH24:MI:SS'));
UTL_FILE.PUT_LINE(v_FileHandle, 'Class Credits Grade');
UTL_FILE.PUT_LINE(v_FileHandle, '------- ------- -----');
FOR v_ClassesRecord in c_CurrentClasses LOOP
-- Determine the number of credits for this class.
SELECT num_credits
INTO v_NumCredits
FROM classes
WHERE course = v_ClassesRecord.course
AND department = v_ClassesRecord.department;
-- Output the info for this class.
UTL_FILE.PUTF(v_FileHandle, '%s %s %sn',
RPAD(v_ClassesRecord.department || ' ' ||
v_ClassesRecord.course, 7),
LPAD(v_NumCredits, 7),
LPAD(v_ClassesRecord.grade, 5));
END LOOP;
-- Determine the GPA.
CalculateGPA(p_StudentID, v_StudentGPA);
-- Output the GPA.
UTL_FILE.PUTF(v_FileHandle, 'nnCurrent GPA: %sn',
TO_CHAR(v_StudentGPA, '9.99'));
-- Close the file.
UTL_FILE.FCLOSE(v_FileHandle);
EXCEPTION
-- Handle the UTL_FILE exceptions meaningfully, and make sure
-- that the file is properly closed.
WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20061,
'PrintTranscript: Invalid Operation');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20062,
'PrintTranscript: Invalid File Handle');
WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20063,
'PrintTranscript: Write Error');
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20064,
'PrintTranscript: Invalid Mode');
WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20065,
'PrintTranscript: Internal Error');
友情提示:
信息收集于互联网,如果您发现错误或造成侵权,请及时通知本站更正或删除,具体联系方式见页面底部联系我们,谢谢。
其他相似内容:
-
orcale 把日期当做查询条件
根据日期查询范围
精确到天
select * from table where to_char( time,'yyyy mm dd ' ) <= '2000 ...
-
oracle decode函数和 sign函数
流程控制函数 DECODE
decode()函数简介:
主要作用:
将查询结果翻译成其他值(即以其他形式表现出来,以...
-
图说Oracle基础知识(一)
本文主要对Oralce数据库操作的基础知识进行一下梳理,以便进行归纳总结。适用于未使用过Oracle数据库的读者,...
-
DELETE操作出现:ORA-30036: 无法按 1024 扩展段 (在撤消表空间 'UNDOTBS_1' 中)
在测试过程中需要清空数据库中的几张表,...
-
.Net程序员学用Oracle系列(8):触发器、任务、序列、连接
《.Net程序员学用Oracle系列:导航目录》
本文大纲
1、触发器
1.1、创建...
-
Oracle12C相关
1.jar包安装到MVN本地库 mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0....
-
关于Oracle安装在多系统
我电脑有win7x86和XP双系统
其中win7在D盘安装有Oracle,数据库名为orcl
请问如何在xp中使用orcl
------...
-
oracle 触发器,序列,索引
oracle 触发器,序列,索引
1
2
3 --1,触发器 ----trigger
4 /*触发器是一种特殊的存储过程,它与数...
-
Oracle 设置表空间自增长
Oracle修改表空间大小
使用Oracle10g建立数据库后,向数据库中导入了部分数据,第二天继续向数据库中导入...
-
ORACLE分区表梳理系列(二)- 分区表日常维护及注意事项(红字需要留意)
版权声明:本文发布于http://www.cnblogs.com/yumiko/,版权由Yumik...