Compiling a function or procedure using JDBC removes all records from all_statements view

After I execute/compile a stored procedure/function, I query the all_statements view to extract the statements that were in the stored procedure/function like below:

select * from all_statements where owner = 'MY_USER' and object_name='MY_FUNCTION' and object_type='FUNCTION' order by line asc;

However, one of my applications run the following code:

    try (Connection con = DriverManager.getConnection(url,username,password); Statement statement = con.createStatement();) {
        statement.execute("ALTER FUNCTION MY_FUNCTION COMPILE");
    } catch (Exception e) {
        LOGGER.error("error while executing query: {}", query, e);
    }

Whenever this code is run, the all_statements view is cleared. Even if I execute this same function like below:

    try (Connection con = DriverManager.getConnection(url,username,password); Statement statement = con.createStatement();) {
        statement.execute("SELECT MY_FUNCTION(123) FROM DUAL");
    } catch (Exception e) {
        LOGGER.error("error while executing query: {}", query, e);
    }

There are no new entries in all_statements showing the statements that were within my MY_FUNCTION function

I need to log into SQL Developer and compile the function manually, for this to start working again.

What is the cause for this?

Any reason for this?

Answer

all_statements is populated by PL/Scope. By default, SQL Developer enables PL/Scope. By default, most other connections do not.

Within your JDBC session, you can enable PL/Scope

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

If you do so, compiling the procedure will cause all_statements to be populated.

Here is a dbfiddle that shows an example where I create a trivial function, show that neither creating nor compiling it populates all_statements, then enable PL/Scope, recompile it, and all_statements is populated.