Calling JAVA method to connect Database through JDBC URL from PLSQL by Pranay Tiwari

Recently I got a requirement in my project to form a connection with the different database servers and fetch data to display for further processing. 

To accomplish this requirement we have registered PLSQL package as a java source. 

You can find more details on the below link

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjdev/calling-Java-from-PL-SQL.html#GUID-499ABE6B-4391-43C8-A527-74A6C7B0A0FF

Registering PLSQL package as java source to form database connection JDBC. 

CREATE OR REPLACE AND COMPILE  JAVA SOURCE NAMED "pocJavaTest"

   AS import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class pocJavaTest

{

  public static String world()

  {

     String s1="";

   String driver = "oracle.jdbc.driver.OracleDriver";

    String SQLSTMT = 

                "SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME,STATUS FROM all_objects WHERE object_type IN ('SEQUENCE','PACKAGE','PACKAGE BODY','INDEX','TABLE') AND OBJECT_NAME LIKE '%EMP%'" ;

     Connection conn=null;

              try {

                Class.forName(driver);

                System.out.println(" SearchBtn  Class.forName(driver)");

            } catch (ClassNotFoundException e) {

                // TODO

                System.out.println("   Class.forName(driver)" + e);

            }

            try

            {

   conn = DriverManager.getConnection("jdbc:oracle:thin:@<hostName>:<portnum>/<servicename>","username","password");

  }

  catch (SQLException sqle)

  {

   System.out.println(" SQLException  Class.forName(driver)" + sqle);

  }

  if (conn!=null)

  {

  try{

    PreparedStatement pstmt;

         

                pstmt = conn.prepareStatement(SQLSTMT);

pstmt.setFetchSize(10000);

                ResultSet rs = pstmt.executeQuery();

                                while (rs.next()) 

                                {

                    System.out.println("Name= " + rs.getString(1));

                    s1=rs.getString(1);

                    }

                    }

                     catch (SQLException e) {

                     System.out.println(" ERROR in result set" + e);

           

                     }

     return "hello value is "+s1;     

  }

  else

  {

    return "pocJavaTest world";

    }

  }

};

/

SHOW ERR;

/

Once above PLSQL Procedure is compiled, now next step is to create a PLSQL function to access or call Java stored procedure

CREATE OR REPLACE

FUNCTION pocjavatestfun RETURN VARCHAR2 AS

LANGUAGE JAVA NAME 'pocJavaTest.world () return java.lang.String';

/

Once PLSQL Function is ready then write anonymous block or prepare SQL query to access the java stored procedure to display value

Anonymous Block

DECLARE

   my_string VARCHAR2(400 CHAR);

BEGIN

   my_string := apps.pocjavatestfun();

   dbms_output.put_line('value of ' || my_string);

END;

/

SQL query 

SELECT apps.pocjavatestfun() AS "Employees" FROM dual