Oracle SQL and PL/SQL context switch as reason for unexpected ORA-06533: Subscript beyond count

 If I run this script, I get ORA-06553 :

CREATE TABLE TBL
(
  COL  NUMBER
);

declare
ln_numbers   apex_t_number := apex_t_number(1,2);
ln_numbers_count NUMBER := ln_numbers.count;
begin
for i in 1..ln_numbers_count + 1 loop
  dbms_output.put_line('i: '||i||' ln_numbers_count: '||ln_numbers_count||
  ' inserting '||case when i <= ln_numbers_count then ln_numbers(i) else 3 end);
  insert into TBL(COL) values(case when i <= ln_numbers_count then ln_numbers(i) else 3 end);
  dbms_output.put_line('insert completed');
end loop;
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
  raise;
end;

DBMS OUTPUT:

i: 1 ln_numbers_count: 2 inserting 1

insert completed

i: 2 ln_numbers_count: 2 inserting 2

insert completed

i: 3 ln_numbers_count: 2 inserting 3

ORA-06533: Subscript beyond count

It seems like Oracle tries to claculate ln_numbers(i) for i=3 but I can not figure out why? Case sentence is

case when i <= ln_numbers_count then ln_numbers(i) else 3 end

and ln_number_count is 2. It should work without error. 

If I extract case sentence outside insert it works as expected:

declare

ln_numbers   apex_t_number := apex_t_number(1,2);

ln_numbers_count NUMBER := ln_numbers.count;

ln_number NUMBER;

begin

for i in 1..ln_numbers_count + 1 loop

  ln_number := case when i <= ln_numbers_count then ln_numbers(i) else 3 end;

  dbms_output.put_line('i: '||i||' ln_numbers_count: '||ln_numbers_count||

  ' inserting '||ln_number);   

  insert into TBL(COL) values(ln_number);

  dbms_output.put_line('insert completed');

end loop;

EXCEPTION

  WHEN OTHERS THEN

  dbms_output.put_line(SQLERRM);

  raise;

end;

DBMS OUTPUT:

i: 1 ln_numbers_count: 2 inserting 1

insert completed

i: 2 ln_numbers_count: 2 inserting 2

insert completed

i: 3 ln_numbers_count: 2 inserting 3

insert completed

I posted question to Oracle PL/SQL Community and I got clear answer from Solomon Yakobson:

When you execute

ln_number := case when i <= ln_numbers_count then ln_numbers(i) else 3 end;

case statement is part of PL/SQL and short circuit evaluation is applied by PL/SQL and ln_numbers(3) is never evluated. 

However when you execute:

insert into TBL(COL) values(case when i <= ln_numbers_count then ln_numbers(i) else 3 end);

case statement is not part of PL/SQL code, it is part of SQL code (insert statement). Therefore insert transforms into

INSERT INTO TBL(COL) VALUES(CASE WHEN :B3 <= :B2 THEN :B1 ELSE 3 END)

where bind variable :B3 is assigned PL/SQL variable i value, bind variable :B2 is assigned PL/SQL variable ln_numbers_count value and :B1 is assigned PL/SQL variable ln_numbers(i) value. As you probaly see it now, case statement becomes part of SQL and third iteration is trying to bind :B1 with  ln_numbers(3) before SQL executes case statement.

Compiler ignores NOCOPY case

 I have a PL/SQL procedure withOUTparameters. I wanted the OUT parameter values to be written also in case of exception. I found out this very helpful post. Code in answer commented with "Example 4 -- OUT NOCOPY parameter value is changed if an exception was raised" should solved my problem. I checked this one:

declare
    x number := 1;
    procedure p(x out NOCOPY number)
    as
    begin
        x := 2;
        raise program_error;
    end p;
begin
    p(x);
exception
    when program_error then
        dbms_output.put_line
        (
            case
                when x is null then 'null'
                else to_char(x)
            end
        );
end;
/

Above code works as expected. DBMS output is: 2. But my real case was not working like that. The data type of actual parameter was different than the data type of the formal parameter. It was not very obvious because I used TYPE attribute in actual parameter declaration. I used:

x table.col%TYPE := 1;

It turned out  table.col%TYPE is VARCHAR2. My equivalent of above code was: 

declare

    x varchar(1) := 1;

    procedure p(x out NOCOPY number)

    as

    begin

        x := 2;

        raise program_error;

    end p;

begin

    p(x);

exception

    when program_error then

        dbms_output.put_line

        (

            case

                when x is null then 'null'

                else to_char(x)

            end

        );

end;

/

DBMS output is: 1

This is unexpected. Here we can find explanation.

The compiler ignores NOCOPY in these cases:

• The actual parameter must be implicitly converted to the data type of the formal parameter.

• ....

Above code works exactly the same as without NOCOPY word because actual parameter (VARCHAR2) is implicitly converted to the data type of the formal parameter (NUMBER).

