As mentioned by Tom A little codeing of pl/sql will slove everything. Just make a Procedure to do such kind of thing. Pass it the apporprate value so that it can transfer From DB1 to DB2 Usinglinks . This way Java won't come in between.

This is quite helpful hint to update the CLOB. We had to do record by record because the remote table has primary key generated by a sequence.

Though I would love to use the following sql, in this case I cannot use

insert into remote_table select * from localtable

as I have not heard that Oracle has any way to generate unique id on the fly.

If there is anyway that can be solved then it would be great.

Anyway, thanks Tom it was really a useful hint to do an update after insert of rest of field. I will try it out.

Hi Tom,

I tried to run procedure in local database, and procedure in local database had an insert sql statement, to insert data to table "A" in remote database which has table structure: (id NUMBER, test_clob CLOB). After exec the procedure in local database, it give me an error like:

ORA-22992: cannot use LOB locators selected from remote tables

My procedure in local database is look like:

Create or Replace procedure run_clob(p_id in number, p_file_name in varchar2)
AS
l_clob clob;
l_bfile bfile;
begin
insert into clob_table@dlog_link (id, test_clob ) values
(p_id, empty_clob() )
returning the_clob into l_clob;
l_bfile := BFILENAME ('TEMP_DIR', p_file_name);
DBMS_LOB.FILEOPEN(l_bfile);
DBMS_LOB.LOADFROMFILE(l_clob, l_bfile,
DBMS_LOB.getlength(l_bfile));
DBMS_LOB.FILECLOSE(l_bfile);
end;
/

Thank you

Can you give me some sample code, how to do that please.

Thanks

Tom,
First I've created procedure and run on local (database A).

Create or replace procedure test_clob (p_id in number)
AS
    l_clob    clob;
    l_bfile   bfile;
begin
    insert into clob_table (id, the_clob) values
    (p_id, empty_clob()) returning the the_clob into l_clob;
    
    l_bfile := BFILENAME ('TEMP_DIR', 'logfile.txt');
    DBMS_LOB.FILEOPEN(l_bfile);
    DBMS_LOB.LOADFROMFILE(l_clob, l_bfile,
            DBMS_LOB.getlength(l_bfile));
    DBMS_LOB.FILECLOSE(l_bfile);
end;
/

Procedure created

SQL> exec test_clob(1);

PL/SQL procedure successfully completed

SQL> select * from clob_table;
ID         THE_CLOB

1          testing clob column

After that, i've create a global temporary table in remote database (database B).

SQL> create global temporary table foo (id number, text clob);

Table created

SQL> insert into foo select * 
SQL> from clob_table@db_link;

0 row created.

When i ran "insert into foo select * from clob_table@db_link", one row support to insert into foo table instead of 0 row. Do you know why?

Thanks 

After i've followed your instruction like:

SQL> insert into foo@db_link from select * from gtt;

2 rows created.

And then i went to remote database 

SQL> select * from foo;

no rows selected.

but in local database, i did

SQL> select count(*) from foo@db_link;

2 rows created.

Can you tell me why no rows return when i do "select * from foo;" in remote database.

Thanks 

You have to commit your insert before you can see the data in the remote database.

I've follow your suggestion, but still can not see the data in remote database.

SQL> insert into foo@db_link select * from gtt;

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from foo@db_link;

COUNT(*)
--------
       0

but when i ignore the commit statement and the count value is 1:

SQL> insert into foo@db_link select * from gtt;

1 row created.

SQL> select count(*) from foo@db_link;

COUNT(*)
-------
      1

But either way, still can not see the data in remote database. What is wrong with this?

Thanks Tom 

you meant, just pick another table instead of gtt.

That happen occur on window. But when i try to test on unix box with same procedure verything is fine. I don't know why.

What is your Oracle version (4 figures, please)?
And your Windows version (with SP number)?

Michel

