CREATE OR REPLACE PROCEDURE sp_fco_crea_tablas
(
    pvc_tabla varchar2
)
AS
    vvc_sql       varchar2(255);
    vin_id_cursor integer;
    vin_rows	  integer;
    vvc_tabla   varchar2(10);

BEGIN
     IF Substr(pvc_tabla,5,2) = 'VW' THEN
        vvc_tabla := Substr(pvc_tabla,1,10);
     ELSE
        vvc_tabla := Substr(pvc_tabla,1,7);
     END IF;

    /* Crear la tabla */
    vvc_sql := 'CREATE TABLE adam.' || pvc_tabla || ' AS SELECT * FROM ' || vvc_tabla;
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error (-20001, '20001-Error al crear la tabla');
    END ;

    /* Crear el sinonimo */
    vvc_sql := 'CREATE PUBLIC SYNONYM ' || pvc_tabla || ' FOR adam.' || pvc_tabla;
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error (-20001, '20001-Error al crear sinonimo publico para la tabla');
    END ;

    /* Asignar permisos */
    vvc_sql := 'GRANT SELECT, UPDATE, DELETE, INSERT ON '||pvc_tabla||' TO PUBLIC';
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error (-20001, '20001-Error al otorgar permisos a tabla');
    END;

    COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_tablas FOR ADAM.sp_fco_crea_tablas;
GRANT EXECUTE ON sp_fco_crea_tablas TO adama;
