Home » SQL & PL/SQL » SQL & PL/SQL » Java problems
Java problems [message #193001] Thu, 14 September 2006 09:26 Go to next message
NeO_GeO
Messages: 2
Registered: September 2006
Junior Member
this is an example of java code that i' ve created to use unix utilities in Windows or sql loader :



import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.lang.String;




public class Shell {

private static final String path_unix_utilities = "c:\\unix\\bin\\";
private static final String path_oradb = "c:\\Programmi\\Oracle\\OraDb\\BIN\\";
private static final String string_rifiuto = "Totale dei record logici rifiutati:";
private static final String string_scarto = "Totale dei record logici scartati:";


public Shell(){

}


public static boolean eseguiSQLLDR(String parametri_connessione,String path,String file_controllo){

try {

String comando = path_oradb+"sqlldr userid="+parametri_connessione+" control="+file_controllo;


// Esegui il comando
Process pr = Runtime.getRuntime().exec(comando,null,new File(path));


// Cattura dell' output
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Output: " + buff);
Thread.sleep(100);
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
} finally {

br_in.close();

}

// Cattura dell' errore
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Errore : " + buff);
Thread.sleep(100);
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
} finally {
br_err.close();
}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}

return Shell.controlloSQLLDR(path,file_controllo);
}

public static boolean eseguiSQLLDR(String parametri_connessione,String path,String file_sh,String file_controllo){


boolean bash = Shell.eseguiComando_Bash(path, file_sh);
if(!bash) return false;

try {

String comando = path_oradb+"sqlldr userid="+parametri_connessione+" control="+file_controllo;


// Esegui il comando
Process pr = Runtime.getRuntime().exec(comando,null,new File(path));


// Cattura dell' output
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Output: " + buff);
Thread.sleep(100);
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
} finally {

br_in.close();

}

// Cattura dell' errore
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Errore : " + buff);
Thread.sleep(100);
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
return false;
} finally {

br_err.close();

}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}

return controlloSQLLDR(path,file_controllo);
}

public static boolean eseguiComando_Bash(String path,String file_sh) {

try {

String comando = Shell.path_unix_utilities+"bash "+file_sh;



// Esegui il comando
Process pr = Runtime.getRuntime().exec(comando,null,new File(path));




// Cattura dell' output
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Output: " + buff);
Thread.sleep(100);
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
} finally {

br_in.close();

}

// Cattura dell' errore
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Errore : " + buff);
Thread.sleep(100);
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
return false;
} finally {

br_err.close();

}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
return true;
}

private static String eseguiComando_Sed (String ricerca,String path,String file){

String appoggio = null;
try {

String comando_sed = Shell.path_unix_utilities+"sed -r -n -e \"s/"+ricerca+"([^0-9])/\\1/p\" "+file;

// Esegui il comando
Process pr = Runtime.getRuntime().exec(comando_sed,null,new File(path));

// Cattura dell' output
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
appoggio = br_in.readLine().trim();
Thread.sleep(100);
br_in.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
} finally {

br_in.close();

}

// Cattura dell' errore
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Errore : " + buff);
Thread.sleep(100);
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Lista degli errori :");
ioe.printStackTrace();
} finally {

br_err.close();

}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
return appoggio;
}

private static boolean controlloSQLLDR(String path,String file_controllo) {

String file_log = replaceAll(file_controllo,".ctl",".log");
int righe_scartate = 0;
int righe_rifiutate = 0;
try {
righe_scartate = Integer.parseInt(Shell.eseguiComando_Sed(Shell.string_rifiuto, path, file_log));
righe_rifiutate = Integer.parseInt(Shell.eseguiComando_Sed(Shell.string_scarto, path, file_log));

}catch(NumberFormatException nfex)
{
System.out.println("\"" +nfex.getMessage() +"\" non un numero");

}
if((righe_scartate == 0) &&(righe_rifiutate == 0)){
return true;

}
else{
return false;
}

}


