Home » SQL & PL/SQL » SQL & PL/SQL » Calling Java function from pl/sql (Oracle 11g)
icon9.gif  Calling Java function from pl/sql [message #673282] Wed, 14 November 2018 04:32
dineshkumar18
Messages: 28
Registered: September 2016
Junior Member
Dear All,

I have created a java code for SFTP purpose and Java code is working fine as i have tested the same from my local machine.

Note: I have already imported Jsch library into Oracle data base using loadjava functionality.
adter that i have created the below code

Below the code :


create or replace and compile java source named sftp as
import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;
import java.io.IOException;
import java.io.FileNotFoundException;

public class sftp {

	public static void downloadfile(String server ,String userid, String pwd , String src_path , String dest_path) throws IOException {


	        JSch jsch = new JSch();
	        Session session = null;
	        try {
	           session = jsch.getSession(userid, server, 22);
	            session.setConfig("StrictHostKeyChecking", "no");
	            session.setPassword(pwd);
	            session.connect();
	            Channel channel = session.openChannel("sftp");
	            channel.connect();
	            ChannelSftp sftpChannel = (ChannelSftp) channel;
	            sftpChannel.get(src_path, dest_path);
	            sftpChannel.exit();
	            session.disconnect();
	        }
             catch (Exception e)
	 	     {
	 		       
	        	e.printStackTrace();
	 	      } 

	   }

	}






code has been successfully compiled and i have created a procedure to execute the above java code:



CREATE OR REPLACE PROCEDURE PR_SFTP (server in varchar2,userID in varchar2,pwd in varchar2,src in varchar2,dest in varchar2)
AS LANGUAGE JAVA
NAME 'sftp.downloadfile(java.lang.String ,java.lang.String ,java.lang.String ,java.lang.String ,java.lang.String)';



when i execute the above procedure , it is keeping executing with out any result.

To test the functionality of my java code , i have created one more version with main class :







import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;
import java.io.IOException;

public class SFTPS {
	
	
	public static void main (String args[])  throws IOException {

		String server="10.1.15.130";
		String userid="xdinesh";
		String pwd ="xdinesh@123";
		String src_path ="/export/home/xdinesh/ret_qry.fmb"; 
		String dest_path ="/SANVOL/DW_REPORTS/REPORTS/CRDB_Statements/";
		
	
		   
		for (int i=0; i<args.length; i++)
	    {
	     if(i==0)
	     {
	    	 server = args[i]; 
	     }
	     if(i==1)
	     {
	    	 userid = args[i]; 
	     }
	     if(i==2)
	     {
	    	 pwd = args[i]; 
	     }
	     if(i==3)
	     {
	    	 src_path = args[i]; 
	     }
	     if(i==4)
	     {
	    	 dest_path = args[i]; 
	     }
	    }
	
	        JSch jsch = new JSch();
	        Session session = null;
	        try {
	           session = jsch.getSession(userid, server, 22);
	            session.setConfig("StrictHostKeyChecking", "no");
	            session.setPassword(pwd);
	            session.connect(); 
	            Channel channel = session.openChannel("sftp");
	            channel.connect();
	            ChannelSftp sftpChannel = (ChannelSftp) channel;
	            sftpChannel.get(src_path, dest_path);  
	            sftpChannel.exit();
	            session.disconnect();
	        } catch (JSchException e) {
	            e.printStackTrace(); 
	        } catch (SftpException e) {
	            e.printStackTrace();
	        }

	   }

	}


-----------------------------------------------------------Scripts to Compile ---------------------
javac -classpath /tmp/jsch-0.1.38.jar SFTPS.java


-----------------------------------------------------------Scripts to Test---------------------

java -classpath .:/tmp/jsch-0.1.38.jar SFTPS 10.1.15.130 xdinesh xdinesh@123 /export/home/xdinesh/ret_qry.fmb /tmp 


I got the expected result but when i try the same via procedure it is keep executing.

Experts advise please
Previous Topic: resource busy and acquire with NOWAIT specified or timeout expired
Next Topic: calculate a function on whole table
Goto Forum:
  


Current Time: Sun Apr 21 19:44:11 CDT 2019