dbms_space_admin.drop_empty_segments

create or replace
package dbms_space_admin is
————
— OVERVIEW

— This package provides tablespace/segment space administration
— not available through the standard sql.

— SECURITY

— run with SYS privileges. thus any user who has privilege to execute the
— the package gets to manipulate the bitmapes.

— CONSTANTS to be used as OPTIONS for various procedures
— refer comments with procedure(s) for more detail

SEGMENT_VERIFY_EXTENTS constant positive := 1;
— used to verify that the space owned by segment is appropriately reflected
— in the bitmap as used
SEGMENT_VERIFY_EXTENTS_GLOBAL constant positive := 2;
— used to verify that the space owned bu segment is appropriately reflected
— in the bitmap as used and that no other segment claims any of this space
— to be used by it
SEGMENT_MARK_CORRUPT constant positive := 3;
— used to mark a temp segment as corrupt whereby facilitating its
— elimination from the dictionary (without space reclaim)
SEGMENT_MARK_VALID constant positive := 4;
— used to mark a corrupt temp segment as valid. Useful when the corruption
— in the segment extent map or elsewhere has been resolved and the segment
— can be dropped normally
SEGMENT_DUMP_EXTENT_MAP constant positive := 5;
— dump the extent map for a given segment
TABLESPACE_VERIFY_BITMAP constant positive := 6;
— verifies the bitmap of the tablespace with extent maps of the segments
— in that tablespace to make sure everything is consistent
TABLESPACE_EXTENT_MAKE_FREE constant positive := 7;
— makes this range (extent) of space free in the bitmaps
TABLESPACE_EXTENT_MAKE_USED constant positive := 8;
— makes this range (extent) of space used in the bitmaps

SEGMENT_VERIFY_BASIC constant positive := 9;
SEGMENT_VERIFY_DEEP constant positive := 10;
SEGMENT_VERIFY_SPECIFIC constant positive := 11;
HWM_CHECK constant positive := 12;
BMB_CHECK constant positive := 13;
SEG_DICT_CHECK constant positive := 14;
EXTENT_TS_BITMAP_CHECK constant positive := 15;
DB_BACKPOINTER_CHECK constant positive := 16;
EXTENT_SEGMENT_BITMAP_CHECK constant positive := 17;
BITMAPS_CHECK constant positive := 18;
TS_VERIFY_BITMAPS constant positive := 19;
TS_VERIFY_DEEP constant positive := 20;
TS_VERIFY_SEGMENTS constant positive := 21;

SEGMENT_DUMP_BITMAP_SUMMARY constant positive := 27;

NGLOB_HBB_CHECK constant positive := 12;
NGLOB_FSB_CHECK constant positive := 13;
NGLOB_PUA_CHECK constant positive := 14;
NGLOB_CFS_CHECK constant positive := 15;

 

—————————-
— PROCEDURES AND FUNCTIONS

procedure segment_verify(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
verify_option in positive DEFAULT SEGMENT_VERIFY_EXTENTS
);

— Verifies the consistency of the extent map of the segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— verify_option – SEGMENT_VERIFY_EXTENTS or
— SEGMENT_VERIFY_EXTENTS_GLOBAL

procedure segment_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
corrupt_option in positive DEFAULT SEGMENT_MARK_CORRUPT
);

— Marks the segment corrupt/valid so that appropriate error recovery
— can be done/skipped. Only for segments already temporary.
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— corrupt_option – SEGMENT_MARK_CORRUPT or
— SEGMENT_MARK_VALID

procedure segment_drop_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive
);

— Drops a segment currently marked corrupt (without reclaiming space)
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header

procedure segment_dump(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
dump_option in positive DEFAULT SEGMENT_DUMP_EXTENT_MAP
);


— Dumps the extent map of a given segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— dump_option – SEGMENT_DUMP_EXTENT_MAP

procedure tablespace_verify(
tablespace_name in varchar2 ,
verify_option in positive DEFAULT TABLESPACE_VERIFY_BITMAP
);

— Verifies that the extent maps and the bitmaps are in sync.
— Input arguments:
— tablespace_name – name of tablespace
— verify_option – TABLESPACE_VERIFY_BITMAP

procedure tablespace_fix_bitmaps(
tablespace_name in varchar2 ,
dbarange_relative_file in positive ,
dbarange_begin_block in positive ,
dbarange_end_block in positive ,
fix_option in positive
);

— Marks the appropriate dba range (extent) as free/used in bitmap
— Input arguments:
— tablespace_name – name of tablespace
— dbarange_relative_file – relative fileno of dba range (extent)
— dbarange_begin_block – block number of beginning of extent
— dbarange_end_block – block number (inclusive) of end of extent
— fix_option – TABLESPACE_EXTENT_MAKE_FREE or
— TABLESPACE_EXTENT_MAKE_USED

procedure tablespace_rebuild_bitmaps(
tablespace_name in varchar2 ,
bitmap_relative_file in positive DEFAULT NULL,
bitmap_block in positive DEFAULT NULL
);

— Rebuilds the appropriate bitmap(s). If no bitmap block dba specified then
— rebuilds all bitmaps for the given tablespace
— Input arguments:
— tablespace_name – name of tablespace
— bitmap_relative_file – relative file number of bitmap block to rebuild
— bitmap_block – block number of bitmap block to rebuild


procedure tablespace_rebuild_quotas(
tablespace_name in varchar2
);

— Rebuilds quotas for given tablespace
— Input arguments:
— tablespace_name – name of tablespace


procedure tablespace_migrate_from_local(
tablespace_name in varchar2
);

— Migrates a locally-managed tablespace to dictionary managed
— Input arguments:
— tablespace_name – name of tablespace


procedure tablespace_migrate_to_local(
tablespace_name in varchar2,
unit_size in positive DEFAULT NULL,
rfno in positive DEFAULT NULL
);

— Migrates a dictionary-managed tablespace to locally managed
— Input arguments:
— tablespace_name – name of tablespace
— unit_size – bitmap unit size for the tablespace


procedure tablespace_relocate_bitmaps(
tablespace_name in varchar2,
filno in positive,
blkno in positive);


— Tablespace relocate bitmaps to a different location
— Input arguments:
— tablespace_name – name of tablespace


procedure tablespace_fix_segment_states(
tablespace_name in varchar2);


— Tablespace fix segment states. During migration of tablespaces
— the segments are put in a transition state. If migration fails,
— the state of the segments can be corrected by calling this
— procedure. This procedure should be called if migration failed
— and the user tries to run in an incompatible mode.


procedure tablespace_fix_segment_extblks(
tablespace_name in varchar2);


— Tablespace fix segment extents and blocks based on segment
— header entries


procedure tablespace_dump_bitmaps(
tablespace_name in varchar2);


— Tablespace space header of files

function segment_number_extents(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_extents in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_extents,WNDS,WNPS,RNPS);

— Returns the number of extents which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound

function segment_number_blocks(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_blocks in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_blocks,WNDS,WNPS,RNPS);

— Returns the number of blocks which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound

procedure segment_moveblocks(
segment_owner in varchar2,
segment_name in varchar2,
partition_name in varchar2,
segment_type in varchar2,
group_id in natural,
minimum_size in positive,
move_count in positive,
pause_time in natural,
iterations in positive
);

— Moves blocks from the segment header to the process freelist
— It only moves blocks if the list is shorter than the minimum
— size for the move_count blocks
— segment_owner – name of the object’s owner
— segment_name – name of the object
— partition_name – name of the partition (NULL if not partitioned)
— segment_type – object type (TABLE, INDEX, etc – see DBMS_SPACE)
— group_id – freelist group (0 for the segment header)
— minimum_size – do not move if process free list is longer
— move_count – move up to this number of blocks
— pause_time – pause between loop iterations
— iterations – number of iterations (infinite if NULL)

 

procedure assm_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);

— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC
procedure nglob_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);

— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC

procedure assm_tablespace_verify(
tablespace_name in varchar2,
ts_option in positive,
segment_option in positive default NULL);


— Verifies that the tablespace consistency.
— Input arguments:
— tablespace_name – name of tablespace
— ts_option – TS_VERIFY_BITMAPS, TS_VERIFY_DEEP, TS_VERIFY_SEGMENTS
— segment option – used when TS_VERIFY_SEGMENTS, one of SEGMENT_VERIFY_DEEP, SPECIFIC

function assm_segment_synchwm(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
check_only in number default 1
) return pls_integer;


— Synchronize HWMs of the ASSM segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— check_only – whether it is check only default YES
— Output:
— Return TRUE if the segment requires HWM synchronization
— Return FALSE otherwise

procedure flush_lobsegment_stats;

procedure purge_lobsegment_stats;
————————————————————————-
— PROCEDURE securefile_segment_repair
————————————————————————-

— segment repair option: repair metadata blocks
SEGMENT_REPAIR_METADATA constant positive := 1;

procedure segment_repair(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
repair_option in number default SEGMENT_REPAIR_METADATA
);


— Description:
— Repair SecureFile segment
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LON partition segment
— default: NULL
— repair_option – segment repair option,
— values: see SEGMENT_REPAIR_*** definitions
— default: SEGMENT_REPAIR_METADATA
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE segment_extend
————————————————————————-

procedure segment_extend(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
target_size in number default 1
);


— Description:
— SecureFile segment extend in background
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LOB partition segment
— default: NULL
— target_size – segment target size in GB
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE drop_empty_segments
————————————————————————-

procedure drop_empty_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);


— Description:
— Drop segments from empty table(s)/table fragments and dependent
— objects.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema
— For each table, if the table or any of its fragments are found to be
— empty, and the table satisfies certain criteria [restrictions being
— the same as those imposed by segment creation on demand], the empty
— fragments and associated index segments are dropped. A subsequent
— insert will create segments with the same properties.
— Optionally,
— a. no schema name may be specified in which case we would scan
— tables belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects.
————————————————————————-
— PROCEDURE materialize_deferred_segments
————————————————————————-

