Re: how to split strings in pl/sql or in sql/plus

baka wrote:
> Hello Oracle guru's
> Sorry for not providing the details
>
> here is my java program.
> is it ppossible to write this kind of stuf in PL/SQL.
> let me have the sample if possible.
> the below java (as it is i.e minus JDBC connection) works in eclipse
> environment.
>
> Thanks in Advance
> ------------------------------------------------------------------------
> // My first java program for this kind
> // mesun_at_japan............
> // 2005/06/07
> //import java.sql.*;
> import java.io.*;
> import java.util.*;
> public class strpMinjdbc {
> public static void main(String[] args) {
> String line, sql = "";
> //Connection con = null;
> //String driver = "com.oracle.jdbc.xxxDriver";
> // String url = "jdbc:oracle:thin:@xxhost:1521:xxxDB" ;
> // String user = "scott";
> //String password = "tiger";
>
> try {
> //Class.forName(driver);
> //con = DriverManager.getConnection(url,user,password);
> FileReader filereader = new FileReader("c:/jnew/yourfile.csv");
> BufferedReader bufferreader = new BufferedReader(filereader);
> // writing to a file
> FileOutputStream out; // declare a file output object
> PrintStream p; // declare a print stream object
> out = new FileOutputStream("c:/jnew/myfile.txt");
> p = new PrintStream( out );
>
>
>
>
> //Here i am skipping the header data from the log file
> line = bufferreader.readLine();
> //java.sql.Statement stmt = con.createStatement();
> //co.setAutoCommit(false);
> while ((line = bufferreader.readLine()) != null) {
> //System.out.println("orginalline Line:" +line);
> //sql = "INSERT INTO logdata (c1,c2,c3,c4,c5) values(";
> StringTokenizer rdLine = new StringTokenizer(line, ",");
> sql = "";
> int i = 0;
> while (rdLine.hasMoreTokens()) {
> i++;
> //System.out.println("SQL:" + sql);
> String Tokendata = rdLine.nextToken();
> if (i != 6) {
> if (i == 2) { // here i go for spliting the strings
> String searachme="\\";
> int strposn=Tokendata.indexOf(searachme)+1;
> String sql2 =Tokendata.replace('\\',',');
> sql = sql+ sql2 + ",";
> //System.out.println("ffff:" + sql2);
> }
> else
> sql = sql + Tokendata + ",";
>
>
>
> } else {
> sql = sql + Tokendata + ")";
> break;
> //System.out.println("records:" + sql);
> }
> //i++;
> }
> System.out.println("records:" + sql);
> p.println (sql);
>
>
> //stmt.executeUpdate(sql);
> }
> //con.commit();
> filereader.close();
> p.close();
>
> //con.close();
> } catch (Exception e) {
> //System.out.println("SQL:"+sql);
> e.printStackTrace();
> }
> }
> }
> --------------------------------------------------------------------------

You can do this in Java as you have written provided you have Jserver installed in the database (I would use String.split() instead of StringTokenizer() in your example).

Having said that, you can easily do this in PL/SQL also. Just lookup usage for INSTR(), SUBSTR(), UTL_FILE package at http://tahiti.oracle.com

If you cannot do it in SQL then do it in PL/SQL and if you cannot do it in PL/SQL then do it in Java.

Regards
/Rauf Received on Tue Jun 07 2005 - 03:39:10 CDT