ORA-22275: invalid LOB locator specified

Hi All,

I am a newbie to the PL/SQL area. I am trying to generate the XML for a record to insert the data into an audit trail for the table. To do so, I declared a string for VARCHAR2(4000) and it was working fine until the data increased the limit due to a field comments (which itself is VARCHAR2(4000)).

To increase the limit, I changed the type to CLOB and ever since my code has stopped working,

The following is a section of the code that I am using to get the data:

GET_CHANGE_LOG_USERS :=  '<Transaction type="insert">'  || CHR(10) ||
                                                  '<Field value="LOGON_ID">'  || CHR(10) ||
                                                  '<OldValue/>'  || CHR(10) ||
                                                  '<NewValue>'  ||
                                             GET_Record.LOGONID ||
                                                  '</NewValue>'  || CHR(10) ||
                                                  '</Field>'  || CHR(10) ||
                                                  '<Field value="ROLE">'  || CHR(10) ||
                                                  '<OldValue/>'  || CHR(10) ||
                                                  '<NewValue>' ||   GET_ROLE_CD  ||
                                                  '</NewValue>'  || CHR(10) ||
                                                  '</Field>'  || CHR(10) ||

................
There are a copule of more fields..
................
................
................
................
         '<Field value="COMMENTS">'  || CHR(10) ||
                                                  '<OldValue/>'  || CHR(10) ||
                                                  '<NewValue>'  ||
                                             GET_Record.COMMENTS||
                                                  '</NewValue>'  || CHR(10) ||
'</Field>'
'</Transaction>';

INSERT INTO AUDIT
            (
            DATE_CHANGED,
            DOC_GUID,  
            ADMIN_GUID,
            CHANGE    
            )
                              
            VALUES
            (
            SYSDATE,
            GET_DOC_GUID,
            GET_ADMIN_GUID,
            GET_CHANGE_LOG_USERS
            );

I have been bugging my head around for last 4 days trying to get a solution to this by myself and copule of other collegues of mine, but no success........


Your help will be very much appricaited...
Rgds
Rohit Arora
RohitAroraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

csachdevaCommented:
Cause: There are several causes:

The LOB locator was never initialized

The locator is for a BFILE and the routine expects a BLOB/CLOB/NCLOB locator

The locator is for a BLOB/CLOB/NCLOB and the routine expects a BFILE locator

Trying to update the LOB in a trigger body -- LOBs in trigger bodies are read-only

The locator is for a BFILE/BLOB and the routine expects a CLOB/NCLOB locator

The locator is for a CLOB/NCLOB and the routine expects a BFILE/BLOB locator

 
  Action: For (1), initialize the LOB locator by selecting into the locator variable or by setting the LOB locator to empty. For (2),(3), (5), and (6),pass the correct type of locator into the routine. For (4), remove the trigger body code that updates the LOB value.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RohitAroraAuthor Commented:
Hi,

Thanks for comments.

Accepted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.