Home » Server Options » Text & interMedia » ORA-06521-PL/SQL: Error mapping function while running catsearch (Oracle 10.2.0.4/ OS- SUNW,SPARC-Enterprise-T5220)
ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545832] Fri, 02 March 2012 11:30 Go to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Hi,

I am getting ORA-06521-PL/SQL: Error mapping function while running CATSEARCH query as below:


SQL> select * from bls_nm_map where catsearch(NAMEADDRESS,'GOLMAN',null) >0 ;
select * from bls_nm_map where catsearch(NAMEADDRESS,'GOLMAN',null) >0
*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function


Here is DDL for this table/index

SQL> desc bls_nm_map
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INVNOID                                            NUMBER(20)
 NAMEADDRESS                                        VARCHAR2(200)


Storage Preferences:
 begin
CTXSYS.CTX_DDL.create_preference('INSIDER_PRF1','BASIC_STORAGE');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','I_TABLE_CLAUSE','tablespace INSIDER_IDX');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','K_TABLE_CLAUSE','tablespace INSIDER_IDX');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','N_TABLE_CLAUSE','tablespace INSIDER_IDX');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','I_INDEX_CLAUSE','tablespace INSIDER_IDX Compress 2');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','P_TABLE_CLAUSE','tablespace INSIDER_IDX');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','I_ROWID_INDEX_CLAUSE','tablespace INSIDER_IDX');
CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','R_TABLE_CLAUSE','tablespace INSIDER_IDX LOB(DATA) STORE AS (CACHE)');
END;
/

BEGIN
     CTXSYS.CTX_DDL.CREATE_PREFERENCE ('INSIDER_PRF_LEXER', 'BASIC_LEXER');
     CTXSYS.CTX_DDL.SET_ATTRIBUTE ('INSIDER_PRF_LEXER', 'SKIPJOINS',  '- ''.,');
END;
/
BEGIN
     CTXSYS.CTX_DDL.CREATE_PREFERENCE ('INSIDER_PRF_WORDLIST', 'BASIC_WORDLIST');
     CTXSYS.CTX_DDL.SET_ATTRIBUTE ('INSIDER_PRF_WORDLIST', 'WILDCARD_MAXTERMS', 15000 );
END;
/


Text Index creation
CREATE INDEX bls_nm_map_IX1 ON INSIDER.bls_nm_map(nameaddress) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS('storage INSIDER_PRF1 STOPLIST ctxsys.empty_stoplist WORDLIST INSIDER_PRF_WORDLIST') NOPARALLEL


Any thoughts for this error?

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545841 is a reply to message #545832] Fri, 02 March 2012 12:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
I am unable to reproduce the error. The code runs fine on my system as shown below. Is everything you posted in the same schema? Are you able to use catsearch at all? Can you post a copy and paste as I did below, but that produces the error?

INSIDER@orcl_11gR2> CREATE TABLE bls_nm_map
  2    (INVNOID      NUMBER(20),
  3  	NAMEADDRESS  VARCHAR2(200))
  4  /

Table created.

INSIDER@orcl_11gR2> INSERT ALL
  2  INTO bls_nm_map VALUES (1, 'test data')
  3  INTO bls_nm_map VALUES (2, 'golman')
  4  INTO bls_nm_map VALUES (3, 'more test data')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

INSIDER@orcl_11gR2> COMMIT
  2  /

Commit complete.

INSIDER@orcl_11gR2> begin
  2    CTXSYS.CTX_DDL.create_preference('INSIDER_PRF1','BASIC_STORAGE');
  3    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','I_TABLE_CLAUSE','tablespace USERS');
  4    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','K_TABLE_CLAUSE','tablespace USERS');
  5    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','N_TABLE_CLAUSE','tablespace USERS');
  6    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','I_INDEX_CLAUSE','tablespace USERS Compress 2');
  7    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','P_TABLE_CLAUSE','tablespace USERS');
  8    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','I_ROWID_INDEX_CLAUSE','tablespace USERS');
  9    CTXSYS.CTX_DDL.set_attribute('INSIDER_PRF1','R_TABLE_CLAUSE','tablespace USERS LOB(DATA) STORE AS (CACHE)');
 10  END;
 11  /

PL/SQL procedure successfully completed.

INSIDER@orcl_11gR2> BEGIN
  2  	  CTXSYS.CTX_DDL.CREATE_PREFERENCE ('INSIDER_PRF_LEXER', 'BASIC_LEXER');
  3  	  CTXSYS.CTX_DDL.SET_ATTRIBUTE ('INSIDER_PRF_LEXER', 'SKIPJOINS',  '- ''.,');
  4  END;
  5  /

