ora-06553 pls-306 wrong number or types of arguments in call to 'ogc_x'

Question

I´m trying a query in oracle 10g. It goes like this:

SELECT
  *
FROM
  h2h_reg reg,
  h2h_cat_estatus est
WHERE
  reg.FECH_APLICACION = SYSDATE
AND REG.ID_EST        = EST.ID_ESTATUS
AND est.tipo_estatus  = "X";

So it runs smootly, but when I try it adding a group by:

SELECT
  reg.id_arch,
  reg.id_prod
FROM
  h2h_reg reg,
  h2h_cat_estatus est
WHERE
  reg.FECH_APLICACION = SYSDATE
AND reg.id_est        = est.id_estatus
AND EST.TIPO_ESTATUS  = "X"
GROUP BY
  reg.id_arch,
  reg.id_prod;

I get the next message:

ora-06553 pls-306 wrong number or types of arguments in call to 'ogc_x'

Does anyone knows what´s wrong in my query?

Settling

You need DISTINCT since you're not using any aggregates. – Kagera

How is it that the error you've posted references a function (ogc_x) that doesn't appear anywhere in your query? Are you sure that the query you posted and the error that you posted go together? Is one of the objects in the FROM clause a view that references the ogc_x function? Also, strings in Oracle are enclosed in single quotes not double quotes. If you used = "X" in Oracle, that would generate a syntax error. It would be a different syntax error from the one you posted, though. – Leg

Answer 1

you've used double quotes on "X".

this should be 'X'.

the X object is an function in the MDSYS schema, "ogc_x", so when you say est.tipo_estatus = "X" instead of the correct est.tipo_estatus = 'X' it gets translated (as "" is as an identifier so "X" is the same as just typing X) to est.tipo_estatus = mdsys.ogc_x and of course fails.

Obese

Answer 2

Try with DISTINCT :

SELECT DISTINCT reg.id_arch, reg.id_prod
  FROM h2h_reg reg, h2h_cat_estatus est
 WHERE reg.FECH_APLICACION = SYSDATE
   AND reg.id_est = est.id_estatus
   AND est.tipo_estatus = 'X'

Almire

Answer 3

I found that this error was generated because I had used Oracle reserved words to name some of my columns, e.g. date, time, comment, etc. Once I renamed the columns the problem disappeared.

Exhibition

Answer 4

This problem also occured when the column does not exist

Baba