set termout off
/* Copyright (c) 1994 by Oracle Corporation.  All Rights Reserved */
REM $Id: brwupg60.sql,v 1.2 1998/04/28 18:23:28 isimek Exp $
/*
/*

  NAME
	brwupg60.sql    
  
  DESCRIPTION
	This scripts installs the browser_doc_sql table, populates them
	with data from existing queries.

  NOTES
	This used to be called the Query Builder/Browser Upgrade System tables script.
	It was intended primarily to be an upgrade script from 1.0 
	to 2.0 tables.

  OWNER
	dtlai

REM   MODIFIED
Rem     dyee       09/11/97 -  Update version to 6.0.
Rem     dtlai      10/16/96 -  Update changes for 2.5. 
Rem     dtlai      03/31/95 -  . 
Rem     rshoup     10/11/94 -  Remove exit.
Rem     rshoup     09/19/94 -  Remove browser_all_docs_sql.
REM                            Modify browser_docs_sql.
Rem     rshoup     09/19/94 -  Add browser_docs_granted.
REM                            Add query_id to browser*_docs_sql. 
Rem     jwolf      09/19/94 -  Correct view SQL for browser*_docs_sql. 
Rem     gpancha    09/18/94 -  Added grant statements. 
Rem     gpancha    06/17/94 -  Creation 

*/

set echo off

set termout on
PROMPT
PROMPT  Installing the Documents SQL table...
PROMPT
set termout off

PROMPT
PROMPT  Drop old synonyms -- OK if they do not exist
PROMPT

drop public synonym browser_docs_sql;
drop public synonym browser_docs_granted;

PROMPT
PROMPT  Create new table					
PROMPT

create table browser_docs_sql_
	(query_id	number		not null	/* ID of query */
					references browser_docs_(query_id)
					disable,
	text_length	number,				/* Length of SQL text*/
	text		long,
	constraint browser_docs_sql_idx unique(query_id));

comment on table browser_docs_sql_ is
  'Contains SQL information for all Oracle Query Builder saved queries';
comment on column browser_docs_sql_.query_id is
  'ID of query';
comment on column browser_docs_sql_.text_length is
  'Length of SQL text stored in column TEXT';
comment on column browser_docs_sql_.text is
  'SQL text of query';


PROMPT
PROMPT  Create views
PROMPT
				 
create or replace view browser_docs_sql as
  select *
  from browser_docs_sql_;

comment on table browser_docs_sql is
  'Contains SQL for Oracle Query Builder saved queries';


create or replace view browser_docs_granted as
  select d.query_id, d.owner, d.query_name, d.comments, d.create_date,
  d.modified_date, d.modified_by, d.valid, d.version, g.grantee
  from browser_docs_ d, browser_grants_ g
  where d.query_id = g.query_id;


comment on table browser_docs_granted is
  'Contains information for all Oracle Query Builder granted queries';


PROMPT
PROMPT  Create public synonyms	
PROMPT


grant select, insert, delete on browser_docs_sql to public;

create public synonym browser_docs_sql for browser_docs_sql;

grant select on browser_docs_granted to public;

create public synonym browser_docs_granted for browser_docs_granted;


PROMPT 
PROMPT  Insert data into table	
PROMPT

					
DECLARE
  col1 browser_docs_sql_.query_id%TYPE;
  col2 browser_docs_sql_.text_length%TYPE;
  col3 browser_docs_sql_.text%TYPE;

  CURSOR insert_cursor IS 
    SELECT query_id, text_length, text
    FROM browser_docs_ a, sys.dba_views b
    WHERE a.owner=b.owner
    AND a.query_name=b.view_name;


BEGIN 
  OPEN insert_cursor;
  LOOP
    FETCH insert_cursor into col1, col2, col3;
    EXIT WHEN insert_cursor%NOTFOUND;

    INSERT into browser_docs_sql_ VALUES(col1, col2, col3);
  END LOOP;
  CLOSE insert_cursor;  
  COMMIT;
END;
/

set termout on





