Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Concatenating clob, long and char?

Posted on 2008-06-12
11
Medium Priority
?
1,534 Views
Last Modified: 2013-12-07
I want to insert a value into a clob column in a table. The value should be concatenated using a clob value from another table, a long value and some characters. Like this:

insert into mytable1 (myclobcol)
select clobcol || longcol || ' }'
from mytable2;

I have tried all kinds of conversions and I get all kinds of error messages related to faulty datatypes.
0
Comment
Question by:Mamarazzi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21771279

Use the SUBSTR() functoin of the DBMS_LOB PL/SQL package.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349
0
 

Author Comment

by:Mamarazzi
ID: 21777587
Can you give me an example using my code?
It is the LONG value that generates error message: ORA-00932: inconsistent datatypes: expected NUMBER got LONG.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 750 total points
ID: 21779044
Maybe it's easier:

SQL> Var C Clob;
SQL> Declare
  2    P_Clob Clob;
  3    P_Long Long;
  4    P_Vchar Varchar2(10):='}';
  5  Begin
  6    Dbms_Lob.Createtemporary(P_Clob, True);
  7    P_Clob:='This Is The Clob Part';
  8    P_Long:='<-- Long Part -->';
  9    :C:=p_Clob;
 10    Dbms_Lob.Append(:C,P_Long);
 11    Dbms_Lob.Append(:C,To_Clob(P_Vchar));
 12  End;
 13  /
Print C;
 
PL/SQL procedure successfully completed.
 
SQL> 
C
--------------------------------------------------------------------------------
This Is The Clob Part<-- Long Part -->}
 
SQL> 

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:Mamarazzi
ID: 21779891
I've been thinking about the dbms_lob.append...
I have now created a PL/SQL function from your code that returns a clob. I'm calling this function from a cursor for loop. But then I get the error: ORA-22920: row containing the LOB value is not locked.


0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21779928
Oracle Error :: ORA-22920 - row containing the LOB value is not locked

Cause
The row containing the LOB value must be locked before updating the LOB value.

Action
Lock the row containing the LOB value before updating the LOB value.

0
 

Author Comment

by:Mamarazzi
ID: 21780069
I have tried locking the row using select... for update in the cursor but i still get the error message from then function that creates the clob.

FUNCTION (exactly your code)
****************************
function create_clob(
    p_del1 in clob,
    p_del2 in long default null) return clob is
C Clob;
P_Clob Clob;
P_Long Long;
P_Vchar Varchar2(10):='}';    
begin
    Dbms_Lob.Createtemporary(P_Clob, True);
    P_Clob:=p_del1;
    P_Long:= nvl(p_del2,' ');
    C:=p_Clob;
    Dbms_Lob.Append(C,P_Long);
    Dbms_Lob.Append(C,To_Clob(P_Vchar));
    return C;
End;
 
The actual code
**************************
declare 
l_T1 long; 
l_T2 clob;
l_clobcol clob; 
 
cursor c_tab is 
SELECT  col1, col2   
FROM    mytable; 
begin 
    for tabrow in c_tab loop 
            begin
              select mylongcol 
              into l_T1 
              from mylongtable; 
            exception
            when no_data_found then
                l_T1 := null;
            end;
            
            l_T2 := konv_aktu.aktuanteckning('AKTUHANDELSE','ANTECKNING');--function that returns clob value
            
            l_clobcol := create_clob(l_T2, l_T1);
            
            insert into mytargettable (c1, c2, clobcolumn) 
            values (tabrow.col1, tabrow.col2, l_clobcol);  
    end loop; 
end;

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21781543
You may need to add: Dbms_Lob.Createtemporary(????, True); for all the local clobs: C, l_T2, l_clobcol...
 

0
 

Author Comment

by:Mamarazzi
ID: 21784764
Tried that... doesn't make any difference.
0
 

Author Comment

by:Mamarazzi
ID: 21788732
I have now a working version of this, where I first create a row in the table with the clob part inserted in the clob column. After that (within the cursor for loop) I update the column and user dbms_lob.append to add the other parts.

So I'm going for this!
0
 

Author Comment

by:Mamarazzi
ID: 21788734
Forgot to mention that the update select was with the option ... for update to achieve the row lock.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21793548

You did find the correct way to do it, first an insert with empty CLOB, then an update.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question