OFSA中增加Leaf的脚本
来源：互联网 发布：linux 搜索文件命令 编辑：程序博客网 时间：2024/05/18 03:27
在OFSA系统中, 有时候需要从多个维度展示数据,而系统中设置的维度可能不够, 这就要我们自己来添加一个展示的维度,即leaf. OFSA系统目录下提供了一个脚本可以用来在OFSA系统中增加一个leaf。改脚本的内容如下，执行该脚本先是在所有的Instrument表或Ledger表中增加leaf字段， 然后在OFSA中注册该字段。 部分内容加了注释。
PROCEDURE add_leaf (p_login_name IN VARCHAR2, p_colName IN varchar2,
p_displayName IN varchar2, p_leafType IN varchar2,
p_dbfName IN varchar2)
IS
/***************************************************************************
Nested Functions and Procedures
**************************************************************************/
procedure add_column (p_login_name IN VARCHAR2, p_tabName IN VARCHAR2,
p_colName IN VARCHAR2);
procedure column_not_null (p_login_name IN VARCHAR2, p_tabName IN VARCHAR2,
p_colName IN VARCHAR2);
PROCEDURE update_leaf (p_login_name IN VARCHAR2, p_tabName IN VARCHAR2,
p_colName IN VARCHAR2);
PROCEDURE modify_view (p_login_name IN VARCHAR2, p_viewName IN VARCHAR2,
p_colName IN VARCHAR2);
PROCEDURE exec (p_login_name IN VARCHAR2,
p_command IN VARCHAR2,
p_proc_name IN VARCHAR2);
PROCEDURE big_stmt_exec (p_login_name IN VARCHAR2,
p_sqlSTMT IN DBMS_SQL.VARCHAR2S,
p_sqlLB IN NUMBER,
p_sqlUB IN NUMBER,
p_proc_name IN VARCHAR2);
PROCEDURE parse_sql_string ( p_sql_string IN VARCHAR2,
p_viewName IN VARCHAR2,
p_sqlSTMT OUT DBMS_SQL.VARCHAR2S,
p_sqlUB OUT NUMBER);
/**************************************************************************
Private variables
***************************************************************************/
v_tabName VARCHAR2(30);
v_viewName VARCHAR2(30);
v_err_num INTEGER;
/**************************************************************************
Private Exceptions
***************************************************************************/
e_parameter EXCEPTION;
/**************************************************************************
Private Cursors
***************************************************************************/
--得到所有的Instrument表和Ledger表。
cursor bothTables is
SELECT DISTINCT(A.table_name)
FROM ofsa_table_class_assignment A, user_tables U
WHERE A.table_classification_cd in (50, 200, 210, 300, 310, 351, 360, 370)
AND U.table_name = A.table_name
AND NOT EXISTS (select null from user_tab_columns UC2
where UC2.table_name = A.table_name
and UC2.column_name = UPPER(p_colName));
-- 只得到Ledger表。
cursor ledgerTables is
SELECT DISTINCT(A.table_name)
FROM ofsa_table_class_assignment A, user_tables U
WHERE A.table_classification_cd in (352 )
AND U.table_name = A.table_name
AND NOT EXISTS (select column_name from user_tab_columns UC2
where UC2.table_name = A.table_name
and UC2.column_name = UPPER(p_colName));
--所有Instrument表和Ledger表，Leaf类型所需要的视图列表。
-- This cursor gets the list of views required by Leaf Type
-- 'Both Instrument and Ledger'
cursor bothViews IS
SELECT DISTINCT(A.table_name)
FROM ofsa_table_class_assignment A, user_views U
WHERE A.table_classification_cd in (50, 200, 210, 300, 310, 351, 360, 370)
AND U.view_name = A.table_name
AND NOT EXISTS (select null from user_tab_columns UC2
where UC2.table_name = A.table_name
and UC2.column_name = UPPER(p_colName));
/****************************************************************************
Procedure: MODIFY_VIEW
Purpose: This procedure is called by ADD_LEAF to add the leaf column
as a column in the SELECT list of a view.
Any views with an "@" will not be modified
as they are views which access objects across a dblink.
Arguments: p_login_name -- the User Name performing the DDL
v_viewName -- the table being altered
p_colName -- the column being added
****************************************************************************/
procedure modify_view (p_login_name IN VARCHAR2, p_viewName IN VARCHAR2,
p_colName IN VARCHAR2) IS
-- get the view text
cursor view_text is
SELECT text
FROM user_views
WHERE view_name = p_viewName;
v_hold_long_text VARCHAR2(32767); -- holds converted view_text from
-- LONG datatype
v_begin_counter PLS_INTEGER;
v_letters_to_get PLS_INTEGER;
v_text_length PLS_INTEGER; -- length of the view_text
v_dblink_sign PLS_INTEGER;
v_asterisk_sign PLS_INTEGER;
--+ Storage et al. for our dynamic SQL statement
v_sqlSTMT DBMS_SQL.VARCHAR2S; --+actual SQL statement original definition 256
v_sqlUB number := 1; --+stmt table's upper bound
v_sqlLB CONSTANT number := 1; --+stmt table's lower bound
e_dblink EXCEPTION;
BEGIN
--dbms_output.put_line('Begin modify view');
-- parse the LONG view text into a varchar2 data type
FOR the_view_text IN view_text LOOP
v_hold_long_text :=the_view_text.text;
END LOOP;
-- Remove carriage returns
v_hold_long_text := replace(v_hold_long_Text,chr(10),' ');
-- Remove square characters
v_hold_long_text := replace(v_hold_long_Text,chr(13),' ');
-- Replace tabs with spaces
v_hold_long_text := replace(v_hold_long_Text,chr(9),' ');
-- search the view text for the @ sign
v_dblink_sign :=0;
v_dblink_sign := instr(v_hold_long_text,'@');
--dbms_output.put_line('db_link is '||v_dblink_sign);
-- Only process views with no @ sign - db link views not supported
-- for this utility
IF v_dblink_sign = 0 THEN
--dbms_output.put_line('no dblink');
-- search the view for an asterisk ('*') - for views without a *,
-- we need to explicitely add in the new leaf column in the select
-- list
v_asterisk_sign :=0;
v_asterisk_sign := instr(v_hold_long_text,'*');
IF v_asterisk_sign = 0 THEN
-- Convert to uppercase
v_hold_long_text := UPPER(v_hold_long_text);
-- Replace "SELECT" with "SELECT leaf_col,"
v_hold_long_text := replace(v_hold_long_text,'SELECT','SELECT '||p_colName||',');
END IF;
-- add a new line character after every comma
-- this ensures that our SQL statement won't be put into
-- a single line (which overflows the line buffer)
v_hold_long_text := replace(v_hold_long_text,',',','||chr(10));
v_hold_long_text := 'CREATE OR REPLACE VIEW '||p_viewName||' AS '||v_hold_long_text;
-- send the SQL statement to the parse function to split each new line
-- into an array element
parse_sql_string(v_hold_long_text,p_viewName, v_sqlSTMT, v_sqlUB);
-- send the SQL statement to be executed
big_stmt_exec(p_login_name,v_sqlSTMT,v_sqlLB, v_sqlUB,'ADD_LEAF');
ELSE
RAISE e_dblink;
END IF;
EXCEPTION
WHEN e_dblink THEN
dbms_output.put_line('View: '||p_viewName);
dbms_output.put_line('References a database link');
dbms_output.put_line('Update this view manually to include the new leaf column');
WHEN OTHERS THEN NULL;
END modify_view;
/****************************************************************************
Procedure: UPDATE_LEAF
Purpose: This procedure is called by ADD_LEAF to set the
value for the new leaf Column to '0'
Arguments: p_login_name -- the User Name performing the update
v_tabName -- the table being updated
p_colName -- the column being updated
****************************************************************************/
PROCEDURE update_leaf (p_login_name IN VARCHAR2, p_tabName IN VARCHAR2,
p_colName IN VARCHAR2) IS
c_tbl_prop_cd_INSTR_REQ_COL CONSTANT NUMBER(5) := 10;
c_tbl_prop_cd_TP_OPTION_COST CONSTANT NUMBER(5) := 100;
c_tbl_class_cd_LEDGER_STAT CONSTANT NUMBER(5) := 50;
c_Target_Rows_Per_Partition CONSTANT NUMBER(6) := 100000;
v_row_count PLS_INTEGER;
v_tot_partitions PLS_INTEGER;
v_org_partitions PLS_INTEGER;
v_orgs_per_partition PLS_INTEGER;
v_org_partition_num PLS_INTEGER;
v_org_upper NUMBER(14);
v_org_lower NUMBER(14);
v_text VARCHAR2(400);
c01 INTEGER;
v_rc INTEGER;
FUNCTION exec_select (p_command IN VARCHAR2) RETURN NUMBER IS
-- Dynamically executes a SELECT statement and returns a
-- single numeric value from returned by that statement
-- (useful for dynamic SELECT COUNT(*) queries).
c001 INTEGER;
v_result INTEGER := 0;
v_return NUMBER;
BEGIN
c001 := dbms_sql.open_cursor;
dbms_sql.parse(c001, p_command , dbms_sql.native );
dbms_sql.define_column(c001, 1, v_return);
v_result := dbms_sql.execute_and_fetch (c001);
dbms_sql.column_value(c001, 1, v_return);
dbms_sql.close_cursor(c001);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END exec_select;
BEGIN -- update_leaf
v_row_count := exec_select('SELECT COUNT(*) FROM ' || p_tabName);
IF v_row_count = 0 THEN
--表中的记录为空时则直接跳过， 不执行将字段值更新为0的过程。
RETURN;
END IF;
v_tot_partitions := ROUND(v_row_count / c_Target_Rows_Per_Partition);
IF (v_tot_partitions <= 1) THEN
v_text := 'UPDATE ' || p_tabName || ' SET '|| p_colName || ' = 0';
exec(p_login_name, v_text, 'UPDATE_LEAF');
COMMIT;
ELSE
-- Partition only by ORG_UNIT_ID.
v_org_partitions := v_tot_partitions;
SELECT ROUND(count(*) / v_org_partitions)
INTO v_orgs_per_partition
FROM ofsa_detail_org_unit;
v_org_upper := -99999999999999;
FOR v_org_partition_num IN 1..v_org_partitions+1 LOOP
v_org_lower := v_org_upper;
IF v_org_partition_num = v_org_partitions+1 THEN
v_org_upper := 99999999999999;
ELSE
v_text :=
'SELECT org.leaf_node FROM ' ||
'(SELECT leaf_node, rownum rn' ||
' FROM ofsa_detail_org_unit' ||
' ORDER BY leaf_node) org ' ||
'WHERE org.rn = ' ||
TO_CHAR(v_org_partition_num * v_orgs_per_partition);
v_org_upper := exec_select(v_text);
IF v_org_upper IS NULL THEN
v_org_upper := 99999999999999;
END IF;
END IF;
-- 把表的新增leaf字段更新为0以org_unit_id的值为范围。
v_text := 'UPDATE ' || p_tabName || ' SET '|| p_colName || ' = 0' ||
' WHERE org_unit_id > '|| TO_CHAR(v_org_lower) ||
' AND org_unit_id <= ' || TO_CHAR(v_org_upper) ;
exec(p_login_name, v_text, 'UPDATE_LEAF');
COMMIT;
IF v_org_upper = 99999999999999 THEN
EXIT;
END IF;
END LOOP;
END IF;
END update_leaf;
/****************************************************************************
Procedure: ADD_COLUMN
Purpose: This procedure is called by ADD_LEAF to perform the DDL
for adding a column to a table
Arguments: p_login_name -- the User Name performing the DDL
v_tabName -- the table being altered
p_colName -- the column being added
****************************************************************************/
procedure add_column (p_login_name IN VARCHAR2, p_tabName IN VARCHAR2,
p_colName IN VARCHAR2) IS
BEGIN
exec(p_login_name, 'ALTER TABLE '||p_tabName||' add '||p_colName||
' NUMBER(14)', 'ADD_LEAF');
END add_column;
/****************************************************************************
Procedure: COLUMN_NOT_NULL
Purpose: This procedure is called by ADD_LEAF to perform the DDL
for modifying the new Leaf column to NOT NULL
Arguments: p_login_name -- the User Name performing the DDL
v_tabName -- the table being altered
p_colName -- the column being modified to NOT NULL
****************************************************************************/
procedure column_not_null (p_login_name IN VARCHAR2, p_tabName IN VARCHAR2,
p_colName IN VARCHAR2) IS
BEGIN
exec(p_login_name, 'ALTER TABLE '||p_tabName||' modify '||p_colName||
' not null', 'COLUMN_NOT_NULL');
END column_not_null;
/****************************************************************************/
PROCEDURE exec (p_login_name IN VARCHAR2,
p_command IN VARCHAR2,
p_proc_name IN VARCHAR2) IS
-- ==========================================================================
-- Executes the SQL statement passed to it, including DDL, using the
-- DBMS_SQL package for dynamic SQL, and logs the operation in the OFSA_STP
-- table. An error is raised if the SQL statement fails.
-- This procedure should only be used for executing DDL or other SQL that
-- must be built "on-the-fly". All other SQL statements should be executed
-- directly or in a declared cursor.
-- ==========================================================================
v_cursor INTEGER ;
v_result INTEGER := 0;
v_sqlerr NUMBER;
BEGIN
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, p_command , dbms_sql.native );
v_result := dbms_sql.execute (v_cursor);
dbms_sql.close_cursor(v_cursor);
BEGIN
INSERT into ofsa_stp (id, username, timestamp,
taskname, description, status)
VALUES (ofsa_stp_seq.nextval, p_login_name, sysdate,
p_proc_name, substr(p_command,1,198), v_result);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(
'Error in posting SQL operation to OFSA_STP. ' ||
' USERNAME: ' || p_login_name ||
'TASKNAME: ' || p_proc_name ||
' SQL: ' || substr(p_command, 1, 198) );
END;
EXCEPTION
WHEN OTHERS THEN
v_sqlerr := sqlcode;
dbms_sql.close_cursor(v_cursor);
BEGIN
INSERT into ofsa_stp (id, username, timestamp,
taskname, description, status)
VALUES (ofsa_stp_seq.nextval, p_login_name, sysdate,
p_proc_name, substr(p_command,1,198), v_sqlerr);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(
'Error in posting SQL operation to OFSA_STP. ' ||
'TASKNAME: ' || p_proc_name ||
' ERROR: ' || v_sqlerr ||
' SQL: ' || substr(p_command, 1, 140));
END;
RAISE;
END exec;
/****************************************************************************/
PROCEDURE big_stmt_exec (p_login_name IN VARCHAR2,
p_sqlSTMT IN DBMS_SQL.VARCHAR2S,
p_sqlLB IN NUMBER,
p_sqlUB IN NUMBER,
p_proc_name IN VARCHAR2) IS
-- ==========================================================================
-- Used for large SQL statements with linesize >2500
-- Executes the big SQL statement passed to it, including DDL, using the
-- DBMS_SQL package for dynamic SQL, and logs the operation in the OFSA_STP
-- table. An error is raised if the SQL statement fails.
-- This procedure should only be used for executing DDL or other SQL that
-- must be built "on-the-fly". All other SQL statements should be executed
-- directly or in a declared cursor.
-- ==========================================================================
v_cursor INTEGER ;
v_result INTEGER := 0;
v_sqlerr NUMBER;
BEGIN
--+ Open a cursor handle and parse the statement.
v_cursor := dbms_sql.open_cursor;
--dbms_output.put_line('sqlstmt1 '||p_sqlSTMT(1));
-- dbms_output.put_line('sqlstmt_last '||p_sqlSTMT(sqlUB-1));
--dbms_output.put_line('UB = '||p_sqlUB);
dbms_sql.parse(v_cursor,p_sqlSTMT,p_sqlLB,p_sqlUB-1,TRUE, --+not sure if this is needed but assume no harm done
dbms_sql.native);
--dbms_output.put_line('after the parse');
-- dbms_sql.parse(v_cursor, v_hold_long_text , dbms_sql.native );
v_result := dbms_sql.execute (v_cursor);
dbms_sql.close_cursor(v_cursor);
BEGIN
INSERT into ofsa_stp (id, username, timestamp,
taskname, description, status)
VALUES (ofsa_stp_seq.nextval, p_login_name, sysdate,
p_proc_name, substr(p_sqlSTMT(p_sqlLB),1,198), v_result);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(
'Error in posting SQL operation to OFSA_STP. ' ||
' USERNAME: ' || p_login_name ||
'TASKNAME: ' || p_proc_name ||
' SQL: ' || substr(p_sqlSTMT(p_sqlLB), 198) );
END;
EXCEPTION
WHEN OTHERS THEN
v_sqlerr := sqlcode;
dbms_sql.close_cursor(v_cursor);
BEGIN
INSERT into ofsa_stp (id, username, timestamp,
taskname, description, status)
VALUES (ofsa_stp_seq.nextval, p_login_name, sysdate,
p_proc_name, substr(p_sqlSTMT(p_sqlLB),1,198), v_sqlerr);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(
'Error in posting SQL operation to OFSA_STP. ' ||
'TASKNAME: ' || p_proc_name ||
' ERROR: ' || v_sqlerr ||
' SQL: ' || substr(p_sqlSTMT(p_sqlLB), 1, 140));
END;
RAISE;
END big_stmt_exec;
/****************************************************************************
Procedure: PARSE_SQL_STRING
Purpose: This procedure is called by modify_view to delimit
large sql statements into separate items within a
PL/SQL table. This allows large statements to be
dynamically executed, where the linesize would
otherwise be >2500 bytes.
Arguments: sql_string IN VARCHAR2
****************************************************************************/
procedure parse_sql_string ( p_sql_string IN VARCHAR2, p_viewName IN VARCHAR2,
p_sqlSTMT OUT DBMS_SQL.VARCHAR2S,
p_sqlUB OUT NUMBER) IS
v_sql_text varchar2(32700);
v_new_line number;
c_line_size CONSTANT number := 250; --+stmt table's column size
e_out_of_bounds EXCEPTION;
begin
p_sqlUB :=1; -- initialize the Upper bound
v_sql_text := p_sql_string;
--dbms_output.put_line('in the parse');
while v_sql_text is not null loop
v_new_line := instr(v_sql_text, chr(10));
--dbms_output.put_line('v_new_line ='||v_new_line);
if v_new_line = 0 then
v_new_line := c_line_size; --+no more delimiters
p_sqlSTMT(p_sqlUB) := substr(v_sql_text, 1, v_new_line-1);
elsif v_new_line > c_line_size then
v_new_line := c_line_size; --+just in case
p_sqlSTMT(p_sqlUB) := substr(v_sql_text, 1, v_new_line);
end if;
--+ Added extra line for debugging
p_sqlSTMT(p_sqlUB) := substr(v_sql_text, 1, v_new_line-1);
p_sqlUB := p_sqlUB + 1;
IF p_sqlUB > 256 THEN
RAISE e_out_of_bounds;
END IF;
v_sql_text := substr(v_sql_text, v_new_line+1);
end loop;
--dbms_output.put_line('outside the loop');
EXCEPTION
WHEN e_out_of_bounds THEN
dbms_output.put_line('View: '||p_viewName);
dbms_output.put_line('Exceeds limit of 256 explicit columns in SELECT list');
dbms_output.put_line('Use * in the SELECT list to reduce number of explicit columns');
dbms_output.put_line('in the view SELECT list');
end parse_sql_string;
BEGIN
--判断字段名长度不能大于30,显示名长度不能大于40,并且leaf类型只能为B或L.
IF LENGTH(p_colName) > 30 or LENGTH(p_displayName) > 40
or UPPER(p_leafType) NOT IN ('L','B') THEN
RAISE e_parameter;
END IF;
--如果为B,则执行该部分.
IF UPPER(p_leafType) = 'B' THEN
--检查所有的Instrument表。
OPEN bothTables;
LOOP
FETCH bothTables into v_tabName;
EXIT when bothTables%NOTFOUND;
--循环在表中增加字段。
add_column (p_login_name, v_tabName, p_colName);
--顶用FDM ADMIN的过程注册一个字段， 并且OFSA数据类型为Leaf
--原代码已经被封装
ofsa_ins_utils.ins_tab_column_record(v_tabName, UPPER(p_colName), 10, 1,
p_displayName, substr(p_dbfName,1,10),
p_displayName, v_err_num);
-- 如果表中已有数据把新增加的字段的值更新为默认的值0
update_leaf(p_login_name, v_tabName, p_colName);
--把表的字段设置为非空。
column_not_null(p_login_name, v_tabName, p_colName);
END LOOP;
CLOSE bothTables;
--查找所有相关的视图。
OPEN bothViews;
LOOP
FETCH bothViews into v_viewName;
EXIT when bothViews%NOTFOUND;
--更新所有的视图
modify_view (p_login_name, v_viewName, p_colName);
--调用FDM ADMIN存储过程来注册字段。 并把OFSA数据类型设置为Leaf.
ofsa_ins_utils.ins_tab_column_record(v_viewName, UPPER(p_colName), 10, 1,
p_displayName, substr(p_dbfName,1,10),
p_displayName, v_err_num);
END LOOP;
CLOSE bothViews;
--如果leaf类型为L，则只更新leadger_stat表，
ELSIF UPPER(p_leafType) = 'L' THEN
OPEN ledgerTables;
LOOP
FETCH ledgerTables into v_tabName;
EXIT when ledgerTables%NOTFOUND;
add_column (p_login_name, v_tabName, p_colName);
--调用FDM ADMIN存储过程注册字段，并将OFSA数据类型设置为Leaf.
ofsa_ins_utils.ins_tab_column_record(v_tabName, UPPER(p_colName), 10, 1,
p_displayName, substr(p_dbfName,1,10),
p_displayName, v_err_num);
--如果原表中有数据，则将新增加的leaf设置为0
update_leaf(p_login_name, v_tabName, p_colName);
-- 把字段类型该为非空字段。
column_not_null(p_login_name, v_tabName, p_colName);
END LOOP;
CLOSE ledgerTables;
END IF;
EXCEPTION
when e_parameter then
dbms_output.put_line('ERROR: Invalid Parameter');
dbms_output.put_line('Leaf Column Name must be <= 30 characters');
dbms_output.put_line('Display Name must be <= 40 characters');
dbms_output.put_line('Leaf Type must be in L or B');
END add_leaf;
- OFSA中增加Leaf的脚本
- 如何在OFSA中注册leaf
- OFSA中树的导入导出
- OFSA中Profit数据处理中错误的解决
- OFSA中Instrument表的创建及注册
- OFSA系统中ORA-100问题的处理
- OFSA中FDM、PA中显示中文
- 增加网页访问量的脚本
- 增加系统安全的shell脚本
- sqlite中fts的数据结构说明:segment leaf nodes
- powerdesigner如何给生成的mysql 4.0 脚本数据库中增加注释
- Oracle EBS R12 - 一段Oracle EBS中给指定用户增加指定职责的PLSQL脚本
- Oracle EBS R12 - 一段Oracle EBS中给指定用户增加指定职责的PLSQL脚本
- velocity:在eclipse和ultraedit中增加对vm脚本语法的高亮显示支持
- powerdesigner16.5 如何给生成的mysql脚本数据库中增加注释
- PDF10 增加书签功能的JS脚本
- Nginx增加PHP脚本的执行时间
- openvpn增加记录log的脚本
- [转]Siege：压力模拟/测试工具
- eXtremeTable导出数据为XLS和PDF时的中文问题解决办法
- 爱情名言
- 抓不住的按钮实现
- begin with the end in mind
- OFSA中增加Leaf的脚本
- 隔夜茶的作用
- 西红柿-生吃还是熟吃
- ListView扩展
- SFComicCMS开发笔记
- 第一天
- 生日，就这样过去了
- JSP程序员成长全计划
- Java和JSP编程应注意的常见问题
