Próbuję utworzyć clob z ciągu> 4000 znaków (dostarczonych w zmiennej powiązanej file_data) do użycia w predykacie Oracle SELECT poniżej:

myQuery=
select *
from dcr_mols
WHERE flexmatch(ctab,:file_data,'MATCH=ALL')=1;

Jeśli dodam TO_CLOB () round file_data, nie osiągnie niesławnego limitu Oracle 4k dla varchar (jest w porządku dla łańcuchów <4k). Błąd (w SQL Developer) to:

ORA-01460: unimplemented or unreasonable conversion requested
01460. 00000 -  "unimplemented or unreasonable conversion requested"

FYI Funkcja flexmatch służy do wyszukiwania cząsteczek i jest opisana tutaj: http://help.accelrysonline.com/ulm /onelab/1.0/content/ulm_pdfs/direct/developers/direct_2016_developersguide.pdf

Sama funkcja jest nieco skomplikowana, ale istotą jest to, że drugim parametrem musi być clob. Moje pytanie brzmi więc, jak przekonwertować zmienną bind_variable ciągu Java o ponad 4000 znaków na clob w sql (lub Java).

Wypróbowałem poniższą metodę (która działa przy wstawianiu clobs) w Javie (Spring boot 2) używając:

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("file_data", fileDataStr,Types.CLOB);
jdbcNamedParameterTemplate.query(myQuery,parameters,…

Ta metoda powinna działać, ale kończy się niepowodzeniem z konwergentnym błędem flexmatch, którym FYI jest:

SQL state [99999]; error code [29902]; ORA-29902: error in executing ODCIIndexStart() routine\nORA-20100: 
MDL-0203: Unable to read from CLOB (csfrm=1, csid=873): 
ORA-22922: nonexistent LOB value\nMDL-0021: Unable to copy LOB to string\nMDL-1051: Molstructure search query is not a valid molecule\nMDL-0976: 
Molecule index search initialization failed\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 329\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 309\n; nested exception is java.sql.SQLException: 
ORA-29902: error in executing ODCIIndexStart() routine\nORA-20100: MDL-0203: Unable to read from CLOB (csfrm=1, csid=873): 
ORA-22922: nonexistent LOB value\nMDL-0021: Unable to copy LOB to string\nMDL-1051: Molstructure search query is not a valid molecule\nMDL-0976: 
Molecule index search initialization failed\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 329\nORA-06512: at \"C$MDLICHEM80.MDL_MXIXMDL\", line 309\n"

Uwaga: używam SpringBoot 2, ale nie mogę uzyskać żadnej metody używającej OracleConnection (uzyskanej z mojego obiektu Spring NamedParametersJdbcTemplate) do działania (nawet na clobs <4k), więc podejrzewam, że zrobiłem coś głupiego. Próbowałem:

 @Autowired
 NamedParameterJdbcTemplate  jdbcNamedParameterTemplate;
OracleConnection conn =  this.jdbcNamedParameterTemplate.getJdbcTemplate().getDataSource().getConnection().unwrap(OracleConnection.class);
Clob myClob =  conn.createClob();
myClob.setString( 1, fileDataStr);
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("file_data", myClob,Types.CLOB);

Application.properties:

spring.datasource.url=jdbc:oracle:thin:@//${ORA_HOST}:${ORA_PORT}/${ORA_SID}
spring.datasource.username=${ORA_USER}
spring.datasource.password=${ORA_PASS}

Zauważ, że działa dobrze, jeśli pójdę do starej szkoły i użyję połączenia innego niż sprężynowe oraz PreparedStatement, które ma metodę setClob ():

OracleDataSource ods = new OracleDataSource();
String url ="jdbc:oracle:thin:@//" + ORA_HOST +":"+ORA_PORT +"/"+ORA_SID;
ods.setURL(url);
ods.setUser(user);
ods.setPassword(passwd);
Connection conn = ods.getConnection();
Clob myClob=conn.createClob();
PreparedStatement ps = conn.prepareStatement("select dcr_number from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1");
myClob.setString(1,myMol);
ps.setClob(1,myClob);
ResultSet rs =ps.executeQuery();

Ale wolałbym rozwiązanie Spring 2 w Javie lub Sql. Każda pomoc, sugestie mile widziane.

11
DS. 1 kwiecień 2020, 18:14

5 odpowiedzi

Najlepsza odpowiedź

Musiałem wrócić do korzystania z PreparedStatement, ale poprawiłem nieco normalną implementację, uzyskując połączenie ze Spring i używając apache commons BeanListHandler do mapowania zestawu wyników na listę obiektów

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

@Autowired
DataSource dataSource;

List<MyDao> myMethod(String fileData){
    String myQuery="select * from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1";

try {
    Connection conn = this.dataSource.getConnection()   // Get connection from spring

    Clob myClob =  conn.createClob();   // Open a dB clob 
    myClob.setString( 1, fileData);     // add data to clob
    PreparedStatement ps = conn.prepareStatement(myQuery);
    ps.setClob(1,myClob);              // Add a clob into the PreparedStatement
    ResultSet rs =ps.executeQuery();   // Execute the prepared statement

    //ResultSetHandler<List<MyDao>> handler = new BeanListHandler<MyDao>(MyDao.class);   // Define the ResultSet handler
    ResultSetHandler<List<MyDao>> handler = new BeanListHandler<MyDao>(MyDao.class, new BasicRowProcessor(new GenerousBeanProcessor()));  // This is better than the above handler , because GenerousBeanProcessor removes the requirement for the column names to exactly match the java variables

    List<MyDao> myDaoList = handler.handle(rs);   // Map ResultSet to List of MyDao objects
    }catch (Exception e) {
        e.printStackTrace();
    }

return myDaoList;
}
1
DS. 7 maj 2020, 08:00

Przesyłaj strumieniowo. Nie możesz po prostu wkleić dużej wartości do instrukcji SQL.

Musisz:

  • Wstaw pusty BLOB w instrukcji INSERT (używając EMPTY_BLOB ()? ... nie całkiem pamiętam).
  • Pobierz strumień wyjściowy dla pustego obiektu BLOB.
  • Następnie pobierz strumień wejściowy z pliku. Nie ładuj całego pliku do pamięci.
  • Następnie przenieś bloki ze strumienia wejściowego do strumienia wyjściowego przy użyciu buforowania. Bufor 16 KB powinien wystarczyć.
  • Zamknij oba strumienie.

To jest standardowy sposób radzenia sobie z ogromnymi danymi w Oracle. Mnóstwo przykładów.

Pobieranie ogromnych danych (typy BLOB i CLOB) działa w ten sam sposób. W takim przypadku po prostu użyj InputStreams.

5
The Impaler 1 kwiecień 2020, 15:29

Możesz nazwać swoją staromodną funkcję w ten sposób. Utwórz klasę do obsługi zestawu wyników

 class MyPreparedStatementCallback implements PreparedStatementCallback {
    public Object doInPreparedStatement(PreparedStatement preparedStatement)
            throws SQLException, DataAccessException {
        ResultSet rs = preparedStatement.executeQuery();
        List result = new LinkedList();
        rs.close();
        return result;
    }
}

I wywołaj zapytanie, używając JdbcTemplate w swojej metodzie

 jdbcTemplate.execute(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection)

                throws SQLException, DataAccessException {

            PreparedStatement ps = connection.prepareStatement("select dcr_number from dcr_mols WHERE flexmatch(ctab,?,'MATCH=ALL')=1");
            Clob myClob =  connection.createClob();
            myClob.setString( 1, fileDataStr);
            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("file_data", myClob, Types.CLOB);
            ps.setClob(1,myClob);
            return ps;

        };
    }, new MyPreparedStatementCallback());
