CREATE OR REPLACE PROCEDURE ABC AS (TAB_NAME1 NVARCHAR2, TAB_NAME2 NVARCHAR2)
AS 
TAB_NAME1_CNT NUMBER := 0;
TAB_NAME2_CNT NUMBER := 0;

BEGIN 
SELECT COUNT(*) INTO TAB_NAME1_CNT FROM TAB_NAME1 @dblink1;
SELECT COUNT(*) INTO TAB_NAME2_CNT FROM TAB_NAME2 @dblink1;

INSERT INTO TOT_TAB_CNT (SNO,TABLE1_CNT, TABLE2_CNT)
VALUES (1,TAB_NAME1_CNT,TAB_NAME2_CNT);
COMMIT;
END;

DECLARE
  tab_name1     VARCHAR2(30) := 'MY_LINKED_TABLE';
  tab_name1_cnt NUMBER := 0;
  cur_data1     SYS_REFCURSOR;

BEGIN 
 OPEN  cur_data1 FOR 'SELECT count(*) FROM '||tab_name1||'@dblink1';
 FETCH cur_data1 INTO tab_name1_cnt;
 dbms_output.put_line(tab_name1_cnt);
END;

CREATE OR REPLACE PROCEDURE ABC AS (TAB_NAME1 NVARCHAR2, TAB_NAME2 NVARCHAR2)
AS 
TAB_NAME1_CNT NUMBER ;
TAB_NAME2_CNT NUMBER ;
ABC VARCHAR2(50);
DEF VARCHAR2(50);

BEGIN 
ABC : = 'SELECT COUNT(*)  FROM' || TAB_NAME1||  '@dblink1';

EXECUTE IMMEDIATE (ABC) INTO TAB_NAME1_CNT;
DEF:= 'SELECT COUNT(*) FROM' || TAB_NAME2 ||  '@dblink1';
EXECUTE IMMEDIATE (DEF) INTO TAB_NAME2_CNT ;

INSERT INTO TOT_TAB_CNT (SNO,TABLE1_CNT, TABLE2_CNT)
VALUES (1,TAB_NAME1_CNT,TAB_NAME2_CNT);
COMMIT;
END;