My oracle is 9.2.0.1.0 and window is XP Home Edition SP 2

Tom,

Your answer is very excellent and very helpful on my project.
I have another question relate to previous post.

Create or replace procedure test_clob (p_id in number)
AS
    l_clob    clob;
    l_bfile   bfile;
begin
    insert into clob_table (id, the_clob) values
    (p_id, empty_clob()) returning the the_clob into l_clob;
    
    l_bfile := BFILENAME ('TEMP_DIR', 'logfile.txt');
    DBMS_LOB.FILEOPEN(l_bfile);
    DBMS_LOB.LOADFROMFILE(l_clob, l_bfile,
            DBMS_LOB.getlength(l_bfile));
    DBMS_LOB.FILECLOSE(l_bfile);
end;
/

I ran the procedure and data were inserted into the "clob_table" table and display by the following sql statement:

SQL> select id, dbms_lob.getlength(the_clob) from clob_table;

        ID DBMS_LOB.GETLENGTH(THE_CLOB)
---------- ----------------------------
         3                         2260
         4                         2260
         5                         8333
         6                        44497

After that, I've created a report in HTMLDB to display the information in the "clob_table" and i've an error like:

report error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

and I know the error popup because the length of the "id(6) column is greater than 32767 bytes. I've search around in this forum and otn website, but can not came up with any idea how to do it. Can you show me how to handle that situation please?

Thanks 
 

Tom,

but how can i write a code to break a file and append the file, which located in server if the file greater than 32767.

thank you

Assume, i've create a table similar like:

CREATE table clob_table (id number, file_name varchar2(40),
                         output clob);

I'm also have two files name "logfile.txt and logfile2.txt" that are located in TEMP_DIR directory. "logfile.txt" has size = 10234 bytes, and "logfile2.txt" has size = 44479 bytes. 

I want to insert "logfile.txt and logfile2.txt" into clob_table by the following procedure below:

Create or replace procedure test_clob (p_id in number,
            p_file_name in varchar2, p_output in varchar2)
AS
    l_clob    clob;
    l_bfile   bfile;
begin
    insert into clob_table (id, file_name, output) values
    (p_id, p_file_name, empty_clob()) returning the output into l_clob;
    
    l_bfile := BFILENAME ('TEMP_DIR', 'p_output');
    DBMS_LOB.FILEOPEN(l_bfile);
    DBMS_LOB.LOADFROMFILE(l_clob, l_bfile,
            DBMS_LOB.getlength(l_bfile));
    DBMS_LOB.FILECLOSE(l_bfile);
end;
/

and first, i execute the procedure to insert a "logfile.txt" file into the clob_table by the following command:

SQL> exec test_clob (1,'log_file.txt', 'logfile.txt');

SQL> select id, file_name, dbms_lob.getlength(output)
     from clob_table;

ID    FILE_NAME     DBMS_LOB.GETLENGTH(OUTPUT)

1     log_file.txt  10234

SQL> select * from clob_table;

ID     FILE_NAME     OUTPUT

1      log_file.txt  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

and then, i execute that procedure again to insert a "logfile2.txt" file into the "clob_table" table:

SQL> exec test_clob (2, 'log_file2.txt', 'logfile2.txt');


SQL> select id, file_name, dbms_lob.getlength(output)
     from clob_table;

ID       FILE_NAME       DBMS_LOB.GETLENGTH(OUTPUT)

1        log_file.txt    10234
2        log_file2.txt   44479

SQL> select * from clob_table;

ORA-06502: PL/SQL: numeric or value error: character string buffer to small;

Can you explain for me why select statement is ok when i try to retrieve the information for first time, but the second time, it poput the error message above.

How can i resolve that problems?

Thank your very much












 

Sorry, that was my fault, I meant that query cause in HTMLDB not in sqlplus. Can I ask you one more question?

How can i break a row into multiple piece if that row has a size greater than 32767 bytes.

Thank you so much.

