Dear Tom,

I have a requirement for displaying all the tables in the sql statement also columns that are participating in joins in the same sql statement.
I am storing the queries that needs to be parsed in a table "PARSEQRY". However, post parsing, only few tables participating in the sql statement
are getting listed, same is the case with columns participating in joins i.e.. for example, 10 tables are being used in the sql statement all the
10 tables has to be listed. In my case only few out of 10 are listed and same is the case with columns.

Can you please suggest where I am going wrong. If this issue was already discussed in this blog, my apologies for raising it once again.

Details :-


Version of Oracle database
=========================================================================================================
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0

TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
=========================================================================================================

PARSEQRY contains query that needs to be parsed.

CREATE GLOBAL TEMPORARY TABLE PARSEQRY(
QRY VARCHAR2(4000)-- contains query that needs to be parsed
)
ON COMMIT PRESERVE ROWS;


Below is the sql statement that is stored inside the table PARSEQRY and same is to be parsed.

SELECT 1 FROM COMM_ACC_SUMMARY INNER JOIN REG_ACC_SUMMARY ON COMM_ACC_SUMMARY.N_ACCT_SKEY = REG_ACC_SUMMARY.N_ACCT_SKEY
AND COMM_ACC_SUMMARY.N_MIS_DATE_SKEY = REG_ACC_SUMMARY.N_MIS_DATE_SKEY AND
COMM_ACC_SUMMARY.N_ENTITY_SKEY = REG_ACC_SUMMARY.N_ENTITY_SKEY INNER JOIN LEGAL_ENTITY_DET ON
REG_ACC_SUMMARY.N_RUN_SKEY= LEGAL_ENTITY_DET.N_RUN_SKEY AND REG_ACC_SUMMARY.N_ENTITY_SKEY = LEGAL_ENTITY_DET.N_ENTITY_SKEY
AND REG_ACC_SUMMARY.N_MIS_DATE_SKEY = LEGAL_ENTITY_DET.N_MIS_DATE_SKEY INNER JOIN DIM_DATES ON
DIM_DATES.N_DATE_SKEY = COMM_ACC_SUMMARY.n_mis_date_skey INNER JOIN DIM_ORG_STRUCTURE ON
DIM_ORG_STRUCTURE.n_entity_skey = COMM_ACC_SUMMARY.n_entity_skey INNER JOIN RUN ON RUN.N_RUN_SKEY = REG_ACC_SUMMARY.N_RUN_SKEY
INNER JOIN PROD_TYPE ON COMM_ACC_SUMMARY.N_PRODUCT_TYPE = PROD_TYPE.N_PROD_TYPE_SKEY LEFT OUTER JOIN DIM_ISSUER ON
COMM_ACC_SUMMARY.N_ISSUER_SKEY = DIM_ISSUER.N_ISSUER_SKEY LEFT OUTER JOIN REG_PROD_CLAS ON
REG_ACC_SUMMARY.N_REG_PROD_CLASSIFICATION_SKEY = REG_PROD_CLAS.N_REG_PROD_CLASSIFICATION_SKEY LEFT OUTER JOIN
ENTITY_TYPE ON LEGAL_ENTITY_DET.N_ENTITY_TYPE_SKEY = ENTITY_TYPE.N_ENTITY_TYPE_SKEY LEFT OUTER JOIN REG_DEP_TYPE ON
REG_ACC_SUMMARY.N_REG_DEPOSIT_TYPE_SKEY = REG_DEP_TYPE.n_reg_deposit_type_skey LEFT OUTER JOIN PROD ON
REG_ACC_SUMMARY.N_PROD_SKEY = PROD.N_PROD_SKEY AND RUN.F_REPORTING_FLAG = 'Y'

I am using the below logic to parse the above sql statement for listing all the tables, columns participating in joins.

