Java in the database

Bit of java to run in the database…. quick instructions

[oracle@collabn1 java]$ cat status.java

import java.sql.*;
public class status {
public void printSuccess() {
System.out.println("Success!!!")
}
public void printdatafiles() {
System.out.println("Might do something here!");
}

public static void main(String[] args)
throws ClassNotFoundException, SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
// jdbc:oracle:thin:@host:port/service
String url = "jdbc:oracle:thin:@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK:1521/LONDON_SVC.RACATTACK";
Connection conn =
DriverManager.getConnection( "jdbc:oracle:thin:@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK", "system", "racattack");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery("select BANNER from SYS.V_$VERSION");
while (rset.next()) { System.out.println (rset.getString(1)); }
stmt.close();
System.out.println (" Global Name");
Statement stmt2 = conn.createStatement();
ResultSet rset2 =
stmt2.executeQuery("select * from global_name");
while (rset2.next()) { System.out.println (rset2.getString(1)); }
stmt2.close();
System.out.println (" Datafiles");
Statement stmt3 = conn.createStatement();
ResultSet rset3 =
stmt3.executeQuery("select * from dba_data_files");
while (rset3.next()) { System.out.println (rset3.getString(1)); }
stmt3.close();
status mystatus=new status();
mystatus.printSuccess();
mystatus.printdatafiles();
}
}

-tell java where the classes live, i.e. status and odbjc oracle connection class

[oracle@collabn1 java]$ env | grep CLASS

CLASSPATH=/u01/jdk1.8.0_144/jre/lib/ext:/home/oracle/java

Run java compiler to compile the java code

[oracle@collabn1 java]$ /u01/jdk1.8.0_144/bin/javac status.java

And execute it

[oracle@collabn1 java]$ /u01/jdk1.8.0_144/bin/java status

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Global Name

LONDON

Datafiles

+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/system.317.938518113
+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/sysaux.316.938518113
+DATA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/users.285.939218541

Success!!!

Might do something here!

[oracle@collabn1 java]$

[oracle@collabn1 java]$ . oraenv

ORACLE_SID = [oracle] ? MADRID

The Oracle base has been set to /u01/app/oracle

[oracle@collabn1 java]$ loadjava -u system/racattack@collabn-cluster scan.racattack:1521/LONDON_SVC.RACATTACK status.java

[oracle@collabn1 java]$ sqlplus system/racattack@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 13:47:50 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Aug 07 2017 13:47:26 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> create or replace procedure status -- or any name you want
as language java

name 'status.main(java.lang.String[])';

/

Procedure created.

SQL> > select text from user_source where name = 'status.java';

SQL> set head off

SQL> /

import java.sql.*;
public class status {
....
etc

80 rows selected.

SQL> exec status;

BEGIN status; END;

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception:

java.security.AccessControlException: the Permission (java.net.SocketPermission

192.168.78.253:1521 connect,resolve) has not been granted to SYSTEM. The PL/SQL
to grant this is dbms_java.grant_permission( 'SYSTEM',

'SYS:java.net.SocketPermission', '192.168.78.253:1521', 'connect,resolve' )

ORA-06512: at "SYSTEM.STATUS", line 1

ORA-06512: at line 1

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

[oracle@collabn1 ~]$ tnsping MADRID

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-AUG-2017 14:42:54

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=collabn-cluster-scan.racattack)(PORT = 1525)) (CONNECT_DATA = (SID= MADRID1)))

TNS-12541: TNS:no listener

[oracle@collabn1 ~]$ sqlplus system/racattack@collabn-cluster-scan.racattack:1521/LONDON_SVC.RACATTACK

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 14:43:03 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Aug 07 2017 14:19:01 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> exec dbms_java.grant_permission( 'SYSTEM','SYS:java.net.SocketPermission','colabn-cluster-scan.racattack:1521', 'connect,resolve' )

PL/SQL procedure successfully completed.

NOTE from the error above:

exec dbms_java.grant_permission( 'SYSTEM', 'SYS:java.net.SocketPermission', '192.168.78.253:1521', 'connect,resolve' )

SQL> commit;

Commit complete.

SQL> exec status();

PL/SQL procedure successfully completed.

-no output so switch serveroutput on!!

SQL> set serveroutput on;

SQL> call dbms_java.set_output(50000);

Call completed.

SQL> exec status();

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Global Name

LONDON

Datafiles

+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/system.317.938518113
+FRA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/sysaux.316.938518113
+DATA/MADRID/4A9C024E8375174BE055000000000001/DATAFILE/users.285.939218541

Success!!!

Might do something here!

PL/SQL procedure successfully completed.

Et voilla….!!!!!!!