| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> CLOB / Charset / Java / Unix Issue
Hi,
I'm encountering the following problem.
I'm working on a 3-tiers architecture with an Oracle Database (8.1.7) a Weblogic application server and a Web server under Aix 4.3 (all 3 are under AIX 4.3 on the same platform).
My application has a web interface that allows users to upload files to the server from their PC clients and a webbrowser, that insert each file into a oracle Clob (via Java Code) and that call a stored procedure (with java code again) to extract this clob to a file (UTL_file package), then, the extracted file is processed line by line and information inserted in others tables.
The issue is that some characters (acute, grave accent ....etc) appears as question marks in the database or that some date from the file can't be processed because they are structured as DD/MM/YYYY (french notation, but that normal I'm from france).
I first thougt of an NLS_LANG problem but on the weblogic server it is set to french_france.WE88859P15 that seems to be right and the same the database configuration.
I then tried to performs some conversion when the clob data were extracted to file (from WE88859P15 TO CP152 or vice-versa) with the oracle convert function but it seems that it doesn't work.
An other but coherent symptoms is that the extracted files (from the clob columns) seems not to be fine (accent are not recognized).
This is the java code used to load file to clob (on the weblogic server side)
con = dbHandle.getAdminConnection();
con.setAutoCommit(false);
/// NEW IMPORT
// int taskId = DBTools.getOraSeqValue("vtr.VTR_SEQ_LOG_IMPORT",
DBTools.NEXTVAL,con);
int taskId =
DBTools.getOraSeqValue(SqlQueryDefinition.seqLogImport.toString(),
DBTools.NEXTVAL,con);
Debug.out.println("taskId " + taskId);
// String cmd = "insert into vtr.vtr_log_import
(cod_task,DTE_DEBUT,lob_imp,lob_rej,lob_log, txt_nom_fic_orig,
txt_utilisateur) " +
// "values ("+ taskId
+",sysdate,empty_clob(),empty_clob(),empty_clob(), '"+file+"','"+
((UserBean)request.getSession().getAttribute("userbean")).getIdentifier()+"'
)";
// stmt = con.createStatement();
// stmt.executeQuery(cmd);
// stmt.close();
pstmt =
con.prepareStatement(SqlQueryDefinition.initLigneImport.toString());
pstmt.setInt(1,taskId);
pstmt.setString(2,file);
pstmt.setString(3,((UserBean)request.getSession().getAttribute("userbean")). getIdentifier());
pstmt.executeQuery();
pstmt.close();
con.commit();
// Writing CLOB
// cmd = "SELECT cod_task,lob_imp,lob_rej,lob_log FROM vtr.vtr_log_import
WHERE cod_task="+ taskId +" for update";
// stmt = con.createStatement();
// rset = stmt.executeQuery(cmd);
pstmt =
con.prepareStatement(SqlQueryDefinition.setBlobImport.toString());
pstmt.setInt(1,taskId);
rset = pstmt.executeQuery();
rset.next();
File csvFile = new File(localFile);
System.out.println("csvFile length = " + csvFile.length());
File unixFile = new File(localFile+".ux"); Tools.dos2Unix(csvFile, unixFile);
FileInputStream instream = new FileInputStream(unixFile);
// support Weblogic
clob = ClobComponent.factory(DBUtil.getInstance().isWebLogicPlatform());
clob.setClob(rset,2);
outstream = clob.getAsciiOutputStream();
size = clob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;
while ((length = instream.read(buffer)) != -1) outstream.write(buffer, 0, length);
instream.close();
outstream.close();
rset.close();
// stmt.close();
pstmt.close();
rset=null;
// stmt = null;
pstmt=null;
con.commit();
// IMPORT
cs = con.prepareCall(SqlQueryDefinition.importStoredProc.toString());
index = 1;
cs.setString(index++, fullPath); // 1
cs.setString(index++,
((UserBean)request.getSession().getAttribute("userbean")).getIdentifier());
// 2
cs.registerOutParameter(index++,java.sql.Types.VARCHAR); // 3 cs.registerOutParameter(index++,java.sql.Types.VARCHAR); // 4 cs.registerOutParameter(index++,java.sql.Types.NUMERIC); // 5 cs.setInt(index++, taskId); // 6 cs.executeQuery(); String fichier1 = cs.getString(3);
System.out.println("returnCode/fichier1/2 : " + returnCode + " & " + fichier1 + " & " + fichier2);
cs.close();
con.commit();
This is the PL/SQL code used to unload clob to dile (on the oracle side)
PROCEDURE writeToFile (id NUMBER, a_fichier VARCHAR2)
IS
result CLOB;
cvl_tmp VARCHAR2 (32000);
nvl_amount NUMBER := 250;
nvl_pos NUMBER := 1;
nvl_clob_length NUMBER;
instr_pos NUMBER;
file_handle UTL_FILE.file_type;
BEGIN
file_handle := UTL_FILE.FOPEN(
substr(a_fichier, 1, instr(a_fichier, file_separator, -1, 1)-1), --
dir
substr(a_fichier, instr(a_fichier, file_separator, -1, 1)+1), --
file
'W');
select lob_imp
INTO result
from vtr_log_import
where cod_task = id;
nvl_clob_length := DBMS_LOB.getlength (result);
cvl_tmp := NULL;
nvl_amount := 250;
nvl_pos := 1;
LOOP
instr_pos :=
DBMS_LOB.INSTR (result, CHR (10), nvl_pos, 1) -
nvl_pos;
--DBMS_OUTPUT.PUT_LINE(nvl_pos||': Of length : '||instr_pos);
IF nvl_pos + instr_pos > nvl_clob_length
THEN
instr_pos := nvl_clob_length - nvl_pos;
DBMS_LOB.READ (
lob_loc=> result,
amount=> instr_pos,
offset=> nvl_pos,
buffer=> cvl_tmp
);
EXIT;
END IF;
DBMS_LOB.READ (
lob_loc=> result,
amount=> instr_pos,
offset=> nvl_pos,
buffer=> cvl_tmp
);
-- DBMS_OUTPUT.PUT_LINE(cvL_tmp);
cvl_tmp := CONVERT(cvl_tmp, 'WE8MSWIN1252', 'WE8ISO8859P15');
UTL_FILE.put_line (file_handle, cvl_tmp);
nvl_pos := nvl_pos
+ instr_pos
+ 1;
IF nvl_pos > nvl_clob_length
THEN
EXIT;
END IF;
END LOOP;
UTL_FILE.fclose (file_handle);
Many thanks in advance, I have no idea even if I suspect the java store to file or the UTL_file extraction to file steps to be in cause !
Run-O Received on Wed Jun 18 2003 - 17:58:15 CDT
 |
 |