| xml search + single quote handling (merged) [message #356516] |
Thu, 30 October 2008 23:55  |
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
|
Member |
|
|
i m using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
i want to search in xml
i have this query
SELECT /*+ FIRST_ROWS INDEX(p I01_GPAPR_010_FULL) */
P.DATA_ID_DATE,
P.DATA_ID_SEQ,
P.DATA_VERSION,
P.DATA.GETCLOBVAL()
FROM GPAPR_010_KREDIT_ABLAGE_DAT P
WHERE CONTAINS(DATA,'((a'b WITHIN Kr_Fo_Container_Nr )) WITHIN OrderAttributes') > 0
AND ROWNUM <= 100
ORDER BY P.ARCHIVE_TIMESTAMP DESC
i m not allowed to search for a'b so i change it to a''b
but it returns me value in xml which contains
i get two output
a'b
a b
so can any one tell me how to handle single quote(') in xml select query.
|
|
|
|
|
|
| Re: xml search + single quote handling (merged) [message #356672 is a reply to message #356516] |
Fri, 31 October 2008 16:54  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
By default when you create a context index, it treats characters like the apostrophe the same as white space. You can declare them as printjoins as the attribute of a lexer and use that lexer in your parameters during index creation. Please see the reproduction and correction below.
-- reproduction:
SCOTT@orcl_11g> CREATE TABLE test_tab (test_col VARCHAR2(30))
2 /
Table created.
SCOTT@orcl_11g> INSERT ALL
2 INTO test_tab VALUES ('ab')
3 INTO test_tab VALUES ('a''b')
4 INTO test_tab VALUES ('a b')
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 /
Index created.
SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
AB
B
SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a''b') > 0
2 /
TEST_COL
------------------------------
a'b
a b
SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a b') > 0
2 /
TEST_COL
------------------------------
a'b
a b
SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'ab') > 0
2 /
TEST_COL
------------------------------
ab
-- correction:
SCOTT@orcl_11g> DROP INDEX test_idx
2 /
Index dropped.
SCOTT@orcl_11g> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '''');
4 END;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('LEXER test_lex')
4 /
Index created.
SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
A'B
AB
B
SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a''b') > 0
2 /
TEST_COL
------------------------------
a'b
SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a b') > 0
2 /
TEST_COL
------------------------------
a b
SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'ab') > 0
2 /
TEST_COL
------------------------------
ab
SCOTT@orcl_11g>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
