Hi Gabor,
Apologies for this delayed reply on this, other priorities got in the way. However this remains an issue for me for the production of SDF export files.
This command is run only on windows exactly with the code that was attached to my previous post , the code that produces the SDF text file is reproduced in full. It simply uses the oracle UTIL_FILE procedures to write out the data to the operating system file. The relevent command is:
UTL_FILE.PUT_LINE(v_file,
v_ctab ||
'> <PUBCHEM_EXT_DATASOURCE_REGID>' || CHR(10) ||
cur_rec.AKOS_NUMBER || CHR(10) || CHR(10) ||
'> <PUBCHEM_EXT_DATASOURCE_URL>' || CHR(10) ||
'http://www.akosgmbh.de/AKosSamples/index.html' || CHR(10) || CHR(10) ||
'> <PUBCHEM_EXT_SUBSTANCE_URL>' || CHR(10) ||
'http://akosservice.de/akossamplesservice/php/akossamplesretrieval.php?IDNUMBERS=' || cur_rec.AKOS_NUMBER || CHR(10) || CHR(10) ||
'> <PUBCHEM_DEPOSITOR_RECORD_DATE>' || CHR(10) ||
v_date || CHR(10) ||
'$$$$'
);
the variable v_ctab is loaded with either
(a) a direct select on the CLOB column, in which case it produces a normally formed molfile output.
select ctab_clob into v_ctab
from akos_moltable where AKOS_NUMBER = cur_rec.AKOS_NUMBER;
(b) a call to the molconvert function, in which case extra line feed appear in the output.
select jcf.molconvert(CTAB_CLOB,'mol') into v_ctab
from akos_moltable where AKOS_NUMBER = cur_rec.AKOS_NUMBER;
This should be easy to reproduce:
- Create a simple regular (not JChem) molecule table with a CLOB column and possibly an ID column.
- Insert some molefile data into the CLOB Column
- create an index on the clob column with : CREATE INDEX AKOS_JC_MOLIX ON AKOS_MOLTABLE(ctab_clob) INDEXTYPE IS jchem.jc_idxtype PARAMETERS('haltOnError=nf' );
- run a version of the plsql script against the clob column to output the data into text file via the UTIL_FILE procedures.
- Compare the results with the usage of method (a) and (b) above.
kind regards
Bernard