Home » Server Options » Text & interMedia » xml search + single quote handling (merged)
xml search + single quote handling (merged) [message #356516] Thu, 30 October 2008 23:55 Go to next message
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: single quote handling [message #356543 is a reply to message #356516] Fri, 31 October 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.

Regards
Michel
Re: xml search + single quote handling (merged) [message #356672 is a reply to message #356516] Fri, 31 October 2008 16:54 Go to previous message
Barbara Boehmer
Messages: 9077
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>

Previous Topic: Problems with CTX_DOC.SNIPPET on HTML documents
Next Topic: BFile content index And Network Share
Goto Forum:
  


Current Time: Thu Mar 28 13:25:59 CDT 2024