"string too long" with queries > 4k - ChemAxon Forum Archive

ChemAxon aa7c50abf8

24-08-2007 18:47:38

Quote:

When doing a structure search with a big molecule, whose molfile string longer than 4k, we got a "string too long" error. We have to convert the molfile to smiles in order to perform the search. But in that way, some stereo-chemistry information will loose.

No need to change format. way to get around this is to

1. create a temporary CLOB;

2. inject the query structure into the temporary CLOB;

3. pass this CLOB as the query structure parameter to jc_compare and finally

4. free the temporary CLOB.

One possible implementation using an anonymous PL/SQL block looks something like:

Code:

declare

cursor srch(qry_struct clob) is

select * from c_nci_10k

where jc_compare(structure, qry_struct, 't:e stereoSearch:n') = 1;

q varchar2(32767) := '78

ROtclserve11150008142D 0 0.00000 0.00000 78

6290-84-2

67 72 0 0 0 0 0 0 0 0 3 V2000

4.5981 -2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

4.5981 -3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.4641 -1.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

3.7321 -3.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.4641 -0.5000 0.0000 O 0 3 0 0 0 0 0 0 0 0 0 0

6.3301 -2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.8660 -3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

3.7321 -4.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

6.3301 0.0000 0.0000 Cu 0 4 0 0 0 0 0 0 0 0 0 0

7.1962 -1.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.0000 -3.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.8660 -5.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.4641 0.5000 0.0000 O 0 3 0 0 0 0 0 0 0 0 0 0

7.1962 -0.5000 0.0000 O 0 3 0 0 0 0 0 0 0 0 0 0

7.1962 0.5000 0.0000 O 0 3 0 0 0 0 0 0 0 0 0 0

8.0622 -2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.0000 -4.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.4641 1.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

7.1962 1.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.0622 -3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.9282 -1.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

6.3301 2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

4.5981 2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.0622 2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.9282 -3.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

9.7942 -2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

4.5981 3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

3.7321 1.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.0622 3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

9.7942 -3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

3.7321 3.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.8660 2.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.9282 3.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.8660 3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

9.7942 3.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.9282 4.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

10.6603 3.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

9.7942 5.0000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

10.6603 4.5000 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

4.0611 -1.6900 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

5.1350 -3.3100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

5.9316 -2.4749 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

6.7287 -2.4749 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

2.8660 -2.3800 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

4.2690 -4.8100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

1.4631 -3.1900 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

2.8660 -5.6200 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

1.4631 -4.8100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

7.5252 -3.3100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

8.9282 -0.8800 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

6.7287 2.4749 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

5.9316 2.4749 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

8.5991 1.6900 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

8.9282 -4.1200 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

10.3312 -1.6900 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

5.1350 3.3100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

3.7321 0.8800 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

7.5252 3.3100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

10.3312 -3.3100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

3.7321 4.1200 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

2.3291 1.6900 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

2.3291 3.3100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

9.7942 2.3800 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

8.3913 4.8100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

11.1972 3.1900 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

9.7942 5.6200 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

11.1972 4.8100 0.0000 H 0 0 0 0 0 0 0 0 0 0 0 0

1 2 2 0 0 0 0

1 3 1 0 0 0 0

2 4 1 0 0 0 0

3 5 2 0 0 0 0

3 6 1 0 0 0 0

4 7 2 0 0 0 0

4 8 1 0 0 0 0

5 9 1 0 0 0 0

6 10 1 0 0 0 0

7 11 1 0 0 0 0

8 12 2 0 0 0 0

9 13 1 0 0 0 0

9 14 1 0 0 0 0

9 15 1 0 0 0 0

10 16 1 0 0 0 0

11 17 2 0 0 0 0

13 18 2 0 0 0 0

15 19 2 0 0 0 0

16 20 2 0 0 0 0

16 21 1 0 0 0 0

18 22 1 0 0 0 0

18 23 1 0 0 0 0

19 24 1 0 0 0 0

20 25 1 0 0 0 0

21 26 2 0 0 0 0

23 27 2 0 0 0 0

23 28 1 0 0 0 0

24 29 2 0 0 0 0

25 30 2 0 0 0 0

27 31 1 0 0 0 0

28 32 2 0 0 0 0

29 33 1 0 0 0 0

31 34 2 0 0 0 0

33 35 2 0 0 0 0

33 36 1 0 0 0 0

35 37 1 0 0 0 0

36 38 2 0 0 0 0

37 39 2 0 0 0 0

10 14 2 0 0 0 0

12 17 1 0 0 0 0

19 22 1 0 0 0 0

26 30 1 0 0 0 0

32 34 1 0 0 0 0

38 39 1 0 0 0 0

1 40 1 0 0 0 0

2 41 1 0 0 0 0

6 42 1 0 0 0 0

6 43 1 0 0 0 0

7 44 1 0 0 0 0

8 45 1 0 0 0 0

11 46 1 0 0 0 0

12 47 1 0 0 0 0

17 48 1 0 0 0 0

20 49 1 0 0 0 0

21 50 1 0 0 0 0

22 51 1 0 0 0 0

22 52 1 0 0 0 0

24 53 1 0 0 0 0

25 54 1 0 0 0 0

26 55 1 0 0 0 0

27 56 1 0 0 0 0

28 57 1 0 0 0 0

29 58 1 0 0 0 0

30 59 1 0 0 0 0

31 60 1 0 0 0 0

32 61 1 0 0 0 0

34 62 1 0 0 0 0

35 63 1 0 0 0 0

36 64 1 0 0 0 0

37 65 1 0 0 0 0

38 66 1 0 0 0 0

39 67 1 0 0 0 0

M CHG 4 5 1 13 1 14 1 15 1

M RAD 1 9 2

M END

> <NSC>

78

> <CAS_RN>

6290-84-2

> <SMILES>

C1C(=[O+][Cu]2([O+]=C(CC(=[O+]2)C3=CC=CC=C3)C=CC4=CC=CC=C4)[O+]=C1C5=CC=CC=C5)C=CC6=CC=CC=C6

> <HASH>

93d0d20c895f161d

$$$$';

tmp_clob clob;

begin

dbms_lob.createtemporary(tmp_clob, false);

dbms_lob.write(tmp_clob, length(q), 1, q);

for t in srch(tmp_clob) loop

dbms_output.put_line('>>> ' || to_char(t.id));

end loop;

dbms_lob.freetemporary(tmp_clob);

end;