Skip to Main Content
Forums

Enterprise Manager

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create a custom page in Oracle Enterprise Manager

3387302May 26 2020 — edited May 27 2020

Hi,

We have multiple(12) production weblogic servers that are being monitoring by OEM. In order to check the JVM usage of each managed server, currently we have to traverse to each page in OEM. Instead if we are able to create a custom page where it displays the JVM metric of the interested servers, then it would help us like a one page dashboard. We would need the whole graph to be visible for the last 24hour period and hence the report would not help here.

Can someone guide on how to create a custom page that would house various available metric information in one customized page.

Thanks,

Vijay Francis.

Comments

Solomon Yakobson
Answer

You can use VARRAY:

create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
is
lv_count number(10);
begin
for v_i in 1..iv_req_id.count loop
select count(1) into lv_count from staging where req_id = iv_req_id(v_i);
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = iv_req_id(v_i)) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id
   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
end loop;
commit;
end sp_main_target;
/

Procedure created.

SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
Insertion into target table
Insertion into target table
Request id is not available


PL/SQL procedure successfully completed.


SQL> select * from target_tab;


    KEY_ID     INV_ID I_NAME                             REQ_ID
---------- ---------- ------------------------------ ----------
       110       1001 test1                                   1
       111       1002 test2                                   2


SQL>

SY.

Marked as Answer by Albert Chao · Oct 25 2021
Albert Chao

@solomon-yakobson Can we do this using for loop cursor?

Solomon Yakobson

We could but why call SQL from PL/SQL if all can be done in PL/SQL?

create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
is
lv_count number(10);
begin
for v_rec in (select * from table(iv_req_id)) loop
select count(1) into lv_count from staging where req_id = v_rec.column_value;
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = v_rec.column_value) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id
   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
end loop;
commit;
end sp_main_target;
/

Procedure created.

SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
Insertion into target table
Insertion into target table
Request id is not available

PL/SQL procedure successfully completed.

SQL> select * from target_tab;

    KEY_ID     INV_ID I_NAME                             REQ_ID
---------- ---------- ------------------------------ ----------
       110       1001 test1                                   1
       111       1002 test2                                   2

SQL>

SY.

Albert Chao

@solomon-yakobson Actually this stored procedure I am going to invoke from the front end so will this work? Front end wherein I will provide the request-id and process will happen. Or do we have to use connect by caluse for comma-separated inputs?

Solomon Yakobson

Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.
SY.

Albert Chao

@solomon-yakobson Thanks. Got it

1 - 6

Post Details

Added on May 26 2020
1 comment
224 views