nedelja, 6. junij 2021

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).









sreda, 2. junij 2021

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.