6 Replies Latest reply: Feb 15, 2013 1:09 PM by user6585739 RSS

    insert clob via rsObject

    user6585739

      Hello!

      I work with Oracle Database 11.2.0.3, ojdbc6.jar and do not understand how it works. I want to copy some data from one table to another table with the same fields (CLOBs fields included) of another scheme in another database then the program fails, but if the schemes are in the same database if it works fine.

      The program is:

      <code>
      public static void main(String[] args)
      {
      try
      {
      Class.forName ("oracle.jdbc.OracleDriver");
      // java.sql.Connection cdev = java.sql.DriverManager.getConnection("jdbc:oracle:thin:EDU/******@racestad.idescat.local:1521/bdintern.idescat.local");
      java.sql.Connection cdev = java.sql.DriverManager.getConnection("jdbc:oracle:thin:EDU/******@10.116.2.159:1521/destad.idescat.local");
      cdev.setReadOnly(true);


      java.sql.Connection cproduccio = java.sql.DriverManager.getConnection("jdbc:oracle:thin:rep/*************@racestad.idescat.local:1521/bdintern.idescat.local");
      // java.sql.Connection cproduccio = java.sql.DriverManager.getConnection("jdbc:oracle:thin:estrangeria/************@10.116.2.159:1521/destad.idescat.local");

      cproduccio.setAutoCommit(false);


      java.sql.Statement st=cdev.createStatement();
      java.sql.ResultSet rsFont=st.executeQuery("select * from termes_primaria");

      ResultSetMetaData meta=rsFont.getMetaData();

      int cols=meta.getColumnCount();

      String[] columnNames=new String[cols];
      Object[] columnValues=new Object[cols];
      int[] tipus=new int[cols];

      for(int k=0; k < cols; k++ )
      {
      columnNames[k]=meta.getColumnName(k+1);
      tipus[k]=meta.getColumnType(k+1);
      }

      StringBuffer insertum=new StringBuffer("insert into termes_primaria (");
      for(int k=0; k < cols; k++)
      {
      insertum.append(columnNames[k]);
      if(k < cols-1)insertum.append(",");
      }
      insertum.append(") values (");
      for(int k=0; k < cols; k++)
      {
      insertum.append("?");
      if(k < cols-1)insertum.append(",");
      }
      insertum.append(")");
      System.out.println("insert... "+insertum);


      // llegir
      PreparedStatement aDesti=cproduccio.prepareStatement(insertum.toString());
      int conta=0;
      while(rsFont.next())
      {
      for(int k=0; k < cols; k++)
      {
      columnValues[k] = rsFont.getObject(columnNames[k]);
      if ( rsFont.wasNull() ) aDesti.setNull(k+1, tipus[k]);
      else aDesti.setObject(k+1, columnValues[k]);
      }
      System.out.println("Conta:"+conta);
      aDesti.executeUpdate();
      aDesti.clearParameters();
      conta++;
      }

      aDesti.close();
      rsFont.close();
      st.close();
      cproduccio.commit();
      cproduccio.close();
      cdev.close();
      System.out.println("Hi han "+conta);

      }
      catch(Exception e)
      {
      e.printStackTrace(System.out);
      }
      }

      </code>

      But if I change the connection, cproduccio, to a scheme of another database (which is said to create the connection) fails to run and gives the error is:



      Error:

      java.sql.SQLSyntaxErrorException: ORA-00942: la tabla o vista no existe

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
      at provesmeves.ProvesMeves.main(ProvesMeves.java:83)


      In fact I think the strongest argument there to say this is not right, is as follows (in the sequence of calls in the example):

      As defined in the interface PreparedStatment calls setObject, the execution should only fail, in my opinion, (somewhere in the sequence of calls) to:

      a. connection misconfigured
      b. inject an object that does not match (you can not convert) the column index corresponding to the parameter
      c. use an incorrect column index
      d. use an SQL statement that is not correct or refers to a nonexistent table

      None of these things happening in the example fails (using two connections)


      It seems that the CLOB contains connection information source and used in some way in connection fate in implementing Oracle has done this interface.

      This seems completely wrong, among other things, that the connection information is implicit in the target object PreparedStatemt destination: this behavior would be as absurd as for a hypothetical case of a String table a certain connection and the call for a PreparedStatement SetObject associated with another connection using the connection table and the original object is how we obtained the value of a column in a database table, simply .

      This behavior makes the code work when connecting source and destination are the same but if it is different.

      What is not acceptable is the message of the exception java.sql.SQLSyntaxErrorException: ORA-00942: No such view or the tabla, for obvious reasons.


      Any idea what's going on?

      thanks!

      Miguel

      Edited by: user6585739 on 14-feb-2013 2:21

      Edited by: user6585739 on 14-feb-2013 7:09

      Edited by: user6585739 on 14-feb-2013 8:03

        • 1. Re: insert clob via rsObject
          rp0428
          >
          work with Oracle Database 11.2.0.3, ojdbc6.jar and do not understand how it works. I want to copy some data from one table to another table with the same fields (CLOBs fields included) of another scheme in another database then the program fails, but if the schemes are in the same database if it works fine.
          . . .
          This behavior makes the code work when connecting source and destination are the same but if it is different.

          What is not acceptable is the message of the exception java.sql.SQLSyntaxErrorException: ORA-00942: No such view or the tabla, for obvious reasons.

          Any idea what's going on?
          >
          The exception message is telling what is going on. The table or view does not exist. Either the table/view does not exist at all in the DB you are connected to or the user that is connecting does not have the privileges needed to INSERT records into the table.

          Use sql*plus or sql developer, connect to the target DB and do a test INSERT into that table. Until that works your Java code won't.

          Also, you have selected the WORST possible way to copy data from one table to another.

          The simplest way is to use a database link, connect to the target database and then execute a simple query of the form:
          INSERT INTO myTargetTable (col1, col2, col3) SELECT sourceCol1, sourceCol2, sourceCol3 from SourceTable@sourceDBLink;
          The next easiest way is to just use the SQL*Plus COPY command as show in the example in the SQL*Plus User's Guide
          http://docs.oracle.com/cd/B19306_01/server.102/b14357/apb.htm

          Have your DBA grant the appropriate privileges on the target table to the user you are trying to use and also create a database link that you can use.

          Then modify your Java code to use a simple 'static' query of the form above to do the copy.

          Your problem has NOTHING to do with JDBC or an Oracle issue.
          • 2. Re: insert clob via rsObject
            user6585739
            Thanks for your answer.

            We know that could be done via "insert as select" with a dblink. (Besides, that was the first thing I tried when it did not work to check that it was not a question of privileges of the target table)
            But the problem lies elsewhere:

            It may be because the user privileges of the target table is the owner of the table and can do whatever I want with it, can you explain more:

            If we change the Java code that reads from the source table and then perform the insert into the target table with this one, the program works:



            PreparedStatement aDesti=cproduccio.prepareStatement(insertum.toString());
            int conta=0;
            while(rsFont.next())
            {

            for(int k=0; k < cols; k++)
            {
            columnValues[k] = rsFont.getObject(columnNames[k]);
            if ( rsFont.wasNull() ) aDesti.setNull(k+1, tipus[k]);
            else
            {
            if ( tipus[k] != 2005 ) aDesti.setObject(k+1, columnValues[k]);
            else
            {
            Clob clob = (Clob) columnValues[k];
            aDesti.setClob(k+1, clob.getCharacterStream());
            }
            }
            }
            System.out.println("Conta:"+conta);
            aDesti.executeUpdate();
            aDesti.clearParameters();
            conta++;
            }

            (Note: tipus[k]=2005, the field is a clob)


            So we thought it might rsObject implementation () is not quite right. Because if the target database and source is the same, the original code works perfectly, but when different always gives the same error, but we exchange databases.

            Thanks,

            Miguel
            • 3. Re: insert clob via rsObject
              rp0428
              >
              If we change the Java code that reads from the source table and then perform the insert into the target table with this one, the program works:
              >
              The code is nice but it would be more helpful if you provide an explanation of what you changed and the difference between what you posted and the original code.

              Also, did you try the test I suggested?
              >
              Use sql*plus or sql developer, connect to the target DB and do a test INSERT into that table. Until that works your Java code won't.
              >
              Also I'm not sure you understand that the 'getObject' method called on a CLOB only returns the Locator; it doesn't return the actual CLOB value. So you can't put that locator into the table on a different database; you need to create a new locator.

              That is why when you switched to 'getCharacterStream' (which DOES read the entire CLOB) it works.

              See the JDBC Dev Guide
              http://docs.oracle.com/cd/B19306_01/java.102/b14355/oralob.htm#sthref851
              >
              •To read from a CLOB, use the getAsciiStream or getCharacterStream method of an oracle.sql.CLOB object to retrieve the entire CLOB as an input stream. The getAsciiStream method returns an ASCII input stream in a java.io.InputStream object. The getCharacterStream method returns a Unicode input stream in a java.io.Reader object.
              >
              Read that entire chapter about how to read/write CLOBs; it has example code as well.

              Reading and Writing BLOB and CLOB Data
              http://docs.oracle.com/cd/B19306_01/java.102/b14355/oralob.htm#i1058044
              • 4. Re: insert clob via rsObject
                user6585739
                Thanks again.

                I already tried your suggestion and it works perfectly. I knew you could do this.

                I understand that the only getObject returns a CLOB locator of and therefore can not work when you make a PreparedStatement setObject on a different database. But, do not you think it's a bad implementation of getObject () and setObject ()?

                As I am not at all agree is the error returned by Oracle to perform the insert, the ORA-00942, because all it does is confuse and waste your time. If the error had been to run something like: ORA-22992 (can not use LOB locators selected from remote tables) would be clearer and you would realize the mistake before, I think.
                • 5. Re: insert clob via rsObject
                  rp0428
                  >
                  I understand that the only getObject returns a CLOB locator of and therefore can not work when you make a PreparedStatement setObject on a different database. But, do not you think it's a bad implementation of getObject () and setObject ()?
                  >
                  No - that is an appropriate implementation.

                  In general LOBs are NOT stored in-line in the table segment with the rest of the table data. They are stored in a LOB segment.

                  If Oracle had to read the entire LOB for every row when you query the table that would slow access to the table to the point it would be unusable. Oracle returns the LOB locator (the pointer to the LOB) and you can now either ignore the LOB itself or fetch it as you wish.

                  LOBs are different, and are used differently, that ordinary data. Generally users do very little DML manipulation of lobs.
                  • 6. Re: insert clob via rsObject
                    user6585739
                    Hello!
                    I quite agree with what you tell me and I understand perfectly.

                    But what I can say that I returned the error oracle (ORA-942)????.


                    Not true, I am the owner of the table will insert.

                    The only thing I can think is:

                    That passed as parametre the clob locator of the target table in the remote database via PreparedStatement.setObject (), for some reason, the select statement is modified internally ... (may be a nonsense what I just wrote, I do not know)

                    thank you very much for everything