Automatic recompile of invalid PL/SQL Code

[Dave Smylie]

Hi

Should ruby-plsql be recompiling database code that has been left in
an invalid state? (Eg a related table changes etc).

Ordinarily, oracle should recompile these as they are accessed or
executed so the fact they were temporarily invalidated is invisible.

I'm finding that within ruby-plsql though, when I try and access an
invalid package, I get an error similar to the below:

ActiveRecord::StatementInvalid: OCIError: ORA-06550: line 2, column
21:
PLS-00302: component 'CLIENT_20' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored: custom create method
from /home/dgs/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract_adapter.rb:202:in `rescue
in log'
from /home/dgs/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract_adapter.rb:194:in `log'
from /home/dgs/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-
oracle_enhanced-adapter-1.3.2/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:1078:in `log'
from /home/dgs/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-
oracle_enhanced-adapter-1.3.2/lib/active_record/connection_adapters/
oracle_enhanced_procedures.rb:156:in `create_using_custom_method'


Once I go and recompile the package, the error goes away.
Is this normal behaviour?

Cheers
Dave Smylie

[Raimonds Simanovskis]

Actually when accessing invalid package with ruby-plsql then you should get ArgumentError "is not in valid status" error, see

https://github.com/rsim/ruby-plsql/blob/master/lib/plsql/schema.rb#L207-210

The reason for that is that if database object is in INVALID status then Oracle data dictionary views might not contain correct procedure signature information (number, names and types of arguments and returned results) and therefore I probably cannot construct correct method call.

But in your case it seems that ruby-plsql did not raised this error and procedure was called and Oracle error was raised - could you investigate what was database object status at the time of calling and why ruby-plsql did not raise "is not in valid status" error?

But anyway the best approach is after each database objects deployment check for all invalid database objects and force recompilation of them (with ALTER ... COMPILE or with DBMS_DDL.ALTER_COMPILE(...)). Then you can check if all database objects recompile correctly during deployment as hoping that they will recompile correctly during first usage is more fragile approach.

Raimonds

[Dave Smylie]

The issue was (I think) that the package I was calling was in a VALID
state, but it in turn was calling a function in another package that
was INVALID. Though, I would have thought at this point that as this
second call would be from inside the database, that Oracle would do
it's automatic recompilation thing....

Yeah, that's been my current approach - just recompile everything
(usually on application startup). The downfall to this is that if
another developer is messing round in the database, they can
inadvertently cause something to become invalid and not recompile it.

Thanks
Dave Smylie

On Aug 23, 7:13 pm, Raimonds Simanovskis

<raimonds.simanovs...@gmail.com> wrote:
> Actually when accessing invalid package with ruby-plsql then you should get

> ArgumentError "is not in valid status" error, seehttps://github.com/rsim/ruby-plsql/blob/master/lib/plsql/schema.rb#L2...