How does fuzzy match works
Pratap Deshmukh, June 13, 2002 - 12:05 am UTC
Hi Tom
I plan to develop an application for name and address search using Oracle text in 9i. I would like to know the concept behind the fuzzy match, how Oracle really does this fuzzy matching. If Oracle does not disclose the concept then at least what to except (and what not to expect) from the fuzzy match results.
This would be of great help for me to use the tool effectively in my application.
Thanks and regards
Pratap
Help needed
David Jiang, June 27, 2003 - 12:38 pm UTC
Tom,
In the exapmple given:
SELECT ......FROM TITLE
WHERE ........Title LIKE '%Biography%'
we know the wild card search here wil search any combination of 'Biography' with any letters in front of or after it such as: 'my-Biography', 'BiographyForJohn'
If you use interMedia Text or Oracle Text search like shown in your example:
create index search_idx
on Title(title)
indextype is ctxsys.context
/
Now, you can search like this:
select *
from title
where contains( title, 'Biography' ) > 0
1. Does this select query search only for 'Biography' or it can search for 'my-Biography', 'BiographyForJohn' also? What kind of setting you have to use to make the text search possible like wild card search? Please give an example.
2. Is it dynamic ie seach sting can be anything eg Biblography?
3. In Database Configuration Assistant, oracle 9i, there is option for interMedia Text, Oracle Text, Does Oracle Text option will install all the text search capability you want or you have to choose both of them?
Thank you very much.
June 27, 2003 - 1:32 pm UTC
1) you can use wild cards -- it won't by default. you can use fuzzy searches. you can use near "find the rows where oracle near tom" and so on.
See the oracle text docs, it really is in there -- all of the options.
2) yes, the string can be anything, every time you search on asktom, you are doing a search using that technology
3) they are one in the same.
Why does interMedia Text does not help?
David Jiang, June 30, 2003 - 11:36 am UTC
Tom,
Thank you so much for your prompt answer. I reviewed documents you have mentioned about near, fuzzy etc. Thats is a great help!
I have a table called OBJECT_DATA with 389 Million rows in it.
CREATE TABLE "CENTRA"."OBJECTS_DATA" ("OBJECT_ID" NUMBER NOT NULL,"ATTRIBUTE_ID" NUMBER NOT NULL, "FIELD_VALUE" VARCHAR2(80 byte),
CONSTRAINT "CPK_OBJECTS_DATA" PRIMARY KEY("OBJECT_ID",
"ATTRIBUTE_ID")
When I do a query like this:
10:00:54 SQL> Select count(object_id) from objects where lower(title) like '%cable%';
COUNT(OBJECT_ID)
----------------
87886
Elapsed: 00:00:44.02
SQL> Select object_id from objects where lower(title) like '%cable%';
87886 rows selected.
Elapsed: 00:11:12.09
It is very slow because of full table scan. So I installed intrMedia and Text from 9i R2 and created an Oracle Text index on title:
create index search_idx
on objects(title)
indextype is ctxsys.context;
I did the select qurey again:
13:44:00 SQL> select count(object_id) from objects where lower(title) like '%cable%';
COUNT(OBJECT_ID)
----------------
87886
Elapsed: 00:00:44.02
SQL>Select object_id from objects where contains(title, cable) >0;
86733 rows selected.
Elapsed: 00:13:09.07
It was an index scan, but the queries takes more time to finish.
1. Would you please tell me why it is getting slower with text index search?
2. what can I do to speed up the search if interMedia text does or does not help here?
3. I tried this query with and without setting the pga and setting work area policy to auto/manual. Apprently by setting the above work area to auto further slowed down the query. Please note that I even tried pga b/w 25 MB to 500 MB. I used your queries to calculate pga mentioned under another heading.
Here is my init param:
pga_aggregate_target=25165824 # 25 MB. I tried upto 500 MB
sort_area_size=16777216
sort_area_retained_size=8000000
workarea_size_policy=AUTO #I tried auto and manual both.
Please also advise how to configure pga for this issue.
I hope I am not asking too much :)
June 30, 2003 - 1:24 pm UTC
performing 86,733 TABLE ACCESS BY ROWID's is going to be "not fast" -- they were all single block IO.
besides, the difference (wall clock time) between 11 and 13 is not of any consequence. run the queries under REAL WORLD conditions (you were mostly timing how fast sqlplus was able to print to your screen)
Run the queries with lots of different inputs. Run the queries fetching only the rows you would really in real life (what UI do you have that would really get 86k rows???). Run the queries without SCREEN IO -- eg:
alter session set sql_trace=true;
set autotrace traceonly
select ....
set autotrace off
exit
and use tkprof.
Intermedia Text vs Shadow Column
Rafique Awan, September 12, 2004 - 12:29 am UTC
Tom,
I have a requirement to search data using wildcard for a column which has max length of varchar2(100). I want to use index search as full table scan is very slow while I am doing wild card searches.
Which one option is better between following
1) Using Intermedia Text
2) Using shadow column which has index on it and obviously I need to maintain this shadow column using trigger on the table.
Or is there any other better option that you can think of?
September 12, 2004 - 11:09 am UTC
you'll need to be more specific. you seem to think a shadow column can be helpful -- if that is the case, we must be missing some piece of information here because I cannot imagine how it would be useful? I mean, I have the string:
'how now brown cow'
what would a shadown column look like that would facilitate index retrieval of %brown%?
Also, is the wildcard search supposed to find %wn% or %wn c% in this string? If so, text will not be as fast as it could be (%wn) or it won't be applicable really (%wn c%). text works by creating an inverted word list index -- so the index would have
how -> rows
now -> rows
brown -> rows
cow -> rows
the %wn would have to query this index to find ALL keys that have wn at the end of them -- and that could explode quickly into a situation where text says "no, too many keys match this" (meaning a full scan is correct and proper actually!)
the %wn c% would be problematic since that would not be something that can be gotten from that index.
You are right, Shadow Column is not usefull in this case!
Rafique Awan, September 12, 2004 - 1:23 pm UTC
Tom,
Sorry, I just realized that shadow column will not be useful in this case.
As you asked:(
Also, is the wildcard search supposed to find %wn% or %wn c% in this string?)
Yes, wildcard search supposed to find both strings. I tested this, If I use intermedia text then it will use index for both strings.
What do you mean by this?
(The %wn c% would be problematic since that would not be something that can be gotten from that index.)
Do you mean that it will be problematic even if use intermedia text?
Thanks for your help.
September 12, 2004 - 4:49 pm UTC
it is that the text stuff is going to have to
find all %wn,
then find all c%
in its index. then find the all of the rows that have both, then inspect the rows that have both and see if %wn c% is in it.
It is just "expensive" -- maybe less than full scan, maybe lots more. That kind of wild card search is really expensive.....
Thanks!
Rafique Awan, September 12, 2004 - 7:20 pm UTC
Thanks a lot for your quick response.
So what is your final word on this?
As you said Intermedia Text is also expensive to fulfill the above requirement, so can you think of any better option/trick that we can use over here?
Thanks again.
September 12, 2004 - 8:11 pm UTC
the final word is "this is not going to be something that is efficient"
Text would be the best bet, it'll do what it can -- as best it can.
%wn c%
A reader, September 13, 2004 - 12:41 am UTC
Hi Tom,
The search conditions like "%wn c%" are very common with the search engines like google. So is there a different technology at play here? Or what we see is really "%wn" and "c%" (there aren't many results with exact match of "%wn c%", anyway).
Oracle Text and google searches are quite similar otherwise, both will ignore words that are too common like "in","and". Oracle Text gives an option of customisation of this as well.
Thanks
September 13, 2004 - 7:53 am UTC
it is????
where? they use 'words' just like i do here on this site.
where does google do wildcards? they use * as a "word wildcard in their phrases, but "*wn c*" is turned into the phrase
* wn c *
you can see that by clicking advanced search, where it will have moved "*wn c*" into the phrase box.
But in any case -- is the technology behind google and oracle text different? about as night and day different as you can get probably. They index very differently then text does yes.
But in any case -- when you think about the different search possibilities between:
a) keywords
b) anything goes with wildcards
you have to acknowledge that a) is going to perform much better than b) given that a) is what the technology does and b) is performed by multiple operations.
If you can -- I'd make the search "a simple keyword search" (no leading wildcards, trailing wildcards on single words only) with an advanced search that supports full wild carding (but with the appropriate really big caveat that "this'll take longer and impact others work since it consumes many more resources)
I could be wrong on google -- but I see no wildcarding in the fashion of SQL LIKE
Using LIKE without wildcards
Martin, November 29, 2004 - 4:36 am UTC
Tom,
I've heard previously (probably erroneously) that using LIKE without specifying a wildcard character is like using a direct =, i.e.
SELECT 1 FROM dual WHERE dummy LIKE 'x'
However, we have a lot of queries which utilise the LIKE operator against numeric columns, and from my testing it seems to be always treating the LIKE as a string operator, i.e.
SQL> DESC T
Name Null? Type
------------ -------- ---------
X NUMBER
SQL> EXPLAIN PLAN FOR
2 SELECT x FROM t WHERE x LIKE 1;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | T | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR("T"."X") LIKE '1')
Note: rule based optimization
14 rows selected.
As you can see, the TO_CHAR is applied to the column since it treats it always as a string.
So, my question is, assuming that LIKE does always treat the
numeric as a string, what is your approach for ensuring that
indexes on numeric columns which need to be searched, and which always uses LIKE, gets used? Is the answer (other than to rewrite the code, to use an = if no wildcards exist in the search condition) solely a function-based index?
Thanks in advance
November 29, 2004 - 8:24 am UTC
you use LIKE on numbers?!?
anyway, you can create a function based index on to_char(T.X)
ops$tkyte@ORA9IR2> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> create index t_idx on t(to_char(object_id));
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id like '5';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=98)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=98)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)
ops$tkyte@ORA9IR2> set autotrace off
Don't like it...
Martin, November 29, 2004 - 8:30 am UTC
Unfortunately, the application involves custom querying screens where the user is free to query on anything. In order to cater for this, the resultant SQL is generated dynamically, and is of the form, 'WHERE column LIKE :1', which works great
when the user queries on a character column, they can put anything from 'x' to '%x%x%' (don't ask!).
The problem is when this approach is used against numeric columns, then the indexes on the columns are not utilised, but they would be if the predicate was 'WHERE column = :1'.
Oh well, looks like a re-code of the application then for numeric columns!
Thanks for your help.
NOT LIKE '%something%'
A reader, December 07, 2004 - 10:19 pm UTC
Would Oracle Text help for queries involving
where column not like '%something%'
Thanks
December 08, 2004 - 10:16 am UTC
if %something% was really meant to be:
find the documents that do not contain the WORD something
then yes. but if it was to filter out "hello xsomethingx there"
not so much.
Finding blank spaces
A reader, August 26, 2005 - 4:12 pm UTC
Tom,
I am trying to find from all tables and all columns in our database, which column has spaces in it. Not Nulls but spaces. Could be one space, 2 spaces or n..spaces.
Can we find it using LIKE operator? if so, what's the WildCard character for finding all spaces?
Thanks for your help
August 26, 2005 - 4:29 pm UTC
where column like '% %'
But not other characters
A reader, August 26, 2005 - 4:38 pm UTC
Tom,
using '% %' , won't it return spaces with any character on either side?
I just want to find columns with nothing else but Blank spaces [of any length]
Thanks,
August 26, 2005 - 5:10 pm UTC
ops$tkyte@ORA10GR1> create table t ( a int, x varchar2(5) );
Table created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t values ( 1, null );
1 row created.
ops$tkyte@ORA10GR1> insert into t values ( 2, 'x x' );
1 row created.
ops$tkyte@ORA10GR1> insert into t values ( 3, ' x' );
1 row created.
ops$tkyte@ORA10GR1> insert into t values ( 4, 'x ' );
1 row created.
ops$tkyte@ORA10GR1> insert into t values ( 5, ' ' );
1 row created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> column xxx format a5
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select a,
2 '"' || x || '"' ,
3 '"' || translate( x, ' x', 'x ' ) || '"',
4 '"' || rpad('x',length(x),'x') || '"' xxx
5 from t
6 where translate( x, ' x', 'x ' ) = rpad('x',length(x),'x' );
A '"'||X| '"'||TR XXX
---------- ------- ------- -----
5 " " "x" "x"
try this
Tyler, August 26, 2005 - 4:55 pm UTC
select 1
from dual
where trim(' ') is null
so in your case
where trim(COLUMN_NAME) is null
whoops
Tyler, August 26, 2005 - 5:20 pm UTC
sorry, forgot to include the check to make sure it doesn't grab nulls.
so mine would be.
select 1
from dual
where trim(' ') is null
and ' ' is not null
which translates into
where trim(COLUMN_NAME) is null
and COLUMN_NAME is NOT NULL
for your purposes.
Another solution
A reader, August 26, 2005 - 7:04 pm UTC
SELECT *
FROM MYTABLE
WHERE LENGTH( REPLACE( COLUMN, ' ', '' ) ) = 0
REPLACE( NULL, x, y ) = NULL
LENGTH( NULL ) = NULL
NULL = 0 -> NULL -> Not True (it's not false either)
Thanks guys..
A reader, August 27, 2005 - 1:28 pm UTC
Specially Tyler for his simple solution for finding Spaces in a varchar2.
Thanks
Like Question
V, November 09, 2005 - 1:17 pm UTC
Tom,
I have the following scenario:
select -1 from vin_test A where A.business_name like ('%3 PRODUCTS INC%')
AND A.out_of_business_id != ('O');
VIN_TEST has an index on BUSINESS_NAME
there are 153K rows in the table of which only 1 is 3 PRODUCTS INC
134k have a out_of_business_id != ('O')
when I trace the query it uses the index without the
AND A.out_of_business_id != ('O');
but does not use it when I add it back in. Is this expected? Can I force it to always use the index. I tried adding the INDEX hint & it still ingored it.
Thanks
November 11, 2005 - 10:23 am UTC
when you do just business_name is it doing an index FAST FULL scan isn't it. It is not using the index to "range scan", it is full scaning the index as if it were a skinny version of the table.
when you add in out_of_business_id - it says "i'd have to hip hop to and from the index to the table" so it just full scans the table (more efficient)
leading % - every single row has to be inspected.
are you willing to use Oracle Text to create a text index that can make the leading % query efficient?
or you can add out_of_business_id to the existing index and just fast full scan that index instead of full scanning the table.
Like vs. Contains
S, June 20, 2006 - 4:21 pm UTC
Tom,
Which is faster ?
title Like '%Biography%'
or Contains( title, 'Biography' ) > 0
or an intersection of two indexes ?
Scenario 1
I use a Like predicate with title. title is column 2 in a multi column index named myindex.
Another column in my query is column 1 of myindex and the query is indicated in an explain as using myindex via that column.
an intermedia index on title doesn't exist.
Scenario 2
myindex does not exist
an intermedia index on title exists.
Scenario 3
Both myindex and an intermedia index on title exists.
thank you
June 21, 2006 - 4:24 pm UTC
just one will do -
it is highly likely that the contains would be faster in general. In fact, contains can be used to speed up leading "%" searches in general.
you can also look at a ctxcat index to see if it is sufficient, it can do scenario 1, 2, and 3 since it can index relational and "text"
search text between two words
A reader, August 06, 2006 - 4:55 am UTC
Hi
I have rows like this
'CON_ID 213434 OFF'
'CON_ID 1082374 OFF'
'CON_ID 664343 OFF'
I would like to get the numbers only so it would be
213434
1082374
664343
I have been able to produce the output by using several instr substr combinations, looks really awful and not readble.
Is there any easier way to do it?
August 06, 2006 - 9:10 am UTC
beauty is in the eye of the beholder I guess.
ops$tkyte%ORA10GR2> select substr( x, instr( x, ' ', 1, 1 )+1,
2 instr( x, ' ', 1, 2 )- instr( x, ' ', 1, 1 ) )
3 from t;
SUBSTR(X,INSTR(X,'',
--------------------
123425
doesn't hurt my eyes. What hurts is that you seem to have loaded at least three attributes munged into a single string as a single attribute. That looks awful and not usable to me :) that should have been corrected at data entry (load) time.
you could look into regex (regular expressions) as well if you like, but the substr/instr works and can be every elegantly "hidden" via a view
what about a hint
Donat Callens, October 12, 2006 - 9:10 am UTC
I used this for a query that used "... like '%...%'"
select /*+first_rows*/...
this way the index is used even through the 'like' clause. another option is to use the "/*+index*/" hint.
this solution requires an application code change but is good if you don't have context.
October 12, 2006 - 11:37 am UTC
it had to fast full scan the index here - not going to be as effective as using a text index that supports substring prefixing to index specifically for searches with a leading %
wild card search on two columns
Anji, March 10, 2007 - 8:18 pm UTC
Hi Tom
Thanks for your great site serving as oracle knowledge hub and special thanks for your books, I bought all now. We have a query as below.
SELECT b.title,b.bookname, bp.purchase_site
FROM book b, book_purchases bp
WHERE title LIKE '%' || var1 || '%'
AND bookname LIKE '%' || var2 || '%'
AND b.book_id = bp.book_id
(Oracle version : 9iR2)
book is master table with 400 rows. Oracle thinks that after evaluating predicates produces only 1 row and uses merge catesian join on the detailed table which has 10000 rows. This is very slow for cases where user searches for very generic title and booknames resulting in 300 * 10000 rows in catesian product step of runtime execution plan. Can this be improved in anyways ? Can Oracle Text help in this scenario to provide better estimates ? Your guidance is most appreciated. This is my first query on this site, please let me know if these details are sufficient or an example with autotrace and tkprof output would be preferrable. Apologies if this question is supposed to be a new question, I will wait for my chance.
NOTE: LIKE '%biography%' should work similar to SQL LIKE syntax ignoring case, not word searching i.e. should output only 'My Biography', 'BusinessBiography', 'biography of Tom' etc.
March 12, 2007 - 5:40 pm UTC
the optimizer is reallying thinking that
like '%' var '%'
is returning 1 row? got plan?
could text help? depends - if var1 and var2 are WORDS yes, if they are any arbitrary string (like a partial word) not as much.
wild card search on two columns
Anji, March 26, 2007 - 12:18 am UTC
Hi Tom
Here is autotrace output. I could not reproduce cartesian merge join, it's using nested loops now. I shall check what has changed in the environment, but autotrace guesses 1 row. Could you figure anything wrong from this ? Please let me know if more details are required.
tom@HOMEDB92> desc book;
Name Null? Type
-----------------------------------------------------
BOOK_ID NOT NULL VARCHAR2(10)
TITLE NOT NULL VARCHAR2(50)
BOOK_NAME NOT NULL VARCHAR2(100)
AUTHOR NOT NULL VARCHAR2(100)
PUBLISHER NOT NULL VARCHAR2(100)
tom@HOMEDB92> desc book_purchases;
Name Null? Type
-----------------------------------------------------
PURCHASE_ID NOT NULL VARCHAR2(30)
BOOK_ID NOT NULL VARCHAR2(10)
PURCHASE_SITE NOT NULL VARCHAR2(100)
SELLER_ID NOT NULL VARCHAR2(10)
PRICE NOT NULL NUMBER(5,2)
PURCHASE_DATE NOT NULL DATE
SHIP_STATUS NOT NULL VARCHAR2(1)
SHIP_DATE DATE
INDEXES CREATED
---------------
CREATE INDEX idx_bk_title ON book(title);
CREATE INDEX idx_bk_name ON book(book_name);
tom@HOMEDB92> BEGIN
2 dbms_stats.delete_schema_stats(USER);
3 dbms_stats.gather_schema_stats(USER,estimate_percent => NULL,method_opt => NULL,cascade => TRUE);
4 END;
5 /
PL/SQL procedure successfully completed.
tom@HOMEDB92> select count(*) from book where book_name like '%' || :v_name || '%';
COUNT(*)
----------
400
tom@HOMEDB92> select count(*) from book where title like '%' || :v_title || '%';
COUNT(*)
----------
400
tom@HOMEDB92> select count(*) from book;
COUNT(*)
----------
400
tom@HOMEDB92> select count(*) from book_purchases;
COUNT(*)
----------
10000
tom@HOMEDB92> set autotrace traceonly;
tom@HOMEDB92> variable v_title varchar2(50);
tom@HOMEDB92> variable v_name varchar2(100);
tom@HOMEDB92> exec :v_title := 'TITLE';
PL/SQL procedure successfully completed.
tom@HOMEDB92> exec :v_name := 'BOOK';
PL/SQL procedure successfully completed.
tom@HOMEDB92> SELECT b.title,
2 b.book_name,
3 bp.purchase_site
4 FROM book b,
5 book_purchases bp
6 WHERE b.title LIKE '%' || :v_title || '%'
7 AND b.book_name LIKE '%' || :v_name || '%'
8 AND b.book_id = bp.book_id
9 /
10000 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=40 Bytes=2720)
1 0 NESTED LOOPS (Cost=34 Card=40 Bytes=2720)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=3 Card=1 Bytes=39)
3 2 INDEX (RANGE SCAN) OF 'IDX_BK_NAME' (NON-UNIQUE) (Cost=2 Card=4)
4 1 TABLE ACCESS (FULL) OF 'BOOK_PURCHASES' (Cost=31 Card=40 Bytes=1160)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
79974 consistent gets
0 physical reads
0 redo size
142230 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
TKPROF Report
***************************
Rows Row Source Operation
------- -------------------------------------------------- 10000 NESTED LOOPS
400 TABLE ACCESS BY INDEX ROWID BOOK
400 INDEX RANGE SCAN IDX_BK_NAME (object id 30002)
10000 TABLE ACCESS FULL BOOK_PURCHASES
March 26, 2007 - 7:36 am UTC
if you cannot reproduce what you said was happening, why post this?
but basically, your example is so trivially small - any query plan on the planet would likely be better than good enough - no comment.
Tom, please, help me.
Richard, September 27, 2007 - 11:57 am UTC
Tom,
I haven´t yet found a solution for my problem. I have a query like this:
select ...
from ...
where
nombreplanopers LIKE '%|%' || INTS_PACK_GENERAL.INTS_FUNC_TEXTOPLANO(:v5) ||
'%|%'|| INTS_PACK_GENERAL.INTS_FUNC_TEXTOPLANO(:v6) || '%'
and I want to transform it into a new predicate using Intermedia Text, but I don´t know how can I do it because Intermedia ignores the '|' symbol and I want to keep it in my searches. As example, this is another table in the same situation:
SQL> select * from t;
C
--------------------
How#Now#Red#Cow
How Now Red Cow
How # Now Red Cow
jorge|perez
on-demand
SQL> SELECT * FROM t WHERE CONTAINS (c,'{jorge}', 1) > 0;
C
--------------------
jorge|perez ----> OK
SQL> SELECT * FROM t WHERE CONTAINS (c,'%' || '|' || '%') > 0;
C
--------------------
How#Now#Red#Cow
How Now Red Cow NO!
How # Now Red Cow
jorge|perez
on-demand
I don´t want avoid the '|' symbol. How can I do this?
Thank you Tom!
September 28, 2007 - 4:21 pm UTC
http://asktom.oracle.com/pls/ask/search?p_string=printjoins I would suggest however you that you do not bind:
'%|%' ||
INTS_PACK_GENERAL.INTS_FUNC_TEXTOPLANO(:v5) ||
'%|%'|| INTS_PACK_GENERAL.INTS_FUNC_TEXTOPLANO(:v6) || '%'
but rather
a) assign that to a variable in your program
b) bind that variable
to avoid accidentally having your functions invoked over and over and over and over...