Oracle - vyhľadanie chyby v kóde procedúry

Čo spraviť ak sa v databáze nachádza invalidný objekt a máme k dispozícií iba klienta sqlplus? Nasledujú kroky kompilácie a v prípade neúspechu debug.

SQL> select * from dba_objects where status='INVALID';

Ak select vráti objekty, je možné najskôr skúsiť kompiláciu.

Kompilácia sa nie vždy podarí, vtedy možno dohľadať aj chyby v kóde:

SQL> alter PROCEDURE AB.PROC_BATCH_V1 compile;
Warning: Procedure altered with compilation errors.


SQL> show err   (alebo select * from dba_errors)
Errors for PROCEDURE AB.PROC_BATCH_V1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: Statement ignored
5/1      PLS-00201: identifier 'DBS.REG_BATCH_V1' must be declared
SQL>


SQL> select text from dba_source where name = 'REG_BATCH_V1';

TEXT
----------------------------------------------------------------------------------
PROCEDURE      "AB.PROC_BATCH_V1"
(batchId IN VARCHAR2, batchType IN VARCHAR2, params IN VARCHAR2)
IS
begin
dbs.reg_batch_v1(batchId,batchType,params);
end;

7 rows selected.

Pre hromadné kompilácie možno použiť "exec utl_recomp.recomp_parallel(), alebo aj manuálny skript:

Select 'alter ' || object_type ||' '|| owner || '.' || object_name ||' compile;'
From dba_objects
Where status <> 'VALID'
And object_type IN ('VIEW','SYNONYM',
'PROCEDURE','FUNCTION',
'PACKAGE','TRIGGER')
union
Select 'alter package ' || owner || '.' || object_name || ' compile body;'
From dba_objects
where status <> 'VALID'
And object_type = 'PACKAGE BODY'
union
select 'alter materizlized view ' || owner || '.' || object_name || ' compile;'
From dba_objects
where status <> 'VALID'
And object_type ='UNDEFINED'
union
Select 'alter type '|| owner || '.' || object_name ||' compile body;'
From dba_objects
where status <> 'VALID'
And object_type ='TYPE BODY'
union
Select 'alter public synonym '||owner || '.' || object_name||' compile;'
From dba_objects
Where status <> 'VALID'
And owner = 'PUBLIC'
And object_type = 'SYNONYM';