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"

}

]

}