Thanks Chris for your response. Your query works.

I added some little cosmetic changes if anyone else wants to use the query:

SET SERVEROUTPUT ON;
DECLARE

l_session_name VARCHAR2(30) := 'UNDEFINED';
l_owner VARCHAR2(30) := 'SCOTT';
l_cnt NUMBER := NULL;
sql_stmt VARCHAR2(1000) := NULL;

BEGIN

SELECT SYS_CONTEXT('userenv','session_user') INTO l_session_name FROM DUAL;
DBMS_OUTPUT.PUT_LINE('CURRENT SESSION USER IS: '||l_session_name);
DBMS_OUTPUT.PUT_LINE('CHECKING COUNTS AGAINST: '||l_owner);
SELECT LENGTH(l_owner) INTO l_cnt FROM DUAL;

dbms_output.put_line (chr(13)||chr(10)||rpad('TABLE OWNER', l_cnt+1, ' ') || rpad('TABLE NAME', 30, ' ')|| ': ' || 'ROW COUNT' || chr(13)||chr(10));
l_cnt := null;
FOR i IN (SELECT owner, object_name FROM all_objects WHERE object_type = 'TABLE' AND owner = l_owner AND secondary = 'N' ORDER BY object_name)
loop
sql_stmt := 'SELECT COUNT(1) FROM ' || i.owner ||'.' || i.object_name;
EXECUTE IMMEDIATE sql_stmt INTO l_cnt;
dbms_output.put_line (i.owner || '.' || rpad(i.object_name, 30, ' ') || ': ' || l_cnt);
END loop;
END;
/

When i try to do it like this and assign count to v_count_of_rows variable. It gives me error.

</>
ORA-00905: missing keyword
ORA-06512: at "LBI_OGG_MK.PRC_VRG_MFS_REGISTRATIONS", line 90
ORA-06512: at line 2
</>
v_partition_name variable has the partition name from where i want to query data.

</>
v_SQL := 'SELECT count(*) into v_count_of_rows
FROM
lbi_ogg_mk.t_o_audit_identity partition ( ' || v_partition_name || ') a
LEFT JOIN lbi_edm_mk.t_e_customer cust
ON cust.customer_id = a.on_iden_id
LEFT JOIN lbi_ogg_mk.T_O_SCY_CREDENTIAL scy
on scy.identity_id = cust.customer_id
WHERE
end_time >= to_date(to_char(SYSDATE - 1,''yyyymmdd'') || ''000000'', ''yyyymmddhh24miss'')
AND end_time < to_date(to_char(SYSDATE - 1,''yyyymmdd'') || ''235959'', ''yyyymmddhh24miss'')
AND a.event_type IN (
''CREATE_CUSTOMER'',
''RESET_CUSTOMER_PIN'',
''CHANGE_CUSTOMER_PIN''
)'
;
dbms_output.put_line (v_SQL);
execute immediate (v_SQL);
</>

I got the answer right after posting the query.


execute immediate v_sql into v_type;



Cheers. Thanks Chris