| Author |
 Topic  |
|
gor
Retired Admin
Netherlands
5511 Posts |
 Posted - 09 March 2001 : 17:35:36
|
Ok, I can search now using Oracle and even in the CLOB fields.
Another thing is that this search default is Case sensitive.
So if I search for snitz it won't return Snitz
With Oracle the logonname and password are Case sensitive too because of this.
1) Is there a workaround ?
2) Aren't all the other databases by default Case neutral in their SQL statements ?
Pierre< |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
|
|
h2o
Starting Member
46 Posts |
Posted - 09 March 2001 : 18:48:45
|
Oracle PLSQL has its own Ucase function. It is the UPPER function here is the syntax using the example in the other post.
SQL> select * from clob1 where dbms_lob.instr(UPPER(clobcol), UPPER('abc'))>0;
that should work... let me know...
h2o
< |
 |
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 09 March 2001 : 19:03:12
|
Nope, didn't work this time.
Makes sence thought that it didn't work, because clobcol is a reference to a column, and not an actual value, so UPPER(clobcol) doesn't work in this select statement.
Pierre< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 09 March 2001 : 19:20:19
|
sorry...
let me look around some more.
Edited by - h2o on 09 March 2001 19:21:42< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 10 March 2001 : 20:35:44
|
Hey gor, I am currently working with oracle directly to find a solution to this... they say that you cannot perform a case insensitive search on a clob using the dbms_lob.instr() function. We are currently working on a PLSQL version of the search. Hopefully we can just subsitute the sql statement with the PLSQL version an that would be it. I will keep you posted.
h2o
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 11 March 2001 : 04:08:17
|
Great, thanks 
Like I said, for this page I wouldn't mind if we had to create a Oracle specific fix, so even something like a stored procedure of function in this case would be acceptable if that was the only way to make it work. I know we don't use stored procedures because we want the code to be as database independent as possible, but if it is the only way to do this...and it only is in this one place.
For now I'm just going to go ahead and change all the other code so we'll have something to test with. I'm going to have to add the changes to the latest alpha after that anyway, I started to code using Alpha 3, but for now just getting it to work would be nice.
Pierre< |
|
|
|
THWR
Starting Member
9 Posts |
Posted - 13 March 2001 : 04:51:05
|
Hi
as h2o told, you need to build some pieces of PL/SQL Code. generally (most times with the to_chat function) works upper/lower for you. but not at blob's.
sorry for replying this late... 
< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 14 March 2001 : 01:48:53
|
Ok, I finally spoke with Oracle about the case insesitive search on a CLOB. There are really only two solutions. The fist is probably not one we want to go with. It requires the installion of another Oracle server product named Inter Media (expensive). This will allow you to perform a context search on a CLOB.
The only other thing to do is transfer the CLOB value into a temporary string variable. Then convert the string to uppercase then perform the search. Since you would basically have to batch through all of the replies then move each one to a temp variable then perform the case search you may want to write this in PLSQL. THat way you would not have to pass each record back and forth... it would all be done in Oracle's upper memory... only the records with the target text will be passed back to the .asp application. This would be more CPU expensive, however this may be the only way to implement the search function in Oracle. Let me know what you think.
Sorry it took me so long to respond. I have been working on a WINSOCK application for a homebanking product. Sometimes I hate the TCP stack.
h2o
Edited by - h2o on 14 March 2001 01:54:38< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 14 March 2001 : 02:39:48
|
The Inter Media option isn't an option I think, unless 99% of the Oracle users also has that. Otherwise it would be useless. Second problem ofcourse is: I don't have it, and I wasn't planning on spending money on Oracle just to support their database.
The second option would be the only option then.
Any chance I could get you to give this a try, doesn't have to be in ASP, if you have a good Visual Basic example I'll probably be able to convert it. Since Oracle is very new to me, comming up with completely new things sometimes just takes a lot more time.
Pierre< |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 14 March 2001 : 07:10:35
|
Uhmmm....since you have to convert a clob to a string to get the search to work, why not just use a varchar field?? In oracle 8i, you can create varchar fields over 64,000 characters (that's the number which sticks in my head, could be much higher). You'd have to check the documentation to get the exact count, but the varchar field should be big enough for our needs....
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead < |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 14 March 2001 : 07:54:14
|
I think to have read somewhere that the total length of a record has limits also. So that would cause problems in the MEMBERS table (?)
Don't know if I read this with regard to Oracle of Interbase though.
Pierre< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 14 March 2001 : 13:05:12
|
Good point Dave... is there a character limit for the textarea html controll. If there is and it is less than 4,000 characters (which is the max for vchar2 database field, I think 32,767 is the max for vchar2 type variables in PLSQL) then we may be able to use vchar2. Back when I was posting samples for gor I remember having to post two messages because the first was cut off due to its length. I wonder if this is a database limitiation or if the textare controll has limitations.
Gor, i will attempt to either write or gather a example in VB. I may also be able to write VB code to create the PLSQL procedure.
I will get back to you guys soon...
h2o
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 14 March 2001 : 13:20:53
|
The error you got when posting a long topic was a site specific (here at snitz.com) thing. Actually a database specific thing. We're using one of the last pré-setup.asp databases on Sql Server I think
Pierre< |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 14 March 2001 : 15:15:12
|
quote:
If there is and it is less than 4,000 characters (which is the max for vchar2 database field, I think 32,767 is the max for vchar2 type variables in PLSQL)
If I remember correctly, the length of vchar2 database and vchar2 pl/sql variables are now equal in oracle 8i...
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 02 April 2001 : 15:48:40
|
Hey gor.. how is the case search going?
Edited by - h2o on 02 April 2001 15:53:23< |
|
|
|
Topic |
|
Forum Locked
