Home » Developer & Programmer » JDeveloper, Java & XML » PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN
PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN [message #475717] Fri, 17 September 2010 00:31 Go to next message
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 #475720 is a reply to message #475717] Fri, 17 September 2010 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Without formatting we can't see if you use '' or ".

A quote is missing in your second query:
'select count(*) X from ' || table_name || ' where CTRNR' || ' = ''123456789'''

Regards
Michel
Re: PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN [message #475776 is a reply to message #475720] Fri, 17 September 2010 08:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN [message #475959 is a reply to message #475776] Mon, 20 September 2010 01:11 Go to previous messageGo to next message
haezeban
Messages: 2
Registered: September 2010
Junior Member
Hi,

Tkx for the answer. But I had still the same error.


Then I investigated all the tables that contains that column_name. And I saw in 1 table the type of the field = Undefined.

So I excluded that table in my qry and then it works.
Re: PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN [message #475961 is a reply to message #475959] Mon, 20 September 2010 01:18 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a view.
Use ALL_TAB_COLUMNS and not ALL_TAB_COLS and exclude the (owner,table) names that are in ALL_VIEWS, also exclude those that are in USER_RECYCLEBIN.

Regards
Michel
Previous Topic: DBMS_XMLSTORE not able to insert values for nested tags
Next Topic: Pass data to another page
Goto Forum:
  


Current Time: Thu Mar 28 18:54:42 CDT 2024