MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Copy external files

with one comment

This demonstrates how to create Java libraries that let you copy a file from one virtual directory to another virtual directory.

1. Create virtual directories as SYS, SYSTEM, or privilege user:

SQL> CREATE DIRECTORY images AS 'C:\Data\Files\Images';
SQL> CREATE DIRECTORY text AS 'C:\Data\Files\Text';

2. Grant read and write permissions on the virutal directories:

SQL> GRANT READ, WRITE ON DIRECTORY images TO plsql;
SQL> GRANT READ, WRITE ON DIRECTORY text TO plsql;

3. Grant Java full permissions on the physical directories as the SYS user:

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('PLSQL'
                            ,'SYS:java.io.FilePermission'
                            ,'C:\Data\Files\Images\*'
                            ,'read,write,delete');
  DBMS_JAVA.GRANT_PERMISSION('PLSQL'
                            ,'SYS:java.io.FilePermission'
                            ,'C:\Data\Files\Text\*'
                            ,'read,write,delete');
END;
/

4. This creates a class with two methods. One copies a text file, and the other copies an image file. You must run the SQL*Plus SET DEFINE OFF command before running this code if you compile it at a SQL prompt because the “&&” (and operator) triggers a variable substitution process otherwise.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Copy" AS
  // Java library imports.
  import java.io.File;
  import java.io.IOException;
  import java.io.FileReader;
  import java.io.FileWriter;
  import javax.imageio.stream.FileImageInputStream;
  import javax.imageio.stream.FileImageOutputStream;
  import java.security.AccessControlException;
 
  // Class definition.  
  public class Copy
  {
    // Define variable(s).
    private static int c;
    private static File file1,file2;
    private static FileReader inTextFile;
    private static FileWriter outTextFile;
    private static FileImageInputStream inImageFile;
    private static FileImageOutputStream outImageFile;
 
    // Define copyText() method.
    public static int copyText(String fromFile,String toFile) throws AccessControlException
    {
      // Create files from canonical file names.
      file1 = new File(fromFile);
      file2 = new File(toFile);
 
      // Copy file(s).
      try
      {
        // Define and initialize FileReader(s).
        inTextFile  = new FileReader(file1);
        outTextFile = new FileWriter(file2);
 
        // Delete older file when present.
        if (file2.isFile() && file2.delete()) {}
 
        // Read character-by-character.
        while ((c = inTextFile.read()) != -1) {
          outTextFile.write(c); }
 
        // Close Stream(s).
        inTextFile.close();
        outTextFile.close(); }
      catch (IOException e) {
        return 0; }
    return 1; }
 
    // Define copyImage() method.
    public static int copyImage(String fromFile,String toFile) throws AccessControlException
    {
      // Create files from canonical file names.
      file1 = new File(fromFile);
      file2 = new File(toFile);
 
      // Copy file(s).
      try
      {
 
      // Define and initialize FileReader(s).
      inImageFile  = new FileImageInputStream(file1);
      outImageFile = new FileImageOutputStream(file2);
 
      // Delete older file when present.
      if (file2.isFile() && file2.delete()) {}
 
      // Read character-by-character.
      while ((c = inImageFile.read()) != -1) {
        outImageFile.write(c); }
 
      // Close Stream(s).
      inImageFile.close();
      outImageFile.close(); }
    catch (IOException e) {
      return 0; }
    return 1; }}
/

5. These creates wrapper functions to the two methods:

CREATE OR REPLACE FUNCTION copy_text_file
(from_file VARCHAR2, to_file VARCHAR2) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'Copy.copyText(java.lang.String,java.lang.String) return java.lang.int';
/
CREATE OR REPLACE FUNCTION copy_image_file
(from_file VARCHAR2, to_file VARCHAR2) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'Copy.copyImage(java.lang.String,java.lang.String) return java.lang.int';
/

If you raise the following type of error, it is likely that the quote is not an ordinary ASCII value.

PLS-00103: Encountered the symbol
"Copy.copyText(java.lang.String,java.lang.String) return
java.lan" WHEN expecting one OF the following:
<a single-quoted SQL string>

6. This tests the copy of the image file (you can find the get_canonical_local_bfilename function here):

DECLARE
  file1 BFILE := BFILENAME('IMAGES','Raiders3.png');
  file2 BFILE := BFILENAME('TEXT','CopyOfRaiders3.png');
BEGIN
  IF copy_image_file(get_canonical_local_bfilename(file1)
                    ,get_canonical_local_bfilename(file2)) = 1 THEN
    DBMS_OUTPUT.put_line('It Worked');
  END IF;
END;
/

7. This tests the copy of the text file:

DECLARE
  file1 BFILE := BFILENAME('TEXT','Raiders3.txt');
  file2 BFILE := BFILENAME('IMAGES','CopyOfRaiders3.txt');
BEGIN
  IF copy_text_file(get_canonical_local_bfilename(file1)
                   ,get_canonical_local_bfilename(file2)) = 1 THEN
    DBMS_OUTPUT.put_line('It copied the file!');
  END IF;
END;
/

Written by maclochlainn

July 29th, 2008 at 6:22 am

One Response to 'Copy external files'

Subscribe to comments with RSS or TrackBack to 'Copy external files'.

  1. Thank you, very easy to follow.

    Mark

    21 Mar 12 at 7:16 am

Leave a Reply