NinGoo的把常用工具打包进工具箱的方法很好.
然而木匠天生缺乏分类和组织能力,一路坚持学习,持续遗忘,自身却没有留下啥积累;好比猴子搬苞谷,抱在怀里的玉米棒永远也不会增多,郁闷.
所以鄙人决定依靠GTD方法,每周总结学到的新知识,然后放进自己的便携工具箱.
好了,鉴于本人不喜欢dbms_output.put_line,索性把NinGoo的RowID工具改造成了Table Function.
通过简单的查询,可以在任何地方使用.
后面附的PL/SQL Package源代码,包含了许多PL/SQL编程优秀实践,将来找机会慢慢展开来讲.
用法:
SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));
COLUMN_VALUE
--------------------------------------------------------------------------------
rowid: AAARUtAAEAAABm2AAA
file_id: 4
block_id: 6582
data_object_id: 70957
owner: INVDB
object_name: BOOK_UPLOAD_LOG
extent_id: 0
7 rows selected.
tb_ 是toolbox的缩写.
工具用户需要的对象权限:
grant select on dba_objects to utility;
grant select on dba_extents to utility;
源代码:
注意: 如果你还没有安装捕获错误工具包u$err和(instrument)代码跟踪包debug, 可以先用备注屏蔽掉相关的代码.
CREATE OR REPLACE PACKAGE UTILITY.tb_obj
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
TYPE print_tab IS TABLE OF varchar2(200);
FUNCTION rowid_info (p_rowid in varchar2) return print_tab PIPELINED DETERMINISTIC;
PROCEDURE subprogram_name;
END tb_obj;
/
CREATE OR REPLACE PACKAGE BODY UTILITY.tb_obj
/*
| Copyright Information Here
|
| File name:
|
| Overview:
Tool box to get object information.
| Author(s):
|
| Modification History:
| Date Who What
31-Mar-2009 Charlie (Yi): Create the package.
It meets user requirements
It is maintainable
It runs fast enough to minimize user frustration
**) Pl/SQL development workflow, Four steps of preparing an application
- Validate program requirements
# ask lots of questions
# what users ask for is not always the easiest way to solve a problem
# consider other approaches, include business processes and programming algorithms
- Implement header of the program
# good name for the program, accurately represent the purpose of the program
# inputs and outputs
# overload sub-procedure ?
- Define the test cases
# Verify it works
# how will I know when I am done with this program
- Build test code
|
|
*/
IS
PROCEDURE initialize_pkg
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE( 'tb_obj','init' );
END initialize_pkg;
FUNCTION rowid_info (p_rowid in varchar2)
return print_tab PIPELINED DETERMINISTIC
/*
| Copyright Information Here
|
| File name:
|
| Overview: Display extended rowid information.
|
| Author(s): Charlie MuJiang
|
| Modification History:
| Date Who What
31-Mar-2009 Charlie (Yi): Create the function.
Privilege:
grant select on dba_objects to utility;
grant select on dba_extents to utility;
Call example:
SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));
COLUMN_VALUE
--------------------------------------------------------------------------------
rowid: AAARUtAAEAAABm2AAA
file_id: 4
block_id: 6582
data_object_id: 70957
owner: INVDB
object_name: BOOK_UPLOAD_LOG
extent_id: 0
7 rows selected.
|
*/
IS
ln_block_id number;
ln_file_id number;
ln_object_id number;
ln_extent_id number;
ls_object_name varchar2(30);
ls_owner varchar2(30);
PROCEDURE initialize
IS
BEGIN
DBMS_APPLICATION_INFO.SET_ACTION( 'rowid_info' );
END initialize;
PROCEDURE cleanup
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
END cleanup;
BEGIN
initialize;
/*
Main body of program
*/
debug.f('start by user %s', user);
select dbms_rowid.ROWID_BLOCK_NUMBER(p_rowid),
dbms_rowid.ROWID_RELATIVE_FNO(p_rowid),
dbms_rowid.ROWID_OBJECT(p_rowid)
into ln_block_id,ln_file_id,ln_object_id
from dual;
select owner,object_name
into ls_owner,ls_object_name
from dba_objects
where data_object_id=ln_object_id;
PIPE ROW(' rowid: '||p_rowid);
PIPE ROW(' file_id: '||ln_file_id);
PIPE ROW(' block_id: '||ln_block_id);
PIPE ROW('data_object_id: '||ln_object_id);
for c in (
select extent_id,
owner, segment_name,partition_name, segment_type,tablespace_name
from dba_extents
where
owner=ls_owner and
segment_name=ls_object_name and
file_id=ln_file_id and
ln_block_id between block_id and block_id + blocks - 1
)
loop
PIPE ROW(' owner: '||c.owner);
PIPE ROW(' object_name: '||c.segment_name);
PIPE ROW('partition_name: '||c.partition_name);
PIPE ROW(' segment_type: '||c.segment_type);
PIPE ROW(' tbs_name: '||c.tablespace_name);
PIPE ROW(' extent_id: '||c.extent_id);
end loop;
debug.f('process %s rows', SQL%RowCount);
cleanup;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
cleanup;
return;
WHEN OTHERS
THEN
/* Don't forget to clean up here, too! */
cleanup;
/* Standard error logging mechanism */
u$err.err;
RollBack;
raise;
END rowid_info;
PROCEDURE subprogram_name
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
PROCEDURE initialize
IS
BEGIN
DBMS_APPLICATION_INFO.SET_ACTION( 'subprogram_name' );
END initialize;
PROCEDURE cleanup
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
END cleanup;
BEGIN
initialize;
/*
Main body of program
*/
debug.f('update %s rows', SQL%RowCount);
cleanup;
EXCEPTION
WHEN OTHERS
THEN
/* Don't forget to clean up here, too! */
cleanup;
/* Standard error logging mechanism */
u$err.err;
--RollBack;
raise;
END subprogram_name;
BEGIN
initialize_pkg;
END tb_obj;
/
Posts
