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: 1541
  • Last Modified:

Concatenating clob, long and char?

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
Mamarazzi
Asked:
Mamarazzi
  • 6
  • 5
1 Solution
 
MikeOM_DBACommented:

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
 
MamarazziAuthor Commented:
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
 
MikeOM_DBACommented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
MamarazziAuthor Commented:
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
 
MikeOM_DBACommented:
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
 
MamarazziAuthor Commented:
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
 
MikeOM_DBACommented:
You may need to add: Dbms_Lob.Createtemporary(????, True); for all the local clobs: C, l_T2, l_clobcol...
 

0
 
MamarazziAuthor Commented:
Tried that... doesn't make any difference.
0
 
MamarazziAuthor Commented:
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
 
MamarazziAuthor Commented:
Forgot to mention that the update select was with the option ... for update to achieve the row lock.
0
 
MikeOM_DBACommented:

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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