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:

1. What is the meaning of the line starting with the hash sign? I am not familiar with such a construct in Java, something specific to Java stored procedures in Oracle?
2. How can I get I create and compile the Java stored procedure? Allegedly the code is already running on another instance, so the code should work.

CodePudding user response：

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