SQL to get DB Server Details

SELECT (SELECT UTL_INADDR.get_host_address FROM DUAL) local_host_address
     , (SELECT UTL_INADDR.get_host_name( (SELECT UTL_INADDR.get_host_address FROM DUAL)) FROM DUAL) host_name
     , (SELECT UTL_INADDR.get_host_address( 'google.com') FROM DUAL) host_address
     , (SELECT UTL_INADDR.get_host_name FROM DUAL) local_host_name
     , (SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL) host_terminal
     , (SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL) host
     , (SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL) ip_Address
     , (SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') FROM DUAL) server_host
     , (SELECT host_name FROM v$instance) local_host_name_v$instance
     , (SELECT LOWER(USER|| '@' || SYS_CONTEXT('USERENV', 'INSTANCE_NAME')) x FROM DUAL) instance_name
  FROM DUAL

Build Dynamic block for DML operations....

PROCEDURE execute_dml_dynamically(
          p_table_name         IN VARCHAR2
        , p_where_column       IN VARCHAR2
        , p_where_column_val   IN INTEGER
        , p_upd_col_name       IN VARCHAR2
        , p_set_value          IN VARCHAR2)
IS
   l_cursor            PLS_INTEGER := DBMS_SQL.open_cursor;
   l_execute           PLS_INTEGER;
BEGIN
   DBMS_SQL.parse(l_cursor
                ,    'BEGIN update '
                  || p_table_name
                  || ' set '
                  || p_upd_col_name
                  || ' = '
                  || p_set_value
                  || ' where '
                  || p_where_column
                  || ' = '
                  || p_where_column_val
                  || '; END;'
                , DBMS_SQL.native);

   l_execute       := DBMS_SQL.execute( l_cursor);

   IF l_execute > 0
   THEN
      DBMS_OUTPUT.put_line(   'Value of '
                           || p_upd_col_name
                           || ' updated to '
                           || p_set_value);
   ELSE
      DBMS_OUTPUT.put_line(   'Update of '
                           || p_upd_col_name
                           || '  to '
                           || p_set_value
                           || ' failed.');
   END IF;

   DBMS_SQL.close_cursor( l_cursor);
END;
/

How to use Limits for Bulk Collect (PL/SQL Collections)

DECLARE
   CURSOR c_emp_cur
   IS
      SELECT * FROM emp;

   TYPE emp_cur_tbl_typ IS TABLE OF c_emp_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   emp_cur_tbl                                  emp_cur_tbl_typ;
BEGIN
   OPEN c_emp_cur;

   LOOP
      FETCH c_emp_cur BULK COLLECT INTO emp_cur_tbl LIMIT 100;

      EXIT WHEN c_emp_cur%NOTFOUND;

      FOR indx IN 1 .. emp_cur_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line( emp_cur_tbl( indx));
      END LOOP;
   END LOOP;

   CLOSE c_emp_cur;
END ;

How to assign the values to a DB Collection (Array ) When it is defined as Out Parameter in the Procedure/Functions

-- Define the Object Types , Collections as Below 

CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_OBJ AS OBJECT(
   SERIAL_NUMBER                   VARCHAR2(240)
 , STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SERIAL_NUMBER_OBJ
IS
   STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ
   IS
   BEGIN
      RETURN XX_SERIAL_NUMBER_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_TBL AS TABLE OF XX_SERIAL_NUMBER_OBJ;
/
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , SERIAL_NUMBER           VARCHAR2(240)
 , PROCESS_STATUS       VARCHAR2(40)
 , ERROR_ID               VARCHAR2(240)
 , ERROR_MESSAGE           VARCHAR2(2000)
 , STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_ORG_REASSIGN_STATUS_OBJ
IS
   STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ
   IS
   BEGIN
      RETURN XX_ORG_REASSIGN_STATUS_OBJ(NULL
                                        , NULL
                                        , NULL
                                        , NULL
                                        , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_TBL AS TABLE OF XX_ORG_REASSIGN_STATUS_OBJ;

--Define Procedure to assign the values to the Collection type

PRocedure xx_test_proc (
  p_serial_number_tbl IN xxrb_serial_number_tbl
, x_org_reassignment_status_tbl OUT NOCOPY xxrb_org_reassign_status_tbl)
IS 
--Define Local variable for the Object Type as below
org_reassign_status_obj xx_org_reassign_status_obj;

--Define Local variable for the Collection Type as below
xx_org_reassign_sts_tbl xx_org_reassign_status_tbl;
BEGIN
-- Initialize the local object type and DB table type 
org_reassign_status_obj := 

xx_org_reassign_status_obj.org_reassign_status_obj;

xx_org_reassign_sts_tbl:= xx_org_reassign_status_tbl( org_reassign_status_obj);

For i in p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST 
LOOP

-- Write your core logic which will derive the values for the Columns which needs to assign to the object type as shown below
org_reassign_status_obj.org_id       := 102;
org_reassign_status_obj.serial_number:= 'XYZ';
org_reassign_status_obj.process_status := 'S';
org_reassign_status_obj.error_id       := 'ORA-123';

-- Use the extend Function foe the Array as shown below
 
xx_org_reassign_sts_tbl.EXTEND;

-- Assign the Local record type declared in the declare section as -- below 

xx_org_reassign_sts_tbl( xx_org_reassign_sts_tbl.COUNT) := org_reassign_status_obj;

END LOOP;

x_org_reassignment_status_tbl :=
xx_org_reassign_sts_tbl

EXCEPTION
WHEN OTHERS 
THEN
DBMS_OUTPUT.PUT_LINE('UNEXP_ERROR : '||SUBSTR(SQLERRM, 1, 250));
END;

How to Invoke the Database Object Types in Anonymous Blocks

 -- Create DB Object Types and Collections

CREATE OR REPLACE TYPE XX_SYNC_ORG_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJECT
      RETURN XX_CC_SYNC_ORG_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SYNC_ORG_OBJ
IS
   STATIC FUNCTION GET_OBJECT
      RETURN XX_SYNC_ORG_OBJ
   IS
   BEGIN
      RETURN XX_SYNC_ORG_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SYNC_ORG_TBL AS TABLE OF XX_SYNC_ORG_OBJ;
/
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_EBS_SYNC_OBJ AS OBJECT(
   INSTANCE_REFERENCE       VARCHAR2(50)
 , SERIAL_NUMBER            VARCHAR2(240)
 , SERVICE_START_DATE       DATE
 , SERVICE_END_DATE         DATE
 , SUPPORT_LEVEL            VARCHAR2(50)
 , PARTNER                  VARCHAR2(1)
 , NODE_CATEGORY            VARCHAR2(50)
 , ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_EBS_SYNC_OBJ
IS
   STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ
   IS
   BEGIN
      RETURN XX_EBS_SYNC_OBJ(NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_EBS_SYNC_TBL AS TABLE OF XX_EBS_SYNC_OBJ;
/
--  Invoke it in anonymous Block...

DECLARE
   p_cc_org_tbl   xx_cc_sync_org_tbl := xx_cc_sync_org_tbl();
   x_ebs_sync_tbl xx_ebs_cc_sync_tbl;
   l_index           NUMBER := 1;
BEGIN
   -- Use Below Method When Using Hard coding
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 1):= xx_cc_sync_org_obj( '1212121');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 2):= xx_cc_sync_org_obj( '123123213213');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 3):= xx_cc_sync_org_obj( '1231232132131');

   -- Use Below Method When Using Dynamic fetch
    FOR i IN (SELECT DISTINCT attribute21
               FROM csi_item_instances
              WHERE NVL(active_end_date, SYSDATE) >= SYSDATE
                AND attribute21 IS NOT NULL)
   LOOP
     p_cc_org_tbl.EXTEND;
     p_cc_org_tbl( l_index) := xx_cc_sync_org_obj( i.attribute21);
     l_index                                   := l_index + 1;
   END LOOP;

   DBMS_OUTPUT.put_line(   'Table Count : '
                        || p_cc_org_tbl.COUNT);
 
   ebs_ks_cc_sync(p_cc_org_tbl, x_ebs_cc_sync_tbl);
END;