How to find used tables from wrapped packages

We may encounter some situation ,where client code is completely wrapped and situation may come to know table names used for that package without unwrapping code.


Below query will help us to know some information for it.

SQL> SELECT * FROM USER_DEPENDENCIES 
                        WHERE TYPE ='PACKAGE BODY'                 
                        AND REFERENCED_OWNER = '&OWNER_NAME' 
                        AND NAME = '&NAME_OF_THE_OBJECT';



as we all know, In Normal unwrapped code , below query will help us to know anything.

SQL> select * from user_source 
               where TEXT LIKE '%&Enter_Any_String_Which_You_Want_to_Search_in_Complete_Schema%'

and Same way you may want to find DB Link where its used in your Database objects.

We can use following query to find DB link used objects:-



SQL> SELECT * FROM USER_DEPENDENCIES 
      WHERE REFERENCED_LINK_NAME='db link name';

How to find Oracle_Home

1) How to find Oracle Home ?

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Connected as NZDCU1
 
SQL> select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;

SYS_CONTEXT('USERENV','ORACLE_
--------------------------------------------------------------------------------
/scratch/oracle/app/ora12c/product/12.1.0/dbhome_1

Above Query will work only from 12c Onwards

 and  10g and 11g , you can use below one( Login as SYSDBA)

SQL> var Ora_Home Varchar2(1000);
SQL> exec dbms_system.get_env('ORACLE_HOME', :Ora_Home) ;

PL/SQL procedure successfully completed
Ora_Home
---------
C:\app\Venky\product\11.2.0\dbhome_1

SQL> Print ORA_HOME;
Ora_Home
---------
C:\app\Venky\product\11.2.0\dbhome_1



In Unix , after setting Variable, we can use below  one (By the way to set variable we need Oracle_Home :)  so get oracle_home from developer tool).

$ echo $ORACLE_HOME

Table,View Or Sequence reference 'SEQUENCE.NEXTVAL' not allowed in this context

You would get this error when you use "Sequence in Plsql block with assignment ",

But actually, assigning sequence to particular variable can be only from 11g version on wards.

So check database version before using Sequence with assignment operator.

SELECT * FROM V$VERSION;--- To know DB version

10g: Using select Statement for Sequence


DECLARE
  V_FILE_NAME VARCHAR2(200);
BEGIN
  SELECT TO_CHAR(SYSDATE, 'DDMMYYYY') || '_' ||
         LPAD(SEQ_CBOP_FILE.NEXTVAL, 4, 0) || '.TXT'
    INTO V_FILE_NAME
    FROM DUAL;
END;

11g: Using assignment operator for sequence. and its new feature in 11g for sequence.

DECLARE
  V_FILE_NAME VARCHAR2(200);
BEGIN
  V_FILE_NAME := TO_CHAR(SYSDATE, 'DDMMYYYY') || '_' ||
                 LPAD(SEQ_CBOP_FILE.NEXTVAL, 4, 0) || '.TXT';

END;