CODEWITHSUNDEEP
sqloracle-databaseoracle11g
michael nesterenko
michael nesterenko

Reputation: 14449

collect function on clobs

I need to select several clobs as a nested table.

create table t (vc_val varchar2(100), clob_val clob);
create type varchar_t as table of varchar2(100);
create type clob_t as table of clob;

Following query works fine:

select cast(collect(vc_val) as varchar_t) from t;

And following fails, why?

select cast(collect(clob_val) as clob_t) from t;

Link to this example http://sqlfiddle.com/#!4/b01e7/3

Can someone explain me why second query fails?

Upvotes: 0

Views: 3204

Answers (2)

tbone
tbone

Reputation: 15483

CREATE OR REPLACE TYPE t_clob_tab as table of clob;

declare
    l_clob_tab t_clob_tab;
begin
    -- collect some data as clobs into a nested table
    select
    cast(multiset(
        select to_clob(object_name)
        from dba_objects
        where rownum <= 10)
    as t_clob_tab)
    into l_clob_tab
    from dual;

    -- show the data
    for i in 1 .. l_clob_tab.count
    loop
        dbms_output.put_line('Clob' || i || ' Value is: ' || l_clob_tab(i));
    end loop;

end;

Output:

Clob1 Value is: C_OBJ#
Clob2 Value is: I_OBJ#
Clob3 Value is: TAB$
Clob4 Value is: CLU$
Clob5 Value is: C_TS#
Clob6 Value is: I_TS#
Clob7 Value is: C_FILE#_BLOCK#
Clob8 Value is: I_FILE#_BLOCK#
Clob9 Value is: C_USER#
Clob10 Value is: I_USER#

As for the CAST function support for LOB types:

CAST does not directly support any of the LOB data types. When you use CAST to convert a CLOB value into a character data type or a BLOB value into the RAW data type, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target data type. If the resulting value is larger than the target type, then the database returns an error.

This seems to refer to converting from a CLOB -> Varchar. But if you already have Clobs, you should be able to put them into a collection (a nested table in this case).

I typically use CAST + MULTISET instead of COLLECT, I think its easier and less fussy. I think your problem is with COLLECT + CAST here, not CAST itself (similar issue with NUMBER precisions).

EDIT: I removed any suggestion of using Collect function, although I could use it without error in a simple select, I could not use it in pl/sql. Also, in addition to the CAST + MULTISET option above (SQL or pl/sql), you can (in pl/sql anyway) simply do:

select clob_col
bulk collect into l_clob_tab
from t;

Hope that helps.

Upvotes: 3

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

It doesn't work because CAST doesn't support LOB types.

You can read about this in Oracle's Documentation: CAST Function In Oracle

Using your test data from SQLFiddle, CAST can convert a CLOB to a VARCHAR2:

SELECT CAST(clob_val AS VARCHAR2(100)) FROM t;

Result:

CAST(CLOB_VALASVARCHAR2(100))
-----------------------------
clob1
clob2

But we can't do it the other way around, the CLOBs are just not supported:

SELECT CAST(vc_val AS CLOB) FROM t;

> 00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Upvotes: 3

Related Questions