Home » SQL & PL/SQL » SQL & PL/SQL » Inserting multiple varchar2 values into a single CLOB row
Inserting multiple varchar2 values into a single CLOB row [message #229710] Mon, 09 April 2007 16:49 Go to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
can we Insert multiple varchar2 values into a single CLOB row? IF so, please provide an example.


Thanks in advance.

Re: Inserting multiple varchar2 values into a single CLOB row [message #229711 is a reply to message #229710] Mon, 09 April 2007 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
just concatenate them togther.
Why is this so difficult?
Show us what you tried so far.
Re: Inserting multiple varchar2 values into a single CLOB row [message #229712 is a reply to message #229711] Mon, 09 April 2007 17:14 Go to previous messageGo to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
Hey,


I dont want to insert the values at the same time. I want to insert values to the same row every time when the procedure is called.
Re: Inserting multiple varchar2 values into a single CLOB row [message #229713 is a reply to message #229710] Mon, 09 April 2007 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I want to insert values to the same row every time when the procedure is called.
INSERT INTO .....

Again show us what you done & the actual problem. use CUT & PASTE!
Re: Inserting multiple varchar2 values into a single CLOB row [message #229714 is a reply to message #229713] Mon, 09 April 2007 17:32 Go to previous messageGo to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
I havent created any procedure so far. But this is what I was I was thinking:


There is a table(test_clob) with the following columns:
val_value clob;


The following procedure will insert values into the table as follows:

create or replace proc1 (p_line_text varchar2(1000))
is
begin
Insert into test_clob values(p_line_text)
end;


My problem is when the procedure is called 2 times values have to be inserted to only one row of the table (test_clob) and not into 2 different rows
Re: Inserting multiple varchar2 values into a single CLOB row [message #229779 is a reply to message #229714] Tue, 10 April 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

update?
merge?

Re: Inserting multiple varchar2 values into a single CLOB row [message #229846 is a reply to message #229779] Tue, 10 April 2007 02:57 Go to previous messageGo to next message
ramaswamynanda
Messages: 6
Registered: April 2007
Junior Member
hello,

try this. please let me know whether it helped.

SET SERVEROUTPUT ON ;

create table test_clob(val_clob clob);

create or replace procedure proc1 (p_line_text in varchar2) as

myclob clob;

begin

select val_clob into myclob from test_clob ;

dbms_lob.append(myclob, p_line_text);

update test_clob set val_clob = myclob;

exception
--this will handle first time processing
WHEN NO_DATA_FOUND
THEN
begin
INSERT INTO TEST_CLOB VALUES(P_LINE_TEXT);
end;

end;


execute proc1('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

execute proc1('BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');

Use the 2 statements below to verify that data has been inserted/appended

select length(val_clob) from test_clob;

SELECT * FROM TEST_CLOB;

thanks
nanda
Re: Inserting multiple varchar2 values into a single CLOB row [message #229851 is a reply to message #229846] Tue, 10 April 2007 03:05 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> var t varchar2(100);
SQL> exec :t := 'aaaaaaaaa';

PL/SQL procedure successfully completed.

SQL> merge into test using (select :t t from dual) on (1=1)
  2  when matched then update set col=col||t
  3  when not matched then insert values (t)
  4  /

1 row merged.

SQL> select * from test;
COL
-----------------------------------------------------------------
aaaaaaaaa

1 row selected.

SQL> exec :t := 'bbbbbbbbbb';

PL/SQL procedure successfully completed.

SQL> merge into test using (select :t t from dual) on (1=1)
  2  when matched then update set col=col||t
  3  when not matched then insert values (t)
  4  /

1 row merged.

SQL> select * from test;
COL
-----------------------------------------------------------------
aaaaaaaaabbbbbbbbbb

1 row selected.

Regards
Michel
Previous Topic: tune this procedure
Next Topic: DBMS_metata export all database with roles and grants
Goto Forum:
  


Current Time: Wed Dec 07 10:59:24 CST 2016

Total time taken to generate the page: 0.15265 seconds