SQL & PL/SQL

Hi everyone,

I think this question had already posted.But i am not able to figure out this problem..

what i am trying to do is



I have a table in the remote database with a CLOB column like this
REMOTE_TABLE
============
REMOTE_TABLE_ID (Populated with sequence)
REMOTE_CLOB CLOB

In my Local database i have to write a Procedure to gather some information on a particular record (My Requirement) and save that CLOB in the REMOTE_TABLE.

I built that procedure like this

Declare
var_clob CLOB; /* I need to processs several records and keep all data in a clob

begin

/***** Processed several records in a local database and stored in the variable var_clob which i need to insert into remote database ****/

Insert into remote_table@remote values (remote_table_seq.nextval,var_clob);

/*** when i try to execute the above command i am getting the following error

ORA-06550: line 6, column 105:
PL/SQL: ORA-22992: cannot use LOB locators selected from remote tables
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored *****/

/***For a test i created the same table in local db****/

Insert into local_table values (local_table_seq.nextval,var_clob);

It is working fine and i am able to see the entire CLOB what i want.


surprisingly if i pass some value instead of a varibale to the remote table like the following..
Insert into remote_table@remote values (remote_table_seq.nextval,'Hiiiiiiiii');
It is working fine...

I tried the following too..

decalre
var_clob clob;
begin
var_clob := 'Hiiiiiiiiiiiiiii';
Insert into remote_table@remote (remote_table_id) values (1);
commit;
update remote_table@remote set remote_clob = var_clob where remote_table_id = 1;
commit;
end
I am getting the following error..

ORA-22922: nonexistent LOB value
ORA-02063: preceding line from CARDIO
ORA-06512: at line 6



Could someone please help me in fixing this issue..I need to process all the data to a variable like var_clob and insert that clob into remote table..

Thanks in advance..
phani