private static String replaceAll(String source,String toReplace,String replacement) {
int idx = source.lastIndexOf( toReplace );
if ( idx != -1 ) {
StringBuffer ret = new StringBuffer( source );
ret.replace( idx, idx+toReplace.length(), replacement );
while( (idx=source.lastIndexOf(toReplace, idx-1)) != -1 ) {
ret.replace( idx, idx+toReplace.length(), replacement );
}
source = ret.toString();
}

return source;
}




}



Ok all run in java.......then i' ve loaded from a shell the Shell.class :
..........loadjava -user scott/tiger -oci8 -resolve Shell.class

Ok i find Shell.class in my database and i write a function and procedure on jdeveloper :


function loader (par1 CHAR,par2 CHAR,par3 IN CHAR,par4 IN CHAR) return boolean
IS LANGUAGE JAVA NAME 'Shell.eseguiSQLLDR(java.lang.String , java.lang.String , java.lang.String , java.lang.String )return boolean';

PROCEDURE PROCEDURA_LOADER AS

BEGIN
DECLARE
PAR1 CHAR(200);
PAR2 CHAR(200);
PAR3 CHAR(200);
PAR4 CHAR(200);
v_Return BOOLEAN;
BEGIN
PAR1 := 'giord/giord';
PAR2 := 'Z://cdg/db/txt/cosisan/';
PAR3 := 'totati_medico.sh';
PAR4 := 'Medici.ctl';

execute immediate 'CALL DBMS_JAVA.SET_OUTPUT(1000000)';
v_Return := LOADER(PAR1,PAR2,PAR3,PAR4);

END;

END;

when i compile it...... it' s ok but when i run it .........this is the result :

Connecting to the database dwh4.
Errore : totati_medico.sh: totati_medico.sh: No such file or directory
Output:
Output: SQL*Loader: Release 10.2.0.1.0 - Production on Gio Set 14 12:34:16 2006
Output:
Output: Copyright (c) 1982, 2005, Oracle. All rights reserved.
Output:
Errore : SQL*Loader-500: Impossibile aprire file (Medici.ctl)
Errore : SQL*Loader-553: file non trovato
Errore : SQL*Loader-509: Errore di sistema: Impossibile trovare il file specificato.
null
"null" non un numero
Process exited.
Disconnecting from the database dwh4.

I' ve understood that the command is not executed in the the working directory that i' ve specified but WHY ?????????

In a ide as eclipse or netbean or jdeveloper with this code i have not problems.

i write a main method like this :

public static void main(String[] args) {

Shell.eseguiSQLLDR("scott/tiger","path","file_sh.sh","file_controllo.ctl"){

}

and the application runs !


in pl/sql this java method =>"Runtime.getRuntime().exec(String comand,String array[],File path);" to launch the command in the "path" directory does not seem to work !!!




Re: Java problems [message #193033 is a reply to message #193001] Thu, 14 September 2006 10:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is your database-server the same machine as your IDE runs on, or did you test at your client and run a db on a server?
Re: Java problems [message #193069 is a reply to message #193001] Thu, 14 September 2006 14:46 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
Make sure that there is Medici.ctl in the current directory where you run program. Or specify an absolute path to Medici.ctl.
Re: Java problems [message #276200 is a reply to message #193001] Wed, 24 October 2007 06:19 Go to previous message
cecilkur
Messages: 4
Registered: August 2007
Location: Indonesia
Junior Member
Thank you very much.. this solve my problems Razz

My problem is I have to execute sql loader for few files and sequential
I don't know when sql loader stop.. So my application keep runing even sql loader haven't finished.
But know, after I used input stream. I can run my application after sql loader finished

Thank you
Previous Topic: Enforcing a processing time limit
Next Topic: Triggers on Views
Goto Forum:
  


Current Time: Wed Dec 07 12:36:55 CST 2016

Total time taken to generate the page: 0.16682 seconds