rem
rem  $Id: dealtr.sql,v 1.1 1998/01/08 01:39:57 yxia Exp $
rem
rem  Copyright (c) Oracle Corporation 1991.  All Rights Reserved, Worldwide.
rem 
rem  dealtr.sql
rem
rem  This script modifies the installion of DE specific tables already
rem  installed in a central location (ususally SYSTEM).            
rem
rem  This script will also point out potential problems with the contents
rem  of the DE_ATTACHED_LIBS table.  
rem

rem  +-----------------------------------------------+
rem  |  DE_ATTACHED_LIBS - PL/SQL Context Attributes |
rem  +-----------------------------------------------*/

rem  Add column used to store location of library and expand the name 
rem  column to accomodates full path names.

alter table de__attached__libs
  add    (location   varchar(30))    
  modify (libname    varchar(255));   

rem  Set the location of all existing libraries to FILESYSTEM.  Becaues of
rem  a bug in previous versions of DE, it was impossible to successfully
rem  store an attachment record for a library in the database. 

update de__attached__libs
  set   location = 'FILESYSTEM'
  where location is null;

rem  Remove any broken attempts to store attachment records for libraries
rem  in the database.  Any reference to these records will generate a DE
rem  error.  There is no significant information contained in these 
rem  malformed records so they can be safely removed.

delete from de__attached__libs
  where libname = 'CURRENT DATABASE';

rem  Workaround RDBMS bug that does not properly invalidate the 
rem  DE_ATTACHED_LIBS view when the underlying table is altered.
rem  Note that there is also a bug in ALTER VIEW view_name COMPILE such
rem  that even after issuing it, the view does not correctly reflect
rem  the new column.

drop   view de_attached_libs;

create view de_attached_libs as
  select * from de__attached__libs
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

accept uname char prompt 'Grant access to user: ';

set verify off;

grant select, insert, update, delete on de_attached_libs to &uname;

set verify on;

rem  Commit the changes...

commit;
