先修改init.ora
例如:
utl_file_dir=/usr //路径为 oracle所在的盘:/usr
例如:
utl_file_dir=/usr //路径为 oracle所在的盘:/usr
解决方案 »
- 创建ORACLE10G表空间问题
- 急求用存储过程实现一个业务查询问题,非常感谢!
- 将帖子提前
- sql实现按照序号奇数、偶数生成新结果集
- 有没有办法。解决这样的查询
- 一个很简单的问题:如何得到除法运算的后两位
- 如何将SQL SERVER2000库移入到Oracle8i(Oracle9i)呀?诚请高人指点
- 帮忙把SQL格式改为ORACLE格式
- 谁知道传说中<<ORACLE初学者指南>>的下载地址,请tell me.谢谢!
- 本地计算机上的ArcSde Sercice(esri_sde)服务启动后停止。某些服务未在由其他服务或进程使用时将自动停止。
- 两张大表关联合成一张表时索引问题请教
- 请问怎样将图片存入数据库?急啊~
REM Version 1.0, last updated 7/13/97
REM This procedure uses UTL_FILE to print student transcripts, as described in
REM Chapter 18 of _Oracle8 PL/SQL Programming_ by Scott Urman.CREATE OR REPLACE PROCEDURE PrintTranscript (
/* Outputs a transcript 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, 'w'); 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: %s\n',
v_StudentRecord.ID);
UTL_FILE.PUTF(v_FileHandle, 'Student Name: %s %s\n',
v_StudentRecord.first_name, v_StudentRecord.last_name);
UTL_FILE.PUTF(v_FileHandle, 'Major: %s\n',
v_StudentRecord.major);
UTL_FILE.PUTF(v_FileHandle, 'Transcript Printed on: %s\n\n\n',
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 %s\n',
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, '\n\nCurrent GPA: %s\n',
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 OTHERS THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE;
END PrintTranscript;
/
REM Version 1.0, last updated 7/13/97
REM This procedure uses UTL_FILE to print student transcripts, as described in
REM Chapter 18 of _Oracle8 PL/SQL Programming_ by Scott Urman.CREATE OR REPLACE PROCEDURE PrintTranscript (
/* Outputs a transcript 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, 'w'); 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: %s\n',
v_StudentRecord.ID);
UTL_FILE.PUTF(v_FileHandle, 'Student Name: %s %s\n',
v_StudentRecord.first_name, v_StudentRecord.last_name);
UTL_FILE.PUTF(v_FileHandle, 'Major: %s\n',
v_StudentRecord.major);
UTL_FILE.PUTF(v_FileHandle, 'Transcript Printed on: %s\n\n\n',
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 %s\n',
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, '\n\nCurrent GPA: %s\n',
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 OTHERS THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE;
END PrintTranscript;
/
REM Version 1.0, last updated 7/13/97
REM This procedure uses UTL_FILE to print student transcripts, as described in
REM Chapter 18 of _Oracle8 PL/SQL Programming_ by Scott Urman.CREATE OR REPLACE PROCEDURE PrintTranscript (
/* Outputs a transcript 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, 'w'); 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: %s\n',
v_StudentRecord.ID);
UTL_FILE.PUTF(v_FileHandle, 'Student Name: %s %s\n',
v_StudentRecord.first_name, v_StudentRecord.last_name);
UTL_FILE.PUTF(v_FileHandle, 'Major: %s\n',
v_StudentRecord.major);
UTL_FILE.PUTF(v_FileHandle, 'Transcript Printed on: %s\n\n\n',
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 %s\n',
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, '\n\nCurrent GPA: %s\n',
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 OTHERS THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE;
END PrintTranscript;
/
应该就可以了
应该就可以了
fid := UTL_FILE.FOPEN ('/abc', abc.txt', 'R');
grant create any directory to scott;
grant create any library to scott;
create or replace directory utllobdir as 'C:\ep';
utl_file包的使用,查一下oracle文档,很详细
utl_file_dir='C:\'