Skip to Main Content
Forums

Database Software

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!

Default Port Range on Exadata Database Machine

mafsarJun 17 2020

Is there any default port range on Exadata database machine for newer Image versions [Example: 18.1.28.0.0.200512] ?

(Doc ID 1532838.1)--This document lists some port numbers but this document is archived and is marked for 11.2.

The requirement is we wanted to start a port and reserve/block it for a specific third party application so it is not to be used by any other process/application. For this we are looking for a port which is not any default port or reserved for Exadata Image/Software that it needs to run. Please let me know if you have any new port range for newer exadata versions.

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 Jun 17 2020
#exadata, #performance-availability
0 comments
218 views