Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to create Oracle Text users". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how to create Oracle Text users".

1. Create an Oracle Text user

Before creating an Oracle Text index and using the Oracle Text PL / SQL package, you need to create a user with the CTXAPP role. The role can do the following:

Create and delete Oracle Text index preferences

Use the Oracle Text PL / SQL software package

-1. Create the user ctxdevCREATE USER ctxdev IDENTIFIED BY oracle default tablespace tbs_ctxdev;--2. Assign roles to users ctxdevGRANT RESOURCE, CONNECT, CTXAPP TO ctxdev;--3. Execute permissions granted to the CTX PL/SQL package GRANT EXECUTE ON CTXSYS.CTX_CLS TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_DDL TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_DOC TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_QUERY TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_REPORT TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_THES TO ctxdev;GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO ctxdev;-- these permissions have been granted to the CTXAPP role. However, because role permissions do not always work during the PL/SQL process, the safest way is to explicitly grant these permissions to users who already have the CTXAPP role. SQL > select role,privilege,table_name from role_tab_privs where role='CTXAPP' order by 2 Pol 3 ROLE PRIVILEGE TABLE_NAME- CTXAPP DELETE DR$DICTIONARYCTXAPP EXECUTE CTX_ANLCTXAPP EXECUTE CTX_DDLCTXAPP EXECUTE CTX_ENTITYCTXAPP EXECUTE CTX_OUTPUTCTXAPP EXECUTE CTX_THESCTXAPP EXECUTE CTX_TREECTXAPP EXECUTE CTX_ULEXERCTXAPP EXECUTE DRIENTLCTXAPP EXECUTE DRITHSLCTXAPP INSERT DR$DICTIONARYCTXAPP INSERT DR$THSCTXAPP INSERT DR$THS_BTCTXAPP INSERT DR$THS_FPHRASECTXAPP INSERT DR$THS_PHRASECTXAPP SELECT DR$DICTIONARYCTXAPP UPDATE DR$DICTIONARYCTXAPP UPDATE DR$THS_PHRASE

If Oracle Text is not installed in the database, granting CTXAPP reports the following error:

ORA-01919: role 'CTXAPP' does not exist

At this point, we need to install Oracle Text for the database. The test environment is 12c multi-tenancy. You can install the Oracle Text component directly in PDB (please install it in CDB first, otherwise the next time you restart the database, no Oracle Text,PDB in CDB will enter restricted mode will be detected):

Method 1: execute the catctx.sql script SQL > alter session set container=orcl;SQL > select COMP_ID,COMP_NAME,VERSION,STATUS,SCHEMA from user_registry where comp_id='CONTEXT';no rows selected--1. Install Oracle TextSQL > @? / ctx/admin/catctx.sql oracle SYSAUX TEMP NOLOCK-execute script + parameters [ctxsys user password, user default permanent sysaux, temporary tablespace temp, whether the user locks nolock after creation | lock] SQL > col comp_name for a20 position SQL > set linesize 200 position SQL > col schema for a20 position SQL > col status for a20 position SQL > col version for a20 position SQL > col comp_id for a20 position SQL > select COMP_ID,COMP_NAME,VERSION,STATUS,SCHEMA from user_registry where comp_id='CONTEXT' COMP_ID COMP_NAME VERSION STATUS SCHEMA -CONTEXT Oracle Text 12.2.0.1.0 VALID CTXSYS--2. Install the default language preference SQL > @? / ctx/admin/defaults/dr0defin.sql "AMERICAN";-- method 2:DBCA silent install-- Note: adding database components (such as Oracle Text) to a single PDB through DBCA is not supported in 12.1.0.2 or 12.2.0.1. This feature will be added in 18.1. Syntax:-- non-multi-group environment: dbca-silent-configureDatabase-sourceDB-sysDBAUserName-sysDBAPassword]-addDBOption ORACLE_TEXT-- multi-tenant environment: dbca-silent-configurePluggableDatabase-sourceDB-pdbName-sysDBAUserName SYS-sysDBAPassword-addDBOption ORACLE_TEXT--1. Install Oracle Textdbca-silent-configureDatabase-SourceDB lycdb-sysDBAUsername sys-sysDBAPassword oracle-addDBOption ORACLE_TEXT--2. Install default language preferences if the language is not English, install the appropriate language-specific default preferences. The $ORACLE_HOME/ctx/admin/defaults directory contains scripts to create language-specific default preferences for each language supported by Oracle Text, such as English (US), Danish (DK), Dutch (NL), Finnish (SF), French (F), German (D), Italian (IT), Portuguese (PT), Spanish (E) and Swedish (S). They are named in the form of drdefXX.sql, where XX is the international credential code. If you do not know the international voucher code, you can also call dr0defdp.sql (remove default preferences) and dr0defin.sql (installation preferences based on language name) in general English: AMERICAN, Chinese SIMPLIFIED CHINESE for example, to manually install German default preferences, log in to sqlplus as CTXSYS and run the following statement: SQL > connect / sysdbaSQL > spool defprefs_install.logSQL > alter user ctxsys identified by ctxsys account unlock SQL > connect ctxsys/ctxsysSQL > @? / ctx/admin/defaults/dr0defdp.sqlSQL > @? / ctx/admin/defaults/dr0defin.sql "SIMPLIFIED CHINESE"; SQL > connect SYS/password as SYSDBASQL > alter user ctxsys account lock password expire;SQL > spool off

two。 Query application simple experience

