invalid LOB locator specified: ORA-22275

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Just a simple reminder (I tend to forget things over the centuries) when using procedure DBMS_LOB.CONVERTTOCLOB with persistent lob locators:

1. don't forget to include the FOR UPDATE clause in the SELECT statement fetching the lob locators. 
2. don't forget to initialize the destination lob (in this case the clob column DOC)  with EMPTY_CLOB().

otherwise ORA-22275 may occur (see also another cause of this error).

...
 select doc, bdoc, charset
   into l_clob, l_blob, l_cset
   from file_imports
  where id = p_fileid
    for update;

...
 dbms_lob.converttoclob(
  l_clob,
  l_blob,
  dbms_lob.lobmaxsize,
  l_coff,
  l_boff, 
  l_cset,
  l_context,
  l_warn);
...
 
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 

In the PL/SQL fragment above, column DOC was null, which is a different value from EMPTY_CLOB().
As this column is always null when the record is created, I modified the default value of the column as follows:

alter table file_imports modify doc default empty_clob();

This avoids the annoyance of having to update the row and set DOC to EMPTY_CLOB() before calling DBMS_LOB.CONVERTTOCLOB.


See message translations for ORA-22275 and search additional resources.

LPX-00209: PI names starting with XML are reserved

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I don't know why it took me more than the necessary to understand where was the problem when I hit the following error.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00209: PI names starting with XML are reserved

At any rate, in my case the reason was not the same as reported in other sites (like having a duplicate XML declaration).
The trouble was caused by a simple blank character located before the initial XML file declaration.
The error occurred on Oracle 10.2 (XE), while on Oracle 11.2.0.3.0 EE the blanks seem to give no concerns whatsoever to the parser.
I ignore the results on the Oracle versions in between.

You can easily simulate the problem with the following query in SQL Developer:

select xmltype(' ' || dbms_xmlgen.getxml('select 1 from dual')) x from dual;

If you have a clob object containing an xmlfile with extra blanks at the beginning of the file and attempting to parse it you get this error, you may easily fix the problem by TRIMming it first.

See message translations for ORA-19202, LPX-00209 and search additional resources.

Apex Text Messages as substitution strings - Wanted

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you, like me, think that it could be useful having text messages available as substitution strings (ideally using the #syntax#) inside certain Apex's element attributes like button attributes, report links, form attributes and so on, then you might want to vote for this feature request on Apex feature application

https://apex.oracle.com/pls/apex/f?p=55447:1:0
You need an OTN membership to log on to the application.

The feature request is titled: "Make text messages available as substitution strings"
The rationale behind this request follows:

"It happens frequently that one needs to add certain HTML attibutes like ALT, TITLE inside certain APEX elements like links, buttons, icons and so on. In the best case Apex makes available this attributes as translatable text, but unfortunately this stuff is often a mix of HTML fragments and real text which is not so desirable given the risk of corrupting the syntax in the translated form. It would be much better to have text messages available as substitution strings either using the "hash" syntax or the ampersand and period syntax throughout the entire page or region after specifying somewhere which messages we really need to have at hand in that context."

I also happen to desire such feature when I need to manually build links in Apex reports using the APEX_ITEM APIs. Instead of calling the APEX_LANG.MESSAGE function inside the query, I'd prefer to put a single occurrence of TITLE="#TITLE_MESSAGE#" or ALT="#ALT_MESSAGE#" in the appropriate place so that the API is called only once by Apex before rendering the region.
In order to decide which messages I need to get translated, there could be a region (or page level) attribute where I specify a comma separated list of message names, such that only the required ones will be made available as substitution strings.

I know that there could be some workarounds out there where one allocates a certain number of substitution strings and dynamically alters their content with the desired text messages, but this makes the whole thing just too cumbersome in my view, so I'd prefer a more straightforward approach.    

It's just a proposal, so have your say.