CREATE OR REPLACE TYPE XPLAN IS OBJECT (PARSED_OBJ VARCHAR2(1000),PARSED_OBJ_TYPE VARCHAR2(50),ERR_CODE VARCHAR2(150),
ERR_MSG VARCHAR2(250), QRY VARCHAR2(5000));

CREATE OR REPLACE TYPE PARSED_ITEMS IS TABLE OF XPLAN;

FUNCTION GET_PARSED_ITEMS
RETURN PARSED_ITEMS
IS
C NUMBER;
I VARCHAR2(30);
L NUMBER;
ERR_CODE VARCHAR2(100);
ERR_MSG VARCHAR2(250);
PARSED_LIST PARSED_ITEMS;
BEGIN
L:=60;--V$OPEN_CURSOR SQL_TEXT VARCHAR2(60 BYTE) Y
C:=DBMS_SQL.OPEN_CURSOR();

FOR MST IN (SELECT QRY FROM PARSEQRY)
LOOP
BEGIN
DBMS_SQL.PARSE(C,MST.QRY,DBMS_SQL.NATIVE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR_CODE := SQLCODE;
ERR_MSG := SUBSTR(SQLERRM, 1, 200);
SELECT XPLAN(PARSED_OBJECTS,PARSED_TYPE,PARSED_ERROR_CODE,PARSED_ERROR_MSG,PARSED_QRY) BULK COLLECT INTO PARSED_LIST FROM
(SELECT 'FAILED' PARSED_OBJECTS, 'ERORR' PARSED_TYPE, ERR_CODE PARSED_ERROR_CODE, ERR_MSG PARSED_ERROR_MSG,
MST.QRY PARSED_QRY FROM DUAL);
RETURN PARSED_LIST;
WHEN OTHERS THEN
ERR_CODE := SQLCODE;
ERR_MSG := SUBSTR(SQLERRM, 1, 200);
SELECT XPLAN(PARSED_OBJECTS,PARSED_TYPE,PARSED_ERROR_CODE,PARSED_ERROR_MSG,PARSED_QRY) BULK COLLECT INTO PARSED_LIST FROM
(SELECT 'FAILED' PARSED_OBJECTS, 'ERORR' PARSED_TYPE, ERR_CODE PARSED_ERROR_CODE, ERR_MSG PARSED_ERROR_MSG,
MST.QRY PARSED_QRY FROM DUAL);
RETURN PARSED_LIST;
END;
BEGIN
SELECT DISTINCT SQL_ID INTO I FROM V$OPEN_CURSOR WHERE SID IN (SELECT SID FROM V$MYSTAT) AND SUBSTR(SQL_TEXT,1,L) = SUBSTR(MST.QRY,1,L);
EXCEPTION
WHEN NO_DATA_FOUND THEN
I:='ERROR';
END;

SELECT XPLAN(PARSED_OBJECTS,PARSED_TYPE,NULL,NULL,NULL) BULK COLLECT INTO PARSED_LIST FROM
(SELECT OBJECT_NAME PARSED_OBJECTS, OBJECT_TYPE PARSED_TYPE FROM V$SQL_PLAN_STATISTICS_ALL WHERE SQL_ID=I AND
OBJECT_NAME IS NOT NULL UNION
SELECT SUBSTR(OBJECT_ALIAS,1,(INSTR(OBJECT_ALIAS,'@')-1)) PARSED_OBJECTS, 'TABLE' PARSED_TYPE FROM V$SQL_PLAN_STATISTICS_ALL
WHERE SQL_ID=I AND OBJECT_ALIAS IS NOT NULL UNION ALL
SELECT ACCESS_PREDICATES PARSED_OBJECTS, 'COLUMN' PARSED_TYPE FROM V$SQL_PLAN_STATISTICS_ALL
WHERE SQL_ID=I AND ACCESS_PREDICATES IS NOT NULL
);
END LOOP;
RETURN PARSED_LIST;
END GET_PARSED_ITEMS;

SQL> select count(*)
  2  from  scott.emp e,
  3        scott.dept d
  4  where e.deptno = d.deptno
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------