How to define BLOB column in Private Temporary Table?

user_7756682 0
Viewed 671

During writing of certain procedure in Oracle database I came across a problem, where I tried to define PRIVATE temporary table with column of BLOB data type and it did not let me.

Code for creating PRIVATE temporary table:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_Test (
        id NUMBER(19), 
        userdata BLOB);

When I try to execute statement above, I get:

ORA-14451: unsupported feature with temporary table
14451. 00000 - "unsupported feature with temporary table"
*Cause: An attempt was made to create an IOT, specify physical attributes,
specify partition or parallel clause.
*Action: do not do that.

But if I comment userdata column, it executes successfully.

All I could find in documentation was, that limitations/constraints of creating PRIVATE temporary tables (avaliable since 18c) should be the same as for PUBLIC temporary tables (which are also avaliable before version 18c), but that is not true, because I tried to define PUBLIC temporary table with BLOB column, and it worked.

oracle18c temp-tables blob oracle
1 Answers

I tried to define PRIVATE temporary table with column of BLOB data type and it did not let me.

This is reproducible, for CLOB as well as BLOB. Probably it's is a bug but a documentation bug rather than an implementation bug.

The thing about Private temporary tables is that they are memory structures, and session memory structures at that. Consequently there are complications with memory management when BLOBs are involved. (Slightly related, one of the other causes ORA-14451 is creating a PTT in a PDB with the in-memory option enabled.)

On the other hand, we can create a collection of BLOBs in PL/SQL so there is no absolute barrier to wrangling BLOBs in memory structures. Which means this may just be a limitation of the initial implementation that will be addressed in a subsequent release.

None of which helps you now, of course, but there isn't much we can suggest except that raise an SR with Oracle (assuming you work for an organisation which has an Oracle Support contract).

user_1463250
Related