Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-900/ORA-600 When Casting to a Domain in PL/SQL

Philipp SalvisbergDec 21 2024 — edited Dec 22 2024

The next script causes

ORA-00900: invalid SQL statement
ORA-00600: internal error code, arguments: [qctVCO : bfc], [60], [0], [1], [873], [1], [2], [1279], [], [], [], []

The issue is reproducible on the FREE edition 23.5, 23.6 and Enterprise Edition 23.6.

-- show versions
set serveroutput on size unlimited
select * from v$version;

-- works
drop domain if exists dbl_semantic_version force;

-- works
create domain dbl_semantic_version as varchar2(60 byte) 
   constraint dbl_semantic_version_has_major_minor_patch_ck check (regexp_like(dbl_semantic_version, '^\d{1,6}\.\d{1,6}\.\d{1,6}$'))
   display dbl_semantic_version;

-- works
with
strings (string_col) as (values
    ('1.9.0'),
    ('1.10.0'),
    ('1.11.0')
),
base as (
    select string_col,
            cast(string_col as dbl_semantic_version) as domain_col -- cause ORA-900/ORA-600 only in PL/SQL
      from strings
)
select *
from base;

-- causes ORA-00900: invalid SQL statement
-- ORA-00600: internal error code, arguments: [qctVCO : bfc], [60], [0], [1], [873], [1], [2], [1279], [], [], [], []
-- reproducible in 23.5 and 23.6 
declare
   c_actual sys_refcursor;
begin
   for r in (
         with
            strings (string_col) as (values
               ('1.9.0'),
               ('1.10.0'),
               ('1.11.0')
            ),
            base as (
               select string_col,
                      cast(string_col as dbl_semantic_version) as domain_col -- cause ORA-900/ORA-600 only in PL/SQL
                 from strings
            )
         select *
           from base
   ) loop
      dbms_output.put_line(r.string_col);
   end loop;
end;
/

Here's the output produced in an OCI instance via Database Actions (same with any client on the FREE editions):

BANNER                                                                  BANNER_FULL                                                                                  BANNER_LEGACY                                                           CON_ID 
----------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ----------------------------------------------------------------------- ------ 
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.11 Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production      0 


Elapsed: 00:00:00.005
1 rows selected.


Domain DBL_SEMANTIC_VERSION dropped.

Elapsed: 00:00:00.007


Domain DBL_SEMANTIC_VERSION created.

Elapsed: 00:00:00.007


STRING_COL DOMAIN_COL 
---------- ---------- 
1.9.0      1.9.0      
1.10.0     1.10.0     
1.11.0     1.11.0     


Elapsed: 00:00:00.007
3 rows selected.


ORA-00900: invalid SQL statement
ORA-00600: internal error code, arguments: [qctVCO : bfc], [60], [0], [1], [873], [1], [2], [1279], [], [], [], []

https://docs.oracle.com/error-help/db/ora-00900/
Error at Line: 37 Column: 0

This is a simplified version of a real case where I want to test a domain with utPLSQL. The workaround is to use dynamic SQL, since the SQL used in the for loop works outside of PL/SQL.

This post has been answered by MartinBach-Oracle on Jan 16 2025
Jump to Answer

Comments

MartinBach-Oracle Jan 8 2025

Hi Salomon,

please have a look at this blog post written by @ulrike-schwinn-oracle :

https://blogs.oracle.com/coretec/post/easy-sql-statement-tracking-in23c

I hope this answers your question, if not, please shout!

- Martin

Solomon Yakobson Jan 8 2025

@martinbach-oracle - No, it doesn't answer my question. Article you pointed to shows uses:

SQL> alter system set sql_history_enabled=true scope=both;

And in my post I said “Works fine when enabled on system level”. My question was about

SQL> ALTER SESSION SET SQL_HISTORY_ENABLED = TRUE;

where I showed SQL history was NOT captured even though it should be based on SQL_HISTORY_ENABLED:

Modifiable **ALTER SESSION**, ALTER SYSTEM

SY.

MartinBach-Oracle Jan 8 2025

As per the article I shared the situation is as follows at the moment

  • You must enable SQL history PDB-wide (only a DBA can do that) so there's a certain level of control over the feature
  • Your session has access to the SQL history
  • If you don't want to record anything, set sql_history_enabled to false.

I'm currently assessing if that's intended behaviour (in which case the documentation should be amended) or a feature not working as it should (in which case it needs fixing). The parameter is indeed session-modifyable, but not in the sense you expected.

We'll keep you posted.

- Martin

Solomon Yakobson Jan 8 2025

Do you mean it must be enabled on system level and not on session level and all session can do is disabe it for the session?

SY.

MartinBach-Oracle Jan 14 2025

Yes,

that's correct as of Oracle Database Free 23.6.

- Martin

Solomon Yakobson Jan 14 2025

Thanks Martin, I hope this will be added to 23AI docs soon.

SY.

1 - 6

Post Details

Added on Dec 21 2024
6 comments
200 views