SQL Developer

This has already been reported and logged as a bug.

At time being, either:
a) revert to SQL Dev 21.4.3 (or 22.2.1)
Or:
b) insert-select from v$sql into a “real” database table (a global temporary table will do); the sql_fulltext column from that table will not anymore be a temporary CLOB, hence the bug will not happen (as it affects only temporary CLOB columns).

From @thatjeffsmith-oracle's comments in other posts, it would seem this issue is being treated as a high priority bug.

Regards,

yes, i expect a 23.1.1. release to fix this and a few other regressions found in 23.1

any ETA?

I also consider this as quite annoying (and should not be that difficult to fix). Fortunately SQLcl works properly even with temporary LOBs.

Hopefully end of September.

Hello,

also in the new Version 23.1.1.339 this bug still exists.😭

So we must go on working with an older version.

in the new Version 23.1.1.339 this bug still exists.😭

This sounds hard to believe, but sadly, it's true.

And it's all the more surprising as the release notes (link) mention the following bug as fixed:

35338513 TEMPORARY CLOBS ARE TRUNCATED WHEN EXECUTING STATEMENTS

This issue had been originally discussed in this post, and obviously the test case still fails in 23.1.1.

So we must go on working with an older version.

I definitely see it as a blocking issue, too: temp CLOB are everywhere—think V$SQL.SQL_FULLTEXT, DBMS_METADATA.GET_DLL, you name it! I'm afraid I'll have to stick to 21.4.3 until this is fixed.

Best regards,

The workaround is to use xmltype.

The workaround is to use xmltype.

That is one of the workarounds. The simpler one was mentioned already. I am “still” (??) using version 20.4.1; in my case it's as a “workaround” for my laziness, pure and simple, but it also works for the various issues raised here and in other places.

Which use case if failing for you? If I use SET LONG appropriately, it's grabbing everything as expected.

Of course it does work with SET LONG in script output—it always has.

This issue only affects temporary CLOBs displayed in data grids, either when running queries using “Run Statement” / F9 in SQL worksheets, or in reports of style "Table".

(IIRC, SET LONG has no influence over the data grid.)

For convenience I'll repeat the test case here (with minor simplifications). It was originally posted in this comment (dated 3 May, 2023).

Test setup

create table tstclob (
   c1 clob
);

-- Insert 1 row into the TSTCLOB table, with c1 containing exactly 10,000 characters
-- (100 lines of 100 characters each)

declare
   l_clob clob;
begin
   for i in 0 .. 99 loop
      l_clob := l_clob || to_char(i, 'FM009')
         || q'<-+----|1---+----|2---+----|3---+----|4---+----|5---+----|6---+----|7---+----|8---+----|9---+---->'
         || chr(10);
   end loop;
   insert into tstclob (c1) values (l_clob);
   commit;
end;
/

create or replace function as_temp_clob(in_clob in clob) return clob
authid current_user
as
   l_tmp clob;
begin
   dbms_lob.createtemporary(l_tmp, false);
   dbms_lob.append(l_tmp, in_clob);
   return l_tmp;
end as_temp_clob;
/

Test query (to be run using “Run Statement” / F9)

with t( stored_clob, temp_clob ) as (
    select c1,
           as_temp_clob(c1)
      from tstclob
)
select length(stored_clob) as stored_clob_len,
       length(temp_clob)   as temp_clob_len,
       temp_clob,
       stored_clob
  from t;

Results: (SQL Dev 23.1.1.339.1213 + Oracle JDK 11.0.21)

Regards,

Another way to reproduce the issue quickly is in running the following query:

select a.sql_id, a.len, b.sql_fulltext
  from ( select *
           from ( select sql_id, 
                         any_value(length(sql_fulltext)) as len,
                         min(con_id) as con_id
                    from v$sqlarea
                   where parsing_schema_id = 0
                   group by sql_id
                   order by len desc
                )
          where rownum <= 5
       ) a,
       v$sqlarea b
 where a.sql_id = b.sql_id
   and a.con_id = b.con_id
 order by a.len desc;

(required privilege: READ on V$SQLAREA)

That query returns the 5 longest queries in the cursor cache parsed in the SYS schema; it's more or less guaranteed that queries of a couple of kbytes will be returned on any container.

Remark: I suppose this issue was filed as the following (internal) bug:

35338513 - TEMPORARY CLOBS ARE TRUNCATED WHEN EXECUTING STATEMENTS

MOS note 2989599.1 reports this bug as fixed in SQL Developer 23.1.1, so it looks like something went wrong here.

Could it be that the build which was put online on 6 Dec. 2023, and which apparently included the wrong JDK (as confirmed here), also failed to include this fix as originally planned?

Best regards,

We've fixed this issue but failed to properly bundle it for th release, we're going to update the 23.1.1 download again, shortly.

We've got a 23.1.1.339 version on 06-DEC-2023. And then an updated version 23.1.1.342 on 11-DEC-2023 where the bug is not fixed. So the third published version of 21.1.1 should contain the fix. Right?

I have also tested it and it does not work properly with version 23.1.1.342. Tested with:

select to_clob('loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo???') clob_string 
from dual

it only works if we resize the column on the output grid and execute the query again. if we change then the string the first execution does not work (only shows the first few characters) until we resize the column in the grid an execute the query again. also in the “value view” it only shows the correct full value after we have resized the column in the grid an executed the query again…

waiting for next release…

Tested this today and version 23.1.1.345 does resolve issue.

Tested this today and version 23.1.1.345 does resolve issue.

Correct. But with this version, there's a workaround available. Resize the grid column containing the CLOB (a tiny bit is enough) and re-query (not always necessary) and voilà the full content is available. Annoying, I know. But better than nothing.