Converting Oracle APEX Instance Between Full Development and Runtime Environments and Back

 We are using database accounts for APEX INTERNAL workspace authentication.

We decided to switch to runtime environment on simulation and production. Runtime environment works as expected. After a while we switched back to full development environment for a day. 

We were unpleasantly surprised - nobody can login into APEX administration! APEX switched INTERNAL workspace authentication to default Application Express accounts. However there is a solution: running apxchpwd.sql. After that APEX instance admin can login into INTERNAL administration. He switched INTERNAL workspace authentication from Application Express accounts back to database accounts.

 Next, DBA switch to runtime environment again. Again we have runtime environment with database accounts authentication.

Print the Contents of Ref Cursors

 I needed to debug and test a lot of PL/SQL procedures that return SYS_REFCURSORs in output parameters. I wanted to print contents of this SYS_REFCURSORs. I checked https://oracle-base.com/articles/misc/check-the-contents-of-ref-cursors. Because we have APEX installed the winner was obvious: "JSON Using APEX_JSON".  

Script:

declare 
   l_cursor1 SYS_REFCURSOR;
   l_cursor2 SYS_REFCURSOR;

   procedure returnTwoCursors (
        po_cursor1 OUT SYS_REFCURSOR,
        po_cursor2 OUT SYS_REFCURSOR
    )
    as
    begin
     open  po_cursor1 for
     SELECT level AS id,
             'Description for ' || level AS description
      FROM   dual
      CONNECT BY level <= 5;
      
      open  po_cursor2 for
      SELECT level AS id,
             'Random value for level' || level ||' is '|| dbms_random.random AS description
      FROM   dual
      CONNECT BY level <= 3;
    end;   

begin
  returnTwoCursors (l_cursor1, l_cursor2);
  
  APEX_JSON.initialize_clob_output;
  APEX_JSON.open_object;
  APEX_JSON.write('l_cursor1', l_cursor1);
  APEX_JSON.close_object;
  
  APEX_JSON.open_object;
  APEX_JSON.write('l_cursor2', l_cursor2);
  APEX_JSON.close_object;
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
end;

DBMS Output:

{

"l_cursor1":[

{

"ID":1

,"DESCRIPTION":"Description for 1"

}

,{

"ID":2

,"DESCRIPTION":"Description for 2"

}

,{

"ID":3

,"DESCRIPTION":"Description for 3"

}

,{

"ID":4

,"DESCRIPTION":"Description for 4"

}

,{

"ID":5

,"DESCRIPTION":"Description for 5"

}

]

}

{

"l_cursor2":[

{

"ID":1

,"DESCRIPTION":"Random value for level1 is 238819274"

}

,{

"ID":2

,"DESCRIPTION":"Random value for level2 is -900822882"

}

,{

"ID":3

,"DESCRIPTION":"Random value for level3 is -929089605"

}

]

}

Subquery vs join performance example

Issue

We had very slow query running from 20 to 70 minutes. Also, it affected some other database processes during execution. We replaced subquery with join and overall performance of query is much better now. Let's see performance graph created with OBI. Performance improved to 6 minutes in average. You can see minutes on Y axis.

In this article I try to restore issue on EMP, DEPT example. Please download and run emp_dept.sql script. It will insert 4004 records in DEPT table and 1300013 records in EMP table.

Non-optimized query

select e.job, e.deptno from emp e
group by e.job, e.deptno 
having avg(e.sal) > 
(
    select avg(e1.sal) from emp e1 where e1.deptno = e.deptno
); 

It takes more than 2 minutes on Oracle database version 11.2.0.4.0 to fetch first 500 of 4004 rows. 

Optimized query

with avgs as

(

select deptno, avg(sal) avg_sal from emp group by deptno

) 

select e.job, e.deptno from emp e

join avgs on avgs.deptno = e.deptno

group by e.job, e.deptno, avgs.avg_sal 

having avg(e.sal) > avgs.avg_sal;

It takes 1 second to fetch first 500 of 4004 rows!

Execution plans

Non-optimized query

Plan hash value: 2799045373

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |  1303K|    39M|  2606   (6)| 00:00:32 |

|*  1 |  FILTER             |      |       |       |            |          |

|   2 |   HASH GROUP BY     |      |  1303K|    39M|  2606   (6)| 00:00:32 |

|   3 |    TABLE ACCESS FULL| EMP  |  1303K|    39M|  2499   (2)| 00:00:30 |

|   4 |   SORT AGGREGATE    |      |     1 |    26 |            |          |

|*  5 |    TABLE ACCESS FULL| EMP  | 13031 |   330K|  2505   (2)| 00:00:31 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(AVG("E"."SAL")> (SELECT AVG("E1"."SAL") FROM 

              "BK_APEX_MANUAL_CMS"."EMP" "E1" WHERE "E1"."DEPTNO"=:B1))

   5 - filter("E1"."DEPTNO"=:B1)

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_9rx6m0mzg4ja1984ce69e" used for this statement

