Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3587
  • Last Modified:

Appending VARCHAR to CLOB

I'm writing a trigger which will, when a row is INSERTed, populate a field with a concatenation of four of the inserted fields.  Some fields are CLOBs and some are VARCHARs.  Appending CLOBs to each other is easy - DBMS_LOB.APPEND(), as is appending VARCHARs to each other - using ||.  But how do I append a VARCHAR to a CLOB?

DBMS_LOB.APPEND() throws all its toys on the floor and I can't find a suitable conversion function.
0
NukeEmUp
Asked:
NukeEmUp
  • 3
1 Solution
 
NukeEmUpAuthor Commented:
Points increased to 75 as no answer in 24hrs.
0
 
NukeEmUpAuthor Commented:
Points increased to 200 cos this is getting urgent ;-)
0
 
zebadaCommented:
If it is urgent then this answer may be a little late......
but anyway try this:
SQL> desc x
 Name                            Null?    Type
 ------------------------------- -------- ----
 KEY                                      VARCHAR2(8)
 DATA                                     CLOB

SQL> select * from x;

KEY      DATA
-------- -----------------------------------------------------------------------
---------
One      First

SQL> select dbms_lob.substr(data,dbms_lob.getlength(data)) || key from x;

DBMS_LOB.SUBSTR(DATA,DBMS_LOB.GETLENGTH(DATA))||KEY
--------------------------------------------------------------------------------
--------------------
FirstOne
0
 
NukeEmUpAuthor Commented:
You're right - it was too late.  To get the code finished on time I didn't bother with a trigger, just did the concatenation in perl before bunging the data into Oracle.  But now I know how it _should_ be done so I'll give you the points anyway ;-)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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