-- The PL/SQL performance improves with the use of bind 
-- variables.  Below the &v_type value uses a defined variable.
-- Upon execution of this script in SQLPLUS, it prompts for 
-- v_type.
-- When converted into a function used by a front end 
-- application, v_type can be a parameter and validation can be
-- performed in UI.

DECLARE
 v_type       VARCHAR2(10) := TRIM(UPPER('&v_type'));
 v_tab        VARCHAR2(60);
 v_count      SIMPLE_INTEGER := 0;
 v_trans_id   NUMBER(16) := 10;

BEGIN
 IF v_type IS NOT NULL THEN
    v_tab := CASE v_type
              WHEN 'ALLSALES' THEN sales_trans
              WHEN 'SHIPMENT' THEN shipped_trans
              WHEN 'RECEIVE'  THEN received_trans
             END;
 
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tab||
     ' WHERE trans_id = :v_typ ' INTO v_count 
     USING v_tran_id;

   DBMS_OUTPUT.PUT_lINE(v_type||' Transaction Count = '||v_count);
 END IF;

END;
/