SELECT * FROM <CHORAL_OWNER>.choral_settings;
Use the isvalidmolecule method. Note, there are some structures which are qualified as valid, but cannot be indexed by the cartridge.
Check the logs in <choral_home>/logs. The logging level can be set in <choral_home>/conf/application.properties file.
Check warnings in the CHORAL_LOG table (with permission to select the table). See details of logging configuration relating CHORAL_LOG table. Look for messages like Invalid structure with rowid <rowid>: <error>
By this query on an indexed table:
select t.* from <index_name>_tbl,<table_name> t where is_valid=0 and t.rowid=rid;
note: this is an unofficial solution, it may be changed without notice between versions.
By this query (preferably on an indexed table):
select * from test where rowid in ( select rowid from test minus select rowid from test where sample_search(mol,'*','SUBSTRUCTURE')=1 );
SELECT * FROM <table_name> WHERE rowid=(SELECT long_to_rowid(data_object_id,<key>) FROM ALL_OBJECTS WHERE object_name='<TABLE_NAME>' AND owner='<TABLE_OWNER>');
SELECT * FROM v$session_longops WHERE sofar<totalwork ORDER BY start_time DESC;
sdf file (example.sdf)
Oracle DB access with table creation grants
The following example will import the example.sdf file records into the mol column. Note that the fields of the sdf records are not separated and imported into separate columns.
Create table in the database (this table will be used to import the data into):
CREATE TABLE testtable ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, mol CLOB NOT NULL);
Create an sqlldr control file with the following content.
sdfload.ctl: load data infile 'example.sdf' "str '$$$$\n'" into table testtable fields terminated by '$$$$' (mol char(10000))
Load the sdf file from command line with sqlldr command (the command will ask you username and password to access the database). The sqlldr command should be executed in the directory where the example.sdf and sdfload.ctl are located.
sqlldr CONTROL=sdfload.ctl LOG=/tmp/test.log BAD=/tmp/test.bad
ORA-06598: insufficient INHERIT PRIVILEGES privilege error can be received in case of Oracle 12.1 if statements using the operators, functions of Choral are issued by a dba_user (Sys or System).
grant inherit any privileges on user <dba_user> to <choral_owner>;
Inconsistent search results might be received if during the run of the search some modifications (insert, update or delete) of stored chemical structures of the chemical structure table(s) affected by the search are committed by a different user than the searcher one.
Indexing can be slow due to not doing calculations in parallel in special cases. If the indexed table is not owned by the user who issued the CREATE INDEX command and the indexing user don’t have select role with grant option on the indexed table or doesn’t have the create view role and the cartridge owner doesn’t have select role on the indexed table.
Cost estimation does not work for query structures in CLOB.
CREATE OR REPLACE FUNCTION my_search(query CLOB) RETURN NUMBER AS query_v VARCHAR2(32767); result NUMBER; BEGIN IF dbms_lob.getlength(query) <= 32767 THEN query_v := dbms_lob.substr(query, dbms_lob.getlength(query), 1); SELECT COUNT(*) INTO result FROM test WHERE general_search(mol, query_v, 'SUBSTRUCTURE') = 1; ELSE SELECT COUNT(*) INTO result FROM test WHERE general_search(mol, query, 'SUBSTRUCTURE') = 1; END IF; RETURN result; END; / SELECT my_search(TO_CLOB('CC')) FROM DUAL;
CREATE OR REPLACE FUNCTION my_search2(table_name VARCHAR2, query CLOB, strategy VARCHAR2) RETURN NUMBER AS query_v VARCHAR2(32767); result NUMBER; BEGIN IF (dbms_lob.getlength(query) <= 32767) then query_v := dbms_lob.substr(query, dbms_lob.getlength(query), 1); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_name || ' WHERE general_search(mol, ''' || query_v || ''', ''' || strategy || ''') = 1' INTO result; ELSE EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_name || ' WHERE general_search(mol, ''' || query || ''', ''' || strategy || ''') = 1' INTO result; END IF; RETURN result; END; / SELECT my_search2('test', TO_CLOB('C'), 'SUBSTRUCTURE') FROM DUAL; SELECT my_search2('test', TO_CLOB('C'), 'DUPLICATE') FROM DUAL; SELECT my_search2('test', (SELECT mol FROM test WHERE id = 4), 'SUBSTRUCTURE') FROM DUAL;
Cost estimation automatically runs for a given environment using predefined values. It is not possible to run an automated calibration for it to adjust to the custom environment. However, cost estimation factors can be adjusted manually and thus the plan selected for chemical search by Oracle can be influenced. See how to make calibration settings manually.
After an apparently successful installation and index creation, the search processes can throw No more data to read from socket error.
Ocassionally, the error appears also at the end of the installation process.
At the moment, the only viable workaround is repeating the installation.
In the case molecule types with tautomer=GENERIC parameter, similarity search gives false results. These is no workaround at the moment, please do not execute similarity search in structure columns having molecule type with tautomer=GENERIC parameter.
From version 21.9.0 similarity search works correctly even in the case of molecule types with tautomer=GENERIC parameter.