2 Replies Latest reply: May 1, 2013 11:31 PM by 1000092 RSS

    Exception in thread "main" java.sql.SQLException: ORA-01704: string literal

    1000092

      Hi Everyone,

      I am Using This Code to read the Mail from server and inserting those into database but i am getting error like



      import java.io.*;
      import java.util.*;
      import javax.mail.*;
      import java.sql.*;
      public class ReadMail_OLD {
      static StringBuffer s= new StringBuffer();
      static StringBuffer s1= new StringBuffer();
      public static void main(String args[]) throws Exception {
      String host = "***.****.co.in";
      String user = "***@***.**.**";
      String password = "******";
      String Message_2000 = new String();
      String Message_2001 = new String();
      Connection conn;
      Class.forName("oracle.jdbc.driver.OracleDriver");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@1.2.3.5:1050:abcgdb", "abc", "efg");

      Statement stm = conn.createStatement();
      String strsql = "insert into READ_MAIL_MSG (MESSAGE_ID,SENT_DATE,MAIL_FROM,MAIL_TO,MAIL_SUBJECT,MESSAGE) values (" ;

      // Get system properties
      Properties properties = System.getProperties();

      // Get the default Session object.
      Session session = Session.getDefaultInstance(properties);

      // Get a Store object that implements the specified protocol.
      Store store = session.getStore("pop3");

      //Connect to the current host using the specified username and password.
      store.connect(host, user, password);

      //Create a Folder object corresponding to the given name.
      Folder folder = store.getFolder("inbox");

      // Open the Folder.
      folder.open(Folder.READ_ONLY);
      Message[] message = folder.getMessages();
      // Display message.
      for (int i = 0; i < message.length; i++)
      {

      //System.out.println("------------ Message " + (i + 1) + " ------------");
      //System.out.println("SentDate : " + message.getSentDate());
      //System.out.println("From : " + message[i].getFrom()[0]);
      //System.out.println("Subject : " + message[i].getSubject());
      //System.out.print("Message : ");
      InputStream stream = message[i].getInputStream();
      int j = 0;
      Message_2000 = "";
      Message_2001 = "";

      while(stream.available()>0 && j < 500)
      {
      // read the byte and convert the integer to character
      s.append((char) stream.read());
      j = j+1;
      }
      Message_2000 = s.toString();
      Message_2001 = Message_2000.replace("'"," ");


      System.out.println(strsql+"'"+(i + 1)+"','"+message[i].getSentDate()+"','"+message[i].getFrom()[0]+"','***@***.co.in','"+message[i].getSubject()+"','"+s.toString()+"')");
      stm.execute(strsql+"'"+(i + 1)+"','"+message[i].getSentDate()+"','"+message[i].getFrom()[0]+"','***@****.co.in','"+message[i].getSubject()+"','"+Message_2001+"')");
      }
      folder.close(true);
      store.close();
      }
      }

      when i execute this some messages get inserted into table but for some messages


      Exception in thread "main" java.sql.SQLException: ORA-01704: string literal too long

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
      at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
      at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
      at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:942)
      at ReadMail_OLD.main(ReadMail_OLD.java:64)

      Edited by: 997089 on May 1, 2013 9:29 PM

      Edited by: 997089 on May 1, 2013 9:30 PM

        • 1. Re: Exception in thread "main" java.sql.SQLException: ORA-01704: string literal
          rp0428
          >
          when i execute this some messages get inserted into table but for some messages

          Exception in thread "main" java.sql.SQLException: ORA-01704: string literal too long
          >
          You haven't posted your table DDL or a copy of the query string that is causing the error.

          But Oracle VARCHAR2 table columns are limited to 4000 bytes (that is BYTES - not characters).

          That exception means just what it says: 'string literal too long.

          If you have a column value longer than 4000 bytes (not characters) you need to use a CLOB datatype.

          See '14 Working with LOBs and BFILEs' in the JDBC Developer's Guide
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/oralob.htm#g1070326
          • 2. Re: Exception in thread "main" java.sql.SQLException: ORA-01704: string literal
            1000092
            Hi All,
            Thanks for Your Reply .
            I got the answer i have made a mistake in loop after every iteration i have not cleared the stringBuffer Varaible so the after one or two iteration it givesthe error.

            import java.io.*;
            import java.util.*;
            import javax.mail.*;
            import java.sql.*;
            public class ReadMail_OLD {
            static StringBuffer s= new StringBuffer();
            static StringBuffer s1= new StringBuffer();
            public static void main(String args[]) throws Exception {
            String host = "***.****.co.in";
            String user = "***@***.**.**";
            String password = "******";
            String Message_2000 = new String();
            String Message_2001 = new String();
            Connection conn;
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@1.2.3.5:1050:abcgdb", "abc", "efg");


            Statement stm = conn.createStatement();
            String strsql = "insert into READ_MAIL_MSG (MESSAGE_ID,SENT_DATE,MAIL_FROM,MAIL_TO,MAIL_SUBJECT,MESSAGE) values (" ;

            // Get system properties
            Properties properties = System.getProperties();

            // Get the default Session object.
            Session session = Session.getDefaultInstance(properties);

            // Get a Store object that implements the specified protocol.
            Store store = session.getStore("pop3");

            //Connect to the current host using the specified username and password.
            store.connect(host, user, password);

            //Create a Folder object corresponding to the given name.
            Folder folder = store.getFolder("inbox");

            // Open the Folder.
            folder.open(Folder.READ_ONLY);
            Message[] message = folder.getMessages();
            // Display message.
            for (int i = 0; i < message.length; i++)
            {


            //System.out.println("------------ Message " + (i + 1) + " ------------");
            //System.out.println("SentDate : " + message.getSentDate());
            //System.out.println("From : " + message.getFrom()[0]);
            //System.out.println("Subject : " + message.getSubject());
            //System.out.print("Message : ");
            InputStream stream = message.getInputStream();
            int j = 0;
            Message_2000 = "";
            Message_2001 = "";

            while(stream.available()>0 && j < 500)
            {
            // read the byte and convert the integer to character
            s.append((char) stream.read());
            j = j+1;
            }
            Message_2000 = s.toString();
            Message_2001 = Message_2000.replace("'"," ");


            System.out.println(strsql+"'"+(i + 1)+"','"message.getSentDate()"','"message.getFrom()[0]"','***@****.co.in','"message.getSubject()"','"s.toString()"')");
            stm.execute(strsql+"'"+(i + 1)+"','"message.getSentDate()"','"message.getFrom()[0]"','***@****.co.in','"message.getSubject()"','"Message_2001"')");

            s.delete(0, s.length());
            folder.close(true);
            store.close();
            }
            }


            Thanks for Reply

            Edited by: 997089 on May 1, 2013 9:28 PM

            Edited by: 997089 on May 1, 2013 9:30 PM