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?
CodePudding user response:
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.