[Home] [Help]
PACKAGE BODY: APPS.CSD_MIG_SLABS_PKG
Source
1 PACKAGE BODY csd_mig_slabs_pkg AS
2 /* $Header: csdslabb.pls 115.4 2002/11/19 02:00:00 swai noship $*/
3
4 /*Get a value from the sql statement passed*/
5 FUNCTION get_sql_value(sqlstr IN VARCHAR) RETURN NUMBER IS
6 l_cursor_handle INTEGER;
7 l_value NUMBER;
8 l_n_temp NUMBER;
9
10 BEGIN
11 l_cursor_handle := dbms_sql.open_cursor;
12
13 begin
14 dbms_sql.parse(l_cursor_handle,sqlstr,dbms_sql.native);
15 exception
16 when others then
17 raise_application_error(-20001,'error while parsing sql:'||sqlerrm);
18 end;
19
20 DBMS_SQL.DEFINE_COLUMN(l_cursor_handle,1,l_value);
21 l_n_temp := dbms_sql.execute(l_cursor_handle);
22
23 IF dbms_sql.fetch_rows(l_cursor_handle) > 0 THEN
24 dbms_sql.column_value(l_cursor_handle,1,l_value);
25 END IF;
26 --Close cursor
27 DBMS_SQL.close_cursor(l_cursor_handle);
28 return l_value;
29
30 END get_sql_value;
31
32
33 /* Get min and max value of next available slab*/
34 PROCEDURE get_table_slabs
35 ( p_table_name IN VARCHAR,
36 p_module in VARCHAR,
37 p_slab_number IN NUMBER,
38 x_start_slab OUT NOCOPY NUMBER,
39 x_end_slab OUT NOCOPY NUMBER) IS
40
41 CURSOR upg_slabs IS
42 select slab_start, slab_end
43 FROM csd_upg_slabs
44 where slab_number = p_slab_number
45 and source_table = p_table_name
46 and module = p_module;
47
48 BEGIN
49 For r in upg_slabs loop
50 x_start_slab := r.slab_start;
51 x_end_slab := r.slab_end;
52 exit when true;
53 end loop;
54
55 END get_table_slabs;
56
57
58 /*Create slabs for a table/view */
59 PROCEDURE create_table_slabs
60 ( p_table_name IN VARCHAR,
61 p_module in VARCHAR,
62 p_min_sql IN VARCHAR,
63 p_max_sql IN VARCHAR,
64 p_no_of_slabs IN NUMBER ,
65 p_min_slab_size IN NUMBER
66 ) IS
67
68 l_min NUMBER; --min pk
69 l_max NUMBER; -- max pk
70 l_slab_size NUMBER; --size of the slab
71 l_slab_num NUMBER;
72 l_last_slab NUMBER; --end of last slab
73 l_max_slab NUMBER;
74 l_temp NUMBER;
75
76 begin
77
78 delete from csd_upg_slabs
79 where source_table = p_table_name
80 and module = p_module;
81
82 l_min := get_sql_value(p_min_sql);
83 l_max := get_sql_value(p_max_sql);
84 l_slab_size := ceil((l_max-l_min+1) / p_no_of_slabs);
85
86 --if calculated slab size is smaller then use the min slab size
87
88 if l_slab_size < p_min_slab_size then
89 l_slab_size := p_min_slab_size;
90 end if;
91
92 l_last_slab := l_min - 1;
93 l_slab_num := 0;
94
95 /*if no available in the table then dont create slabs*/
96 if l_min is not null then
97 loop
98 l_slab_num := l_slab_num + 1;
99 l_max_slab := l_last_slab + l_slab_size;
100 if l_max_slab > l_max then
101 l_max_slab := l_max;
102 end if;
103 INSERT INTO CSD_UPG_SLABS
104 (SOURCE_TABLE,
105 MODULE,
106 SLAB_NUMBER,
107 SLAB_START,
108 SLAB_END,
109 CREATED_BY,
110 CREATED_ON)
111 VALUES (p_table_name,
112 p_module,
113 l_slab_num,
114 l_last_slab + 1,
115 l_max_slab,
116 fnd_global.user_id,
117 sysdate);
118 l_last_slab := l_last_slab + l_slab_size;
119 exit when l_last_slab >= l_max ;
120 end loop;
121 end if;
122
123 end create_table_slabs ;
124
125 /*Create slabs for a table/view */
126 PROCEDURE create_table_slabs
127 ( p_table_name IN VARCHAR,
128 p_module IN VARCHAR,
129 p_pk_column IN VARCHAR,
130 p_no_of_slabs IN NUMBER ,
131 p_min_slab_size IN NUMBER
132 ) IS
133
134 l_min_str varchar2(250);
135 l_max_str varchar2(250);
136
137 Begin
138 l_min_str := 'select min('||p_pk_column||') from '||p_table_name;
139 l_max_str := 'select max('||p_pk_column||') from '||p_table_name;
140
141 create_table_slabs
142 (p_table_name => p_table_name,
143 p_module => p_module,
144 p_min_sql=>l_min_str,
145 p_max_sql=>l_max_str,
146 p_no_of_slabs => p_no_of_slabs,
147 p_min_slab_size => p_min_slab_size);
148
149 end create_table_slabs;
150
151 end csd_mig_slabs_pkg;