This content has been marked as final. Show 11 replies
For posterity: The workaround is to enclose table variables in double quotes, e.g.
SELECT * FROM messages "x" WHERE existsNode("x".object_value, '/message')
Neither the 7445 nor the 6553 occur when you do that.
This is such a basic, and ubiquitous, XML query it is hard to believe it escaped notice. For benefit of anyone needing to repro, below is the .xsd to register. Then just execute the queries in this thread from sqlplus.
<?xml version="1.0" encoding="UTF-8"?>
elementFormDefault="qualified" attributeFormDefault="unqualified" xdb:storeVarrayAsTable="true">
<xsd:element name="message" type="witsml:obj_message" xdb:defaultTable="MESSAGE"/>
<xsd:complexType name="obj_message" xdb:SQLType="OBJ_MESSAGE_T">
<xsd:element name="nameWell" type="xsd:string">
<xsd:documentation>Human recognizable context for Well. </xsd:documentation>
value(x) should be used when a table alias in use...
It seems that the source of this mess is the public synonym named x as seen in:
select * from all_objects where lower(object_name) = 'x';
Another workaround would be to make alias with another name (not y, because it exists, too :) ):
SELECT * FROM messages msg WHERE existsNode(msg.object_value, '/message')
If you have view based on view and want to select same column which exist in that "groubd" view....this could happened!
I got this error when a view was created on a table that did not contain all the fields that were specified in the definition of the view.
ex. table 'abc' contains columns a, b and c
create or replace view
x.a = 1;
Do NOT use "x" as an alias. In some database versions there is already an "x" object by default (and "y" for that matter). Try checking for instance via "desc x" or select on all_objects where object_name = "X"
As an alternative use
create or replace view viewname as select xv.a, xv.b, xv.c, xv.d from abc XV where xv.a = 1;
what happens if you use a name for an alias and there's an object with the same name in the database?
some tests on 22.214.171.124
1. table alias and the function name are the same:
2. alias and a table name are the same
SQL> create function dum return number is 2 begin 3 return 3; 4 end; 5 / Function created. SQL> create table dum2(v number); Table created. SQL> insert into dum2 values(1); 1 row created. SQL> select dum.v,dum from dum2 dum; V DUM ---------- ---------- 1 3
3. package and table alias are the same:
SQL> create table dum3 as select 2 v from dual; Table created. SQL> select dum2.v from dum3 dum2; V ---------- 2
SQL> create package dum4 as 2 function v(id number) return number; 3 end; 4 / Package created. SQL> create package body dum4 as 2 function v(id number) return number is 3 begin 4 return 4; 5 end; 6 end; 7 / Package body created. SQL> SQL> select dum4.v,dum4.v(2) from dum2 dum4; V DUM4.V(2) ---------- ---------- 1 4
Have you encountered any problems while aliasing like that?
The issue is not with your code, the issue is caused by using "X" or "Y" (without the double quotes otherwise X and Y become case-sensitive) in certain versions off the Oracle database where X and Y are synonyms...
Have a read here: Re: Getting :WRONG NUMBER OR TYPES OF ARGUMENTS IN CALL TO 'OGC_X' ERROR
By the way, this also why the workaround demonstrated here is also working. By using double quotes the "x" is translated in lowercase x and not uppercase X and therefore pinpoints to the alias "x" and not the synonym X
The error occurs every time when the ogc_x synonym is the same as your alias, therefore it should be always checked what is the synonym for ogc_x and not to use it.
SQL> create public synonym xv for ogc_x; Synonym created. SQL> select xv.a 2 from table(xmlsequence(extract(xmltype('<a>1</a>'),'/a'))) xv; select xv.a * ERROR at line 1: ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'
You got it, and that is why there aren't "x" or "y" synonyms in the 11g databases anymore. The phenomenon is more interesting than you might think though...