0

I am trying to get count of all rows from views in oracle schema and my code is working fine. But when i try to add a condition like where actv_ind = 'Y', i am unable to get it working. Any suggestions on how to modify the code to make it working?

SELECT view_name,
TO_NUMBER(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) cnt from '||owner||'.'||view_name||
'where'||view_name||'.'||'actv_ind= Y')),'/ROWSET/ROW/CNT')) as VIEW_CNT
FROM all_views
WHERE owner = 'ABC'  AND view_name not in ('LINK$')

I am getting the error ACTV_IND : Invalid Identifier.

Arun.K
  • 103
  • 2
  • 4
  • 21

1 Answers1

1

The error messages from DBMS_XMLGEN are not very helpful so you need to be very careful with the syntax of the SQL statement. Add a space before and after the where, and add quotation marks around the Y:

SELECT view_name,
TO_NUMBER(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) cnt from '||owner||'.'||view_name||
' where '||view_name||'.'||'actv_ind= ''Y''')),'/ROWSET/ROW/CNT')) as VIEW_CNT
FROM all_views
WHERE owner = 'ABC'  AND view_name not in ('LINK$');

The query still assumes that every view contains the column ACTV_IND. If that is not true, you might want to base the query off of DBA_TAB_COLUMNS WHERE COLUMN_NAME = 'ACTV_IND'.

Here's a simple sample schema I used for testing:

create user abc identified by abc;
create or replace view abc.view1 as select 1 id, 'Y' actv_ind from dual;
create or replace view abc.view2 as select 2 id, 'N' actv_ind from dual;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Thanks Jon. I modified the query to include all tab columns and it worked in Oracle. But when i am executing from a third party application its throwing a new error with invalid number. Not able to figure out why its throwing that error?.Any pointers? SELECT table_name, TO_NUMBER(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) cnt from '||owner||'.'||table_name|| ' where '||table_name||'.'||'actv_ind= ''Y''')),'/ROWSET/ROW/CNT')) VIEW_CNT FROM all_tab_columns WHERE owner = 'ABC' and column_name='ACTV_IND' AND table_name not in ('LINK$'); – Arun.K Jun 18 '20 at 18:02
  • @Arun.K That error implies that some of the ACTV_IND columns are not strings. You may need to filter out non-string values with a condition like this: `and data_type in ('VARCHAR2', 'VARCHAR', 'CHAR', 'CLOB', 'NVARCHAR2', 'NVARCHAR', 'NCHAR', 'NCLOB'); `. You may need to create a separate SQL statement to handle numeric ACTV_IND, and then `UNION ALL` the two queries. Or you could have `case when data_type = ...` logic that builds the right predicate for each data type. – Jon Heller Jun 19 '20 at 00:32