Use UTL_FILE to print a transacript to a file. : UTL_FILE « System Packages « Oracle PL / SQL

Oracle PL / SQL
1. Aggregate Functions
2. Analytical Functions
3. Char Functions
4. Constraints
5. Conversion Functions
6. Cursor
7. Data Type
8. Date Timezone
9. Hierarchical Query
10. Index
11. Insert Delete Update
12. Large Objects
13. Numeric Math Functions
14. Object Oriented Database
15. PL SQL
16. Regular Expressions
17. Report Column Page
18. Result Set
19. Select Query
20. Sequence
21. SQL Plus
22. Stored Procedure Function
23. Subquery
24. System Packages
25. System Tables Views
26. Table
27. Table Joins
28. Trigger
29. User Previliege
30. View
31. XML
Java
Java Tutorial
Java Source Code / Java Documentation
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Oracle PL / SQL » System Packages » UTL_FILE 
Use UTL_FILE to print a transacript to a file.
  

CREATE TABLE lecturer (
  id               NUMBER(5PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10001'Scott', 'Lawson','Computer Science', 11);

INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
              VALUES (10002'Mar', 'Wells','History', 4);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10003'Jone', 'Bliss','Computer Science', 8);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10004'Man', 'Kyte','Economics', 8);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10005'Pat', 'Poll','History', 4);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10006'Tim', 'Viper','History', 4);

CREATE TABLE myStudent (
  student_id NUMBER(5NOT NULL,
  department CHAR(3)   NOT NULL,
  course     NUMBER(3NOT NULL,
  grade      CHAR(1)
  );




INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000'CS', 102'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10002'CS', 102'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10003'CS', 102'C');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000'HIS', 101'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10001'HIS', 101'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10002'HIS', 101'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10003'HIS', 101'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10004'HIS', 101'C');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10005'HIS', 101'C');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10006'HIS', 101'E');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10007'HIS', 101'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10008'HIS', 101'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10009'HIS', 101'D');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10010'HIS', 101'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10008'NUT', 307'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10010'NUT', 307'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10009'MUS', 410'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10006'MUS', 410'E');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10011'MUS', 410'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000'MUS', 410'B');


CREATE OR REPLACE PROCEDURE PrintTranscript (
  p_ID IN lecturer.ID%TYPE,
  p_FileDir IN VARCHAR2,
  p_FileName IN VARCHAR2AS

  myLecturerGPA NUMBER;
  myLecturerRecord  lecturer%ROWTYPE;
  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NumCredits NUMBER;

  CURSOR c_CurrentClasses IS
    SELECT *
      FROM myStudent
      WHERE student_id = p_ID;

BEGIN
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, 'a');

  SELECT *
    INTO myLecturerRecord
    FROM lecturer
    WHERE ID = p_ID;

  UTL_FILE.PUTF(v_FileHandle, 'Student ID: %s\n', myLecturerRecord.ID);
  UTL_FILE.PUTF(v_FileHandle, 'Student Name: %s %s\n', myLecturerRecord.first_name, myLecturerRecord.last_name);
  UTL_FILE.PUTF(v_FileHandle, 'Major: %s\n', myLecturerRecord.major);
  UTL_FILE.PUTF(v_FileHandle, 'Transcript Printed on: %s\n\n\n', TO_CHAR(SYSDATE, 'Mon DD,YYYY HH24:MI:SS'));

  UTL_FILE.FCLOSE(v_FileHandle);

EXCEPTION
  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');
END PrintTranscript;
/

drop table lecturer;

drop table myStudent;


   
  
Related examples in the same category
1. Writing "Hello World!" to a File
2. Use UTL_FILE.PUT_LINE to write a line of text to a file
3. Use utl_file.fopen to open a file
4. Use UTL_FILE.FCLOSE to close a file
5. Use UTL_FILE to read from a file and insert into the lecturer table.
6. Catch different UTL_FILE related errors
7. Save data in a cursor to a file
8. Load the data into table myTable. You may use any valid number for column c1's data.
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.