The inconsistencies of DBMS_LOB

Translation of previous post

The DBMS_LOB package provides functions to manipulate CLOBs (and BLOBs) like we have in varchar2.

Well, sort off... The most strange case caso is DBMS_LOB.SUBTR where the parameter order of the initial position (offset)and the number of char to get (amount) is distinct from the order we have then in the "normal" substr function.



substr( string, offset , amount ) RETURN VARCHAR2 ;

dbms_lob.substr(lob_loc,amount,offset) RETURN VARCHAR2;

In the normal INSTR function and in DBMS_LOB.INSTR the parameter order is the same.

instr( string, pattern [, offset [, nth_appearance ] ] )

dbms_lob.instr(lob_loc,pattern,offset,nth_appearance) RETURN INTEGER;

But...the "normal" function allows a negative offset to make a search from string ends to the beginning, but DBMS_LOB.INSTR doesn0 do that search always returning NULL.

Note: the name of parameters of the functions substr and instr are distinct, but to easy the reading I used the same as used in DBMS_LOB