In a basic text query application, the user enters a query word or phrase and expects the application to return a list of documents that best match the query. Such an application involves creating an CONTEXT index and querying it using CONTAINS.

Typically, a query application requires a user interface. An example of how to build such a query application using the CONTEXT index type is provided in the CONTEXT query application.

The following example provides basic SQL statements for loading text tables, indexing documents, and querying indexes.

-1. Create a table that holds text data and insert data SQL > conn ctxdev/oracle@orclCREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2); INSERT INTO docs VALUES (1, 'California is a state in the US.'); INSERT INTO docs VALUES (2,' Paris is a city in France.'); INSERT INTO docs VALUES (3, 'France is in Europe.'); commit;--2. Create a Context index-- index the HTML file by creating a CONTEXT index on the text column, as shown below. Because you are indexing HTML, this example uses the NULL_FILTER preference type (which is not filtered) and the HTML_SECTION_GROUP type. If you index documents in PDF,Microsoft Word or other formats, use CTXSYS.AUTO_FILTER (the default) as the FILTER preference. CREATE INDEX idx_docs ON docs (text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');-3. Query table data-find all documents that contain the word France: COLUMN text FORMAT A40 position SQL > SELECT SCORE (1), id, text FROM docs WHERE CONTAINS (text, 'France', 1) > 0 SCORE (1) ID TEXT--42 Paris is a city in France. 4 3 France is in Europe.--4. Show documents-in a real application, you can display the selected document and highlight the query words. Oracle Text enables you to mark documents using the CTX_DOC package. You can use anonymous PL/SQL blocks to demonstrate HTML document tags in SQL * Plus. However, in a real application, you can display the document in a browser. This PL/SQL example uses the in-memory version of CTX_DOC.MARKUP to highlight the word France in document 3. It allocates a temporary CLOB (character large object data type) to store tagged text and read it back to standard output. Then release the CLOB before exiting: SET SERVEROUTPUT ON;DECLARE mklob CLOB; amt NUMBER: = 40; line VARCHAR2 (80); BEGIN CTX_DOC.MARKUP ('idx_docs',' 3percent, 'France', mklob); DBMS_LOB.READ (mklob, amt, 1, line); DBMS_OUTPUT.PUT_LINE (line); DBMS_LOB.FREETEMPORARY (mklob); END;/ is in Europe.PL/SQL procedure successfully completed.--5. Synchronize indexes after data processing-when creating an CONTEXT index, you need to explicitly synchronize the index to update the index with any insert, update, or delete to the text table. You can use the CTX_DDL.SYNC_INDEX procedure to do this. -- insert new data INSERT INTO docs VALUES (4, 'Los Angeles is a city in California.'); INSERT INTO docs VALUES (5,' Mexico City is big.'); failed to query new data after commit;-- submission: SQL > SELECT SCORE (1), id, text FROM docs WHERE CONTAINS (text, 'city', 1) > 0 SCORE (1) ID TEXT--4 2 Paris is a city in France.-- synchronizes the index with 2 Mb memory Then rerun the query: SQL > EXEC CTX_DDL.SYNC_INDEX ('idx_docs',' 2M') New PL/SQL procedure successfully completed.-- data can be queried normally: SQL > SELECT SCORE (1), id, text FROM docs WHERE CONTAINS (text, 'city', 1) > 0 SCORE (1) ID TEXT--42 Paris is a city in France. 4 4 Los Angeles is a city in California. 4 5 Mexico City is big.

3. Simple experience of directory application

The example provides a basic SQL statement to create a catalog index for auction sites that sell electronic devices, such as cameras and CD players. New inventory is added every day, and the project description, bid date and price must be stored together.

For hybrid queries, the application needs good response time. The key is to determine which columns the user often searches to create the appropriate CTXCAT index. Queries for such indexes use the CATSEARCH operator.

-1. Create an inventory table and insert the data conn ctxdev/oracle@orclCREATE TABLE auction (item_id NUMBER,title VARCHAR2, category_id NUMBER,price NUMBER,bid_close DATE); INSERT INTO AUCTION VALUES (1, 'NIKON CAMERA', 1,400,' 24 murmury 2002'); INSERT INTO AUCTION VALUES (2, 'OLYMPUS CAMERA', 1,300,' 25 murmury 2002'); INSERT INTO AUCTION VALUES (3, 'PENTAX CAMERA', 1200,' 26 murmury 2002') INSERT INTO AUCTION VALUES (4, 'CANON CAMERA', 1,250,' 27 Murray Oct Mei 2002'); commit;--2. Determine the conditions that may be retrieved. In this example, make sure that all queries search for product descriptions in the title column, and that most queries are sorted by price. When using the CATSEARCH operator, specify terms for text columns and conditions for structured clauses. -3. Create a subindex to sort by price-- create an index set named auction_set and add a subindex EXEC CTX_DDL.CREATE_INDEX_SET ('auction_iset'); EXEC CTX_DDL.ADD_INDEX (' auction_iset','price');-- 4. Create CTXCAT index CREATE INDEX auction_titlex ON AUCTION (title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set auction_iset');-- 5. Query COLUMN title FORMAT a40 select title, price FROM auction WHERE CATSEARCH (title, 'CAMERA',' order by price') > 0 TITLE PRICE---PENTAX CAMERA 200CANON CAMERA 250OLYMPUS CAMERA 300NIKON CAMERA 400SQL > SELECT title Price FROM auction WHERE CATSEARCH (title, 'CAMERA',' price

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.