Sorry, I'm not explain very clear.

Ok, Assume I have a file that located at TEMP_DIR directory, and TEMP_DIR directory contain the file name "log_file.txt" that has a size > 32767 bytes. I've created a procedure to insert "log_file.txt" that located at TEMP_DIR directory into "clob_table" table, like the following code below.

Create or replace procedure test_clob (p_id in number,
p_file_name in varchar2)
AS
l_clob clob;
l_bfile bfile;
begin

-- Need some code here to break the "log_file.txt"
-- before insert into clob_table. Because HTMLDB
-- just limited the size of each row up to 32767 bytes
-- otherwise will popup an error
-- report error:
-- ORA-06502: PL/SQL: numeric or value error:
-- character string buffer too small

insert into clob_table (id, the_clob) values
(p_id, empty_clob())
returning the the_clob into l_clob;

l_bfile := BFILENAME ('TEMP_DIR', 'p_file_name');
DBMS_LOB.FILEOPEN(l_bfile);
DBMS_LOB.LOADFROMFILE(l_clob, l_bfile,
DBMS_LOB.getlength(l_bfile));
DBMS_LOB.FILECLOSE(l_bfile);
end;
/

and I want to make a report to display the information in "clob_table" by using HTMLD. Since HTMLDB built using mod_plsql, the maximum size of a single report row is limited to 32767 bytes. That's why, i need to break the "log_file.txt" into multiple piece before insert into the "clob_table".

Thank you.

Thank you so much Tom. It's work wonderful.

Tom,

Can I user HTML within pl/sql to make the text file look more organize? If yes, Please give me an example.

Thanks

Can you give me little of sample code using HTML in PL/SQL?

Thank you

Thank you so much Tom. My project can not complete without your help.

Thank you

Is there any way to load a file into a CLOB column in a remote database using PLSQL? The local machine only has Oracle Client (SQL*Plus) installed. I understand that dbms_lob.fileopen will only work when the file is on the database server. What's the workaround?

However, there is no local database. The local machine only has Oracle client installed. Given the case, is it possible to load the file using only PL/SQL?

OK so i've gathered from this that a CLOB can be copied using a procedure across a database link. I'm trying to bring the CLOB across the database link based on a trigger. I have it set up so that the trigger copies the CLOB from one database to another (via dblink). It seems to only work when the clob is under 4000 characters. Is this possible? My lead dba is saying its not possible to copy clobs over a db link in a trigger.

I was thinking of piecing it over 4000 characters at a time but that is very undesireable.

Heres my code:

SELECT count(*)
INTO intExists
FROM emaar_active.xxx@usar b
WHERE b.proj_no = :NEW.ts_proj_num;

IF intExists > 0 THEN
--UPDATE mash_admin.usr_re
-- SET ts_dd_1391 = empty_clob(),
-- ts_new_acquisition = empty_clob()
-- WHERE ts_id = :NEW.ts_id;

INSERT INTO stgng_area.usr_clob_temp_table
(dd_1391, ts_new_acquisition, ts_proj_num, ts_id)
SELECT b.justf_memo, b.proj_memo, b.proj_no, :NEW.ts_id
FROM emaar_active.xxx@usar b
WHERE b.proj_no = :NEW.ts_proj_num;

SELECT ts_new_acquisition
INTO :NEW.ts_new_acquisition
FROM stgng_area.usr_clob_temp_table
WHERE ts_proj_num = :NEW.ts_proj_num
AND ts_id = :NEW.ts_id;

DELETE FROM stgng_area.usr_clob_temp_table
WHERE ts_proj_num = :NEW.ts_proj_num
AND ts_id = :OLD.ts_id;

This is just a portion of my trigger. Also i'm pulling two clobs over and i've only seen this issue on one of the CLOBS i'm pulling over. The other seems to always come over fine. Is it possible that a column in my new instance could be corrupted?

Thanks in advance!!!!