Rem
Rem $Header: admin_create_synonyms.sql 01-jul-2003.14:51:49 edemembe Exp $
Rem
Rem admin_create_synonyms.sql
Rem
Rem Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      admin_create_synonyms.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    edemembe    07/01/03 - Adding partition support
Rem    rpinnama    06/03/02 - rpinnama_reorg_rep_scripts_3
Rem    rpinnama    05/17/02 - Created
Rem

--
--  Create dbms_shared_pool synonym, if it doesn't exist
--

DECLARE
    l_obj_name VARCHAR2(30);
    l_obj_exists NUMBER;
BEGIN
    l_obj_exists := 0;
 
    -- Check if the synonym already exists. 
    BEGIN 
        l_obj_name := '';
        SELECT object_name INTO l_obj_name
          FROM all_objects 
         WHERE object_name='DBMS_SHARED_POOL'
           AND object_type='SYNONYM';

	l_obj_exists := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
	    l_obj_exists := 0;
        WHEN OTHERS THEN
	    RAISE;
    END;

    -- If synonym doesn't exists, create one.
    IF (l_obj_exists = 0) THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM dbms_shared_pool ' ||
                              'FOR sys.dbms_shared_pool';
        EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('CreateRep: Failed to create ' ||
	                           ' dbms_shared_pool synonym');
            RAISE;
        END;
    END IF;

    l_obj_exists := 0;
 
    -- Check if the synonym already exists. 
    BEGIN 
        l_obj_name := '';
        SELECT object_name INTO l_obj_name
          FROM all_objects 
         WHERE object_name='DBMS_REDEFINITION'
           AND object_type='SYNONYM';

	l_obj_exists := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
	    l_obj_exists := 0;
        WHEN OTHERS THEN
	    RAISE;
    END;

    -- If synonym doesn't exists, create one.
    IF (l_obj_exists = 0) THEN
        BEGIN
            EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM dbms_redefinition ' ||
                              'FOR sys.dbms_redefinition';
        EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('CreateRep: Failed to create ' ||
	                           ' dbms_redefinition synonym');
            RAISE;
        END;
    END IF;

END;
/