Ogc_x ORA-06553 Error in form

Yesterday a classmate asked why he developed the form executive Ctrl+f11 times out the following error:

ora-06553:pls-306: Parameter number or type error when calling ' Ogc_x '

I also feel very puzzled at the beginning of this mistake, in his form did not call any ogc_x method, can only step by step analysis to see what happened.

The first thing to think about is using the help–> feature to see what SQL statements are actually executed.   ? [Copy to Clipboard] View Code SQL

1 2 3 4 5 6

SELECT REF (x), ... X.last_updated_by, x.last_update_date, x.last_update_login from xhu_po_quote_headers_2213_v x

But this SQL doesn't seem to have anything to do with ogc_x. It doesn't make any sense to have a relationship with X.

then use the data dictionary to see, in the end ogc_x is what , always and this SQL statement sorry. [Copy to Clipboard] View Code SQL

1 2 3 4 5 6 7 8

Sql> SELECT owner, object_name, Object_type 2 from dba_objects 3 WHERE object_name = ' ogc_x '; OWNER object_name object_type ------------------------------------- public ogc_x synonym Mdsys ogc_x FUNCTION

There is a ogc_x name function in the system, so from the above ORA-06553 error, it is true that this SQL statement executed this function, but why it was executed. Let's look at the definition of ogc_x. [Copy to Clipboard] View Code SQL

1 2 3 4 5

Sql> Desc ogc_x; Parameter Type Mode Default? ----------------------------------- (Result) number P mdsys. St_point in

If you look at this SQL statement, it doesn't really have anything to do with X. I'll try to change X to another alias, called Poh, execute the modified SQL, there is no error, the original really and alias X has a relationship,

So even if it's the alias X, then the ogc_x and X will have something to do with it, how they're connected. Think about it, synonyms can connect them together, maybe that's the reason. ? [Copy to Clipboard] View Code SQL

1 2 3 4 5 6 7 8

Sql> SELECT owner, Synonym_name, TABLE_OWNER, table_name 2 from dba_synonyms 3 WHERE table_name = ' OGC_ X '; OWNER Synonym_name table_owner table_name --------------------------------------------------- Public ogc_x mdsys ogc_x public X Mdsys ogc_x

A look at the synonyms of the query results, the answer was found, originally ogc_x created a synonym name of x, so in the upper SQL ref (x) is considered to invoke the function of ogc_x, without giving it the parameters invoked, so the number of arguments or types of calls is incorrect.

The key part of the problem has been solved, but further thought, since X has a synonym, will not also be known as the synonym of Y, to see. ? [Copy to Clipboard] View Code SQL

1 2 3 4 5 6 7

Sql> SELECT owner, Synonym_name, TABLE_OWNER, table_name 2 from dba_synonyms 3 WHERE synonym_name = ' Y '; OWNER synonym_name table_owner table_name ------------------------------------------------ --Public Y mdsys ogc_y

There is such a synonym in the system, why should oracle do that, Google one, and find Oracle's official statement:

Oracle Spatial is conformant with Open Geospatial Consortium (OGC) Simple Features specification 1.1.1 (Document 99-049), Starting with Oracle Database release 10g (version 10.1.0.4). Conformance with the SQL92 and Geometry Types implementation means, Oracle Spatial supports all Types, and language constructs detailed in section 3.2 of the specification. Synonyms are created to the match all OGC function names except for X (P-point) and Y (P-point). For this functions, you must use the names Ogc_x and ogc_y instead of just X and Y.

The above question already has the answer, but the form produces the SQL statement how to have an alias X again. The answer is on the form block settings, the database alias is set to X, to completely resolve this as long as you remove the alias or name the other not conflicting alias.

It can be seen from this question that some seemingly unrelated problems are intrinsically linked, that there are no monsters in the development of the program, and that a good name reduces the number of unnecessary errors that occur.