procedure materialize_deferred_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);


— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects)
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema.
— For each table, if the deferred/delayed segment property is set for
— the table or any of its fragments, a new segment is created for
— those fragments and their dependent objects.
— Optionally,
— a. no schema name may be specified in which case we would scan tables
— belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects
————————————————————————-
— PROCEDURE materialize_deferred_with_opt
————————————————————————-

procedure materialize_deferred_with_opt(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL,
partitioned_only in boolean default FALSE
);


— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects), with an additional
— option.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— partitioned_only – apply materialize procedure on partitioned
— tables only, default: FALSE
— Note:
— The materialize_deferred_segments procedure is a wrapper around
— this. This procedure is required for downgrading (from 11.2.0.2)
— to materialize segments for partitioned tables only. The
— partitioned_only argument supports this limited behavior.
————————————————————————-
— PROCEDURE tablespace_fix_affinity
————————————————————————-
procedure tablespace_fix_affinity(
tablespace_name in varchar2,
block_num in binary_integer,
instance_id in binary_integer DEFAULT NULL
);


— Description:
— This procedure sets instance affinity of a File Bitmap Block
— in bigfile tablespace. The block can be either a Level 1 bitmap
— block (FFB) or a Level 2 bitmap block (FSB).
— The report will be generated in the corresponding trace file of
— the session that runs this procedure.

— Input parameters:
— tablespace_name – name of tablespace
— block_num – block number
— instance_id – id of instance to be affined, by default
— it is the current instance

— Note:
— (1) If the block is Second-level File Bitmap block (FSB), and if
— there is another FSB that has already been affined to the instance
— specified, this FSB will be freed.
— (2) This function is only used internally and does not require
— documentation.
end;

Oracle中比对2张表之间数据是否一致的几种方法

大约是2个星期前做一个夜班的时候,开发人员需要比对shareplex 数据同步复制软件在 源端和目标端的2张表上的数据是否一致,实际上后来想了下shareplex 本身应当具有这种数据校验功能, 但是还是希望从数据库的角度得出几种可用的同表结构下的数据比对方法。

 

注意以下几种数据比对方式适用的前提条件:

 

1. 所要比对的表的结构是一致的
2. 比对过程中源端和 目标端 表上的数据都是静态的,没有任何DML修改

 

方式1:

假设你所要进行数据比对的数据库其中有一个版本为11g且该表上有相应的主键索引(primary key index)或者唯一非空索引(unique key &not null)的话,那么恭喜你! 你可以借助11g 新引入的专门做数据对比的PL/SQL Package dbms_comparison来实现数据校验的目的,如以下演示:

 

 

Source 源端版本为11gR2 :

conn maclean/maclean
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com  & www.askmaclean.com

 drop table test1;
 create table test1 tablespace users as select object_id t1,object_name t2 from dba_objects where object_id is not null;
 alter table test1 add primary key(t1);
 exec dbms_stats.gather_table_stats('MACLEAN','TEST1',cascade=>TRUE);

create database link maclean connect to maclean identified by maclean using 'G10R21';
Database link created.

 

以上源端数据库版本为11.2.0.3 , 源表结构为test1(t1 number primary key,t2 varchar2(128),透过dblink链接到版本为10.2.0.1的目标端

 

conn maclean/maclean

SQL> select * from v$version

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

create table test2 tablespace users as select object_id t1,object_name t2
from dba_objects where object_id is not null;
alter table test2 add primary key(t1);
exec dbms_stats.gather_table_stats('MACLEAN','TEST2',cascade=>TRUE);

 

目标端版本为10.2.0.1 , 表结构为test2(t1 number primary key,t2 varchar2(128))。

注意这里2张表上均必须有相同的主键索引或者伪主键索引(pseudoprimary key伪主键要求是唯一键且所有的成员列均是非空NOT NULL)。

实际创建comparison对象,并实施校验:

 

begin
  dbms_comparison.create_comparison(comparison_name    => 'MACLEAN_TEST_COM',
                                    schema_name        => 'MACLEAN',
                                    object_name        => 'TEST1',
                                    dblink_name        => 'MACLEAN',
                                    remote_schema_name => 'MACLEAN',
                                    remote_object_name => 'TEST2',
                                    scan_mode          => dbms_comparison.CMP_SCAN_MODE_FULL);
end;

PL/SQL procedure successfully completed.

SQL> set linesize 80 pagesize 1400

SQL> select * from user_comparison where comparison_name='MACLEAN_TEST_COM';

COMPARISON_NAME                COMPA SCHEMA_NAME
------------------------------ ----- ------------------------------
OBJECT_NAME                    OBJECT_TYPE       REMOTE_SCHEMA_NAME
------------------------------ ----------------- ------------------------------
REMOTE_OBJECT_NAME             REMOTE_OBJECT_TYP
------------------------------ -----------------
DBLINK_NAME
--------------------------------------------------------------------------------
SCAN_MODE SCAN_PERCENT
--------- ------------
CYCLIC_INDEX_VALUE
--------------------------------------------------------------------------------
NULL_VALUE
--------------------------------------------------------------------------------
LOCAL_CONVERGE_TAG
--------------------------------------------------------------------------------
REMOTE_CONVERGE_TAG
--------------------------------------------------------------------------------
MAX_NUM_BUCKETS MIN_ROWS_IN_BUCKET
--------------- ------------------
LAST_UPDATE_TIME
---------------------------------------------------------------------------
MACLEAN_TEST_COM               TABLE MACLEAN
TEST1                          TABLE             MACLEAN
TEST2                          TABLE
MACLEAN
FULL

ORA$STREAMS$NV

           1000              10000
20-DEC-11 01.08.44.562092 PM

 

利用dbms_comparison.create_comparison创建comparison后,新建的comparison会出现在user_comparison视图中;

以上我们完成了comparison的创建,但实际的校验仍未发生我们利用10046事件监控这个数据对比过程:

 

conn maclean/maclean
set timing on;
alter system flush shared_pool;

alter session set events '10046 trace name context forever,level 8';

set serveroutput on

DECLARE
  retval dbms_comparison.comparison_type;
BEGIN
  IF dbms_comparison.compare('MACLEAN_TEST_COM', retval, perform_row_dif => TRUE) THEN
    dbms_output.put_line('No Differences');
  ELSE
    dbms_output.put_line('Differences Found');
  END IF;
END;
/

Differences Found           =====> 返回结果为Differences Found,说明数据存在差异并不一致

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.87

===========================10046 tkprof result =========================

SELECT MIN("T1"), MAX("T1")
FROM
 "MACLEAN"."TEST1"

SELECT MIN("T1"), MAX("T1")
FROM
 "MACLEAN"."TEST2"@MACLEAN

SELECT COUNT(1)
FROM
 "MACLEAN"."TEST1" s WHERE ("T1" >= :scan_min AND "T1" <= :scan_max )

SELECT COUNT(1)
FROM
 "MACLEAN"."TEST2"@MACLEAN s WHERE ("T1" >= :scan_min AND "T1" <= :scan_max )

SELECT q.wb1, min(q."T1") min_range1, max(q."T1") max_range1, count(*)
  num_rows, sum(q.s_hash) sum_range_hash
FROM
 (SELECT /*+ FULL(s) */  width_bucket(s."T1", :scan_min1, :scan_max_inc1,
  :num_buckets) wb1, s."T1", ora_hash(NVL(to_char(s."T1"), 'ORA$STREAMS$NV'),
  4294967295, ora_hash(NVL((s."T2"), 'ORA$STREAMS$NV'), 4294967295, 0))
  s_hash FROM "MACLEAN"."TEST1" s WHERE (s."T1">=:scan_min1 AND s."T1"<=
  :scan_max1) ) q GROUP BY q.wb1 ORDER BY q.wb1

SELECT /*+ REMOTE_MAPPED */ q.wb1, min(q."T1") min_range1, max(q."T1")
  max_range1, count(*) num_rows, sum(q.s_hash) sum_range_hash
FROM
 (SELECT /*+ FULL(s) REMOTE_MAPPED */  width_bucket(s."T1", :scan_min1,
  :scan_max_inc1, :num_buckets) wb1, s."T1", ora_hash(NVL(to_char(s."T1"),
  'ORA$STREAMS$NV'), 4294967295, ora_hash(NVL((s."T2"), 'ORA$STREAMS$NV'),
  4294967295, 0)) s_hash FROM "MACLEAN"."TEST2"@MACLEAN s WHERE (s."T1">=
  :scan_min1 AND s."T1"<=:scan_max1) ) q GROUP BY q.wb1 ORDER BY q.wb1

SELECT /*+ FULL(P) +*/ * FROM "MACLEAN"."TEST2" P

SELECT /*+ FULL ("A1") */
 WIDTH_BUCKET("A1"."T1", :SCAN_MIN1, :SCAN_MAX_INC1, :NUM_BUCKETS),
 MIN("A1"."T1"),
 MAX("A1"."T1"),
 COUNT(*),
 SUM(ORA_HASH(NVL(TO_CHAR("A1"."T1"), 'ORA$STREAMS$NV'),
              4294967295,
              ORA_HASH(NVL("A1"."T2", 'ORA$STREAMS$NV'), 4294967295, 0)))
  FROM "MACLEAN"."TEST2" "A1"
 WHERE "A1"."T1" >= :SCAN_MIN1
   AND "A1"."T1" <= :SCAN_MAX1
 GROUP BY WIDTH_BUCKET("A1"."T1", :SCAN_MIN1, :SCAN_MAX_INC1, :NUM_BUCKETS)
 ORDER BY WIDTH_BUCKET("A1"."T1", :SCAN_MIN1, :SCAN_MAX_INC1, :NUM_BUCKETS)

SELECT ROWID, "T1", "T2"
  FROM "MACLEAN"."TEST2" "R"
 WHERE "T1" >= :1
   AND "T1" <= :2

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   126 |  3528 |     4   (0)| 00:00:01 |
|*  1 |  FILTER                      |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |   126 |  3528 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C006255 |   227 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(:1)<=TO_NUMBER(:2))
   3 - access("T1">=TO_NUMBER(:1) AND "T1"<=TO_NUMBER(:2))

