PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN [message #475717] |
Fri, 17 September 2010 00:31 |
haezeban
Messages: 2 Registered: September 2010
|
Junior Member |
|
|
Hi,
This qry works fine :
SELECT table_name,
(EXTRACTVALUE(
xmltype(DBMS_XMLGEN.getxml(
'select count(*) X from ' || table_name || ' where TARNR' || ' = ''X066''
')),
'/ROWSET/ROW/X'
))
COUNT
FROM all_tab_cols where column_name = 'TARNR';
I look for all tables that contains the field TARNR and then for each of these tablesit counts the number of records where TARNR = 'X066'
Now I will use the same query for an other field :
SELECT table_name,
(EXTRACTVALUE(
xmltype(DBMS_XMLGEN.getxml(
'select count(*) X from ' || table_name || ' where CTRNR || ' = ''123456789'''
)),
'/ROWSET/ROW/X'
))
COUNT
FROM all_tab_cols where column_name = 'CTRNR';
But it doesn't work after a few seconds I receive following error :
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 121
ORA-06512: at line 1
Does somebody see what is wrong? It is not syntax because the qry starts processing.
Tkx,
Jac
[Updated on: Fri, 17 September 2010 00:48] by Moderator Report message to a moderator
|
|
|
|
Re: PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN [message #475776 is a reply to message #475720] |
Fri, 17 September 2010 08:46 |
Solomon Yakobson
Messages: 3303 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Also:
1. You need to use table owner - column might exist in a table you have grants to but do not own.
2. I'd enclose table name in double quotes, otherwise you might get something like this even if you do not have case sensitive table names or table names with non-standard for Oracle identifiers characters but simply use recyclebin (default):
SQL> SELECT owner,
2 table_name,
3 (EXTRACTVALUE(
4 xmltype(DBMS_XMLGEN.getxml(
5 'select count(*) X from ' || owner || '.' || table_name || ' where LOC ' || ' = ''DALLAS'''
6 )),
7 '/ROWSET/ROW/X'
8 ))
9 COUNT
10 FROM all_tab_cols where column_name = 'DNAME';
ERROR:
ORA-19202: Error occurred in XML processing
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
no rows selected
SQL> SELECT owner,
2 table_name,
3 (EXTRACTVALUE(
4 xmltype(DBMS_XMLGEN.getxml(
5 'select count(*) X from ' || owner || '."' || table_name || '" where LOC ' || ' = ''DALLAS'''
6 )),
7 '/ROWSET/ROW/X'
8 ))
9 COUNT
10 FROM all_tab_cols where column_name = 'LOC';
OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
SCOTT DEPT 1
SCOTT DEPTX 1
SCOTT BIN$FpUC2UUIQ3CcWT6xvbtkXA==$0 1 -- dropped table in recyclebin
SCOTT DEPT1 0
SYS ALL_DEF_AUDIT_OPTS 0 -- table is owned by another user
SYS DBA_OBJ_AUDIT_OPTS 0
SYS USER_OBJ_AUDIT_OPTS 0
7 rows selected.
SQL>
sy.
|
|
|
|
|