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

user_3536523 0
Viewed 46

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?

jdbc plsql oracle java
1 Answers

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.

Justin_Cave_103970
Related