SELECT ll.l_rowid, rr.r_rowid, NVL(ll."T1", rr."T1") idx_val
FROM
 (SELECT l.rowid l_rowid, l."T1", ora_hash(NVL(to_char(l."T1"),
  'ORA$STREAMS$NV'), 4294967295, ora_hash(NVL((l."T2"), 'ORA$STREAMS$NV'),
  4294967295, 0)) l_hash  FROM "MACLEAN"."TEST1" l WHERE l."T1">=:scan_min1
  AND l."T1"<=:scan_max1 ) ll FULL OUTER JOIN (SELECT /*+ NO_MERGE
  REMOTE_MAPPED */ r.rowid r_rowid, r."T1", ora_hash(NVL(to_char(r."T1"),
  'ORA$STREAMS$NV'), 4294967295, ora_hash(NVL((r."T2"), 'ORA$STREAMS$NV'),
  4294967295, 0)) r_hash FROM "MACLEAN"."TEST2"@MACLEAN r WHERE r."T1">=
  :scan_min1  AND r."T1"<=:scan_max1 ) rr ON  ll."T1"=rr."T1" WHERE ll.l_hash
  IS NULL OR rr.r_hash IS NULL OR ll.l_hash <> rr.r_hash

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |   190 |   754K|     9  (12)| 00:00:01 |        |      |
|*  1 |  VIEW                           | VW_FOJ_0     |   190 |   754K|     9  (12)| 00:00:01 |        |      |
|*  2 |   HASH JOIN FULL OUTER          |              |   190 |   754K|     9  (12)| 00:00:01 |        |      |
|   3 |    VIEW                         |              |   190 |  7220 |     4   (0)| 00:00:01 |        |      |
|*  4 |     FILTER                      |              |       |       |            |          |        |      |
|   5 |      TABLE ACCESS BY INDEX ROWID| TEST1        |   190 |  5510 |     4   (0)| 00:00:01 |        |      |
|*  6 |       INDEX RANGE SCAN          | SYS_C0013098 |   341 |       |     2   (0)| 00:00:01 |        |      |
|   7 |    VIEW                         |              |   126 |   495K|     4   (0)| 00:00:01 |        |      |
|   8 |     REMOTE                      | TEST2        |   126 |  3528 |     4   (0)| 00:00:01 | MACLE~ | R->S |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LL"."L_HASH" IS NULL OR "RR"."R_HASH" IS NULL OR "LL"."L_HASH"<>"RR"."R_HASH")
   2 - access("LL"."T1"="RR"."T1")
   4 - filter(TO_NUMBER(:SCAN_MIN1)<=TO_NUMBER(:SCAN_MAX1))
   6 - access("L"."T1">=TO_NUMBER(:SCAN_MIN1) AND "L"."T1"<=TO_NUMBER(:SCAN_MAX1))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   8 - SELECT ROWID,"T1","T2" FROM "MACLEAN"."TEST2" "R" WHERE "T1">=:1 AND "T1"<=:2 (accessing
       'MACLEAN' )

 

可以看到以上过程中虽然没有避免对TEST1、TEST2表的全表扫描(FULL TABLE SCAN), 但是好在实际参与HASH JOIN FULL OUTER 的仅是访问索引后获得的少量数据,所以效率还是挺高的。

 

此外可以通过user_comparison_row_dif了解实际那些row存在差异,如:

 

SQL> set linesize 80 pagesize 1400
SQL> select *
  2    from user_comparison_row_dif
  3   where comparison_name = 'MACLEAN_TEST_COM'
  4     and rownum < 2;

COMPARISON_NAME                   SCAN_ID LOCAL_ROWID        REMOTE_ROWID
------------------------------ ---------- ------------------ ------------------
INDEX_VALUE
--------------------------------------------------------------------------------
STA LAST_UPDATE_TIME
--- ---------------------------------------------------------------------------
MACLEAN_TEST_COM                       42 AAATWGAAEAAANBrAAB AAANJrAAEAAB8AMAAd
46
DIF 20-DEC-11 01.18.08.917257 PM

 

以上利用dbms_comparison包完成了一次简单的数据比对,该方法适用于11g以上版本且要求表上有主键索引或非空唯一索引, 且不支持以下数据类型字段的比对

  •     LONG
  •     LONG RAW
  •     ROWID
  •     UROWID
  •     CLOB
  •     NCLOB
  •     BLOB
  •     BFILE
  •     User-defined types (including object types, REFs, varrays, and nested tables)
  •     Oracle-supplied types (including any types, XML types, spatial types, and media types)

 

 

 

若要比对存有以上类型字段的表,那么需要在create_comparison时指定column_list参数排除掉这些类型的字段。

方法1 dbms_comparison的优势在于可以提供详细的比较信息,且在有适当索引的前提下效率较高。
缺点在于有数据库版本的要求(at least 11gR1), 且也不支持LONG 、CLOB等字段的比较。

 

方式2:

利用minus Query 对比数据

这可以说是操作上最简单的一种方法,如:

 

select * from test1 minus select * from test2@maclean;

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 75816 |  3527K|       |  1163  (40)| 00:00:14 |        |      |
|   1 |  MINUS              |       |       |       |       |            |          |        |      |
|   2 |   SORT UNIQUE       |       | 75816 |  2147K|  2984K|   710   (1)| 00:00:09 |        |      |
|   3 |    TABLE ACCESS FULL| TEST1 | 75816 |  2147K|       |   104   (1)| 00:00:02 |        |      |
|   4 |   SORT UNIQUE       |       | 50467 |  1379K|  1800K|   453   (1)| 00:00:06 |        |      |
|   5 |    REMOTE           | TEST2 | 50467 |  1379K|       |    56   (0)| 00:00:01 | MACLE~ | R->S |
-----------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "T1","T2" FROM "TEST2" "TEST2" (accessing 'MACLEAN' )

Select *
  from (select 'MACLEAN.TEST1' "Row Source", a.*
          from (select /*+ FULL(Tbl1)  */
                 T1, T2
                  from MACLEAN.TEST1 Tbl1
                minus
                select /*+ FULL(Tbl2)  */
                 T1, T2
                  from MACLEAN.TEST2@"MACLEAN" Tbl2) A
        union all
        select 'MACLEAN.TEST2@"MACLEAN"', b.*
          from (select /*+ FULL(Tbl2)  */
                 T1, T2
                  from MACLEAN.TEST2@"MACLEAN" Tbl2
                minus
                select /*+ FULL(Tbl1)  */
                 T1, T2
                  from MACLEAN.TEST1 Tbl1) B) Order by 1;

 

MINUS Clause会导致2张表均在本地被全表扫描(TABLE FULL SCAN),且要求发生SORT排序。 若所对比的表上有大量的数据,那么排序的代价将会是非常大的, 因此这种方法的效率不高。

方式2 MINUS的优点在于操作简便,特别适合于小表之间的数据检验。
缺点在于 由于SORT排序可能导致在大数据量的情况下效率很低, 且同样不支持LOB 和 LONG 这样的大对象。

 

方式3:

使用not exists子句,如:

 

select *
  from test1 a
 where not exists (select 1
          from test2 b
         where a.t1 = b.t1
           and a.t2 = b.t2);

no rows selected

Elapsed: 00:00:00.06

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 75816 |  7996K|       |   691   (1)| 00:00:09 |
|*  1 |  HASH JOIN ANTI    |       | 75816 |  7996K|  3040K|   691   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| TEST1 | 75816 |  2147K|       |   104   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| TEST2 | 77512 |  5979K|       |   104   (1)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."T1"="B"."T1" AND "A"."T2"="B"."T2")

 

 

照理说在数据量较大的情况下not exists使用的HASH JOIN ANTI是在性能上是优于MINUS操作的, 但是当所要比较的表身处不同的2个数据库(distributed query)时将无法使用HASH JOIN ANTI,而会使用FILTER OPERATION这种效率极低的操作:

 

 

select *
  from test1 a
 where not exists (select 1
          from test2@maclean b
         where a.t1 = b.t1
           and a.t2 = b.t2)
no rows selected

Elapsed: 00:01:05.76

 --------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 75816 |  2147K|   147K  (1)| 00:29:31 |        |      |
|*  1 |  FILTER            |       |       |       |            |          |        |      |
|   2 |   TABLE ACCESS FULL| TEST1 | 75816 |  2147K|   104   (1)| 00:00:02 |        |      |
|   3 |   REMOTE           | TEST2 |     1 |    29 |     2   (0)| 00:00:01 | MACLE~ | R->S |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM  "B" WHERE "B"."T1"=:B1 AND "B"."T2"=:B2))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "T1","T2" FROM "TEST2" "B" WHERE "T1"=:1 AND "T2"=:2 (accessing
       'MACLEAN' )

 

可以从以上执行计划看到FILTER 操作是十分昂贵的。

 

补充:

有网友反映可以通过增加 unnest hint 让CBO优化器在远程子查询有效的情况下整体考虑整个查询块,这样可以让执行计划用上HASH JOIN RIGHT ANTI, 这是我一开始没有考虑到的。

 

 

select *
  from test1 a
 where not exists (select /*+ unnset */
         1
          from test2@maclean b
         where a.t1 = b.t1
           and a.t2 = b.t2);

           
           

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 1776635653

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 79815 |    12M|       |   594   (1)| 00:00:08 |        |      |
|*  1 |  HASH JOIN RIGHT ANTI|       | 79815 |    12M|  1816K|   594   (1)| 00:00:08 |        |      |
|   2 |   REMOTE             | TEST2 | 20420 |  1575K|       |    56   (0)| 00:00:01 | MACLE~ | R->S |
|   3 |   TABLE ACCESS FULL  | TEST1 | 79815 |  6157K|       |   104   (1)| 00:00:02 |        |      |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."T1"="B"."T1" AND "A"."T2"="B"."T2")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "T1","T2" FROM "TEST2" "B" (accessing 'MACLEAN' )

 

 