Note use of binded variable :B1 in the plan. Following query uses exactly the same execution plan.

select e.job, e.deptno from emp e

group by e.job, e.deptno 

having avg(e.sal) > 

(

    select avg(e1.sal) from emp e1 where e1.deptno = :b1

); 

Check hash value of the plan. It's equal to previous plan hash:

Plan hash value: 2799045373

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |  1303K|    39M|  2606   (6)| 00:00:32 |

|*  1 |  FILTER             |      |       |       |            |          |

|   2 |   HASH GROUP BY     |      |  1303K|    39M|  2606   (6)| 00:00:32 |

|   3 |    TABLE ACCESS FULL| EMP  |  1303K|    39M|  2499   (2)| 00:00:30 |

|   4 |   SORT AGGREGATE    |      |     1 |    26 |            |          |

|*  5 |    TABLE ACCESS FULL| EMP  | 13031 |   330K|  2516   (3)| 00:00:31 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(AVG("E"."SAL")> (SELECT AVG("E1"."SAL") FROM 

              "BK_APEX_MANUAL_CMS"."EMP" "E1" WHERE "E1"."DEPTNO"=TO_NUMBER(:B1)))

   5 - filter("E1"."DEPTNO"=TO_NUMBER(:B1))

Note

-----

   - dynamic sampling used for this statement (level=2)

It's obvious that execution plan doesn't know how many times subquery must be evaluated. Subquery is evaluated for each deptno (4004 times). Subquery unnesting doesn't take place here. 

Execution plan doesn't reflect real effort to process query with nested subquery. 

Optimized query

Execution plan reflects real processing: 

Plan hash value: 652522642

---------------------------------------------------------------------------------------

| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |   987M|    53G|       |   136K (93)| 00:27:20 |

|*  1 |  FILTER                |      |       |       |       |            |          |

|   2 |   HASH GROUP BY        |      |   987M|    53G|       |   136K (93)| 00:27:20 |

|*  3 |    HASH JOIN           |      |   987M|    53G|    47M| 18931  (48)| 00:03:48 |

|   4 |     VIEW               |      |  1303K|    32M|       |  2606   (6)| 00:00:32 |

|   5 |      HASH GROUP BY     |      |  1303K|    32M|       |  2606   (6)| 00:00:32 |

|   6 |       TABLE ACCESS FULL| EMP  |  1303K|    32M|       |  2499   (2)| 00:00:30 |

|   7 |     TABLE ACCESS FULL  | EMP  |  1303K|    39M|       |  2499   (2)| 00:00:30 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("AVGS"."AVG_SAL"<AVG("E"."SAL"))

   3 - access("AVGS"."DEPTNO"="E"."DEPTNO")

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL plan baseline "SQL_PLAN_1kp0nyy5tdw9r987d3348" used for this statement

  

How to check equality of two SQL select result sets in Oracle

Often you need to know if the new SQL select statement is equivalent to old one. Let’s say You rewrite select and it’s much faster, but is it equivalent to old one? You need to compare result sets of old and new select statements. You can compare result sets with different combinations of bind variables. If you don’t have any bind variables, create them by replacing constant values. If results sets are equal in all test scenarios you are good to go. Probability that new SQL select statement is equivalent to old one is greater, if you test selects with more different combinations of bind variables and if result sets are bigger.

Select for comparing old select with new select is:

WITH old_select as (select …)

,new_select as (select …)

select count(*), ‘diffs’ description from

 (

         (

                select * from old_select

                        minus

                 select * from new_select

             )

             union

             (

                    select * from new_select

                        minus

                    select * from old_select

                )

  )

  union all

  select count(*), 'new_select rows' description from new_select   

  union all

  select count(*), 'old_select rows' description from old_select   

 ;

Following result set means that result sets are equal and that there are 130 rows in each result set.

0      ‘diffs’

130    'new_select rows'

130    'old_select rows'

WebLogic server and java.lang.ClassCastException

I got this exception:
java.lang.ClassCastException: weblogic.jdbc.wrapper.Blob_oracle_sql_BLOB cannot be cast to oracle.sql.BLOB.
It's because WLS properly wraps all vendor-specific types when using a connection pool. Oracle BLOB and CLOB are obviously Oracle specific types. Programmatic solution is usage of getVendorConnection() method.
Other solution is disabling type wrapping on WLS for specific data source.

• Open WLS Administration Console and select right data source:

•  Click on tab Connection Pool and expand Advanced settings at the bottom of the page:

• Uncheck CheckBox Wrap Data Types (again at the bottom of the page) and save changes:

 We needed to restart WLS to activate change. Exception is gone!