Forum Stats

  • 3,874,716 Users
  • 2,266,767 Discussions
  • 7,911,959 Comments

Discussions

Native Support for CLOB & BLOB

user466593
user466593 Member Posts: 9 Green Ribbon
edited May 6, 2019 4:59PM in Database Ideas - Ideas

The dbms_lob is too complicated for a supported type.

It should be natively supported.

declare

  longString CLOB;

  longString2 CLOB;

begin

   longString := '456465...asdfsdf'; -- past the current VARCHAR2 limit

   longString2 := substr(longString, 500000 /*Start Index*/, 400000 /*length*/);

   ...

end;

Having a native support would allow to easily move from VARCHAR2 to CLOB when required.

Currently this process is painful.

Sven W.user4377404Jeffrey Kempuser466593
8 votes

Active · Last Updated

«1

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,254 Gold Crown

    Already Offered for CLOBs.

    Not needed for BLOBs.

    DBMS_LOB is a package for manipulating LOBs,  It is not a "type" (UDT/CREATE TYPE).

    You need to be >= 10g in order to use SUBSTR on CLOBs.

    declare

      longstr clob;

      copy2   clob;

    begin

      longstr := cast(rpad('x',32767,'x') as clob) || rpad('y',40000-32767 + 5,'y') || rpad('x',32767,'x') || rpad('z',32767,'z');

      longstr := longstr || longstr || longstr;

     

      dbms_output.put_line('length=' || length( longstr) );

      dbms_output.put_line( substr( longstr, 40000, 32767 ) );

     

      copy2 := substr(longstr, 500000 /*Start Index === offset*/, 400000 /*length*/);

    end;

    /

    You also need to write code that works.  (your SUBSTR is wrong).

    The only "issue" I see is the > 32767 size Static String.  My response:  don't do that.  Load the data into a real table and read it from there.

    MK

    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Sep 26, 2018 7:59AM

    Already Offered for CLOBs.

    Not needed for BLOBs.

    DBMS_LOB is a package for manipulating LOBs,  It is not a "type" (UDT/CREATE TYPE).

    You need to be >= 10g in order to use SUBSTR on CLOBs.

    declare

      longstr clob;

      copy2   clob;

    begin

      longstr := cast(rpad('x',32767,'x') as clob) || rpad('y',40000-32767 + 5,'y') || rpad('x',32767,'x') || rpad('z',32767,'z');

      longstr := longstr || longstr || longstr;

     

      dbms_output.put_line('length=' || length( longstr) );

      dbms_output.put_line( substr( longstr, 40000, 32767 ) );

     

      copy2 := substr(longstr, 500000 /*Start Index === offset*/, 400000 /*length*/);

    end;

    /

    You also need to write code that works.  (your SUBSTR is wrong).

    The only "issue" I see is the > 32767 size Static String.  My response:  don't do that.  Load the data into a real table and read it from there.

    MK

    Agreed, this suggestion boils down to a new CLOB text literal, which I would welcome.

    I think the syntax would have to be along the same lines as the n'text' currently provided for NVARCHAR2 - perhaps c'text' and nc'text'?

  • user4377404
    user4377404 Member Posts: 6 Green Ribbon

    Agreed, this suggestion boils down to a new CLOB text literal, which I would welcome.

    I think the syntax would have to be along the same lines as the n'text' currently provided for NVARCHAR2 - perhaps c'text' and nc'text'?

    There's also this "beauty" with clobs.

    DECLARE

      c1 CLOB;

      c2 CLOB;

      c3 CLOB;

    BEGIN

      c1 := rpad('A', 32000, 'A') || '%' || rpad('B', 32000, 'B');

      c2 := rpad('X', 32000, 'A') || rpad('X', 32000, 'X');

      c3 := REPLACE(c1, '%', c2);

    END;

    And the fact, that you cannot query them over db-link. I'm all for making clob type a 1st class citizen.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    LOBs are not a native PL/SQL data type, and needs "special" handling and storage - after all, a LOB can easily be GB's in size.

    It is naive to think that PL/SQL can guess how your code is going to use a LOB (transient/temporary/persistent), and what the LOB's final size is.

    Typically, a formal API (like DBMS_LOB) requires the developer to think about what the code is doing - which in my experience is seldom the case by cowboys who want to code using LOBs, firing from the hip.

    Peter Hraško
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    There's also this "beauty" with clobs.

    DECLARE

      c1 CLOB;

      c2 CLOB;

      c3 CLOB;

    BEGIN

      c1 := rpad('A', 32000, 'A') || '%' || rpad('B', 32000, 'B');

      c2 := rpad('X', 32000, 'A') || rpad('X', 32000, 'X');

      c3 := REPLACE(c1, '%', c2);

    END;

    And the fact, that you cannot query them over db-link. I'm all for making clob type a 1st class citizen.

    user4377404 wrote:...And the fact, that you cannot query them over db-link. I'm all for making clob type a 1st class citizen.

    SInce 12.2 you can bind lob locators via db links. There are also some other tricks to access/transport lobs over DB links.

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adlob/distributed-LOBs.html#GUID-7E450E86-3E4E-4714-A164…

    Peter Hraško
  • user466593
    user466593 Member Posts: 9 Green Ribbon

    Already Offered for CLOBs.

    Not needed for BLOBs.

    DBMS_LOB is a package for manipulating LOBs,  It is not a "type" (UDT/CREATE TYPE).

    You need to be >= 10g in order to use SUBSTR on CLOBs.

    declare

      longstr clob;

      copy2   clob;

    begin

      longstr := cast(rpad('x',32767,'x') as clob) || rpad('y',40000-32767 + 5,'y') || rpad('x',32767,'x') || rpad('z',32767,'z');

      longstr := longstr || longstr || longstr;

     

      dbms_output.put_line('length=' || length( longstr) );

      dbms_output.put_line( substr( longstr, 40000, 32767 ) );

     

      copy2 := substr(longstr, 500000 /*Start Index === offset*/, 400000 /*length*/);

    end;

    /

    You also need to write code that works.  (your SUBSTR is wrong).

    The only "issue" I see is the > 32767 size Static String.  My response:  don't do that.  Load the data into a real table and read it from there.

    MK

    I corrected the example as per your comment. But note that it would not have worked anyway as the sample strings are past the 32767 limit size. ;) This is exactly my point. The DBMS_LOB package is cubersome to use. Oracle needs to simplify the synthax for PL/SQL.

  • user466593
    user466593 Member Posts: 9 Green Ribbon

    LOBs are not a native PL/SQL data type, and needs "special" handling and storage - after all, a LOB can easily be GB's in size.

    It is naive to think that PL/SQL can guess how your code is going to use a LOB (transient/temporary/persistent), and what the LOB's final size is.

    Typically, a formal API (like DBMS_LOB) requires the developer to think about what the code is doing - which in my experience is seldom the case by cowboys who want to code using LOBs, firing from the hip.

    In my opinion, there is a similar issue with file manipulation. In java, to read and write to a file you need about 10 lines of codes. You need to create a bunch of "Streams". See https://docs.oracle.com/javase/tutorial/essential/io/file.html. In Python, you need one line for each. See https://docs.python.org/2/tutorial/inputoutput.html#reading-and-writing-files. Python does not now how big the files is. Or may be it does... The fact is as a programmer, I just want to be able to build my variables simply. With LOBs, it is not simple. The syntax should be the same as VARCHAR2.

  • user466593
    user466593 Member Posts: 9 Green Ribbon
    user4377404 wrote:...And the fact, that you cannot query them over db-link. I'm all for making clob type a 1st class citizen.

    SInce 12.2 you can bind lob locators via db links. There are also some other tricks to access/transport lobs over DB links.

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adlob/distributed-LOBs.html#GUID-7E450E86-3E4E-4714-A164…

    Thank you for that. We have not moved yet to 12g but this new feature (bind lob locators via db links) is a welcome improvement.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    In my opinion, there is a similar issue with file manipulation. In java, to read and write to a file you need about 10 lines of codes. You need to create a bunch of "Streams". See https://docs.oracle.com/javase/tutorial/essential/io/file.html. In Python, you need one line for each. See https://docs.python.org/2/tutorial/inputoutput.html#reading-and-writing-files. Python does not now how big the files is. Or may be it does... The fact is as a programmer, I just want to be able to build my variables simply. With LOBs, it is not simple. The syntax should be the same as VARCHAR2.

    You are confusing string literals with CLOBs.

    You want to do this:

    longString := '456465...asdfsdf'; -- past the current VARCHAR2 limit

    Which means you want a literal that contains characters, to potentially be GBs in size - just how on earth is this data segment for such a code segment, suppose to be loaded into memory?? In ANY lanaguage?

    The issue here is one of ignorance, and not so-called LOB interface complexities.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    You are confusing string literals with CLOBs.

    You want to do this:

    longString := '456465...asdfsdf'; -- past the current VARCHAR2 limit

    Which means you want a literal that contains characters, to potentially be GBs in size - just how on earth is this data segment for such a code segment, suppose to be loaded into memory?? In ANY lanaguage?

    The issue here is one of ignorance, and not so-called LOB interface complexities.

    Billy~Verreynne wrote:Which means you want a literal that contains characters, to potentially be GBs in size - just how on earth is this data segment for such a code segment, suppose to be loaded into memory?? In ANY language?

    The way it currently is, but with convenient syntax that avoids the need for concatenation.