[Home] [Help]
4: PROCEDURE GET_WB_SERVER_LIST (
5: p_language IN varchar2,
6: p_order_by IN varchar2,
7: p_asc IN varchar2,
8: x_wb_servers_list OUT NOCOPY SYSTEM.IEB_WB_SERVERS_DATA_NST,
9: x_return_status OUT NOCOPY VARCHAR2)
10: AS
11:
12: v_cursorID INTEGER;
58: v_selectStmt := 'select WBSVR_ID, WB_SERVER_NAME, LOG_FILE_NAME,
59: LOG_DBC_FILE_NAME, CCI_DBC_FILE_NAME, STAT_DUMP_SUNDAY_Y_N,
60: STAT_DUMP_MONDAY_Y_N, STAT_DUMP_TUESDAY_Y_N,
61: STAT_DUMP_WEDNESDAY_Y_N, DESCRIPTION
62: from IEB_WB_SERVERS
63: order by ' || p_order_by || ' ' || p_asc;
64:
65: DBMS_SQL.PARSE(v_cursorID, v_selectStmt, DBMS_SQL.V7);
66:
76: DBMS_SQL.DEFINE_COLUMN(v_cursorID, 10, v_desc, 240);
77:
78: v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
79:
80: x_wb_servers_list := SYSTEM.IEB_WB_SERVERS_DATA_NST();
81: loop
82: if DBMS_SQL.FETCH_ROWS(v_cursorID) = 0 then
83: exit;
84: end if;
94: DBMS_SQL.COLUMN_VALUE(v_cursorID, 9, v_wednesday);
95: DBMS_SQL.COLUMN_VALUE(v_cursorID, 10, v_desc);
96:
97: x_wb_servers_list.EXTEND;
98: x_wb_servers_list(x_wb_servers_list.LAST) := SYSTEM.IEB_WB_SERVERS_DATA_OBJ(v_serverId,
99: v_serverName,
100: v_fileName, v_logDbcFileName,
101: v_cciDbcFileName, v_sunday,
102: v_monday, v_tuesday, v_wednesday, v_thursday,
137: cursor c1 is
138: select *
139: from IEO_SVR_SERVERS
140: where type_id = 10020
141: and not exists (select 'x' from IEB_WB_SERVERS SRV, IEB_WB_SVC_CATS SVC
142: where SRV.WB_SERVER_NAME = SERVER_NAME AND
143: SVC.WBSVR_WBSVR_ID = SRV.WBSVR_ID );
144: l_wbsvr_id number(15,0);
145: l_ieo_svr_id number(15,0);
151: -- servers that no longer exist in ieo schema, and the other is to
152: -- create data for new servers created in ieo schema
153:
154: -- this takes care of deleting data from ieb schema
155: DELETE FROM ieb_wb_servers WHERE wb_server_name NOT IN
156: (SELECT server_name FROM ieo_svr_servers WHERE type_id = 10020);
157:
158: if (sql%notfound) then
159: null; -- not finding any such iebs is good
159: null; -- not finding any such iebs is good
160: else
161: -- delete the service categories of the deleted servers
162: DELETE FROM ieb_wb_svc_cats WHERE wbsvr_wbsvr_id NOT IN
163: (SELECT wbsvr_id FROM ieb_wb_servers);
164:
165: IF (sql%notfound) then
166: null; -- not finding any data is not too good because there should be
167: -- default service categories here; but no problem, may be the
187: l_wbsvr_id := NULL;
188: l_ieo_svr_id := NULL;
189: begin
190: select wbsvr_id, ieo_server_id into l_wbsvr_id, l_ieo_svr_id
191: from IEB_WB_SERVERS
192: where WB_server_name = c1_rec.server_name;
193: exception
194: when others then
195: l_wbsvr_id := NULL;
197: end;
198:
199: if ( l_wbsvr_id IS NULL ) then
200: select IEB_WB_SERVER_S1.NEXTVAL into l_wbsvr_id from sys.dual;
201: insert into IEB_WB_SERVERS
202: (wbsvr_id,
203: created_by,
204: creation_date,
205: last_updated_by,
265: 0,
266: NULL,
267: c1_rec.server_id);
268: else if ( l_ieo_svr_id IS NULL ) then
269: Update IEB_WB_SERVERS
270: Set IEO_SERVER_ID = c1_rec.server_id
271: Where WB_server_name = c1_rec.server_name;
272: end if;
273: end if;
277: exit when c1%notfound;
278: commit;
279: end loop;
280:
281: update ieb_wb_servers a
282: set a.IEO_SERVER_ID = (select b.server_id from ieo_svr_servers b where a.WB_SERVER_NAME = b.SERVER_NAME);
283: if (sql%notfound) then
284: null;
285: end if;
412: l_default_count number(5);
413: l_count number(5);
414:
415: cursor c1 is
416: select * from ieb_wb_servers WHERE wb_server_name IN
417: (SELECT server_name FROM ieo_svr_servers WHERE type_id = 10020);
418:
419:
420: cursor c2 is