?
Solved

why ORA-00904: "OBJECT_VALUE": invalid identifier

Posted on 2008-11-01
17
Medium Priority
?
2,600 Views
Last Modified: 2013-12-07
hi,

i know this might be plain question, but i tried to run some examples using XMLQuery & XMLTable from Oracle documentation, it return me with this ORA-00904: "OBJECT_VALUE": invalid identifier error.
0
Comment
Question by:wxII
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 10

Expert Comment

by:dbmullen
ID: 22860047
select * from dba_registry

is XDB installed and valid?
if not, install it.
http://www.adp-gmbh.ch/ora/xml_db/install.html

if yes, could be a grant issue..  hard to tell


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22862394
what was the example code you were running?
0
 

Author Comment

by:wxII
ID: 22864006
hi there,

the example was

SELECT XMLQuery('for $i in /PurchaseOrder
                 where $i/CostCenter eq "A10"
                   and $i/User eq "SMCCAIN"
                 return <A10po pono="{$i/Reference}"/>'
                PASSING OBJECT_VALUE
                RETURNING CONTENT)
  FROM purchaseorder;

in "http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAIFJJJ"

"Example 18-10 Using XMLQuery with Schema-Based Data"

thanks
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 74

Expert Comment

by:sdstuber
ID: 22868540
For that example to work,  dbmullen stated above, XML DB must be installed.

  select * from dba_registry where comp_name like '%XML%'


if that query doesn't return the "Oracle XML Database" component,
you need to request your DBA to install it.
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22868610
this isn't granted to public
select * from dba_registry

you'll need someone with DBA privs to run the query and install XDB
0
 

Author Comment

by:wxII
ID: 22868737
hi there,

it returned me value COMP_ID is "XDB",COMP_NAME is "Oracle XML Database" and the status us "VALID"
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22868808
Are you querying the purchaseorder table of the OE schema?

0
 

Author Comment

by:wxII
ID: 22871404
oh, i'm sorry, i queried on HR instead on OE schema, my mistakes.. . the example works alright in OE; but why others don't have OBJECT_VALUE or COLUMN_VALUE pseudocolumn available?? what do i need to do in order to have these feature. most of the example for xml in xmltable and xmlquery has lengthy usage on those pseudocolumns. any way to enable or check it?

please advise,
WXII
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22872252
your HR schema (or some synonym you can reach) must have a table called purchaseorder that is not an xmltype table.

The OE.purchaseorder table is an xmltype table, that is it has no columns, the table is itself an xml document, so querying it you only have object_value for the contents of it.
0
 

Author Comment

by:wxII
ID: 22872472
that means xmltype column can't access object_value? so everytime i need to get data, i have to explicitly specify the column name instead? the example actually show something that i need, to be able to transform the xml data into relational row and columns. other than the object_value techniques, how am i going to return the data in relational format with xmltype column, the data i need were all reside within attributes of each nested elements?  
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22873190
The oe.purchaseorder example table is

create table oe.purchaseorder of xmltype;

which is handled differently than

create table my_purchase_order (xml  xmltype);


since the oe.purchaseorder table is of xmltype, the "rows" in it are referred to by their object handle which is "object_value"


if you have an xmltype column then you use that




SELECT XMLQuery('for $i in /PurchaseOrder
                 where $i/CostCenter eq "A10"
                   and $i/User eq "SMCCAIN"
                 return <A10po pono="{$i/Reference}"/>'
                PASSING xml
                RETURNING CONTENT)
  FROM my_purchase_order;




0
 

Author Comment

by:wxII
ID: 22874939
on the PASSING and RETURN keyword, can i return in row format? as like relational?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22875844
Sorry, I don't know what you're asking.

Can you illustrate using a table with an xmltype column and the sample data you used in one of your previous questions?
0
 

Author Comment

by:wxII
ID: 22885895
hi there,

sorry for late reply, the example table consist the xmltype column call nmstruct reside inside bststruct table, all the xml data will
reside in nmstruct column.

the data is as follow:-

<country>
  <id>MAL</id>
  <sponsornode spacctid="100000" spacctcode="100000" level="0" legno="1">
    <sponsornode spacctid="NSH" spacctcode="nscode" level="1" legno="1"/>
    <sponsornode spacctid="WXII" spacctcode="wxcode" level="1" legno="2"/>
    <sponsornode spacctid="JOH" spacctcode="jhcode" level="1" legno="3"/>
    <sponsornode spacctid="SNK" spacctcode="sncode" level="1" legno="4"/>
    <sponsornode spacctid="KEN" spacctcode="nnnn" level="1" legno="5"/>
  </sponsornode>
</country>

with XMLQuery or XMLTable, since i'm using column, how do i get the data in this format

spacctid  spacctcode  level   legno
-------------------------------------------------
100000   100000         0         0
NSH         nscode         1         1
WXII        wxcode        1         2
JOH          jhcode         1         3
SNK          sncode        1         4
KEN          nnnn           1         5

please advise and thanks

regards
wxii
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 22892735
try this...
SELECT   EXTRACTVALUE(VALUE(s), '/sponsornode/@spacctid') spacctid,
         EXTRACTVALUE(VALUE(s), '/sponsornode/@spacctcode') spacctcode,
         EXTRACTVALUE(VALUE(s), '/sponsornode/@level') lvl,
         EXTRACTVALUE(VALUE(s), '/sponsornode/@legno') legno
  FROM   bststruct, xmltable('//sponsornode' passing nmstruct) s

Open in new window

0
 

Author Comment

by:wxII
ID: 22895782
thanks very much, this is exactly what i wanted.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22895854
glad I could help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question