Procedure to Create Query for Getting a Table data from Multiple Schemas

 DECLARE

QRY VARCHAR2(4000);

BEGIN

FOR I IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE '%2526%' AND USERNAME NOT IN ('AI2526'))

LOOP

QRY := 'SELECT SUBSTR(A1.CODE,1,2) AS LVL1_CODE,L1.NAME AS LVL1_NAME,SUBSTR(A1.CODE,1,4) AS LVL2_CODE,L2.NAME AS LVL2_NAME,SUBSTR(A1.CODE,1,6) AS LVL3_CODE,L3.NAME AS LVL3_NAME,A1.CODE AS LAST_CODE,A1.NAME AS LAST_NAME,A1.DR,A1.CR,A1.CNIC_NTN_FBR,A1.CITY,A1.ADDR1,A1.ADDR2, '''||I.USERNAME||''' AS SCHEMA_NAME FROM  '||I.USERNAME||'.'||'ACCOUNT'||' A1 LEFT JOIN '||I.USERNAME||'.'||'ACCOUNT'||' L1 ON L1.CODE = SUBSTR(A1.CODE,1,2) LEFT JOIN '||I.USERNAME||'.ACCOUNT L2 ON L2.CODE = SUBSTR(A1.CODE,1,4) LEFT JOIN '||I.USERNAME||'.ACCOUNT L3 ON L3.CODE = SUBSTR(A1.CODE,1,6) WHERE LENGTH(A1.CODE) = 10 '||CHR(10)||'UNION';

DBMS_OUTPUT.PUT_LINE(QRY);

END LOOP;

END;