Helpful queries for Oracle Apps

How to compile the package,package body ,procedures

alter package <package name > compile;
alter package <package name > compile body;
alter procedure <procedure name > compile;

If it is not successfully compiled, we can look at the errors from the below sql
Login to SQL*Plus.

SELECT text
FROM dba_errors
WHERE name = packagename;

The output will look something like this.
TEXT
———————————————————————
PLS-00302: component ‘XYZ’ must be declared
PL/SQL: Statement ignored

How to find the Version Number

Version number helps establish what version of a file is being executed on the system. If the patch failure is with a package or package body, establish the version of the file that the patch installs in the patch/sql directory. The version information is viewed in the file Header information. Compare that version to the version that is compiled
into the database.

The database version can be found by logging in to SQL*PLUS and selecting:

SELECT text
FROM dba_source
WHERE name = package name
AND text like ‘%Header%’;

To establish the version of other files such as forms, reports, sql use the strings command at the Unix command line:

strings -a filename | grep header -i

To find versions of Unix C code for a specific module, such as pyxy.lpc:

Strings PYUGEN | grep ‘$Header: pyxy.lpc’

To find versions of Unix C code for all the modules with in a C module

such as PYUGEN:
Strings PYUGEN | grep ‘$Header’

These can also be done with adident command

How to find the status of invalids in database

To verify objects that are invalid, login to SQL*PLUS as apps:

SELECT object_name, object_type, status
FROM user_objects
WHERE status = ‘INVALID’;

How to find the trigger body

To see what a failing trigger is trying to do, login to SQL*PLUS:

Set long 20000;
SELECT table_name, trigger_name, trigger_body
FROM dba_triggers
WHERE table_name = upper (‘table name’);
or
WHERE trigger_name = upper (‘trigger name’);

How to find the information about constraints

For a check constraint error such as ORA_02290: check constraint
(HR.PAY_BCH_BATCH_STATUS_CHK) violated

Login to SQL*PLUS:

Set long 20000;
Select table_name, search_condition
From dba_constraints
Where constraint_name = upper (‘pay_bch_batch_status_chk’);

Leave a Reply