Im getting the error "PLS-00302: component 'MIN' must be declared" when trying to find min/max value for a distance trip. Any thoughts?

create or replace procedure longandshortdist (p_distance in number)
is
  cursor longshortcursor is 
    select source_town, destination_town, distance
    from distances
    where distance = p_distance;
  distance_row longshortcursor%rowtype;

begin
  for distance_row in longshortcursor
  loop
    dbms_output.put_line('source town is: ' || distance_row.source_town || 'destination 
    town is: ' || distance_row.destination_town || 'shortest trip is: ' || 
    distance_row.min(distance) || 'longest trip is: ' || distance_row.max(distance));
    
  end loop;
end;

The error code I'm getting:

12/1      PL/SQL: Statement ignored
12/169    PLS-00302: component 'MIN' must be declared
Errors: check compiler log

CodePudding user response：

Read comments; seem to be very useful. Meanwhile, I tried to figure out what you wanted and this might be "it".

For sample data as

SQL> SELECT *
  2      FROM distances
  3  ORDER BY distance;

SOURCE_TOW DESTINAT   DISTANCE
---------- -------- ----------
Zagreb     Karlovac         40
Zadar      Split           120
Koprivnica Osijek          200

procedure doesn't accept any parameters (because, you'd fetch only rows whose distance is equal to p_distance (once again, see Connor's comment)) and loops through all rows in a table.

Loop itself displays all towns, but MIN and MAX distance are displayed only once - out of the loop, once you actually calculate their values.

SQL> CREATE OR REPLACE PROCEDURE longandshortdist
  2  IS
  3     mindist  NUMBER := 1E6;
  4     maxdist  NUMBER := 0;
  5  BEGIN
  6     FOR cur_r
  7        IN (SELECT source_town, destination_town, distance FROM distances)
  8     LOOP
  9        mindist := LEAST (mindist, cur_r.distance);
 10        maxdist := GREATEST (maxdist, cur_r.distance);
 11
 12        DBMS_OUTPUT.put_line (
 13              'source town is: '
 14           || cur_r.source_town
 15           || ', destination town is: '
 16           || cur_r.destination_town
 17           || ', distance is: '
 18           || cur_r.distance);
 19     END LOOP;
 20
 21     DBMS_OUTPUT.put_line (
 22        'shortest trip is: ' || mindist || ', longest trip is: ' || maxdist);
 23  END;
 24  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL> EXEC longandshortdist;
source town is: Zagreb, destination town is: Karlovac, distance is: 40
source town is: Zadar, destination town is: Split, distance is: 120
source town is: Koprivnica, destination town is: Osijek, distance is: 200
shortest trip is: 40, longest trip is: 200

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

That is because the min and max are aggregate functions used within a query and not inside a block.

Example:

SELECT max(a.f) max, min(a.f) min
FROM (
select 1 f from dual
union all
select 2 f from dual
union all
select 3 f from dual) a

Result: