How to copy external files with Java and PL/SQL wrappers

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
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;
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;
/