Home » SQL & PL/SQL » SQL & PL/SQL » Java problems
| Java problems [message #193001] |
Thu, 14 September 2006 09:26  |
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  |
Frank
Messages: 7901
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 |
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 |
cecilkur
Messages: 4
Registered: August 2007
Location: Indonesia
|
Junior Member |
|
|
Thank you very much.. this solve my problems 
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
|
|
|
|
Goto Forum:
Current Time: Fri Dec 06 14:08:04 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
