Very recently, we were migrating an application database running on
Oracle 11g (11.2.0.3) to Oracle 12.2 and found that one of the Java stored
procedure got into compilation error.
Here is the java-stored procedure that was working in Oracle 11g.
demo@ORA11G>
create global temporary table dir_list(
2
x varchar2(255) )
3 on
commit delete rows;
Table created.
demo@ORA11G>
create or replace and compile
2 java
source named "DirList"
3 as
4
5
import java.io.*;
6
import java.sql.*;
7
public class DirList
8
{
9 public static void
getList(String directory)
10 throws SQLException
11 {
12
13 File path = new File(
directory );
14 String[] list =
path.list();
15 String element;
16
17 for(int i = 0; i <
list.length; i++)
18 {
19 element = list[i];
20 #sql { INSERT INTO
DIR_LIST (x) VALUES (:element) };
21 }
22 }
23
}
24 /
Java created.
demo@ORA11G>
The above stored procedure failed in Oracle 12.2 like this.
c##rajesh@PDB1>
create or replace and compile
2 java
source named "DirList"
3 as
4
5
import java.io.*;
6
import java.sql.*;
7
public class DirList
8
{
9 public static void
getList(String directory)
10 throws SQLException
11 {
12
13 File path = new File(
directory );
14 String[] list =
path.list();
15 String element;
16
17 for(int i = 0; i <
list.length; i++)
18 {
19 element = list[i];
20 #sql { INSERT INTO
DIR_LIST (x) VALUES (:element) };
21 }
22 }
23
}
24 /
Warning: Java
created with compilation errors.
c##rajesh@PDB1>
show err
Errors for JAVA
SOURCE "DirList":
LINE/COL ERROR
--------
-----------------------------------------------------------------
0/0 An exception has occurred in the compiler
(1.8.0_181). Please
file a bug against the Java compiler
via the Java bug reporting
page (http://bugreport.java.com) after
checking the Bug Database
(http://bugs.java.com) for duplicates.
Include your program and
the following diagnostic in your
report. Thank you.
0/0 at
oracle.aurora.rdbms.Compiler.compile(Compiler.java:317)
0/0 at
com.sun.tools.javac.util.JCDiagnostic.<init>(JCDiagnostic.java:41
2)
0/0 at
com.sun.tools.javac.util.JCDiagnostic$Factory.create(JCDiagnostic
.java:238)
0/0 at
com.sun.tools.javac.util.JCDiagnostic$Factory.error(JCDiagnostic.
java:103)
0/0 at
com.sun.tools.javac.util.AbstractLog.error(AbstractLog.java:117)
0/0 at
com.sun.tools.javac.parser.JavaTokenizer.lexError(JavaTokenizer.j
ava:136)
0/0 at
com.sun.tools.javac.parser.JavaTokenizer.readToken(JavaTokenizer.
java:677)
0/0 at
com.sun.tools.javac.parser.Scanner.nextToken(Scanner.java:115)
0/0 at
com.sun.tools.javac.parser.JavacParser.nextToken(JavacParser.java
:301)
0/0 at
com.sun.tools.javac.parser.JavacParser.accept(JavacParser.java:48
9)
0/0 at
com.sun.tools.javac.parser.JavacParser.blockStatement(JavacParser
.java:2449)
0/0 at
com.sun.tools.javac.parser.JavacParser.blockStatements(JavacParse
r.java:2337)
0/0 at
com.sun.tools.javac.parser.JavacParser.block(JavacParser.java:230
8)
0/0 at
com.sun.tools.javac.parser.JavacParser.block(JavacParser.java:232
2)
0/0 at
com.sun.tools.javac.parser.JavacParser.parseStatement(JavacParser
.java:2480)
0/0 at
com.sun.tools.javac.parser.JavacParser.blockStatement(JavacParser
.java:2391)
0/0 at
com.sun.tools.javac.parser.JavacParser.parseStatementAsBlock(Java
cParser.java:2356)
0/0 at
com.sun.tools.javac.parser.JavacParser.parseStatement(JavacParser
.java:2513)
0/0 at
com.sun.tools.javac.parser.JavacParser.blockStatement(JavacParser
.java:2391)
0/0 at
com.sun.tools.javac.parser.JavacParser.blockStatements(JavacParse
r.java:2337)
0/0 at
com.sun.tools.javac.parser.JavacParser.block(JavacParser.java:230
8)
0/0 at
com.sun.tools.javac.parser.JavacParser.block(JavacParser.java:232
2)
0/0 at
com.sun.tools.javac.parser.JavacParser.methodDeclaratorRest(Javac
Parser.java:3586)
0/0 at
com.sun.tools.javac.parser.JavacParser.classOrInterfaceBodyDeclar
ation(JavacParser.java:3532)
0/0 at
com.sun.tools.javac.parser.JavacParser.classOrInterfaceBody(Javac
Parser.java:3436)
0/0 at
com.sun.tools.javac.parser.JavacParser.classDeclaration(JavacPars
er.java:3285)
0/0 at
com.sun.tools.javac.parser.JavacParser.classOrInterfaceOrEnumDecl
aration(JavacParser.java:3226)
0/0 at
com.sun.tools.javac.parser.JavacParser.typeDeclaration(JavacParse
r.java:3215)
0/0 at
com.sun.tools.javac.parser.JavacParser.parseCompilationUnit(Javac
Parser.java:3155)
0/0 at
com.sun.tools.javac.main.JavaCompiler.parse(JavaCompiler.java:676
)
0/0 at
com.sun.tools.javac.main.JavaCompiler.parseFiles(JavaCompiler.jav
a:1037)
0/0 at
com.sun.tools.javac.main.JavaCompiler.compile(JavaCompiler.java:9
26)
0/0 at
com.sun.tools.javac.main.JavaCompiler.compile(JavaCompiler.java:8
90)
0/0 at
oracle.aurora.jdkcompiler.JdkDriver.compile(JdkDriver.java:572)
0/0 at oracle.aurora.rdbms.Compiler.doCompile(Compiler.java:663)
0/0 at
oracle.aurora.rdbms.Compiler.access$000(Compiler.java:57)
0/0 at
oracle.aurora.rdbms.Compiler$1.run(Compiler.java:319)
0/0 at
java.security.AccessController.doPrivileged(Native Method)
0/0 java.lang.IllegalArgumentException
Server-side SQLJ code is not
supported with Oracle Database 12c release 2 (12.2): This applies to running
stored procedures, functions, and triggers inside the database environment.
However, a little rewrite of the code did that trick.
c##rajesh@PDB1>
create or replace and compile java source named "DirList" as
2
import java.io.*;
3
import java.sql.*;
4
public class DirList {
5
public static void getList(String directory)
6
throws SQLException {
7
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
8
String sql = "INSERT INTO dir_list (x) values (?)";
9
File path = new File(directory);
10
String[] list = path.list();
11
String element;
12
13
for (int i = 0; i < list.length; i++) {
14 element = list[i];
15
16 PreparedStatement pstmt =
conn.prepareStatement(sql);
17 pstmt.setString(1, element);
18
19 pstmt.executeUpdate();
20 pstmt.close();
21
}
22
}
23 }
24 /
Java created.
c##rajesh@PDB1>
create or replace procedure
2
get_dir_list( p_directory in varchar2)
3 as
language java
4 name
'DirList.getList(java.lang.String)';
5 /
Procedure
created.
c##rajesh@PDB1>
exec get_dir_list('/home/oracle');
BEGIN
get_dir_list('/home/oracle'); END;
*
ERROR at line
1:
ORA-29532: Java
call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission
("java.io.FilePermission" "/home/oracle" "read")
has not
granted to
C##RAJESH. The PL/SQL to grant this is dbms_java.grant_permission( 'C##RAJESH',
'SYS:java.io.FilePermission', '/home/oracle', 'read' )
ORA-06512: at
"C##RAJESH.GET_DIR_LIST", line 1
ORA-06512: at
line 1
c##rajesh@PDB1>
declare
2
x number;
3
begin
4
dbms_java.grant_permission(
5
grantee => user,
6
permission_type=> 'java.io.FilePermission',
7
permission_name=>'/home/oracle',
8
permission_action=>'read',
9
key=>x);
10 end;
11 /
PL/SQL
procedure successfully completed.
c##rajesh@PDB1>
exec get_dir_list('/home/oracle');
PL/SQL
procedure successfully completed.
c##rajesh@PDB1>
select * from dir_list where rownum <=3;
X
------------------------------
C1DECM_db_info.out
DALM1_db_info.out
C1DECM.env
c##rajesh@PDB1>
From all of us moving from 12.1 to 19c, thank you so, so, so much for solving this problem for us
ReplyDeleteThis post is so usefull and informative.Keep updating with more information...
ReplyDeletePython Courses In Mumbai
Python Course In Ahmedabad
Python Training In Cochin
Python Course In Trivandrum
Python Course In Kolkata
Very nice post. Thank you for sharing with us.
ReplyDeleteHow to make a paper airplane | Origami paper plane