0

I need to concatenate two columns which are VARCHAR2(4000). The result strings do not fit into a VARCHAR2(4000) column, as they are too long.

Since the concatenate operator can accept CLOBs, I figured I would convert each column on the fly to CLOB and then just || them together.

However, there is no TO_CLOB on 8i. CAST does not work with CLOB.

How can I accomplish this?

Limitations :

  • Oracle 8i
  • read-only access
  • no CREATE permissions
mach128x
  • 320
  • 3
  • 13
  • Did 8i support CLOBs [for concatenation](http://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/operator.htm#997790)? What are you doing with the final value - what will read or use the long/CLOB value, and how? – Alex Poole Jan 16 '17 at 19:01
  • Good point. Sadly the official 8i SQL reference for that operator isn't specific on the types it accepts, contrary to the 10g doc which explicitly list them (including CLOB). Now all I could find in the db was a BLOB column and concatenating on that I got the "ORA-00932: inconsistent datatypes" error. It doesn't look too good for CLOB at this point but there are so many intricacies to Oracle that it might as well work for that subtype of LOB only and not for others! But I can't test it :-( – mach128x Jan 17 '17 at 01:33
  • @AlexPoole Well, if I go by [link](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1197835631433), talking about 8.1.5, Tom says " A string concatenation is a varchar2". So it doesn't look good for concatenating CLOBs in 8i . – mach128x Jan 17 '17 at 01:44
  • There may be a way, possibly; but it depends on how how you're running your query and what you will do with the resulting CLOB. Running from SQL\*Plus with output to screen/file? From Pro*C? From Java? Something else? – Alex Poole Jan 17 '17 at 11:45
  • For now I'm pulling data for daily activities locally from within Toad, until the PHP / Apache front end is ready. The concatenation itself can be done through PHP of course, but I was trying to keep all the logic in SQL (for portability and other technical reasons), hence the question. – mach128x Jan 17 '17 at 12:20

0 Answers0