Hi all,
I need to get number of disks that came out on this query below. Instead of many 1,1,1,1,... I would like to have 12 (number of physical disks for that cellname I qant to query.
I tried a subquery with count and worked fine in other databases newer than 11g, in 11g it shows error :
--Original query is
select count(*) from (SELECT cell.name
FROM /*+ RULE */ v$cell_config cc
, XMLTable('/cli-output/physicaldisk' PASSING XMLTYPE(confval)
COLUMNS
name VARCHAR2(100) PATH 'name',
disktype VARCHAR2(100) PATH 'diskType'
) cell
WHERE cc.conftype = 'PHYSICALDISKS'
and cellname='192.168.10.120'
and cell.diskType='HardDisk'
group by cell.name) ;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11
, XMLTable('/cli-output/physicaldisk' PASSING XMLTYPE(confval)
*
ERROR at line 3:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
--If I try query below it show many "1"s and not the number or rows returned.
SELECT count(*)
FROM v$cell_config cc
, XMLTable('/cli-output/physicaldisk' PASSING XMLTYPE(confval)
COLUMNS
name VARCHAR2(100) PATH 'name',
disktype VARCHAR2(100) PATH 'diskType'
) cell
WHERE cc.conftype = 'PHYSICALDISKS'
and cellname='192.168.10.120'
and cell.diskType='HardDisk'
group by cell.name; 2 3 4 5 6 7 8 9 10 11
1
1
1
1
1
1
1
1
1
1
1
1
Can someone help on this. I'm am not Pl/sql dev.