1
Mukhtiar Ahmed 9 kwiecień 2020, 11:47

Możesz zadeklarować fileDataStr jako CLOB używając con, które jest połączeniem

java.sql.Clob fileDataStr = oracle.sql.CLOB.createTemporary
(con, false, oracle.sql.CLOB.DURATION_SESSION);

A następnie użyj go jak poniżej

 parameters.addValue("file_data", fileDataStr,Types.CLOB);

Ponadto, jeśli używasz identyfikatora SID zamiast nazwy usługi w ciągu połączenia, spróbuj zmienić plik właściwości, jak poniżej

spring.datasource.url=jdbc:oracle:thin:@//${ORA_HOST}:${ORA_PORT}:${ORA_SID}
0
psaraj12 8 kwiecień 2020, 12:43

Czytając dokumentację API „BIOVIA Direct”, na stronie 27 znajduje się interesujący przykład, fragment pokazany poniżej:

select ...
from ...
where flexmatch(
ctab,
(select ctab from nostruct_table),
'all'
)=1

Używa już załadowanego CLOB. Dlatego wydaje mi się, że wystarczająco przyzwoitym rozwiązaniem byłoby załadowanie CLOB-a do Twojej tabeli (lub wstępne załadowanie ich wszystkich z wyprzedzeniem), a następnie po prostu ich użycie.

Krok # 1 - Załaduj swój CLOB do tabeli:

create table mol_file (
  id number(12) primary key not null,
  content clob
);

insert into mol_file (id, content) values (:id, :content);

I użyj kodu Java, aby wstawić CLOB (prawdopodobnie przy użyciu strumieni), jak pokazano w innej odpowiedzi (wiele przykładów w Internecie). Na przykład wstaw zawartość danych mol o identyfikatorze = 123.

Krok # 2 - Uruchom zapytanie, używając już załadowanego pliku mol:

select *
from dcr_mols
WHERE flexmatch(
        ctab,
        (select content from mol_file where id = :id),
        'MATCH=ALL'
      ) = 1;

Możesz ustawić parametr :id na 123, aby użyć wcześniej załadowanego pliku (lub dowolnego innego).

1
The Impaler 8 kwiecień 2020, 13:33