PL/SQL procedure successfully completed.

INSIDER@orcl_11gR2> BEGIN
  2  	  CTXSYS.CTX_DDL.CREATE_PREFERENCE ('INSIDER_PRF_WORDLIST', 'BASIC_WORDLIST');
  3  	  CTXSYS.CTX_DDL.SET_ATTRIBUTE ('INSIDER_PRF_WORDLIST', 'WILDCARD_MAXTERMS', 15000 );
  4  END;
  5  /

PL/SQL procedure successfully completed.

INSIDER@orcl_11gR2> CREATE INDEX bls_nm_map_IX1 ON bls_nm_map(nameaddress)
  2  INDEXTYPE IS CTXSYS.CTXCAT
  3  PARAMETERS
  4    ('STOPLIST ctxsys.empty_stoplist
  5  	 storage  INSIDER_PRF1
  6  	 LEXER	  insider_prf_lexer
  7  	 WORDLIST INSIDER_PRF_WORDLIST')
  8  NOPARALLEL
  9  /

Index created.

INSIDER@orcl_11gR2> select * from bls_nm_map where catsearch(NAMEADDRESS,'GOLMAN',null) >0
  2  /

   INVNOID
----------
NAMEADDRESS
--------------------------------------------------------------------------------
         2
golman


1 row selected.



Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545859 is a reply to message #545841] Fri, 02 March 2012 14:18 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
No.. I am not able to use catsearch at all. I have tried uninstalling/installing oracle text and it failed with the same error
SQL> select  * from bls_accountdata_tx where catsearch(nameaddress,'MFTC-RAPP FBO KIGHTLINGERGRAY, LLP FBO MARY ALICE RAPP GINA O BRIEN 4333 EDGEWOOD ROAD NE CEDAR RAPIDS IA 52499-0001 MFTC-RAPP',NULL) > 0
  2  ;
select  * from bls_accountdata_tx where catsearch(nameaddress,'MFTC-RAPP FBO KIGHTLINGERGRAY, LLP FBO MARY ALICE RAPP GINA O BRIEN 4333 EDGEWOOD ROAD NE CEDAR RAPIDS IA 52499-0001 MFTC-RAPP',NULL) > 0
               *
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545861 is a reply to message #545859] Fri, 02 March 2012 14:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Have you tried just a simple test on a small table without any preferences or parameters used in indexing and everything (table, index, and query) in the same schema? Can you post a copy and paste from SQL*Plus of such a test?

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545862 is a reply to message #545861] Fri, 02 March 2012 15:37 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
I have run the same query while logging in as sysdba and it worked fine. it seems to be privilege issues. Do you know what privileges are required for user to run catsearch. I already granted execute on CTX_DDL and CTX_REPORT.

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545864 is a reply to message #545862] Fri, 02 March 2012 16:43 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
I have created index without preference and storage parameters as below:


Harshad@insider> create table text_test as select * from bls_nm_map where rownum <=2000;

Table created.

Harshad@insider> desc text_test;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 INVNOID                                                        NUMBER(20)
 NAMEADDRESS                                                    VARCHAR2(200)

Harshad@insider> CREATE INDEX INSIDER.text_test_IX1 ON INSIDER.text_test(nameaddress) INDEXTYPE IS CTXSYS.CTXCAT;

Index created.

Harshad@insider> select * from  text_test where catsearch(NAMEADDRESS,'"NY EXCHANGE"',null) >0;
select * from  text_test where catsearch(NAMEADDRESS,'"NY EXCHANGE"',null) >0
*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #545865 is a reply to message #545864] Fri, 02 March 2012 16:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
You could try the CTXAPP role.

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #546190 is a reply to message #545865] Mon, 05 March 2012 11:22 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Barbara,

We have found the issue. There was a CTXSYS install attempt made using application user which has DBA privileges and it was causing this error. We have drop the CTXSYS objects from the app user account and query is working fine now.

Thanks for your help!!

Report message to a moderator

Re: ORA-06521-PL/SQL: Error mapping function while running catsearch [message #546192 is a reply to message #546190] Mon, 05 March 2012 12:06 Go to previous message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Thanks for letting us know.

Report message to a moderator

Previous Topic: ordimageindex problem
Next Topic: Fulltext functional lookup on BLOB
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 06:46:48 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.