This is also based on one of my posting in OTN.
The problem description:
The problem description:
SQL> create table xx2(id varchar2(10),name varchar2(10));
Table created.
SQL> insert into xx2 values (1,'xyz');
1 row created.
SQL> insert into xx2 values (2,'abc');
1 row created.
SQL> insert into xx2 values (3,'zadaf');
1 row created.
SQL> create table xx3(id varchar2(10),name varchar2(10));
Table created.
SQL> insert into xx3 values (1,'afdafef');
1 row created.
SQL> insert into xx3 values ('56,78,2,6','afefef');
1 row created.
SQL> insert into xx3 values ('45,67,3','svfsrgg');
1 row created.
SQL> select * from xx2;
ID NAME
---------- ----------
1 xyz
2 abc
3 zadaf
SQL> select * from xx3;
ID NAME
---------- ----------
1 afdafef
56,78,2,6 afefef
45,67,3 svfsrgg
/*
I want to join table xx2 & x3 based on id and i have to retrive all 4 rows becuase id's of xx1 is there in id's of xx2 but in between commas.So i have to look in between commas as well. */
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 3 11:36:40 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SHOW USER
USER is "SYS"
SQL> --My database version.
SQL> SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> --Preparing the user SCOTT to use Oracle Text.
SQL> ----------------------------------------------
SQL> --Grant Role.
SQL> GRANT ctxapp TO scott;
Grant succeeded.
SQL> --Grant EXECUTE Privileges on CTX PL/SQL Packages.
SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO scott;
Grant succeeded.
SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO scott;
Grant succeeded.
SQL>
[oracle@localhost ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 3 11:59:34 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SHOW USER
USER is "SCOTT"
SQL> --Your table and data setup.
SQL> create table xx2(id varchar2(10),name varchar2(10));
Table created.
SQL> insert into xx2 values (1,'xyz');
1 row created.
SQL> insert into xx2 values (2,'abc');
1 row created.
SQL> insert into xx2 values (3,'zadaf');
1 row created.
SQL> create table xx3(id varchar2(10),name varchar2(10));
Table created.
SQL> insert into xx3 values (1,'afdafef');
1 row created.
SQL> insert into xx3 values ('56,78,2,6','afefef');
1 row created.
SQL> insert into xx3 values ('45,67,3','svfsrgg');
1 row created.
SQL> insert into xx3 values (34,'sff');
1 row created.
SQL> set line 150
SQL> SELECT * FROM xx2;
ID NAME
---------- ----------
1 xyz
2 abc
3 zadaf
SQL> SELECT * FROM xx3;
ID NAME
---------- ----------
1 afdafef
56,78,2,6 afefef
45,67,3 svfsrgg
34 sff
SQL> commit;
Commit complete.
SQL>
Now, The actual setup and testing:
SQL>
SQL> SHOW USER
USER is "SCOTT"
SQL> -- Creating my text index setup.
SQL> --------------------------------
SQL> --Droping my preference, you may not need this for the first time.
SQL> BEGIN
2 CTX_DDL.DROP_PREFERENCE('SAUBHIK_SJ_PREF');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> -- Creating now.
SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('SAUBHIK_SJ_PREF',BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE('SAUBHIK_SJ_PREF','STARTJOINS',',');
4 END;
5 /
ERROR:
ORA-01756: quoted string not properly terminated
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 CTX_DDL.CREATE_PREFERENCE('SAUBHIK_SJ_PREF','BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE('SAUBHIK_SJ_PREF','STARTJOINS',',');
4* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> DROP INDEX xx3_context_idx;
Index dropped.
SQL> CREATE INDEX xx3_context_idx ON xx3(id) INDEXTYPE IS CTXSYS.CONTEXT parameters('lexer SAUBHIK_SJ_PREF');
Index created.
SQL> set line 100
SQL> ed
Wrote file afiedt.buf
1 SELECT t2.id id_xx3,t1.name name_xx2,t2.name name_xx3
2 FROM xx2 t1,xx3 t2
3* WHERE CONTAINS(t2.id,t1.id)>0;
SQL> /
WHERE CONTAINS(t2.id,t1.id)>0;
*
ERROR at line 3:
ORA-00911: invalid character
SQL> ed
Wrote file afiedt.buf
1 SELECT t2.id id_xx3,t1.name name_xx2,t2.name name_xx3
2 FROM xx2 t1,xx3 t2
3* WHERE CONTAINS(t2.id,t1.id)>0
SQL> /
ID_XX3 NAME_XX2 NAME_XX3
---------- ---------- ----------
1 xyz afdafef
56,78,2,6 abc afefef
45,67,3 zadaf svfsrgg
SQL> ed
Wrote file afiedt.buf
1 SELECT t2.id id_xx3,t1.name name_xx2,t2.name name_xx3
2 FROM xx2 t1 RIGHT OUTER JOIN xx3 t2
3* ON CONTAINS(t2.id,t1.id)>0
4 /
ID_XX3 NAME_XX2 NAME_XX3
---------- ---------- ----------
1 xyz afdafef
56,78,2,6 abc afefef
45,67,3 zadaf svfsrgg
34 sff
