• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2044
  • Last Modified:

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.
0
RonQ_Arg
Asked:
RonQ_Arg
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now