NKW_VIEWS.SQL
Criação das visões de dados.
rem ----------------------------------------------------------------------
rem -- 2006 DataPRO Developers - m@urelio
rem ----------------------------------------------------------------------
rem -- Script para criação das visões de dados
rem -- Execute como usuário NK.
rem -- RDBMS Version 11g XE express
rem ----------------------------------------------------------------------
create or replace view viw_nkw_auditab as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_AUDITAB
-- VERSION = 2.0.2
-- COMMENTS = DML Action Audit logs
-- OBJECTS = NKW_AUDITAB
--------------------------------------------------------------------------- ---------------------------------
trunc(a.timestamp) "DATASYS",
a.timestamp "TIMESTAMP",
a.audid "AUDID",
a.audsid "AUDSID",
a.object_name "OBJECT_NAME",
a.dml_action "DML_ACTION",
a.user_id "USER_ID",
a.username "USERNAME",
a.text "TEXT"
from nkw_auditab a
/
create or replace view viw_nkw_columns as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_COLUMNS
-- VERSION = 2.0.2
-- COMMENTS = NK Columns
-- OBJECTS = SYS.COM$ SYS.COL$ SYS.OBJ$
--------------------------------------------------------------------------- ---------------------------------
a.obj# "OBJECT_ID",
a.name "OBJECT_NAME",
decode(a.type#,2,'TABLE',4,'VIEW') "OBJECT_TYPE",
b.col# "COLUMN_ID",
b.name "COLUMN_NAME",
rpad(decode(b.type#,1,'VARCHAR2(' || b.length || ')',
2,'NUMBER' || decode(nvl(b.precision#,0), 0, null, '(' || b.precision#) ||
decode(nvl(b.scale,0), 0,
decode(nvl(b.precision#,0), 0, null, ')'), ',' || b.scale || ')'),
8,'LONG',12,'DATE',69,'ROWID'),15) "DATA_TYPE",
decode(sign(b.null$),-1,'',0,null,'NOT NULL') "NULLABLE",
c.comment$ "COMMENTS"
from sys.com$ c, sys.col$ b, sys.obj$ a
where (a.type# in (2,4))
and (a.owner# = pkg_nkw_sys.get_nkid)
and (a.obj# = b.obj#)
and (b.obj# = c.obj#(+))
and (b.col# = c.col#(+))
/
create or replace view viw_nkw_dnt_ccvies as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_DNT_CCVIES
-- VERSION = 2.0.2
-- COMMENTS = NK Dynamic Triggers - Check Column Values
-- OBJECTS = USER_TAB_COLUMNS NKW_DNT_CCVIES
--------------------------------------------------------------------------- ---------------------------------
a.table_name "TABLE_NAME",
b.column_name "COLUMN_NAME",
a.column_id "COLUMN_ID",
a.dnt "DNT",
a.app_message "APP_MESSAGE",
a.pkg_function "PKG_FUNCTION",
a.text "TEXT"
from user_tab_columns b, nkw_dnt_ccvies a
where (a.table_name = b.table_name)
and (a.column_id = b.column_id)
/
create or replace view viw_nkw_dnt_dvies as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_DNT_DVIES
-- VERSION = 2.0.2
-- COMMENTS = NK Dynamic Triggers - Default Values and Dynamic Assigns
-- OBJECTS = USER_TAB_COLUMNS NKW_DNT_DVIES
--------------------------------------------------------------------------- ---------------------------------
a.table_name "TABLE_NAME",
b.column_name "COLUMN_NAME",
a.column_id "COLUMN_ID",
a.dnt "DNT",
a.insert_flag "INSERT_FLAG",
a.update_flag "UPDATE_FLAG",
a.anvl "ANVL",
a.pkg_function "PKG_FUNCTION",
a.text "TEXT"
from user_tab_columns b, nkw_dnt_dvies a
where (a.table_name = b.table_name)
and (a.column_id = b.column_id)
/
create or replace view viw_nkw_dnt_logies as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_DNT_LOGIES
-- VERSION = 2.0.2
-- COMMENTS = NK Dynamic Triggers - DML Action Audit Log
-- OBJECTS = USER_TAB_COLUMNS NKW_DNT_LOGIES
--------------------------------------------------------------------------- ---------------------------------
a.table_name "TABLE_NAME",
b.column_name "COLUMN_NAME",
a.column_id "COLUMN_ID",
a.insert_flag "INSERT_FLAG",
a.update_flag "UPDATE_FLAG",
a.delete_flag "DELETE_FLAG"
from user_tab_columns b, nkw_dnt_logies a
where (a.table_name = b.table_name)
and (a.column_id = b.column_id)
/
create or replace view viw_nkw_forms as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_FORMS
-- VERSION = 2.0.2
-- COMMENTS = NK Forms Inventory
-- OBJECTS = NKW_FRM
--------------------------------------------------------------------------- ---------------------------------
a.frm_name "FRM_NAME",
a.frm_version "FRM_VERSION",
a.frm_scope "FRM_SCOPE",
a.frm_comments "FRM_COMMENTS"
from nkw_frm a
/
create or replace view viw_nkw_frmblks as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_FRMBLKS
-- VERSION = 2.0.2
-- COMMENTS = NK Form's Blocks
-- OBJECTS = NKW_FRMBLK
--------------------------------------------------------------------------- ---------------------------------
a.frm_sequence "FRM_SEQUENCE",
a.frm_name "FRM_NAME",
a.frm_block "FRM_BLOCK",
a.frm_query_source "FRM_QUERY_SOURCE",
a.frm_dml_target "FRM_DML_TARGET",
a.select_flag "SELECT_FLAG",
a.insert_flag "INSERT_FLAG",
a.update_flag "UPDATE_FLAG",
a.delete_flag "DELETE_FLAG"
from nkw_frmblk a
/
create or replace view viw_nkw_frmsrw as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_FRMSRW
-- VERSION = 2.0.2
-- COMMENTS = NK Form's Tabular Reports
-- OBJECTS = NKW_FRMSRW
--------------------------------------------------------------------------- ---------------------------------
a.frm_name "FRM_NAME",
a.srw_name "SRW_NAME",
b.srw_view "SRW_VIEW",
b.srw_file "SRW_FILE",
decode(b.srw_mode,'C','CHARACTER','G','GRAPHIC','UNKNOWN') "SRW_MODE",
b.srw_comments "SRW_COMMENTS"
from nkw_srw b, nkw_frmsrw a
where (a.srw_name = b.srw_name)
/
create or replace view viw_nkw_grants as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_GRANTS
-- VERSION = 2.0.2
-- COMMENTS = NK Grants
-- OBJECTS = SYS.OBJ$ SYS.USER$ NK.PKG_NKW_GRV
--------------------------------------------------------------------------- ---------------------------------
a.user# "USER_ID",
a.name "USERNAME",
b.obj# "OBJECT_ID",
b.name "OBJECT_NAME",
decode(b.type#, 2, 'TABLE', 4, 'VIEW', 7, 'PROCEDURE', 8, 'FUNCTION',
9, 'PACKAGE') "OBJECT_TYPE",
rpad(pkg_nkw_grv.get_privilege(a.user#, b.obj#, 12),1) "EXECUTE_FLAG",
rpad(pkg_nkw_grv.get_privilege(a.user#, b.obj#, 9),1) "SELECT_FLAG",
rpad(pkg_nkw_grv.get_privilege(a.user#, b.obj#, 6),1) "INSERT_FLAG",
rpad(pkg_nkw_grv.get_privilege(a.user#, b.obj#, 10),1) "UPDATE_FLAG",
rpad(pkg_nkw_grv.get_privilege(a.user#, b.obj#, 3),1) "DELETE_FLAG"
from sys.obj$ b, sys.user$ a
where (a.user# = 1
or a.user# > pkg_nkw_sys.get_nkid)
and (a.user# = 1
or a.type# = 1)
and (b.owner# = pkg_nkw_sys.get_nkid)
and (b.type# in (2,4,7,8,9))
/
create or replace view viw_nkw_locks as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_LOCKS
-- VERSION = 2.0.2
-- COMMENTS = DataBase Locks
-- OBJECTS = V$SESSION V$LOCKED_OBJECT SYS.OBJ$
--------------------------------------------------------------------------- ---------------------------------
a.session_id "SID",
c.serial# "SERIAL#",
c.audsid "AUDSID",
c.user# "USER#",
a.oracle_username "USERNAME",
c.program || ' - ' || c.module "MODULE",
c.osuser "OSUSER",
c.machine "MACHINE",
c.client_info "CLIENT_INFO",
b.obj# "OBJECT_ID",
b.name "OBJECT_NAME",
decode(b.type#,2,'TABLE',4,'VIEW') "OBJECT_TYPE",
decode(a.locked_mode,2,'ROW SHARE', 3,'ROW EXCLUSIVE', 4,'SHARE',
5,'SHARE ROW EXCLUSIVE', 6,'EXCLUSIVE', a.locked_mode) "LOCK_MODE",
c.status "STATUS"
from v$session c, sys.obj$ b, v$locked_object a
where (a.object_id = b.obj#)
and (b.type# <> 5)
and (a.session_id = c.sid)
/
create or replace view viw_nkw_sessions as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_SESSIONS
-- VERSION = 2.0.2
-- COMMENTS = NK Sessions
-- OBJECTS = V$SESSION
--------------------------------------------------------------------------- ---------------------------------
a.sid "SID",
a.serial# "SERIAL#",
a.audsid "AUDSID",
a.process "PROCESS",
a.user# "USER#",
a.username "USERNAME",
a.logon_time "LOGON_TIME",
a.module "MODULE",
a.program "PROGRAM",
a.saddr "SADDR",
a.paddr "PADDR",
a.osuser "OSUSER",
a.machine "MACHINE",
a.client_info "CLIENT_INFO",
a.status "STATUS"
from v$session a
where (a.type = 'USER')
/
create or replace view viw_nkw_tables as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_TABLES
-- VERSION = 2.0.2
-- COMMENTS = DataBase Tables and Views
-- OBJECTS = SYS.COM$ SYS.OBJ$
--------------------------------------------------------------------------- ---------------------------------
a.obj# "OBJECT_ID",
a.name "OBJECT_NAME",
decode(a.type#,2,'TABLE',4,'VIEW') "OBJECT_TYPE",
b.comment$ "COMMENTS"
from sys.com$ b, sys.obj$ a
where (a.obj# = b.obj#(+))
and (a.type# in (2,4))
and (a.owner# = pkg_nkw_sys.get_nkid)
and (b.col# is null)
/
create or replace view viw_nkw_users as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_USERS
-- VERSION = 2.0.2
-- COMMENTS = NK Users
-- OBJECTS = SYS.SYSTEM_PRIVILEGE_MAP SYS.SYSAUTH$ SYS.USER$
--------------------------------------------------------------------------- ---------------------------------
a.user# "USER_ID",
a.name "USERNAME",
a.type# "USER_TYPE",
a.ctime "CREATION",
b.privilege# "PRIVILEGE#",
c.name "PRIVILEGE"
from sys.system_privilege_map c, sys.sysauth$ b, sys.user$ a
where (a.user# = 1
or a.type# = 1)
and (a.user# = b.grantee#(+))
and (b.privilege# = c.privilege(+))
/
create or replace view viw_nkw_views as
select /*+ RULE */
-------------------------------------------------------------------------------------------------------------
-- NAME = VIW_NKW_VIEWS
-- VERSION = 2.0.2
-- COMMENTS = NK Views
-- OBJECTS = SYS.COM$ SYS.OBJ$ SYS.VIEW$
--------------------------------------------------------------------------- ---------------------------------
b.name "VIEW_NAME",
c.comment$ "COMMENTS",
b.stime "TIMESTAMP",
decode(b.status, 0, 'N/A', 1, 'VALID', 'INVALID') "STATUS",
a.textlength "TEXT_LENGTH",
a.text "TEXT"
from sys.com$ c, sys.obj$ b, sys.view$ a
where (a.obj# = b.obj#)
and (a.obj# = c.obj#(+))
and (c.col# is null)
and (b.owner# = pkg_nkw_sys.get_nkid)
/
|
