Before You Begin

This 15-minute tutorial shows you how to search for proper names that differ in spelling because of orthographic variations.

Background

Because spelling rules for proper names differ from one language to another, it can be difficult to apply rules to a name originating from a different language. The Oracle Text name search provides a solution to match proper names that differ in spelling.

What Do You Need?

• Oracle Database 18c
• Oracle SQL Developer 17.3.2

Create a Database Connection

1. To start your SQL Developer session, run sqldeveloper.exe in Windows or sqldeveloper.sh in Linux.
2. On the Connections tab, right-click Connections and select New Connection.
3. In the New / Select Database Connection window, enter or select the details for your database connection. This example uses the following values:
• Connection Name: sys_conn
• Username: sys
• Password: your SYS user password
• Role: SYSDBA
• Hostname: localhost
• SID: orcl
4. Click Test and verify the status of the connection. In this example, the status displays Success above the Help button.

   

5. Click Save, click Connect, and close the window. When a connection is established, a SQL worksheet is opened automatically. The SQL worksheet enables you to run SQL commands by using the connection that you created.

Connect to an Oracle Text User

1. Create the user.

   CREATE USER myuser IDENTIFIED BY your_password;

2. Grant roles to the user.

   GRANT RESOURCE, CONNECT, CTXAPP TO myuser;

The CTXAPP role enables you to create Oracle Text indexes and index preferences and to use PL/SQL packages.

3. For myuser, grant an unlimited quota to the USERS tablespace.

   ALTER USER myuser QUOTA UNLIMITED ON USERS;

4. Create a connection to myuser. On the Connections tab, right-click Connections and select New Connection.
5. In the New / Select Database Connection window, enter or select the details for your database connection. This example uses the following values:
• Connection Name: myuser_conn
• Username: myuser
• Password: your user password
• Role: default
• Hostname: localhost
• SID: orcl
6. Click Test and verify the status of the connection. In this example, the status displays Success above the Help button.

   

7. Click Save, click Connect, and close the window. When a connection is established, a SQL worksheet is opened automatically. The SQL worksheet enables you to run SQL commands by using the connection that you created.

Search for Names

1. Create a table with id and name columns.

   CREATE TABLE names (id NUMBER (10), name VARCHAR2 (200));

2. Populate your table with names.

   INSERT INTO names VALUES (1, '<name>Johnnie Smith</name>');
   INSERT INTO names VALUES (2, '<name>Joana David</name>');
   INSERT INTO names VALUES (3, '<name>John Smith</name>');
   INSERT INTO names VALUES (4, '<name>Jane Doe</name>');
   INSERT INTO names VALUES (5, '<name>Jon Doe</name>');

3. Create a section group called name_sg of the BASIC_SECTION_GROUP type.

EXEC CTX_DDL.CREATE_SECTION_GROUP ('name_sg', 'BASIC_SECTION_GROUP');

BASIC_SECTION_GROUP enables you to define sections where the start and end tags are of the form <tag> and </tag>. In the example in Step 2, <name> and </name> are the start and end tags.

4. In the name_sg section group, create an NDATA section called cust_name for all text within the name tag. The name of the tag marks the start of a section and must be unique within a section group.

   EXEC CTX_DDL.ADD_NDATA_SECTION ('name_sg', 'cust_name', 'name');

The ADD_NDATA_SECTION procedure enables you to find matches that have similar spellings.

5. Create an index on the table by using the section group.

   CREATE INDEX myindex ON names(name) indextype IS 
   CTXSYS.CONTEXT PARAMETERS ('section group name_sg');

6. Search for a name. Because you created an index of CONTEXT type, specify your query expression with the CONTAINS operator in the SELECT statement. To return the score values of each hit, specify the SCORE operator.

   SELECT id, name, SCORE(1) as score FROM names WHERE CONTAINS 
   (name, 'NDATA (cust_name, Jon)', 1) > 0 ORDER BY SCORE(1) DESC;

   

Want to Learn More?

• Using Oracle Text Name Search