在此基础上加入ordered hint 可以让执行计划使用HASH JOIN ANTI

 

   
 select /*+ ordered */ *
  from test1 a
 where not exists (select /*+ unnset */
         1
          from test2@maclean b
         where a.t1 = b.t1
           and a.t2 = b.t2);  

PLAN_TABLE_OUTPUT
--------------------------------------------------
Plan hash value: 3089912131

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 79815 |    12M|       |   594   (1)| 00:00:08 |        |      |
|*  1 |  HASH JOIN ANTI    |       | 79815 |    12M|  7096K|   594   (1)| 00:00:08 |        |      |
|   2 |   TABLE ACCESS FULL| TEST1 | 79815 |  6157K|       |   104   (1)| 00:00:02 |        |      |
|   3 |   REMOTE           | TEST2 | 20420 |  1575K|       |    56   (0)| 00:00:01 | MACLE~ | R->S |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."T1"="B"."T1" AND "A"."T2"="B"."T2")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "T1","T2" FROM "TEST2" "B" (accessing 'MACLEAN' )

 

方式3 的优点在于操作简便, 且当需要对比的表位于同一数据库时效率要比MINUS方式高,但如果是distributed query分布式查询则效率可能会因FILTER操作而急剧下降,这时候需要我们手动添加unnest这样的SQL提示,以保证执行计划使用HASH JOIN ANTI操作,这样能够保证not exists方式的性能。not exists同样不支持CLOB等大对象。

 

方式4:

Toad、PL/SQL Developer等图形化工具都提供了compare table data的功能, 这里我们以Toad工具为例,介绍如何使用该工具校验数据:

 

打开Toad 链接数据库-> 点击Database-> Compare -> Data

 

 

分别在Source 1和Source 2对话框中输入源表和目标表的信息

因为Toad的底层实际上使用了MINUS操作,所以提高SORT_AREA_SIZE有助于提高compare的性能,若使用AUTO PGA则可以不设置。

 

选择所要比较的列

 

 

 

首先可以比较2张表的行数,点击Execute计算count

 

 

使用MINUS 找出其中一张表上有,而另一张没有的行

 

使用MINUS 找出所有的差别

 

 

Toad的compare data功能是基于MINUS实现的,所以效率上并没有优势。但是通过图形界面省去了写SQL语句的麻烦。这种方法同样不支持LOB、LONG等对象。

 

方式5:

这是一种别出心裁的做法。 将一行数据的上所有字段合并起来,并使用dbms_utility.get_hash_value对合并后的中间值取hash value,再将所有这些从各行所获得的hash值sum累加, 若2表的hash累加值相等则判定2表的数据一致。

 

简单来说,如下面这样:

 

create table hash_one as select object_id t1,object_name t2 from dba_objects;

select dbms_utility.get_hash_value(t1||t2,0,power(2,30)) from hash_one where rownum <3;

DBMS_UTILITY.GET_HASH_VALUE(T1||T2,0,POWER(2,30))
-------------------------------------------------
                                         89209477
                                        757190129

select sum(dbms_utility.get_hash_value(t1||t2,0,power(2,30))) from hash_one;

