VARCHAR2 TO LONG or RAW or....

Hello, I'm working with Oracle 8.1.7

The problem:
SELECT descripcion1||descripcion2||descripcion3 from mytable;

Error: ORA-01489: result of string concatenation is too long

Of course, the max value for a varchar2 is 4000, here i'm trying to concat 3 varchar2(2000)

I have to make a simple SELECT statement but the problem is that I have to concatenate 3 varchar2(2000) strings into one field, and I can't do that, I can't convert TO_LOB (ORA-00932: inconsistent datatypes), neither cast as RAW (ORA-00906: missing left parenthesis (that's not true))... anyway, does anyone knows how can i do this???? Please don't tell me it can't be done, that would be really sad... ;)

Thanks,
Ronq.
RonQ_ArgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

konektorCommented:
where do u want to store contacted value ?
if to PL/SQL variable there is no problem. PL/SQL variable can hold up to 32767 characters.

declare
  vData VARCHAR2(32767);
begin
  SELECT descripcion1||descripcion2||descripcion3 into vData from mytable;
end;
/
0
RonQ_ArgAuthor Commented:
Is just to make a QUERY with a LIKE of the 3 fields togheter to execute from PHP to use as a search of the value in those concatenated fields.
This fields have a big text description splited in 3.
Not PL/SQL.
0
michaeljoneillCommented:
Does that mean the 3 fields are really in your WHERE clause rather than the SELECT clause?  If so, then apply the LIKE comparison to each field.

SELECT columns
FROM mytable
WHERE descripcion1 LIKE '%xyz%'
OR descripcion2 LIKE '%xyz%
OR descripcion3 LIKE '%xyz%

or if you truly mean to put descripcion1,2,3 into your SELECT clause, then pass them seperately to PHP variables and concatenate them in PHP code prior to streaming any output.

Good Luck.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RonQ_ArgAuthor Commented:
Thanks michaeljoneill but, that's not a good solution, because a word that i'm searching for could be divided in two fields, for example "soldier" could be in descripcion1 "....sol" and in description2 "dier...."
What I want to do is: concatenate the trhee fields and make a VIEW with that, and then search in the concatenated field (with a LIKE clause).
Anyway if I concat in the PHP script I would loose the LIKE function that search in all the database records, I will have to do a 50000 records (concatenation of the three) loop in the php and search in every one. That's why I want to join the fields in one long field and use the LIKE clause there.
0
slightwv (䄆 Netminder) Commented:
See if this will meet your requirements.  You will still need to bring the 3 columns back to PHP and concatonate them there but the LIKE should still work:

select rowid from tab1
where col1||col2 like '%Hello%' or
            col2||col3 like '%Hello%'
/

As a side note:  It sounds like a kludged design.  If possible I suggest changing the application to use CLOBs instead.  There will be a time that the hoops are just impossible to jump through.



You might would also look into Oracle Text as a possible solution:

drop table tab1;
create table tab1 (col1 varchar2(2000), col2 varchar2(2000), col3 varchar2(2000), idx_col char(1));

insert into tab1 ....

begin
ctx_ddl.drop_preference('my_pref');
end;
/

begin
ctx_ddl.create_preference('my_pref', 'MULTI_COLUMN_DATASTORE');
end;
/

begin
ctx_ddl.set_attribute('my_pref', 'columns',
      'col1 || col2, col2 || col3 '
);
end;
/

create index my_mc_idx on tab1(idx_col)
      indextype is ctxsys.context
      parameters('datastore my_pref');

select rowid from tab1 where contains(idx_col,'%Hello%') > 0
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
michaeljoneillCommented:
Is there a compelling reason that there isn't a single CLOB column rather than piece-mealing with three seperate VARCHAR2 columns?  

Assuming that it is too late or impossible on your end to alter the table to make use of CLOBs, then you could write a procedure to accomplish same.

1. Don't use a view.
2. Create a procedure that returns {0,1} based on an INSTR pattern test against a concatenation of the three strings
3. Use WHERE clause that calls procedure in an inequality comparison to 1 (for true)

here's the procedure:

create or replace function myinstr ( pattern in varchar2, str1 in varchar2, str2 in varchar2, str3 in varchar2 ) return integer
is
   c clob;
begin
   c := str1 || str2 || str3;
   return dbms_lob.instr( c, pattern, 1, 1 );
end;
/


here's the SQL

SELECT primary_key
FROM mytable
WHERE myinstr( 'mypattern', descripcion1, descripcion2, descripcion3 ) != 0;


In this method, your PHP code is shielded from clumsy CLOB logic entirely.  However it is true, the code supplied given isn't a LIKE comparison, rather an INSTR comparison.  But, you could easily change the myinstr procedure to utilize regular expression logic and duplicate the same functionality quite easily.

Good Luck.
0
michaeljoneillCommented:
Update to above code:

My code that implicitly converts strings into a CLOB probably won't work with 8i.  So replace:

c := str1 || str2 || str3;

with:

dbms_lob.createtemporary(c,FALSE,dbms_lob.call);
dbms_lob.writeappend( c, length(str1), str1 );
dbms_lob.writeappend( c, length(str2), str2 );
dbms_lob.writeappend( c, length(str3), str3 );

This accomplishes the same.   Also, 8i doesn't have very robust regular expression builtins either - but you can check out the SYS.OWA_PATTERN package which is still better than LIKE in functionality.
0
slightwv (䄆 Netminder) Commented:
Just wanted to add to my previous post:

w/o knowing the full requirements of your application using 'LIKE' may not be the way to go.  LIKE is case sensitive and may become inefficient when dealing with large amounts of  data.

Oracle Text is the best way to retrieve case insensitive data.  I will warn you:  make sure you are on at least 8.1.7.4 before doing this (there were some bugs in early 8.1.7 and Oracle text).
0
RonQ_ArgAuthor Commented:
I can't change the design of any table of the database, I can only work with what I told you, I didn't design the tables, this was a bought schema and we can't modify it. I know it's a problem but that's why I was looking for an answer...

Thanks everyone for your help, I'm doing the two like select, I also thought the same ugly solution (the two separated concatenations). The function is a good Idea, but doesn't do excactly what I need.

Bye,
Ronq.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.