Recently, I faced an interesting behavior after upgrade of Oracle database to version 19c (Product Version 220.127.116.11.0). I was developing a dynamic creation of views in my PL/SQL stored procedure. After a while I started to deal with an issue. The issue was that half of statements inside of the stored procedure were not executed. Oracle was still claiming that whole execution of the procedure has been successful.
Let’s take a look closer to first example. The example shows execution without any issues. In PL/SQL block, I am creating a view with FORCE option using execute immediate. The select in view returns 1 into column with name MY_COLUMN. Once the view is created, the block will execute statement sleep for 5 seconds. As you can see at pictures below, task is completed in 5,5 second and procedure is done successfully and the view exists.
In second example, you can see demonstrated my issue that I was dealing with. You can see modified create view statement. The change is in object name. I want to query from object with name DUAL_DUAL. I don’t have any object with such name in database. The statement is using create or replace FORCE view option. Therefore, the creation of view should finish successfully but object should have a compilation error.
As you can execution on picture below, the execution of block has been successful and view has been created. The view is in invalid state. So far nothing suspicious everything as expected. Take a look closer how fast was the execution. The picture shows that Task completed in 0.111 seconds. Wait a minute. Is it really 0.111 seconds? What happened to my 5 seconds sleep?
Let’s go into final. On the picture below, you can see the last example. I changed the code like this:
- I put execute immediate into its own block that starts with begin on line number 2
- I added exception handling. The exception handling is catching all raised errors. If error number is -24344 then I don’t want oracle to do nothing. If error number is different then mentioned number then it will throw error and block will stop with error.
As you can see on picture, the execution is successful. The execution is again on 5 seconds. The view is still in invalid state. The sleep has been executed again.
According to my opinion, Oracle Database Version 19c has a bug during creation of invalid views using FORCE option. When the view is created with invalid state then the database is raising an error -24344 Success with Compilation Error. This error is causing the PL/SQL block to end processing after execute immediate statement but the error is not propagated into final status of execution.
The issue is quite interesting because it is giving you a feedback that everything is OK but the database is not executing half of code that is supposed to be executed. This behavior is valid for creation of packages, procedures, functions as well.