SUM(DBMS_UTILITY.GET_HASH_VALU
------------------------------
                40683165992756

select sum(dbms_utility.get_hash_value(object_id||object_name,0,power(2,30))) from dba_objects;

SUM(DBMS_UTILITY.GET_HASH_VALU
------------------------------
                40683165992756

 

 

对于列较多的表,手动去构造所有字段合并可能会比较麻烦,利用以下SQL可以快速构造出我们所需要的语句:

 

放到PL/SQL Developer等工具中运行,在sqlplus 中可能因ORA-00923: FROM keyword not found where expected出错

select 'select sum(dbms_utility.get_hash_value('||column_name_path||',0,power(2,30)) ) from '||owner||'.'||table_name||';'  from (select owner,table_name,column_name_path,row_number() over(partition by table_name order by table_name,curr_level desc) column_name_path_rank from (select owner,table_name,column_name,rank,level as curr_level,ltrim(sys_connect_by_path(column_name,'||''|''||'),'||''|''||') column_name_path from (select owner,table_name,column_name,row_number() over(partition by table_name order by table_name,column_name) rank from dba_tab_columns where owner=UPPER('&OWNER')  and table_name=UPPER('&TABNAME')  order by table_name,column_name) connect by table_name = prior table_name and rank-1 = prior rank)) where column_name_path_rank=1;

 

使用示范:

 

SQL> @get_hash_col
Enter value for owner: SYS
Enter value for tabname: TAB$

'SELECTSUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_NAME_PATH||',0,POWER(2,30)))FROM
--------------------------------------------------------------------------------
select sum(dbms_utility.get_hash_value(ANALYZETIME||'|'||AUDIT$||'|'||AVGRLN||'|
'||AVGSPC||'|'||AVGSPC_FLB||'|'||BLKCNT||'|'||BLOCK#||'|'||BOBJ#||'|'||CHNCNT||'
|'||CLUCOLS||'|'||COLS||'|'||DATAOBJ#||'|'||DEGREE||'|'||EMPCNT||'|'||FILE#||'|'
||FLAGS||'|'||FLBCNT||'|'||INITRANS||'|'||INSTANCES||'|'||INTCOLS||'|'||KERNELCO
LS||'|'||MAXTRANS||'|'||OBJ#||'|'||PCTFREE$||'|'||PCTUSED$||'|'||PROPERTY||'|'||
ROWCNT||'|'||SAMPLESIZE||'|'||SPARE1||'|'||SPARE2||'|'||SPARE3||'|'||SPARE4||'|'
||SPARE5||'|'||SPARE6||'|'||TAB#||'|'||TRIGFLAG||'|'||TS#,0,1073741824) ) from S
YS.TAB$;

利用以上生成的SQL 计算表的sum(hash)值

select sum(dbms_utility.get_hash_value(ANALYZETIME || '|' || AUDIT$ || '|' ||
                                       AVGRLN || '|' || AVGSPC || '|' ||
                                       AVGSPC_FLB || '|' || BLKCNT || '|' ||
                                       BLOCK# || '|' || BOBJ# || '|' ||
                                       CHNCNT || '|' || CLUCOLS || '|' || COLS || '|' ||
                                       DATAOBJ# || '|' || DEGREE || '|' ||
                                       EMPCNT || '|' || FILE# || '|' ||
                                       FLAGS || '|' || FLBCNT || '|' ||
                                       INITRANS || '|' || INSTANCES || '|' ||
                                       INTCOLS || '|' || KERNELCOLS || '|' ||
                                       MAXTRANS || '|' || OBJ# || '|' ||
                                       PCTFREE$ || '|' || PCTUSED$ || '|' ||
                                       PROPERTY || '|' || ROWCNT || '|' ||
                                       SAMPLESIZE || '|' || SPARE1 || '|' ||
                                       SPARE2 || '|' || SPARE3 || '|' ||
                                       SPARE4 || '|' || SPARE5 || '|' ||
                                       SPARE6 || '|' || TAB# || '|' ||
                                       TRIGFLAG || '|' || TS#,
                                       0,
                                       1073741824))
  from SYS.TAB$;

SUM(DBMS_UTILITY.GET_HASH_VALU
------------------------------
                 1646389632463

 

方式5 利用累加整行数据的hash来判定表上数据是否一致, 仅需要对2张表做全表扫描,效率上是这几种方法中最高的, 且能保证较高的准确率。

 

但是该hash方式存在以下几点不足:

1. 所有字段合并的整行数据可能超过4000字节,这时会出现ORA-1498错误。换而言之使用这种方式的前提是表中任一行的行长不能超过4000 bytes,当然常规情况下很少会有一行数据超过4000 bytes,也可以通过dba_tables.avg_row_len平均行长的统计信息来判定,若avg_row_len<<4000 那么一般不会有溢出的问题。

2. 该hash 方式仅能帮助判断 数据是否一致, 而无法提供更多有用的,例如是哪些行不一致等细节信息

3. 同样的该hash方式对于lob、long字段也无能为力

如何禁止特定用户使用sqlplus或PL/SQL Developer等工具登陆?

最早想要实现禁止某些特定用户使用SQLPLUS或PL/SQL Developer等工具登陆是在2010年的3月,当时发现用户的一套数据库中有大量的用户使用老版本的PL/SQL Developer登陆,具体的版本号记不清楚了,大约是PL/SQL Developer 5的版本,是否正版授权不得而知, 反正就是一个办公室里有大量的阿姨、大叔都靠这个图形化工具访问数据库,做一些必要的数据操作,主要是一些SQL查询语句,有时候他们还会用工具栏查一些对象(search object),正是因为他们使用了老版本的PL/SQL Developer,造成在使用一些widget的时候会引起Oracle出现一些非致命的ORA-00600错误,虽然这些600错误不会导致严重的问题,但是只要是出现在告警日志Alert.log中的600还是需要我们去分析。

当时我的想法是直接从Oracle的角度禁止普通用户以PL/SQL Developer工具登陆,虽然当时没有真的这样做。 题外话,要真的这么做了,估计那一办公室的阿姨、叔叔都要找我的麻烦,他们可不会用SQLPLUS来登数据库;让他们升级PL/SQL Developer到高版本的想法也基本可以打住,让阿姨、叔叔们升级可要比登天还难。

Google了一番,没有找到太多有用的信息。

闲来无事,我在著名的Oracle-l Freelist邮件列表中发了一封邮件,集思广益:

 

Hello every,

           Anyone can advise how to ban plsql developer  connect to oracle?
The plsql developer search widget may cause  some ora-600 warning in alert
log . So I want to ban any connection using plsql developer.

 

Oracle-l Free List不愧是卧虎藏龙,第二天就收到了十分有用的信息,感谢这位 Coskan Gundogar的网友。

 

Damir-Vadas 在他的博客上提供了一种有效的方式,通过建立LOGON DATABASE的Trigger触发器,实现了仅允许拥有特定角色(Role)的用户通过sqlplus登陆实例。

 

这和我们的需求大致相仿,值得借鉴。我们的实际需求是: 针对某些已知的数据库用户账号,限制其使用SQLPLUS、PL/SQL Developer、Toad等工具登陆实例; 因为这里所要限制的用户账号和工具模块名(module)都是已知的,所以不必要如Damir-Vadas那样做成白名的形式,而只需要定义blacklist即可。实际这样做的一大目的是尽可能限制人为的登录; 我们知道当应用程序登录数据库时,根据应用程序的构成不同,可能使用JDBC Thin Client、Pro*C、ODBC等多种模块名(Module),我们不想限制应用程序的正常登录, 而仅仅想禁止人为地使用这些应用程序所使用的账号来登录实例(应用程序的账号信息可能被写在应用层中,或者为开发人员所知晓),一般我们只要限制SQLPLUS、PL/SQL Developer、Toad这几种主流的客户端程序,就可以限制人为地登录数据库了;当然这其实是防君子不防小人,实际如果hacker真的掌握了应用程序的账号密码的话,完全可以通过自己编写程序来访问数据库。

 

建立示例的,禁止以SQLPLUS和PL/SQL DEVELOPER登陆的用户账号TRY_LOGON_BY_TOOLS:

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com  & www.askmaclean.com

SQL> create user TRY_LOGON_BY_TOOLS IDENTIFIED BY abc;

User created.

SQL> grant connect,resource to TRY_LOGON_BY_TOOLS;

Grant succeeded.

 

创建以下LOGON DATABASE Trigger即可限制TRY_LOGON_BY_TOOLS用户以SQLPLUS、Toad、PL/SQL DEVELOPER 三种软件登陆数据库,  但是该用户仍能以其他的程序模块登录,如:JDBC、ODBC等;实际就是限制了该账号的人为登录,而应用程序如Java、.Net、Pro*C等语言编写的程序没有使用这里已经禁止的模块名(Module),所以不会被禁止登录。

 

记得修改TRY_LOGON_BY_TOOLS为你需要的用户名列表

-- NAME: BLOCK_TOOLS_LOGON.SQL
-- ------------------------------------------------------------------------
--   Copyright 2011, www.askmaclean.com
--   LAST UPDATED: 12/05/11
--   Written By Maclean.Liu
-- Usage: @BLOCK_TOOLS_LOGON
-- ------------------------------------------------------------------------
-- PURPOSE:
--    This script is to be used to help dba protect database
--    from uninvited logon action
-- ------------------------------------------------------------------------
-- DISCLAIMER:
--    This script is provided for educational purposes only. It is NOT
--    supported by Maclean Liu.
--    The script has been tested and appears to work as intended.
--    You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
-- Script output is as follows:

drop trigger BLOCK_TOOLS_LOGON;

CREATE OR REPLACE TRIGGER BLOCK_TOOLS_LOGON
  AFTER LOGON ON DATABASE
DECLARE

  my_forced_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);
BEGIN
  IF (sys_context('USERENV', 'SESSION_USER') IN ('TRY_LOGON_BY_TOOLS')) -- add your username here
   THEN
    IF (UPPER(sys_context('USERENV', 'MODULE')) LIKE '%SQLPLUS%' OR --SQL*PLUS
       UPPER(sys_context('USERENV', 'MODULE')) LIKE '%TOAD%' OR --TOAD
       UPPER(sys_context('USERENV', 'MODULE')) LIKE '%PLSQLDEV%') --PL/SQL DEVELOPER
     THEN
      RAISE my_forced_exception;
    END IF;
  END IF;
EXCEPTION
  WHEN my_forced_exception THEN
    RAISE_APPLICATION_ERROR(-20101,
                            'USER ' ||
                            sys_context('USERENV', 'SESSION_USER') || ' ' ||
                            'MODULE ' ||
                            UPPER(sys_context('USERENV', 'MODULE')) || ' ' || '
                              Logon Action via tool is not allowed.
                              Please contact Maclean Liu to help you!
                              http://www.askmaclean.com/');
  WHEN OTHERS THEN
    null;
END;
/

 

以DBA身份用户登录并创建以上Trigger:

 

[oracle@vrh8 ~]$ sqlplus system/oracle

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 6 00:34:12 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE TRIGGER BLOCK_TOOLS_LOGON
  2    AFTER LOGON ON DATABASE
  3  DECLARE
  4
  5    my_forced_exception EXCEPTION;
  6    PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);
  7  BEGIN
  8    IF (sys_context('USERENV', 'SESSION_USER') IN ('TRY_LOGON_BY_TOOLS')) -- add your username here
  9     THEN
 10      IF (UPPER(sys_context('USERENV', 'MODULE')) LIKE '%SQLPLUS%' OR --SQL*PLUS
 11         UPPER(sys_context('USERENV', 'MODULE')) LIKE '%TOAD%' OR --TOAD
 12         UPPER(sys_context('USERENV', 'MODULE')) LIKE '%PLSQLDEV%') --PL/SQL DEVELOPER
 13       THEN
 14        RAISE my_forced_exception;
 15      END IF;
 16    END IF;
 17  EXCEPTION
 18    WHEN my_forced_exception THEN
 19      RAISE_APPLICATION_ERROR(-20101,
 20                              'USER ' ||
 21                              sys_context('USERENV', 'SESSION_USER') || ' ' ||
 22                              'MODULE ' ||
 23                              UPPER(sys_context('USERENV', 'MODULE')) || ' ' || '
 24                                Logon Action via tool is not allowed.
 25                                Please contact Maclean Liu to help you!
 26                                http://www.askmaclean.com/');
 27    WHEN OTHERS THEN
 28      null;
 29  END;
 30  /

Trigger created.

 

成功创建后 , 使用指定的TRY_LOGON_BY_TOOLS用户尝试SQLPLUS登录:

 

[oracle@vrh8 ~]$ sqlplus TRY_LOGON_BY_TOOLS/abc

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 6 01:18:47 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20101: USER TRY_LOGON_BY_TOOLS MODULE SQLPLUS@VRH8.ORACLE.COM (TNS V1-V3)
Logon Action via tool is not allowed.
Please contact Maclean Liu to help you!
http://www.askmaclean.com/
ORA-06512: at line 17

 

TRY_LOGON_BY_TOOLS用户以PL/SQL Developer登录:

 

 

 

以上我们通过建立LOGON触发器的形式达到了禁止特定用户以SQLPLUS、PL/SQL Developer登录实例的目的,但是请注意Trigger触发器会消耗额外的资源,对于登录频繁的系统,一个小小的登录触发器可能引发性能的瓶颈。  另外在一些容易hang住的场景中,Trigger可能会引起更多不良的反应,所以请谨慎地在产品数据库中部署这些小玩样。

Oracle中的Package/Procedure/Function存放在哪里?

有同学问Oracle 的package、Procedure、Function 这些PL/SQL程序单元分别存放在哪里?

针对这个问题我们可以通过对create package、Procedure、Function 做trace分析来了解其细节,如:

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------
www.askmaclean.com & www.askmaclean.com

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.

SQL> create or replace procedure maclean
2 as
3 begin
4 null
5 ;
6 end;
7 /

SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_3503.trc

 

我们来分析以上trace的内容:

 

[oracle@vrh8 ~]$ grep "insert"  /s01/admin/G10R21/udump/g10r21_ora_3503.trc

insert into procedure$(obj#,audit$,options) values (:1,:2,:3)
insert into source$(obj#,line,source) values (:1,:2,:3)
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_char$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into settings$(obj#, param, value) values (:1, :2, :3)
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
insert into procedureinfo$(obj#,procedure#,overload#,procedurename,properties,itypeobj#) values (:1,:2,:3,:4,:5,:6)
insert into argument$( obj#,procedure$,procedure#,overload#,position#,sequence#,level#)
insert into procedureplsql$(obj#,procedure#,entrypoint#) values (:1,:2,:3)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into obj$(owner#,name,namespace,obj#,type#,

 

创建一个非常简单的PL/SQL Procedure牵涉到的数据字典基表却不少,包括了:

  • procedure$
  • source$
  • idl_sb4$
  • idl_ub1$
  • idl_char$
  • idl_ub2$
  • idl_sb4$
  • idl_ub1$
  • settings$
  • warning_settings$
  • procedureinfo$
  • access$
  • obj$

 

注意以上这些数据字典基表都存放在SYSTEM表空间上,丢失SYSTEM表空间也就意味着你可能找不回这些程序了(如果你没有其他备份的话),即使使用DUL工具也无法挽回。

 

我来简单介绍一下这些数据字典基表( 11g以前可以在sql.bsq文件中找到以下信息, 11g以后在$ORACLE_HOME/rdbms/admin/dplsql.bsq中):

 

 

procedure$ 基表(普通堆表)用于存放Package程序包和独立的procedure存储过程以及function 函数信息,每一条记录对应一个procedure或function(procedure$ contains information about packages and standalone procedures, functions. There is one row for a top-level object.)

 

 

procedureinfo$基表(普通堆表)用于存放独立的或程序包中procedure或function的详细信息,包括Name名字和属性。

这些属性包括:Aggregate、Pipelined、Parallel、deterministic等:

 

  properties    number not null,                     /* procedure properties */
                /* 0x00001 =     1 = HIDDEN (internally generated) procedure */
                /* 0x00002 =     2 = C implementation (in spec)              */
                /* 0x00004 =     4 = Java implementation (in spec)           */
                /* 0x00008 =     8 = Aggregate function                      */
                /* 0x00010 =    16 = Pipelined function                      */
                /* 0x00020 =    32 = Parallel enabled                        */
                /* 0x00040 =    64 = Retrun Self as result (SQLJ)            */
                /* 0x00080 =   128 = Constructor function (SQLJ)             */
                /* 0x00100 =   256 = deterministic                           */
                /* 0x00200 =   512 = Pipelined func; interface impl          */
                /* 0x00400 =  1024 = Function with invokers rights           */
                /* 0x00800 =  2048 = Func with partitioned argument(s)       */
                /* 0x01000 =  4096 = Func with clustered argument(s)         */
                /* 0x02000 =  8192 = Func with ordered i/p argument(s)       */
                /* 0x04000 =  16384 = Partitioned arg: Hash partitioning     */
                /* 0x08000 = 32768 = Partitioned arg: Range partitioning     */
                /* 0x10000 = 65536 = Partitioned using any partitioning      */
  /* The following field is relevant only for aggregate and pipelined        */
  /*  functions that are implemented using an implementation type            */

 

source$用于存放PL/SQL程序的源代码, 这些代码包括已经被Oracle Wrap加密的代码,一般来说如Oracle EBS这样的应用这张表会非常地大:

 

 

idl_ub1$、idl_char$ 这种表名以idl_打头的字典基表用以存放编译好的PL/SQL程序代码,包括三种种类:

  • DIANA
    • Heap 2 (Diana) : Contains the DIANA (Parse tree metadata) for a PL/SQL object
  • Portable pcode
    • Heap 3 (Pcode): Stores the pseudocode for a PL/SQL object
  • machine-dependent code
    • Heap 4 (Mcode): Machine-dependent pseudocode for a PL/SQL object

 

 

argument$基表记录了存储过程或函数的调用时的参数信息, 如我们所常用的DBMS_METADATA.GET_DDL函数就有7个argument参数

 

select object_name,object_id,object_type from dba_objects where object_name='DBMS_METADATA';

OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
DBMS_METADATA              6097 PACKAGE
DBMS_METADATA              7105 PACKAGE BODY
DBMS_METADATA              6098 SYNONYM

SQL> select procedure$,sequence#,argument from sys.argument$ where procedure$='GET_DDL' and obj#=6097;

PROCEDURE$                      SEQUENCE# ARGUMENT
------------------------------ ---------- ------------------------------
GET_DDL                                 1
GET_DDL                                 2 OBJECT_TYPE
GET_DDL                                 3 NAME
GET_DDL                                 4 SCHEMA
GET_DDL                                 5 VERSION
GET_DDL                                 6 MODEL
GET_DDL                                 7 TRANSFORM

7 rows selected.

 

 

PL/SQL Virtual Machine Memory Usage

PL/SQL Program Units即PL/SQL程序单元,常被叫做”library units”或lib-units.

参考以下模块类型:

  • package spec
  • package body
  • top-level function or procedure
  • type spec
  • type body
  • trigger
  • anonymous blocks.

PL/SQL 虚拟机的内存使用主要体现在4个方面:

  • PGA
    • PL/SQL stack call,用于保存本地变量和其他一些状态结构
    • NCOMP生成的动态链接库文件
  • CGA
    • 二级内存(secondary memory),分配的堆和大的可收缩本地变量如大的strings、Lob或collections
  • UGA
    • 程度单元的实例(library-unit instantiations),如package global variables, DL0/ DL1 dependency vectors, display frame等
  • SGA
    共享池中的MCODE子堆

KGL – Kernel Generic Library Manager
该layer管理会话间需要共享的资源,如PL/SQL MCODE,Diana,Source,SQL cursor,SQL Plan)

KGI – Kernel Generic Instantiation Layer.
该layer管理特定会话非共享的资源,如实例化的包含了包全局变量状态信息的PL/SQL程序单元

KOH/KGH 该layer用以管理heap service堆服务

KGL_Entry_PLSQL_UNIT

 

PLSQL MCODE Heap的属性

  • machine dependent binary format for a compiled PL/SQL library-unit.
  • to execute code in a lib-unit, its MCODE heap must be loaded in memory.
  • MCODE is loaded in SGA and is “pinned” for CALL duration.
  • once unpinned, the heap may be aged; hence, may need to get re-loaded.
  • important to page large data structures in SGA.

MCODE Heap: Subcomponents

  • EntryPoint Piece (PL_UEP)
  • Code Segment or Byte Code Piece (PL_UCP)
  • Constant Pool:
    • Data Segment (PL_UKP)
    • Handle Segment (PL_UHS)
  • SQL Strings Table (PL_USP)

PL/SQL Instantiations

  • When a lib-unit is first referenced by a program (session) an instantiation of the lib unit is created.
  • PL/SQL relies on KGI for inst obj mgmt.
  • A PL/SQL lib-unit instantiation consists of:
    • PLIO struct (the handle of the PL/SQL inst obj)
    • Static Frame
    • Secondary (Heap) Memory for package globals
  • PLIO Struct
    • first portion of PLIO struct is the KGIOB struct (kgi’s portion of the object handle)
    • points to the static frame struct (PLIOST)
    • also contains other book-keeping info (such as memory duration of instantiation’s work area, etc.)
  • Static Frame:
    • represents that part of instantiation’s work area whose size is compile-time determined.
    • the root of the static frame is PLIOST struct which leads the following sub-pieces:
      • depends-on array to global variable vectors (DL0)
      • depends-on array to other instantiations (DL1)
      • Display Frame (DPF)
      • global variable vector for this unit (GF)
      • primary memory for global variables.
  • Secondary Memory for package globals
    • used to allocate data types that are stored out-of-line (heap allocated) e.g., collections, large strings, large records, LOBs, datetime types, etc.

Structure of a PLSQL Instantiation Object
Memory Model In PLSQL Instantiation

PL/SQL Product Sample Code

PL/SQL Product Sample Code

create or replace package XXPAY_ETN_PAY_DIFF_REPORT_PKG is
  -- $ETNHeader: $
  --------------------------------------------------------------------------------------------------
  --    Owner        : EATON CORPORATION.
  --    Application  : Human Resources
  --    Schema       : APPS
  --    Compile AS   : APPS
  --    File Name    : XXPAY_ETN_PAY_DIFF_REPORT_PKG.PKS
  --    Date         : 28-Jun-2014
  --    Author       : TCS
  --    Description  : Package for custom program for XXPAY Payroll Difference Report
  --
  --    Version      : $ETNHeader: $
  --
  --    Parameters  : ERRBUF   -StANDar concurrent prog parameters   
  --                  RETCODE  -StANDar concurrent prog parameters 
  --                  p_payroll_id - Payroll for report.
  --                  p_payroll_period_id  - pay period for report.
  --                   p_organization_id - organization id for report.
  --                  p_element_bal  - elements and balance for report
  --                  p_threshold  - threshold for report
  --                  p_percent - percent for report               
  --
  --    Change History
  --  ========================================================================================
  --         Ver 1.0           Pushkar      28-Jun-2014     Creation
  --  ========================================================================================
  -- =============================================================================================
  -- Procedure: XXPAY_PAY_DIFF_REPORT
  -- =============================================================================================
  --   This procedure generates the XML for the Payroll Difference Report
  -- =============================================================================================                                 

  PROCEDURE XXPAY_PAY_DIFF_REPORT(errbuf              OUT VARCHAR2,
                                  retcode             OUT VARCHAR2,
                                  p_payroll_id        IN NUMBER,
                                  p_payroll_period_id IN NUMBER,
                                  p_organization_id   IN NUMBER,
                                  p_element_bal       IN VARCHAR2,
                                  p_threshold         IN VARCHAR2,
                                  p_percent           IN VARCHAR2);

END;
/
CREATE OR REPLACE PACKAGE BODY XXPAY_ETN_PAY_DIFF_REPORT_PKG IS
  -- $ETNHeader: $
  --------------------------------------------------------------------------------------------------
  --    Owner        : EATON CORPORATION.
  --    Application  : Human Resources
  --    Schema       : APPS
  --    Compile AS   : APPS
  --    File Name    : XXPAY_ETN_PAY_DIFF_REPORT_PKG.PKB
  --    Date         : 28-Jun-2014
  --    Author       : TCS
  --    Description  : Package for custom program for XXPAY Payroll Difference Report
  --
  --    Version      : $ETNHeader: $
  --
  --    Parameters  : ERRBUF   -StANDar concurrent prog parameters   
  --                  RETCODE  -StANDar concurrent prog parameters 
  --                  p_payroll_id - Payroll for report.
  --                  p_payroll_period_id  - pay period for report.
  --                  p_organization_id - organization id for report.
  --                  p_element_bal  - elements and balance for report
  --                  p_threshold  - threshold for report
  --                  p_percent - percent for report               
  --
  --    Change History
  --  ========================================================================================
  --         Ver 1.0           Pushkar      28-Jun-2014     Creation
  --         Ver 2.0           Pushkar      13-Aug-2014     Change for termination date
  --                                                         and '&' character to'&'   
  --  ========================================================================================

  PROCEDURE XXPAY_PAY_DIFF_REPORT(errbuf              OUT VARCHAR2,
                                  retcode             OUT VARCHAR2,
                                  p_payroll_id        IN NUMBER,
                                  p_payroll_period_id IN NUMBER,
                                  p_organization_id   IN NUMBER,
                                  p_element_bal       IN VARCHAR2,
                                  p_threshold         IN VARCHAR2,
                                  p_percent           IN VARCHAR2) IS
  
    CURSOR EMP_PAY_DIFF_CUR(v_payroll_id         IN NUMBER,
                            v_prev_pay_period_id IN NUMBER,
                            v_organization_id    IN NUMBER) IS
      SELECT *
        FROM (SELECT papf1.employee_number Employee_Number,
                     papf1.Full_name Full_Name,
                     papf1.PER_INFORMATION14 Employee_Name,
                     REPLACE(apps.XXPAY_ETN_RPT_UTIL.get_activity_center(paa.assignment_id,
                                                                         ptp.end_date),
                             '&',
                             '&') Activity_centre,
                     REPLACE((SELECT name
                               FROM apps.hr_all_organization_units haou
                              WHERE haou.organization_id =
                                    paaf.organization_id),
                             '&',
                             '&') Organization,
                     papf1.national_identifier National_Identifier,
                     REPLACE(PG.name, '&', '&') GRADE_NAME,
                     REPLACE((SELECT location_code
                               FROM apps.hr_locations_all
                              WHERE location_id = paaf.location_id),
                             '&',
                             '&') Location,
                     to_char(ppos.date_start, 'DD-MON-YYYY') NEW_HIRE_DATE,
                     to_char(ppos.actual_termination_date, 'DD-MON-YYYY') TERMINATION_DATE,
                     ppa.payroll_action_id,
                     ptp.period_name,
                     papf.payroll_name,
                     ptp.time_period_id,
                     paa.assignment_action_id,
                     papf1.business_group_id,
                     ptp.end_date,
                     paaf.assignment_id,
                     ptp.payroll_id,
                     to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                 paaf.business_group_id,
                                                                 'Total Payments',
                                                                 '_ASG_PTD',
                                                                 PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) TOT_PAY_BAL_CURRENT,
                     to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                 paaf.business_group_id,
                                                                 'Total Payments',
                                                                 '_ASG_PTD',
                                                                 PAYROLL_PERIOD_DATES.PREV_PAY_DATE)) TOT_PAY_BAL_PREVIOUS,
                     
                     (to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                    paaf.business_group_id,
                                                                    'Total Payments',
                                                                    '_ASG_PTD',
                                                                    PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) -
                     to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                    paaf.business_group_id,
                                                                    'Total Payments',
                                                                    '_ASG_PTD',
                                                                    PAYROLL_PERIOD_DATES.PREV_PAY_DATE))) GAP,
                     ABS(round(((to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                               paaf.business_group_id,
                                                                               'Total Payments',
                                                                               '_ASG_PTD',
                                                                               PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) -
                               to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                               paaf.business_group_id,
                                                                               'Total Payments',
                                                                               '_ASG_PTD',
                                                                               PAYROLL_PERIOD_DATES.PREV_PAY_DATE))) /
                               decode(to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                                     paaf.business_group_id,
                                                                                     'Total Payments',
                                                                                     '_ASG_PTD',
                                                                                     PAYROLL_PERIOD_DATES.CURR_PAY_DATE)),
                                       0,
                                       1,
                                       to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
                                                                                     paaf.business_group_id,
                                                                                     'Total Payments',
                                                                                     '_ASG_PTD',
                                                                                     PAYROLL_PERIOD_DATES.CURR_PAY_DATE)))),
                               2)) GAP_PERCENT,
                     payroll_period_dates.curr_period_name CURRENT_PAYROLL,
                     payroll_period_dates.prev_period_name PREVIOUS_PAYROLL,
                     payroll_period_dates.curr_time_period_id CURRENT_PAYPERIOD_ID,
                     payroll_period_dates.CURR_PAY_DATE CURRENT_PAYPERIOD_END_DATE,
                     payroll_period_dates.prev_time_period_id PREVIOUS_PAYPERIOD_ID,
                     payroll_period_dates.PREV_PAY_DATE PREVIOUS_PAYPERIOD_END_DATE
                FROM apps.pay_all_payrolls_f papf,
                     apps.pay_payroll_actions ppa,
                     apps.pay_assignment_actions paa,
                     apps.per_time_periods ptp,
                     apps.per_people_f papf1,
                     apps.per_assignments_f paaf,
                     apps.per_periods_of_service ppos,
                     apps.per_grades pg,
                     (SELECT ptp2.end_date       PREV_PAY_DATE,
                             ptp1.end_date       CURR_PAY_DATE,
                             ptp2.period_name    PREV_PERIOD_NAME,
                             PTP1.PERIOD_NAME    curr_period_name,
                             ptp2.time_period_id PREV_TIME_PERIOD_ID,
                             ptp1.time_period_id CURR_TIME_PERIOD_ID,
                             ptp1.payroll_id
                        FROM per_time_periods ptp1, per_time_periods ptp2
                       WHERE ptp1.time_period_id = P_Payroll_period_id
                         AND ptp2.time_period_id = V_PREV_PAY_PERIOD_ID
                         AND ptp2.payroll_id = ptp1.payroll_id) PAYROLL_PERIOD_DATES
               WHERE papf.payroll_id = (v_payroll_id)
                 AND payroll_period_dates.payroll_id = ptp.payroll_id
                 AND ptp.time_period_id IN
                     ((p_payroll_period_id), v_prev_pay_period_id)
                 AND paaf.assignment_id = paa.assignment_id
                 AND papf.payroll_id = ppa.payroll_id
                 AND paa.payroll_action_id = ppa.payroll_action_id
                 AND ppa.time_period_id = ptp.time_period_id
                 AND paaf.period_of_service_id = ppos.period_of_service_id
                 AND paa.assignment_id <> -1
                 AND paaf.assignment_type = 'E'
                 AND ppa.effective_date BETWEEN ptp.start_date AND
                     ptp.end_date
                 AND EXISTS
               (SELECT 'X'
                        FROM per_all_assignments_f  paaf1,
                             pay_assignment_actions paa2,
                             pay_payroll_actions    PPA2,
                             Per_time_periods       ptp2
                       WHERE paaf1.person_id = paaf.person_id
                         AND paaf1.assignment_id = paa2.assignment_id
                         AND ppa2.payroll_action_id = paa2.payroll_action_id
                         AND ppa2.time_period_id = ptp2.time_period_id
                         AND ppa2.action_status = 'C'
                         AND ppa2.action_type IN ('R', 'Q', 'B', 'V', 'I')
                         AND ptp2.time_period_id IN
                             ((P_Payroll_period_id), v_prev_pay_period_id)
                       HAVING COUNT(DISTINCT(ptp2.period_name)) = 2)
                 AND paaf.grade_id = pg.grade_id(+)
                 AND papf1.person_id = paaf.person_id
                 AND trunc(ptp.end_date) between papf1.effective_start_date AND
                     papf1.effective_end_date
                 AND trunc(ptp.end_date) BETWEEN papf.effective_start_date AND
                     papf.effective_end_date
                 AND ppos.person_id = paaf.person_id
                 AND trunc(nvl(ppos.actual_termination_date, ptp.end_date)) between
                     paaf.effective_start_date AND paaf.effective_end_date
                 AND (Paaf.organization_id IN (v_organization_id) OR
                     LEAST(v_organization_id) IS NULL)
                 AND primary_flag = 'Y'
                 AND ppa.action_status = 'C'
                 AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I'))
       order by ACTIVITY_CENTRE, EMPLOYEE_NUMBER, TIME_PERIOD_ID;
  
    CURSOR Elements_balance_VALUE_CUR(V_Payroll_NAME      IN VARCHAR,
                                      V_time_period_id    IN NUMBER,
                                      V_assignment_id     IN NUMBER,
                                      V_business_group_id IN NUMBER,
                                      V_END_DATE          IN DATE) IS
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
                                                           0,
                                                           instr(flv.meaning,
                                                                 '|') - 1),
                                                    substr(flv.meaning,
                                                           instr(flv.meaning,
                                                                 '|') + 1,
                                                           length(flv.meaning)),
                                                    V_Payroll_NAME,
                                                    V_time_period_id,
                                                    V_assignment_id) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'E'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                         V_business_group_id,
                                                         substr(flv.meaning,
                                                                0,
                                                                instr(flv.meaning,
                                                                      '|') - 1),
                                                         substr(flv.meaning,
                                                                instr(flv.meaning,
                                                                      '|') + 1,
                                                                length(flv.meaning)),
                                                         V_END_DATE)) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'B'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT 'TOTAL_PAYMENT' UNIT_NAME,
             'ASG_RUN' UNIT_VALUE_NAME,
             'Total Payment' ELEMENT_UNIT,
             to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                         V_business_group_id,
                                                         'Total Payments',
                                                         '_ASG_PTD',
                                                         V_END_DATE)) UNIT_RESULT,
             
             '00001' ELE_BAL_CODE
        FROM DUAL
      UNION
      SELECT 'Percentage' UNIT_NAME,
             'Percentage' UNIT_VALUE_NAME,
             'Percentage' ELEMENT_UNIT,
             NULL UNIT_RESULT,
             '000015' ELE_BAL_CODE
        FROM DUAL
       ORDER BY ELE_BAL_CODE;
  
    CURSOR Elements_balance_VALUE_DIFF(V_Payroll_NAME        IN VARCHAR,
                                       V_CURR_time_period_id IN NUMBER,
                                       V_PREV_time_period_id IN NUMBER,
                                       V_assignment_id       IN NUMBER,
                                       V_business_group_id   IN NUMBER,
                                       V_CURR_END_DATE       IN DATE,
                                       V_PREV_END_DATE       IN DATE,
                                       V_PERCENTAGE          IN NUMBER) IS
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             TO_CHAR(TO_NUMBER(APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
                                                                             0,
                                                                             instr(flv.meaning,
                                                                                   '|') - 1),
                                                                      substr(flv.meaning,
                                                                             instr(flv.meaning,
                                                                                   '|') + 1,
                                                                             length(flv.meaning)),
                                                                      V_Payroll_NAME,
                                                                      V_CURR_time_period_id,
                                                                      V_assignment_id)) -
                     TO_NUMBER(APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
                                                                             0,
                                                                             instr(flv.meaning,
                                                                                   '|') - 1),
                                                                      substr(flv.meaning,
                                                                             instr(flv.meaning,
                                                                                   '|') + 1,
                                                                             length(flv.meaning)),
                                                                      V_Payroll_NAME,
                                                                      V_PREV_time_period_id,
                                                                      V_assignment_id))) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'E'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
             
             substr(flv.meaning,
                    instr(flv.meaning, '|') + 1,
                    length(flv.meaning)) UNIT_VALUE_NAME,
             flv.description ELEMENT_UNIT,
             to_char((APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          substr(flv.meaning,
                                                                 0,
                                                                 instr(flv.meaning,
                                                                       '|') - 1),
                                                          substr(flv.meaning,
                                                                 instr(flv.meaning,
                                                                       '|') + 1,
                                                                 length(flv.meaning)),
                                                          V_CURR_END_DATE)) -
                     (APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          substr(flv.meaning,
                                                                 0,
                                                                 instr(flv.meaning,
                                                                       '|') - 1),
                                                          substr(flv.meaning,
                                                                 instr(flv.meaning,
                                                                       '|') + 1,
                                                                 length(flv.meaning)),
                                                          V_PREV_END_DATE))) UNIT_RESULT,
             flv.lookup_code ELE_BAL_CODE
        FROM apps.fnd_lookup_values flv
       WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
         AND flv.attribute1 = 'B'
         AND flv.enabled_flag = 'Y'
         AND language = 'US'
         AND (flv.meaning IN (P_element_bal) or
             LEAST(P_element_bal) IS NULL)
         AND flv.tag = 'CN'
      UNION
      SELECT 'TOTAL_PAYMENT' UNIT_NAME,
             'ASG_RUN' UNIT_VALUE_NAME,
             'Total Payment' ELEMENT_UNIT,
             to_char((APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          'Total Payments',
                                                          '_ASG_PTD',
                                                          V_CURR_END_DATE)) -
                     (APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
                                                          V_business_group_id,
                                                          'Total Payments',
                                                          '_ASG_PTD',
                                                          V_PREV_END_DATE))) UNIT_RESULT,
             '00001' ELE_BAL_CODE
        FROM DUAL
      UNION
      SELECT 'Percentage' UNIT_NAME,
             'Percentage' UNIT_VALUE_NAME,
             'Percentage' ELEMENT_UNIT,
             TO_CHAR(V_PERCENTAGE) UNIT_RESULT,
             '000015' ELE_BAL_CODE
        FROM DUAL
       ORDER BY ELE_BAL_CODE;
  
    V_PREV_PAY_PERIOD_ID NUMBER;
    V_payroll_name       VARCHAR2(100);
    V_TIME_PERIOD        VARCHAR2(100);
    V_organization_name  VARCHAR2(100);
    V_period_name        VARCHAR2(100);
    e_error_EXCEPTION EXCEPTION;
    v_error_msg VARCHAR2(1000);
  
  BEGIN
  
    BEGIN
    
      SELECT Payroll_name
        INTO V_payroll_name
        FROM pay_all_payrolls_f
       WHERE payroll_id = P_Payroll_id;
    
      SELECT ptp2.time_period_id
        INTO V_PREV_PAY_PERIOD_ID
        FROM per_time_periods ptp1, per_time_periods ptp2
       WHERE ptp2.end_date = ptp1.start_date - 1
         AND ptp2.payroll_id = ptp1.payroll_id
         AND ptp1.time_period_id = P_Payroll_period_id;
    
      SELECT Period_name
        INTO V_period_name
        FROM per_time_periods
       WHERE time_period_id = P_Payroll_period_id;
    
    EXCEPTION
      WHEN OTHERS THEN
        v_error_msg := 'Error In fetching parameters' || '-' || SQLERRM;
        RAISE e_error_EXCEPTION;
      
    END;
  
    BEGIN
    
      SELECT name
        INTO V_organization_name
        FROM hr_all_organization_units
       WHERE organization_id = P_organization_id;
    
    EXCEPTION
      WHEN OTHERS THEN
        V_organization_name := NULL;
      
    End;
  
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_Payroll_id || '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_Payroll_period_id ||
                      '');
    fnd_file.put_line(fnd_file.output,
                      '' || V_organization_name ||
                      '');
    fnd_file.put_line(fnd_file.output,
                      '' || 'NULL' ||
                      '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_THRESHOLD || '');
    fnd_file.put_line(fnd_file.output,
                      '' || P_PERCENT || '');
  
    For I_EMPLOYEE_REC IN EMP_PAY_DIFF_CUR(P_Payroll_id,
                                           V_PREV_PAY_PERIOD_ID,
                                           P_organization_id) loop
    
      IF (I_EMPLOYEE_REC.GAP >= P_threshold or
         I_EMPLOYEE_REC.GAP_PERCENT >= P_PERCENT) THEN
      
        IF I_EMPLOYEE_REC.TIME_PERIOD_ID =
           I_EMPLOYEE_REC.Current_Payperiod_Id THEN
          V_TIME_PERIOD := '2.CURRENT';
        ELSIF I_EMPLOYEE_REC.TIME_PERIOD_ID =
              I_EMPLOYEE_REC.Previous_payperiod_id THEN
          V_time_period := '1.PREVIOUS';
        END IF;
      
        FOR I_ELE_BAL_REC IN Elements_balance_VALUE_CUR(I_EMPLOYEE_REC.payroll_name,
                                                        I_EMPLOYEE_REC.TIME_PERIOD_ID,
                                                        I_EMPLOYEE_REC.Assignment_Id,
                                                        I_EMPLOYEE_REC.Business_Group_Id,
                                                        I_EMPLOYEE_REC.END_DATE) loop
        
          fnd_file.put_line(fnd_file.output, '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Employee_Number ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.Employee_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.activity_centre ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.ORGANIZATION ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.national_identifier ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.grade_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.location ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.NEW_HIRE_DATE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.TERMINATION_DATE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Payroll_action_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.Payroll_Name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.Period_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.time_period_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Assignment_action_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.business_group_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.END_DATE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.ASSIGNMENT_ID ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.UNIT_Name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_ELE_BAL_REC.UNIT_Value_name ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.ELEMENT_UNIT ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.UNIT_RESULT ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_ELE_BAL_REC.ELE_BAL_CODE ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' || V_TIME_PERIOD || '-' ||
                            I_EMPLOYEE_REC.PERIOD_NAME || '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.payroll_id ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Tot_Pay_Bal_Current ||
                            '');
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.Tot_Pay_Bal_Previous ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.GAP || '');
          fnd_file.put_line(fnd_file.output,
                            '' || I_employee_rec.GAP_PERCENT ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.CURRENT_PAYROLL ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.CURRENT_PAYPERIOD_ID ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.CURRENT_PAYPERIOD_END_DATE ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.PREVIOUS_PAYROLL ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.PREVIOUS_PAYPERIOD_ID ||
                            '');
        
          fnd_file.put_line(fnd_file.output,
                            '' ||
                            I_employee_rec.PREVIOUS_PAYPERIOD_END_DATE ||
                            '');
        
          fnd_file.put_line(fnd_file.output, '');
        
        END LOOP;
      
        IF I_EMPLOYEE_REC.TIME_PERIOD_ID =
           I_EMPLOYEE_REC.Current_Payperiod_Id THEN
        
          BEGIN
          
            FOR I_EMP_DIFF IN Elements_balance_VALUE_DIFF(I_EMPLOYEE_REC.payroll_name,
                                                          I_EMPLOYEE_REC.TIME_PERIOD_ID,
                                                          I_EMPLOYEE_REC.PREVIOUS_PAYPERIOD_ID,
                                                          I_EMPLOYEE_REC.Assignment_Id,
                                                          I_EMPLOYEE_REC.Business_Group_Id,
                                                          I_EMPLOYEE_REC.END_DATE,
                                                          I_EMPLOYEE_REC.PREVIOUS_PAYPERIOD_END_DATE,
                                                          I_EMPLOYEE_REC.GAP_PERCENT) LOOP
            
              fnd_file.put_line(fnd_file.output, '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Employee_Number ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Employee_name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.activity_centre ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.ORGANIZATION ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.national_identifier ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.grade_name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.location ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.NEW_HIRE_DATE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.TERMINATION_DATE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Payroll_action_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Payroll_Name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Period_name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.time_period_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Assignment_action_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.business_group_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.END_DATE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.ASSIGNMENT_ID ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.UNIT_Name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_EMP_DIFF.UNIT_Value_Name ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.ELEMENT_UNIT ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.UNIT_RESULT ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_EMP_DIFF.ELE_BAL_CODE ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || '3.GAP-GAP' ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.payroll_id ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Tot_Pay_Bal_Current ||
                                '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.Tot_Pay_Bal_Previous ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' || I_employee_rec.GAP || '');
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.GAP_PERCENT ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.CURRENT_PAYROLL ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.CURRENT_PAYPERIOD_ID ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.CURRENT_PAYPERIOD_END_DATE ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.PREVIOUS_PAYROLL ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.PREVIOUS_PAYPERIOD_ID ||
                                '');
            
              fnd_file.put_line(fnd_file.output,
                                '' ||
                                I_employee_rec.PREVIOUS_PAYPERIOD_END_DATE ||
                                '');
            
              fnd_file.put_line(fnd_file.output, '');
            
            END LOOP;
          
          END;
        
        END IF;
      
      END IF;
    
    END LOOP;
  
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output,
                      '' || V_payroll_name ||
                      '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output,
                      '' || V_period_name ||
                      '');
    fnd_file.put_line(fnd_file.output, '');
    fnd_file.put_line(fnd_file.output, '');
  
  EXCEPTION
    WHEN e_error_exception THEN
    
      fnd_file.put_line(fnd_file.log, V_error_msg);
      ERRBUF  := V_error_msg;
      RETCODE := 2;
    
    WHEN OTHERS THEN
    
      fnd_file.put_line(fnd_file.log, SQLERRM);
      ERRBUF  := SQLERRM;
      RETCODE := 2;
    
  END;

END;
/



沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569