1 PACKAGE BODY GMA_PURGE_VALIDATE AS
2 /* $Header: GMAPRGVB.pls 115.8 2004/02/12 21:30:52 kmoizudd ship $ */
3
4 FUNCTION is_table(p_purge_id sy_purg_mst.purge_id%TYPE,
5 p_tablename user_tables.table_name%TYPE)
6 RETURN BOOLEAN IS
7 -- check to make sure given name IS a real table
8 -- Added one more cursor parameter owner for UDTF standards by KH 3431801
9
10 CURSOR l_table_cur(c_tablename user_tables.table_name%TYPE,
11 c_schema_owner all_tables.owner%TYPE) IS
12 SELECT 1
13 FROM ALL_TABLES
14 --MADE by Khaja added one OWNER on where clause for UDTF bug fix #3431801
15 --MADE by Khaja FROM user_tables
16 WHERE table_name = upper(c_tablename)
17 and owner=c_schema_owner;
18
19 l_scratch NUMBER(1);
20
21 -- Defining these variables for UDTF bug 3431801 to make use of FND_INSTALLATION api by Khaja
22 v_schema varchar2(100);
23 v_status varchar2(100);
24 v_industry varchar2(100);
25
26 l_schema_owner varchar2(2000);
27 l_tablefound_inschema boolean:=FALSE;
28
29 BEGIN
30
31 -- Fetch all OPM product schema names for UDTF bug 34318001
32 -- Make sure to even append in the APPS schema for temporary table validation
33 -- added by Khaja
34
35 FOR rec in (SELECT APPLICATION_SHORT_NAME from fnd_application
36 where application_id between 550 and 558
37 UNION
38 -- This query given by Pete from AD for UDTF APPS schema retrieve
39 SELECT oracle_username application_short_name FROM fnd_oracle_userid
40 WHERE read_only_flag = 'U')
41 LOOP
42
43 if rec.application_short_name='APPS' then
44 l_schema_owner:=rec.application_short_name;
45 else
46 if fnd_installation.GET_APP_INFO(rec.application_short_name,v_status,v_industry,v_schema) then
47 l_schema_owner:=v_schema;
48 end if;
49 end if;
50
51
52 OPEN l_table_cur(p_tablename,l_schema_owner);
53 FETCH l_table_cur INTO l_scratch;
54
55 IF (l_table_cur%FOUND) THEN
56 l_tablefound_inschema:=TRUE;
57 -- exit the LOOP if table is found in any first schema , not to continue further
58 EXIT;
59 END IF;
60
61 CLOSE l_table_cur;
62
63 END LOOP;
64
65 if not l_tablefound_inschema then
66 RAISE NO_DATA_FOUND;
67 end if;
68
69
70 RETURN TRUE; -- table exists
71
72
73 EXCEPTION
74
75 WHEN NO_DATA_FOUND THEN
76 -- removed this CLOSE cursor stmt since CURSOR never gets opened for exception tables
77 -- CLOSE l_table_cur;
78 if upper(p_tablename) in ('GME_BT_STEP_ACTIVITIES',
79 'GME_BT_STEP_TRANSFERS',
80 'GME_BT_STEP_DEPENDENCS',
81 'GME_BT_STEP_RESOURCES',
82 'GME_BT_ST_RSRC_SUMMARY',
83 'GMD_COMP_SPEC_DISP',
84 'GMD_COMP_RESULTS',
85 'GMD_COMP_RESULT_ASSOC') then
86 RETURN TRUE;
87 else
88 RETURN FALSE; -- Table does not exist
89 end if;
90
91 WHEN OTHERS THEN
92 GMA_PURGE_UTILITIES.printlong(p_purge_id,
93 'Problem raised in GMA_PURGE_VALIDATE.tableexists.');
94 GMA_PURGE_UTILITIES.printlong(p_purge_id,
95 'Unhandled EXCEPTION - ' || sqlerrm);
96 RAISE;
97
98 END is_table;
99
100 /***********************************************************/
101
102 FUNCTION is_tablespace
103 (p_purge_id sy_purg_mst.purge_id%TYPE,
104 p_tablespace_name IN user_tablespaces.tablespace_name%TYPE)
105 RETURN BOOLEAN IS
106 -- This function takes a name and checks the user_tablespaces
107 -- view to make sure the parameter is a valid tablespace name.
108 -- Return TRUE if the tablespace exists, FALSE otherwise.
109
110 CURSOR l_validate_tablespace_cur
111 (c_tablespace_name IN user_tablespaces.tablespace_name%TYPE) IS
112 select 'X'
113 from user_tablespaces
114 where tablespace_name = upper(c_tablespace_name);
115
116 l_tablespace_exists CHAR(1);
117
118 BEGIN
119
120 -- check for the tablespace in the data dictionary
121 OPEN l_validate_tablespace_cur(p_tablespace_name);
122 FETCH l_validate_tablespace_cur INTO l_tablespace_exists;
123 IF l_validate_tablespace_cur%NOTFOUND THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126 CLOSE l_validate_tablespace_cur;
127
128 RETURN TRUE; -- Tablespace exists
129
130 EXCEPTION
131
132 WHEN NO_DATA_FOUND THEN
133 CLOSE l_validate_tablespace_cur;
134 RETURN FALSE; -- The tablespace does not exist
135
136 WHEN OTHERS THEN
137 GMA_PURGE_UTILITIES.printlong(p_purge_id,
138 'Problem raised in GMA_PURGE_VALIDATE.doarchive.');
139 GMA_PURGE_UTILITIES.printlong(p_purge_id,
140 'Unhandled EXCEPTION - ' || sqlerrm);
141 RAISE;
142
143 END is_tablespace;
144
145 /***********************************************************/
146
147 PROCEDURE checksql(p_purge_id sy_purg_mst.purge_id%TYPE,
148 p_purge_type sy_purg_def.purge_type%TYPE) IS
149 -- used for debugging... checks sql syntax
150
151 CURSOR l_sql_cur(c_purge_type sy_purg_def.purge_type%TYPE) IS
152 SELECT sqlstatement
153 FROM sy_purg_def SD
154 WHERE SD.purge_type = c_purge_type;
155
156 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
157 l_cursor INTEGER;
158
159 BEGIN
160
161 OPEN l_sql_cur(p_purge_type);
162 FETCH l_sql_cur INTO l_sqlstatement;
163 CLOSE l_sql_cur;
164
165 l_cursor := DBMS_SQL.OPEN_CURSOR;
166
167 l_sqlstatement := l_sqlstatement || ' and rownum < 1;';
168 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
169
170 -- Just parse it, don't run it
171 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
172
173 DBMS_SQL.CLOSE_CURSOR(l_cursor);
174
175 EXCEPTION
176
177 WHEN OTHERS THEN
178 GMA_PURGE_UTILITIES.printlong(p_purge_id,
179 'Problem raised in GMA_PURGE_VALIDATE.checksql.');
180 GMA_PURGE_UTILITIES.printlong(p_purge_id,
181 'Unhandled EXCEPTION - ' || sqlerrm);
182 RAISE;
183
184 END checksql;
185
186 END GMA_PURGE_VALIDATE;
[Home] [Help]