?
Solved

ORA-22275: invalid LOB locator specified

Posted on 2006-04-24
2
Medium Priority
?
1,095 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:RohitArora
2 Comments
 
LVL 4

Accepted Solution

by:
csachdeva earned 900 total points
ID: 16524716
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
 

Author Comment

by:RohitArora
ID: 16585718
Hi,

Thanks for comments.

Accepted.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question