asked on

Insert a row over database link containing a CLOB via a procedure (ORA-22927: invalid LOB locator specified ORA-02063)

I have a table that contains a CLOB on one oracle database (10.2.0.5). From another oracle database (9.2.0.8), I have a db link and all the rights setup. I can select from the table over the link as long as I exclude the clob field. My problem is that I need to be able to insert over the db link including a CLOB via a procedure.
: (Error): ORA-22927: invalid LOB locator specified ORA-02063:
I can insert with straight sql but inside of a procedure, it fails.
If I make the table local, the insert works but I don't have that option.

INSERT INTO myschema.mytable@mydblink(txtfield1, clobfield1) 
values ('mydata', to_clob('bigdata');

Open in new window

E43509

ASKER

Here is a sample procedure, The clob field in the remote table is 'Body'.

PROCEDURE TEST
   IS
    myclob  CLOB;
BEGIN
        myclob := 'HELLO';
                   INSERT INTO myschema.mytable@mydblink (from_addr, to_addr, subject, body, submitted)
                    VALUES
                        ('from'
                        ,'to'
                        ,'subject'
                        ,myclob
                        ,SYSDATE);
               COMMIT;


END; -- Procedure

Open in new window

slightwv (䄆 Netminder)

If the straight SQL works and the procedure fails, there must be an issue in the procedure.

Can you post an example of what you are doing in the code?

slightwv (䄆 Netminder)

That is service! Post the example before I even ask!

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

E43509

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

Correct. That is what the link I posted says. The LOB locator that you have created in your procedure (the variable called myclob), cannot be used across a database link.

If you need to move more than 32767 bytes, then you will have to work around it.

E43509

ASKER

Accepted my comment as part of the solution since it distilled down the the link from asktom to what I needed to do.