BEGIN
IF <condition 1> THEN
  SELECT <columns>
  INTO   <variables>
  FROM   <tables>;
ELSIF condition 2> THEN
  SELECT <other columns>
  INTO   <other variables>
  FROM   <other tables>;
END IF;

SELECT <columns>
FROM   <tables>
WHERE  <stuff>
AND    :variable_1 is not null
UNION ALL
SELECT <other_columns>
FROM   <other_tables>
WHERE  <other_stuff>
AND    :variable_2 is not null

SET LINESIZE 80

COLUMN PARENT_TABLE FORMAT A20
COLUMN OBJECT_NAME FORMAT A20
COL schema FORMAT A15
COL object_type FORMAT A15

UNDEF table_name
UNDEF owner

select distinct A.owner SCHEMA, referenced_name PARENT_TABLE, name OBJECT_NAME, type OBJECT_TYPE 
from all_dependencies A
where ( referenced_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( A.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct B.owner, table_name, synonym_name, 'SYNONYM' 
from all_synonyms B 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( B.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select C.owner, table_name, constraint_name, decode(constraint_type,'P','PRIMARY_KEY','R','FOREIGN_KEY','U','UNIQUE','C',
'CHECK','N','NULL') 
from all_constraints C 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( C.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct D.owner, table_name, index_name,'INDEX' 
from all_indexes D 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( D.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct E.owner, table_name, trigger_name,'TRIGGER' 
from all_triggers E 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( E.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct p.owner, p.table_name, q.table_name, 'TABLE' 
from all_constraints p,all_constraints q
where p.owner = q.r_owner and p.constraint_name = q.r_constraint_name 
and   ( p.table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( P.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
/


Enter value for table_name: EMP
Enter value for owner: <enter>

SCHEMA          PARENT_TABLE         OBJECT_NAME          OBJECT_TYPE
--------------- -------------------- -------------------- ---------------
SCOTT           EMP                  FK_DEPTNO            FOREIGN_KEY
SCOTT           EMP                  P                    PROCEDURE
SCOTT           EMP                  PK_EMP               INDEX
SCOTT           EMP                  PK_EMP               PRIMARY_KEY

UNDEF table_name
UNDEF owner

/

Enter value for table_name: <enter>
Enter value for owner: <enter>


SCHEMA          PARENT_TABLE         OBJECT_NAME          OBJECT_TYPE
--------------- -------------------- -------------------- ------------
CTXSYS          ALL_INDEXES          DRVDDL               PACKAGE BODY
CTXSYS          ALL_OBJECTS          DRVODM               PACKAGE BODY
CTXSYS          ALL_TRIGGERS         DRVDDL               PACKAGE BODY
CTXSYS          ANYDATA              DRIPARX              PACKAGE
CTXSYS          ANYDATA              DRIPARX              PACKAGE BODY
CTXSYS          ANYDATA              TEXTINDEXMETHODS     TYPE BODY
....