OraFAQ Forum: Forms » Asking for search algorithm

Home » Developer & Programmer » Forms » Asking for search algorithm (Oracle Forms 6i, PL/SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Asking for search algorithm [message #667810]

Wed, 17 January 2018 19:59

NDKA739125
Messages: 33
Registered: January 2018

Member

Good day, my friends.
I'm doing a search box. And the request is: importing a string, the search box will give some suggestion.
For example:
The string: ALEXANDER
input:
ALAXENDER > OK
ALEXSANDER > OK
ALXANDER > OK
ALASANDER > OK
I'm using PL/SQL on Oracle form 6i.
Thanks in advance Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #667811 is a reply to message #667810]

Thu, 18 January 2018 00:13

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Those "OK" options sound like "The string", so you could try with SOUNDEX function. It

Oracle

returns a character string containing the phonetic representation of char . This function lets you compare words that are spelled differently, but sound alike in English.

Or, somewhat more advanced, UTL_MATCH.JARO_WINKLER_SIMILARITY which

Oracle

calculates the measure of agreement between string-1 and string-2, returning a value between 0 (no match) and 100 (perfect match)

Maybe Oracle Text offers such a capabilities, but I didn't use it so I can't comment on that.

Also, there might be some other options. Hopefully, someone else will suggest them.

Report message to a moderator

Re: Asking for search algorithm [message #667813 is a reply to message #667811]

Thu, 18 January 2018 00:58

NDKA739125
Messages: 33
Registered: January 2018

Member

@Littlefoot
SOUNDEX look great, but I think It could not solve my problem, and UTL_MATCH neither.
I type from my keyboard a string, the forms will look into the column TNAME in EMP table, and pick up the list which contains the similar name. I could not use LIKE, because it's slow. Beside, the input would be very complicated.
Here is, the problem.
When I type :
ALEXENDER ONORE (wrong fisrt name ALEXENDER) or
ONORE ALEXANDER (Reverse name) or
ONOR ALAXANDEZ (wrong and reverse name) the forms will give me a suggestion list, include ALEXANDER ONORE, which is the right one.

P/s: I'm thinking about using 'fuzzy', but it requires an index, how can i create an index in forms Sad

Sad

?

[Updated on: Thu, 18 January 2018 01:01]

Report message to a moderator

Re: Asking for search algorithm [message #667814 is a reply to message #667813]

Thu, 18 January 2018 01:05

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Of course you can NOT create index in Forms; index belongs to data, and data is stored in the database. You'd create index on a column in a table.

Report message to a moderator

Re: Asking for search algorithm [message #667816 is a reply to message #667814]

Thu, 18 January 2018 03:59

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

-- table and data for demonstration:

SCOTT@orcl_12.1.0.2.0> create table emp (tname	varchar2(60))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into emp values ('ALEXANDER ONORE')
  3  into emp values ('ALEXANDRA ONORE')
  4  into emp values ('ALEXANDER ONOFRE')
  5  into emp values ('ALEXANDRA ONOFRE')
  6  into emp values ('ALEX DOE')
  7  into emp values ('JOHN ONORE')
  8  into emp values ('JOHN DOE')
  9  select * from dual
 10  /

7 rows created.

-- Oracle Text Context index:

SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname)
  2    indextype is ctxsys.context
  3    parameters ('sync(on commit)')
  4  /

Index created.

-- searches using Oracle Text CONTAINS and FUZZY and SOUNDEX (!) and JARO_WINKLER (for odering)
-- that return results in the order of the closest match:

SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXENDER ONORE'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select tname
  2  from   emp
  3  where  contains
  4  	      (tname,
  5  	       '(fuzzy(' || replace (:string, ' ', ',1,5000,W) and fuzzy(') || ',1,5000,W)) or
  6  		(!(' || replace (:string, ' ', ') and !(') || '))', 1) > 0
  7  order  by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
  8  /

TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDER ONOFRE
ALEXANDRA ONORE
ALEXANDRA ONOFRE

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONORE ALEXANDER'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDER ONOFRE
ALEXANDRA ONOFRE

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOR ALAXANDEZ'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE

4 rows selected.

Report message to a moderator

Re: Asking for search algorithm [message #667821 is a reply to message #667816]

Thu, 18 January 2018 05:15

NDKA739125
Messages: 33
Registered: January 2018

Member

@Barbara Boehmer
Brilliant, the solution look great Surprised

Surprised

.
Thank you so much. I will try it right now Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #667857 is a reply to message #667821]

Mon, 22 January 2018 03:51

NDKA739125
Messages: 33
Registered: January 2018

Member

@Barbara Boehmer.
@Littlefoot
Tks a lot, the fuzzy search worked well, but I wanna improve my search box to be more robust and could catch up as much cases as it could.
For example, when Im using Fuzzy:
Input: MARIA AP ANTONOVA -> no result found, but the correct string is just : MARIA EP ANTONOVA.
So I wanna add MATCHES into the query so It could catch 'MARIA' and also 'ANTONOVA' for the better result ('AP' for another name is fine). Regarding to this problem, I found this link: https://docs.oracle.com/cd/B28359_01/text.111/b28303/quicktour.htm#i1008390
Quote:

2.5.1 Steps for Creating a Classification Application
The following example steps you through defining simple categories, creating a CTXRULE index, and using MATCHES to classify documents.

But I cannot create index. There was an error, indextype does not exist Sad

Sad

.
Thanks in advance

P/s: Another question, the input is a string inclue 'space', how can I split this string by space ? Very Happy

Very Happy

.
I'm thinking about using REGEXP_SUBSTR Very Happy

Very Happy

.

[Updated on: Mon, 22 January 2018 04:16]

Report message to a moderator

Re: Asking for search algorithm [message #667859 is a reply to message #667857]

Mon, 22 January 2018 04:46

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

The ctxrule index and matches are for document classification, which is something entirely different and not what you need here.

You need to leave a space between each term/word/name in the input string, so you don't need to use regexp_substr or anything else to remove it.

Soundex requires that the first letter match and fuzzy does not apply to words or names less than three characters.

What you can do is change the AND's to ACCUM's in the query, so that more results will be returned, but still with the closer matches first. Please see the revised demonstration below.

SCOTT@orcl_12.1.0.2.0> create table emp (tname	varchar2(60))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into emp values ('ALEXANDER ONORE')
  3  into emp values ('ALEXANDRA ONORE')
  4  into emp values ('ALEXANDER ONOFRE')
  5  into emp values ('ALEXANDRA ONOFRE')
  6  into emp values ('ALEX DOE')
  7  into emp values ('JOHN ONORE')
  8  into emp values ('JOHN DOE')
  9  into emp values ('MARIA EP ANTONOVA')
 10  select * from dual
 11  /

8 rows created.

SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname)
  2    indextype is ctxsys.context
  3    parameters ('sync(on commit)')
  4  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := 'MARIA AP ANTONOVA'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select tname
  2  from   emp
  3  where  contains
  4  	      (tname,
  5  	       '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) OR
  6  		(!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
  7  order  by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
  8  /

TNAME
------------------------------------------------------------
MARIA EP ANTONOVA

1 row selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXENDER ONORE'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDER ONOFRE
ALEXANDRA ONOFRE
JOHN ONORE
ALEX DOE

6 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONORE ALEXANDER'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDER ONOFRE
ALEXANDRA ONOFRE
JOHN ONORE
ALEX DOE

6 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOR ALAXANDEZ'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
JOHN ONORE
ALEX DOE

6 rows selected.

[Updated on: Mon, 22 January 2018 04:48]

Report message to a moderator

Re: Asking for search algorithm [message #667870 is a reply to message #667859]

Mon, 22 January 2018 19:12

NDKA739125
Messages: 33
Registered: January 2018

Member

@Barbara Boehmer.
Now everything is perfect Very Happy

Very Happy

.
And, I'm sorry, I can not smash the "LIKE" button because I could not find it =(.
Look like I have to work harder =(.

Report message to a moderator

Re: Asking for search algorithm [message #667903 is a reply to message #667870]

Tue, 23 January 2018 13:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's none, so - don't worry. "Thank you" is just fine.

Report message to a moderator

Re: Asking for search algorithm [message #667907 is a reply to message #667870]

Tue, 23 January 2018 16:55

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Purrfect. I like your avatar.

Report message to a moderator

Re: Asking for search algorithm [message #667965 is a reply to message #667907]

Sun, 28 January 2018 20:07

NDKA739125
Messages: 33
Registered: January 2018

Member

@Barbara Boehmer
@Littlefoot
New update is coming Very Happy

Very Happy

.
My teacher said, in the reality, sometimes, people has mistaskes about space. So, the new request is:
"CUTTING all the spaces between first and last name before SEARCHING".
So, I'll put a replace on the input like Replace(:INPUT,' ','').
Ex:
The correct name _ ALEXANDRA ONORE
Input:
ALEXANDRAONRA
ALAXANDREONOR
ONOELAXENDRA
ONOREALAXENDA
ONORE
ONR
==> Result found

Solution:
I saw that "[..] contains (tname, [..]", tname is a column, so I can not remove the space from a column, "[..] contains (replace(tname,' '','') [..]". I persuaded my teacher that is it possible to create a new column wich contains non-space-names, so we can use CONTAINS and FUZZY on that, and the answer is no, it is a giant databse with a million records. So I can not do anything. Is "FUZZY" could allow me to do that without creating a new non-space-name column Sad

Sad

?

[Updated on: Sun, 28 January 2018 22:13]

Report message to a moderator

Re: Asking for search algorithm [message #667986 is a reply to message #667965]

Mon, 29 January 2018 22:07

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You can use a multi_column_datastore to index virtual columns without creating any new columns in the table. In the following demonstration, I have indexed the names with spaces, the names without spaces, and the names in reverse order without spaces. I have then demonstrated searches with or without spaces in the input string, using the same query as before with fuzzy and soundex and jaro_winkler (for ordering).

SCOTT@orcl_12.1.0.2.0> create table emp (tname	varchar2(60))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into emp values ('ALEXANDER ONORE')
  3  into emp values ('ALEXANDRA ONORE')
  4  into emp values ('ALEXANDER ONOFRE')
  5  into emp values ('ALEXANDRA ONOFRE')
  6  into emp values ('ALEX DOE')
  7  into emp values ('JOHN ONORE')
  8  into emp values ('JOHN DOE')
  9  into emp values ('MARIA EP ANTONOVA')
 10  select * from dual
 11  /

8 rows created.

SCOTT@orcl_12.1.0.2.0> -- multi_column_datastore that indexes virtual columns without spaces:
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('name_ds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute
  4  	 ('name_ds',
  5  	  'columns',
  6  	  'tname,
  7  	   replace(tname,'' '', '''') nospaces,
  8  	   substr(tname,instr(tname,'' '',-1)+1)||substr(tname,1,instr(tname,'' '')-1) backwards');
  9    ctx_ddl.set_attribute ('name_ds', 'delimiter', 'newline');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> -- index that uses multi_column_datastore in parameters:
SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname) indextype is ctxsys.context
  2    parameters
  3  	 ('datastore	  name_ds
  4  	   sync 	  (on commit)')
  5  /

Index created.

SCOTT@orcl_12.1.0.2.0> -- tokens resulting from indexing:
SCOTT@orcl_12.1.0.2.0> select token_text from dr$emp_tname_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
ALEX
ALEXANDER
ALEXANDERONOFRE
ALEXANDERONORE
ALEXANDRA
ALEXANDRAONOFRE
ALEXANDRAONORE
ALEXDOE
ANTONOVA
ANTONOVAMARIA
DOE
DOEALEX
DOEJOHN
EP
JOHN
JOHNDOE
JOHNONORE
MARIA
MARIAEPANTONOVA
ONOFRE
ONOFREALEXANDER
ONOFREALEXANDRA
ONORE
ONOREALEXANDER
ONOREALEXANDRA
ONOREJOHN

26 rows selected.

SCOTT@orcl_12.1.0.2.0> -- example searches without spaces and with spaces using same query:
SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXANDRAONRA'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select tname
  2  from   emp
  3  where  contains
  4  	      (tname,
  5  	       '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) or
  6  		(!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
  7  order  by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
  8  /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ALAXANDREONOR'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOELAXENDRA'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONOREALAXENDA'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONR'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
JOHN ONORE
ALEXANDER ONORE
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE

5 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ALEXANDRA ONRA'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDRA ONOFRE
ALEXANDER ONORE
ALEXANDER ONOFRE
ALEX DOE
JOHN ONORE

6 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := 'ONORE ALAXENDA'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
ALEXANDRA ONORE
ALEXANDER ONORE
ALEXANDRA ONOFRE
ALEXANDER ONOFRE
JOHN ONORE
ALEX DOE

6 rows selected.

Report message to a moderator

Re: Asking for search algorithm [message #668017 is a reply to message #667986]

Wed, 31 January 2018 01:38

NDKA739125
Messages: 33
Registered: January 2018

Member

@Barbara Boehmer
Its incredible, thanks for your help, bro.
When I try to compile the code, there was an error: "PLS-00201: indentifier 'TEN_KD' must be declared. I'm using Oracle SQL Developper.
Is "multi_column_datastore" a procedure ? Should I put it into the Porcedures tab ? When I create a new Procedure in this tab, the init code be like:

create or replace procedure multi_column_datastore as
begin
 null;
end multi_column_datastore;

Is that normal ? Please guide me Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #668023 is a reply to message #668017]

Wed, 31 January 2018 07:59

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's not a procedure, it's something oracle text uses.
Suggest you have a read of the documentation

Where did ten_kd come from? Your last post is the first mention.

Report message to a moderator

Re: Asking for search algorithm [message #668025 is a reply to message #668017]

Wed, 31 January 2018 09:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

NDKA739125 wrote on Tue, 30 January 2018 23:38

@Barbara Boehmer
Its incredible, thanks for your help, bro.
When I try to compile the code, there was an error: "PLS-00201: indentifier 'TEN_KD' must be declared. I'm using Oracle SQL Developper.
Is "multi_column_datastore" a procedure ? Should I put it into the Porcedures tab ? When I create a new Procedure in this tab, the init code be like:
create or replace procedure multi_column_datastore as
begin
 null;
end multi_column_datastore;
Is that normal ? Please guide me .

Do you have a problem reading the code Barbara posted?


SCOTT@orcl_12.1.0.2.0> -- multi_column_datastore that indexes virtual columns without spaces:
SCOTT@orcl_12.1.0.2.0> begin
  2    ctx_ddl.create_preference ('name_ds', 'multi_column_datastore');
  3    ctx_ddl.set_attribute
  4  	 ('name_ds',
  5  	  'columns',
  6  	  'tname,
  7  	   replace(tname,'' '', '''') nospaces,
  8  	   substr(tname,instr(tname,'' '',-1)+1)||substr(tname,1,instr(tname,'' '')-1) backwards');
  9    ctx_ddl.set_attribute ('name_ds', 'delimiter', 'newline');
 10  end;
 11  /

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Asking for search algorithm [message #668027 is a reply to message #668017]

Wed, 31 January 2018 10:36

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

NDKA739125 wrote on Tue, 30 January 2018 23:38

@Barbara Boehmer
Its incredible, thanks for your help, bro.
When I try to compile the code, there was an error: "PLS-00201: indentifier 'TEN_KD' must be declared. I'm using Oracle SQL Developper.
Is "multi_column_datastore" a procedure ? Should I put it into the Porcedures tab ? When I create a new Procedure in this tab, the init code be like:
create or replace procedure multi_column_datastore as
begin
 null;
end multi_column_datastore;
Is that normal ? Please guide me :d.

I am a woman, so I am not your "bro", but I appreciate the sentiment.

I don't know where the 'TEN_KD' came from. You need to post a copy and paste of a run from your system, complete with line numbers and error messages.

You should be running it from SQL*Plus, not SQL Developer, to avoid any problems due to using SQL Developer.

A multi_column_datastore is an Oracle Text feature. Since you were apparently able to use my previous code, including an Oracle Text context index, I presume you have Oracle Text properly installed. You may need to ensure that you have appropriate privileges to create a multi_column_datastore by using the ctx_ddl procedure. You may need to have the ctxsys user grant the ctxapp role to whatever user you are running this from or grant execute on the ctx_ddl procedure directly. The following are examples of doing this for the user scott.

CTXSYS@orcl_12.1.0.2.0> grant ctxapp to scott;

Grant succeeded.

CTXSYS@orcl_12.1.0.2.0> grant execute on ctx_ddl to scott;

Grant succeeded.

You should not attempt to create a procedure named multi_column_datastore, just use the code that I provided. The following is what you need to run to create the multi_column_datastore and use it in your index. I have provided a copy wihtout line numbers, so that you can copy and paste it directly.

-- create a multi_column_datastore that indexes virtual columns without spaces:
begin
  ctx_ddl.create_preference ('name_ds', 'multi_column_datastore');
  ctx_ddl.set_attribute 
    ('name_ds', 
     'columns', 
     'tname, 
      replace(tname,'' '', '''') nospaces,
      substr(tname,instr(tname,'' '',-1)+1)||substr(tname,1,instr(tname,'' '')-1) backwards');
  ctx_ddl.set_attribute ('name_ds', 'delimiter', 'newline');
end;
/
-- create an index that uses the multi_column_datastore above:
create index emp_tname_idx on emp (tname) indextype is ctxsys.context
  parameters 
    ('datastore      name_ds
      sync           (on commit)')
/

Report message to a moderator

Re: Asking for search algorithm [message #668032 is a reply to message #668027]

Thu, 01 February 2018 04:02

NDKA739125
Messages: 33
Registered: January 2018

Member

@All
Sorry for my terrible mistake Sad

Sad

. I was careless, copying a wrong error message from another query Sad

Sad

.
So, went I tried to create a multi_column_datastore, so here was the error Sad

Sad

.

ORA-04063: package body "CTXSYS.CTX_DDL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.CTX_DDL"

Look like my computer need Oracle Text to be installed Very Happy

Very Happy

.

Thank you all for helping me Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #668056 is a reply to message #668032]

Thu, 01 February 2018 21:22

NDKA739125
Messages: 33
Registered: January 2018

Member

I installed ORACLE TEXT, and created the multi_column_datastore. But, when I try to indexed the columns, there was an error:

ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.

How can I fix it ? Sad

Sad

.

Report message to a moderator

Re: Asking for search algorithm [message #668057 is a reply to message #668056]

Fri, 02 February 2018 03:31

NDKA739125
Messages: 33
Registered: January 2018

Member

@All
Last update:
It finally works, I really appreciate your kindly supports, especially @Barbara Boehmer :D:D.

Report message to a moderator

Re: Asking for search algorithm [message #668393 is a reply to message #668057]

Wed, 21 February 2018 01:44

NDKA739125
Messages: 33
Registered: January 2018

Member

@Barbara
Dear miss,
Could 'FUZZY' work with a string that contains number ?
I have tried, using the same query but it did not work.
For example:
The correct string is: 45598365
input: 455598365, 456098365, 45598366, 46098370, etc.
output: all of the string wich is similar with 45598365.

Thanks in advance Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #668404 is a reply to message #668393]

Thu, 22 February 2018 21:55

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

The following shows that fuzzy searching on strings containing numbers using the same query does work, so you must have done something different. If you cannot figure out what you did different, then you need to post a copy and paste of a complete test run as I have done below.

SCOTT@orcl_12.1.0.2.0> create table emp (tname	varchar2(60))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into emp values ('45598365')
  3  into emp values ('455598365')
  4  into emp values ('456098365')
  5  into emp values ('45598366')
  6  into emp values ('46098370')
  7  select * from dual
  8  /

5 rows created.

SCOTT@orcl_12.1.0.2.0> create index emp_tname_idx on emp (tname) indextype is ctxsys.context
  2    parameters ('sync	   (on commit)')
  3  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := '45598365'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select tname
  2  from   emp
  3  where  contains
  4  	      (tname,
  5  	       '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) or
  6  		(!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
  7  order  by score(1) desc, utl_match.jaro_winkler_similarity (tname, :string) desc
  8  /

TNAME
------------------------------------------------------------
45598365
455598365
45598366
456098365

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '455593865'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
455598365
45598365
45598366
456098365

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '456098365'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
456098365
45598365
455598365
45598366

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '45598366'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

TNAME
------------------------------------------------------------
45598366
45598365
455598365
456098365

4 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '47098370'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

no rows selected

Report message to a moderator

Re: Asking for search algorithm [message #668439 is a reply to message #668404]

Sun, 25 February 2018 20:02

NDKA739125
Messages: 33
Registered: January 2018

Member

Dear Ms,
I have tried searching a string containing number using FUZZY operator. It worked well on string with number like AB455885, TK488545 ... But on the string which contains ONLY number like 23799626, it was not, it worked the same way as LIKE operator. What shold I do next ? Please guide me Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #668440 is a reply to message #668439]

Sun, 25 February 2018 20:58

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

As I stated previously, you need to post a copy and paste of a complete test run, like I did. None of us can de-bug code that we cannot see.

Report message to a moderator

Re: Asking for search algorithm [message #668441 is a reply to message #668440]

Sun, 25 February 2018 21:58

NDKA739125
Messages: 33
Registered: January 2018

Member

Dear Ms,

Here is my work.
Pic 1. The correct value is C8ZR80VXW. I put C8ZR8000VXW, FUZZY operator worked well. And the result was perfect.

http://imageshack.com/a/img924/1323/3bQan8.png

Pic 2. The corect value is 92369033.

http://imageshack.com/a/img922/7664/9WyL4C.png

Pic 3. Like pic 1, the string's input value was now changed intentionally for the test, '923699033'

http://imageshack.com/a/img924/6904/AwhobR.png

Pic 4. Result not found Sad

Sad

.

http://imageshack.com/a/img924/7654/vVksiL.png

Report message to a moderator

Re: Asking for search algorithm [message #668477 is a reply to message #668441]

Tue, 27 February 2018 04:56

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I do not get the same results, as shown below. I cannot tell from your pictures which query you are showing the result of, the text query or the other one. I don't know what tool you are running it from, but it would be better if you could test it from SQL*Plus and do a copy and paste from there.

It is possible that when you run your query, the index has not finished indexing yet. You might try waiting a bit and re-running the query. If running from a script, you can use dbms_lock.sleep between index creation and query.

SCOTT@orcl_12.1.0.2.0> create table hslt_temp (sohc  varchar2(60))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into hslt_temp values ('92369033')
  3  into hslt_temp values ('923699033')
  4  into hslt_temp values ('9876543210')
  5  select * from dual
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> create index hslttemp_sohc_idx on hslt_temp (sohc) indextype is ctxsys.context
  2    parameters ('sync (on commit)')
  3  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable string varchar2(60)
SCOTT@orcl_12.1.0.2.0> exec :string := '92369033'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> select sohc
  2  from   hslt_temp
  3  where  contains
  4  	      (sohc,
  5  	       '(fuzzy(' || replace (:string, ' ', ',1,5000,W) ACCUM fuzzy(') || ',1,5000,W)) or
  6  		(!(' || replace (:string, ' ', ') ACCUM !(') || '))', 1) > 0
  7  order  by score(1) desc, utl_match.jaro_winkler_similarity (sohc, :string) desc
  8  /

SOHC
------------------------------------------------------------
92369033
923699033

2 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '923699033'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

SOHC
------------------------------------------------------------
923699033
92369033

2 rows selected.

Report message to a moderator

Re: Asking for search algorithm [message #668686 is a reply to message #668477]

Fri, 09 March 2018 04:52

NDKA739125
Messages: 33
Registered: January 2018

Member

Dear Ms,
I have tried for several days, and the result was the same,FUZZY is powerful on text but I cannot use it on number. So here is my idea. I will create a "clone" column which contains the same data as column sohc, but in the beginning of each row, I will put an "A", so the data will be like.

SOHC
-------
9236990
TK03568

will change to
SOHC
-------
A9236990
ATK03568

And for the input too, I will put an A before the input data. So when I type 9235998, the form will change my input into A9235998 and the correct result will be shown (A9236990).
I have tested my prog that way, and its work. The problem now is the creation of that clone column Very Happy

Very Happy

. Is it possible ? Please guide me.

Report message to a moderator

Re: Asking for search algorithm [message #668695 is a reply to message #668686]

Fri, 09 March 2018 18:05

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You have not provided create table statements that show your data type. Oracle Text works on text columns, like varchar2 and clob, including those containing numeric values. Oracle Text will not index a column of number data type. As I have previously stated, you need to provide a complete test run, as I have done, from SQL*Plus, from start to finish, including either a create table statement or a describe of the table, so that column data types can be seen, and including error messages. Otherwise, all I can tell is that you are not doing what I am doing and not doing what you seem to be saying and can only guess at what you are actually doing.

Your column should be a text data type like varchar2 or clob not a numeric data type like number. If you cannot change that, although Oracle Text will not directly allow indexing of a numeric data type or indexing using a function like to_char or indexing of a virtual column, it will allow creation of a multi_column_datastore that applies to_char to the numeric data. You can find examples of that on this forum and in the online documentation and elsewhere on the internet. I am not going to post an example, since you have not demonstrated exactly what the situation and problem is.

Report message to a moderator

Re: Asking for search algorithm [message #668707 is a reply to message #668695]

Mon, 12 March 2018 01:47

NDKA739125
Messages: 33
Registered: January 2018

Member

Dears Ms,
Here is my code Sad

Sad

. My column is a test data type varchar2. But Fuzzy woked the same way as "LIKE" operator on number, I don't know why Sad

Sad

.

SQL> desc test02
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TESTNUM                                            VARCHAR2(60)

SQL> drop index test02_testnum_idx
  2  /

Index dropped.

SQL> create index test02_testnum_idx on test02 (testnum) indextype is ctxsys.context
  2  parameters ('sync (on commit)')
  3  /

Index created.

SQL> select * from test02
  2  /

TESTNUM
------------------------------------------------------------
86120470
A7391250

SQL> exec :string := 'A7391111'

PL/SQL procedure successfully completed.

SQL> select testnum 
  2  from test02
  3  where contains
  4  (testnum,
  5  '(fuzzy(' || replace (:string, ' ', ',1,5000,w) ACCUM fuzzy(') || ',1,5000,W)) or
  6  (!(' || replace(:string, ' ', ') ACCUM !(') || '))', 1) > 0
  7  order by score(1), utl_match.jaro_winkler_similarity(testnum, :string) desc
  8  /

TESTNUM
------------------------------------------------------------
A7391250

SQL> exec :string := '86120470'

PL/SQL procedure successfully completed.

SQL> /

TESTNUM
------------------------------------------------------------
86120470

SQL> exec :string := '86120578'

PL/SQL procedure successfully completed.

SQL> /

no rows selected

Report message to a moderator

Re: Asking for search algorithm [message #668727 is a reply to message #668707]

Mon, 12 March 2018 17:25

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I have copied and pasted what you posted, removed the line numbers, and run it, but do not get the same results. I get one row from the last query. I am baffled as to why there is such a difference. I have created a script below with some modifications that shows what tokens are created, what is being compared, and what the scores are. I have posted just the script first, then the results that I get after that. Please copy and paste the whole script onto your system, run it as I did, then post the complete results as I did below. Hopefully, we will see what is different and it will help narrow down the problem. Please copy and paste the whole script exactly, as I have made various changes, including using >= 0 instead of 0, so that it will show all results, regardless of score. If you get a 0 where I do not, then we will not that the problem is in the scoring. If you still do not even get a row, then it is something else.

-- test script:

drop table test02
/
create table test02 (testnum  varchar2(15))
/
insert into test02 values ('86120470')
/
insert into test02 values ('A7391250')
/
create index test02_testnum_idx on test02 (testnum) indextype is ctxsys.context
/
select * from test02
/
select token_text from dr$test02_testnum_idx$i
/
variable string varchar2(15)
exec :string := 'A7391111'
column jws    format 999
column string format a15
select score(1), utl_match.jaro_winkler_similarity(testnum, :string) jws, :string string, testnum
from   test02
where contains
        (testnum,
         '(fuzzy(' || replace (:string, ' ', ',1,5000,w) ACCUM fuzzy(') || ',1,5000,W)) or
          (!(' || replace(:string, ' ', ') ACCUM !(') || '))', 1) >= 0
order by score(1) desc, utl_match.jaro_winkler_similarity(testnum, :string) desc
/
exec :string := '86120470'
/
exec :string := '86120578'
/

-- run of above script:

SCOTT@orcl_12.1.0.2.0> drop table test02
  2  /

Table dropped.

SCOTT@orcl_12.1.0.2.0> create table test02 (testnum  varchar2(15))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert into test02 values ('86120470')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into test02 values ('A7391250')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> create index test02_testnum_idx on test02 (testnum) indextype is ctxsys.context
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> select * from test02
  2  /

TESTNUM
---------------
86120470
A7391250

2 rows selected.

SCOTT@orcl_12.1.0.2.0> select token_text from dr$test02_testnum_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
86120470
A7391250

2 rows selected.

SCOTT@orcl_12.1.0.2.0> variable string varchar2(15)
SCOTT@orcl_12.1.0.2.0> exec :string := 'A7391111'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> column jws    format 999
SCOTT@orcl_12.1.0.2.0> column string format a15
SCOTT@orcl_12.1.0.2.0> select score(1), utl_match.jaro_winkler_similarity(testnum, :string) jws, :string string, testnum
  2  from   test02
  3  where contains
  4  	     (testnum,
  5  	      '(fuzzy(' || replace (:string, ' ', ',1,5000,w) ACCUM fuzzy(') || ',1,5000,W)) or
  6  	       (!(' || replace(:string, ' ', ') ACCUM !(') || '))', 1) >= 0
  7  order by score(1) desc, utl_match.jaro_winkler_similarity(testnum, :string) desc
  8  /

  SCORE(1)  JWS STRING          TESTNUM
---------- ---- --------------- ---------------
         4   85 A7391111        A7391250
         0   41 A7391111        86120470

2 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '86120470'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

  SCORE(1)  JWS STRING          TESTNUM
---------- ---- --------------- ---------------
        39  100 86120470        86120470
         0   58 86120470        A7391250

2 rows selected.

SCOTT@orcl_12.1.0.2.0> exec :string := '86120578'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

  SCORE(1)  JWS STRING          TESTNUM
---------- ---- --------------- ---------------
        14   89 86120578        86120470
         0   58 86120578        A7391250

2 rows selected.

Report message to a moderator

Re: Asking for search algorithm [message #668730 is a reply to message #668727]

Mon, 12 March 2018 20:16

NDKA739125
Messages: 33
Registered: January 2018

Member

There are differents between your scores and mines. Here is my result, so the problem is regarding to score .

SQL> select token_text from dr$test02_testnum_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
86120470
A7391250

SQL> variable string varchar2(15)
SQL> exec :string := 'A7391111'

PL/SQL procedure successfully completed.

SQL> column jws format 999
SQL> column string format a15
SQL> select score(1), utl_match.jaro_winkler_similarity (testnum, :string) jws, :string string,testnum
  2  from test02
  3  where contains
  4  (testnum,
  5  '(fuzzy(' || replace (:string, ' ', ',1,5000,W) accum fuzzy(') || ',1,5000,W)) o
  6  (!(' || replace (:string, ' ', ') accum !(') || '))', 1) >= 0
  7  order by score(1) desc, utl_match.jaro_winkler_similarity (testnum, :string) des
  8  /

  SCORE(1)  JWS STRING		TESTNUM
---------- ---- ---------------	------------------------------------------------------------
        23   85 A7391111	A7391250
         0   41 A7391111	86120470



SQL> exec :string := '86120470'

PL/SQL procedure successfully completed.

SQL> /

  SCORE(1)  JWS STRING		TESTNUM
---------- ---- ---------------	------------------------------------------------------------
        39  100 86120470	86120470
         0   58 86120470	A7391250



SQL> exec :string := '86120578'

PL/SQL procedure successfully completed.

SQL> /

  SCORE(1)  JWS STRING		TESTNUM
---------- ---- ---------------	------------------------------------------------------------
         0   89 86120578	86120470
         0   58 86120578	A7391250



SQL>

[Updated on: Mon, 12 March 2018 20:17]

Report message to a moderator

Re: Asking for search algorithm [message #668733 is a reply to message #668730]

Tue, 13 March 2018 03:40

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I have posted my Oracle version below. Please post yours in the same manner. I suspect that the difference is due to changes in the scoring algorithm from one version to another. If you post your version, perhaps someone else on this forum who has the same version can run the last script that I posted and confirm this. If this is the problem, then there is not much you can do, other than upgrade. I can't think of any settings that might make a difference, but I could be missing something. Perhaps someone else has an idea.

SCOTT@orcl_12.1.0.2.0> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

Report message to a moderator

Re: Asking for search algorithm [message #668735 is a reply to message #668733]

Tue, 13 March 2018 05:15

NDKA739125
Messages: 33
Registered: January 2018

Member

Dear Ms,
My SQLPlus version is 11g . I will upgrade now Very Happy

Very Happy

. Hope everything will be ok. I really apreciate for your kindly support Very Happy

Very Happy

.

Report message to a moderator

Re: Asking for search algorithm [message #668736 is a reply to message #668735]

Tue, 13 March 2018 05:17

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

SQLPlus version is not the same as DB version.
And it's the DB version that matters.

Report message to a moderator

Re: Asking for search algorithm [message #668771 is a reply to message #668736]

Tue, 13 March 2018 20:07

NDKA739125
Messages: 33
Registered: January 2018

Member

Thanks cookiemonster,
Then, my Oracle version is

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

.

Report message to a moderator

Previous Topic:	Problem calling database procedure
Next Topic:	send sms from smsCaster from website on local host

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 16:57:48 CST 2025