ORA-06553: PLS-306:调用'OGC_DISTANCE‘时参数的数量或类型错误？

Question

我正在为我的项目编写一个小型查询(ORACLE)，以查找从给定的纬度和经度到其他纬度和经度的距离。

下面是我使用的示例数据

 CREATE table test(id int, title varchar(50), place varchar(20),
 postcode varchar(20), latitude DOUBLE PRECISION, longitude DOUBLE
 PRECISION);

 INSERT INTO test VALUES(1,'sekhar91','kanigiri','91982',16.15074,
 -22.74426);

 INSERT INTO test VALUES(2,'sekhar91','kanigiri','91982',16.13725,
 -22.85822);

 INSERT INTO test VALUES(3,'sekhar91','kanigiri','91982',14.85633,
 -24.72379);

 INSERT INTO test VALUES(4,'sekhar91','kanigiri','91982',14.86949,
 -24.70150);

 INSERT INTO test VALUES(5,'sekhar91','kanigiri','91982',15.03118,
 -24.32523);

 INSERT INTO test VALUES(6,'sekhar91','kanigiri','91982',14.88924,
 -24.29403);

 INSERT INTO test VALUES(7,'sekhar91','kanigiri','91982',14.89500,
 -24.50000);

 INSERT INTO test VALUES(8,'sekhar91','kanigiri','91982',15.20031,
 -23.16798);

 INSERT INTO test VALUES(9,'sekhar91','kanigiri','91982',16.72662,
 -22.92971);

 INSERT INTO test VALUES(10,'sekhar91','kanigiri','91982',16.60005,
 -22.90818);

 INSERT INTO test VALUES(11,'sekhar91','kanigiri','91982',15.19196,
 -23.64427);

 INSERT INTO test VALUES(12,'sekhar91','kanigiri','91982',14.92331,
 -23.52119);

 INSERT INTO test VALUES(13,'sekhar91','kanigiri','91982',14.91637,
 -23.60410);

 INSERT INTO test VALUES(14,'sekhar91','kanigiri','91982',14.92279,
 -23.51720);

 INSERT INTO test VALUES(15,'sekhar91','kanigiri','91982',14.92331,
 -23.52119);

 INSERT INTO test VALUES(16,'sekhar91','kanigiri','91982',15.09882,
 -23.67105);

我写的Sql查询是

SELECT id, 
       title, 
      ((ACOS(SIN(16.15074 * 3.141592653 / 180) *
        SIN(latitude * 3.141592653 / 180) +  COS(16.15074 * 3.141592653 / 180
        ) * COS(latitude * 3.141592653 / 180)* COS((-22.74426 -
       longitude)*3.141592653 /180))*180/3.141592653)*60*1.1515)  as distance
 FROM test  
 HAVING distance<=100

在执行上述查询时，Oracle表示

ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_DISTANCE'

原因是什么，我错在哪里？

任何帮助都将不胜感激。

Answer 1

问题似乎出在HAVING子句上，我删除了HAVING，并将您的查询放在一个子查询中，然后将WHERE放在带有过滤器的外部，它就起作用了(参见SQL Fiddle With Demo)：

select *
from
(
  SELECT id, 
    title, 
    ((ACOS(
          SIN(16.15074 * 3.141592653 / 180) 
          * SIN(latitude * 3.141592653 / 180) 
          + COS(16.15074 * 3.141592653 / 180) 
          * COS(latitude * 3.141592653 / 180)
          * COS((-22.74426 - longitude)*3.141592653 /180))*180/3.141592653)*60*1.1515) as distance 
  FROM test
) x
where distance <= 100