I have inherited an old Oracle 12c database, which I'm running on a local Oracle 19c server. The database contains the following Java Stored Procedure
create or replace and compile java source named fill_share_content as
import java.io.*;
import java.sql.*;
public class Fill_Share_Content
{
public static void execute(String directory) throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String separator = path.separator;
for(int i = 0; i < list.length; i++)
{
String filename = list[i];
File datei = new File( directory + separator + filename );
if ( datei.isFile() )
{
Timestamp filedate = new Timestamp( datei.lastModified() );
#sql { insert into Share_Content (filename, filedate) values (:filename, :filedate) };
}
}
}
};
/
The problem occurs when attempting to execute the statement to create and compile the Java SP: line 20
#sql { insert into Share_Content (filename, filedate) values (:filename, :filedate) };
throws an error
error: illegal character: '#'
Generally I am able to create, compile and execute Java Stored Procedures on the database. Questions:
The code is SQLJ and is documented here.
However, from Oracle 12.2, Oracle does not support running SQLJ.
You need to convert the SQLJ code to JDBC if you want to run it in Oracle 19.
This should give you a start:
CREATE AND COMPILE JAVA SOURCE NAMED fill_share_content as
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.OracleDriver;
public class Fill_Share_Content
{
public static void execute(String directory) throws SQLException
{
Connection con = null;
try {
OracleDriver ora = new OracleDriver();
con = ora.defaultConnection();
} catch (SQLException e) {
return;
}
Timestamp filedate = new Timestamp(System.currentTimeMillis());
PreparedStatement ps = con.prepareStatement(
"insert into Share_Content (filename, filedate) values (:filename, :filedate)"
);
ps.setString(1, directory);
ps.setTimestamp(2, filedate);
ps.execute();
}
};
/
db<>fiddle here