Andrej Tihonov Andrej Tihonov - 6 months ago 7
SQL Question

How to return a table of numbers in a package PL/SQL

I am struggeling the second day with following problem.
I have a package and want to call one function out of another.
I have a table of places with their coordinates. So the first function returns
the distance between two cities. The second one have to return a table of numbers, which represents the distance from one city to the rest.
So I do not know what I am doing wrong here:
So the function Entfernung does not work, but the function abstand works fine.

I get following compilation error:
Fehler(11,20): PLS-00103: Encountered the symbol "(" when expecting one of the following: . into bulk
Fehler(12,7): PLS-00103: Encountered the symbol "EXIT" when expecting one of the following: . ( , % from

set serveroutput on
set echo on

CREATE OR REPLACE PACKAGE Geo AS
TYPE entfernungTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
FUNCTION Abstand(Stadt1 IN Rheinland_Staedte.Stadtname%TYPE, Stadt2 IN Rheinland_Staedte.Stadtname%TYPE) RETURN NUMBER;
FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE) RETURN entfernungTab;
END Geo;

/

CREATE OR REPLACE PACKAGE BODY Geo AS

FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
RETURN entfernungTab
IS
TYPE entfernungTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR cur_ent IS SELECT * FROM Rheinland_Staedte;
BEGIN
OPEN cur_ent;
LOOP
FETCH Abstand(Stadt, cur_ent.Stadtname) INTO entfernungTab
EXIT WHEN cur_ent%NOTFOUND;
END LOOP;
RETURN (entfernungTab);
END;


FUNCTION Abstand (Stadt1 IN Rheinland_Staedte.Stadtname%TYPE, Stadt2 IN Rheinland_Staedte.Stadtname%TYPE)
RETURN NUMBER
IS
v_dis NUMBER;
v_s1 Rheinland_Staedte%ROWTYPE;
v_s2 Rheinland_Staedte%ROWTYPE;
v_yAbstandMin NUMBER;
v_xAbstandMin NUMBER;

BEGIN
SELECT * INTO v_s1 FROM Rheinland_Staedte WHERE Stadtname = Stadt1;
SELECT * INTO v_s2 FROM Rheinland_Staedte WHERE Stadtname = Stadt2;

v_yAbstandMin := ((v_s1.GradNB * 60) + v_s1.MinNB)
- ((v_s2.GradNB * 60) + v_s2.MinNB);

v_xAbstandMin := ((v_s1.GradOL * 60) + v_s1.MinOL)
- ((v_s2.GradOL * 60) + v_s2.MinOL);

v_dis := SQRT((v_yAbstandMin * v_yAbstandMin)) + ((v_xAbstandMin * v_xAbstandMin));

RETURN (v_dis);
END;
END Geo;


Hope u can give me some advices.

Answer

You have a few issues. The immediate error is that you're trying to call a function as part of a fetch, which is not allowed.

This compiles; I've put some notes in as comments.

  FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
  RETURN entfernungTab
  IS 
    -- do not redelare the type in the function (or the package body)
    -- TYPE entfernungTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    CURSOR cur_ent IS SELECT * FROM Rheinland_Staedte;
    -- you need a record to fetch into
    cur_row cur_ent%ROWTYPE;
    -- you need a variable of the return type, and to initialise it
    returnTab entfernungTab;
    -- you need an index variable for the table;
    indx pls_integer := 0;
  BEGIN
    OPEN cur_ent;
    LOOP
      -- fetch into a record, and you can't call the function at this point
      -- FETCH Abstand(Stadt, cur_ent.Stadtname) INTO entfernungTab
      FETCH cur_ent INTO cur_row;
      -- check this condition straight after fetching
      EXIT WHEN cur_ent%NOTFOUND;
      -- add the function result to the table, based on the record field value
      indx := indx + 1;
      returnTab(indx) := Abstand(Stadt, cur_row.Stadtname);
    END LOOP;
    -- close the cursor
    CLOSE cur_ent;
    -- return the variable, not the type
    -- RETURN (entfernungTab);
    RETURN returnTab;
  END;

You can also call the function as part of the cursor query, and if that is the only expression/column in the cursor query, you can select straight into your collection without needing a loop:

  FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
  RETURN entfernungTab
  IS 
    -- call the function in the cursor query
    CURSOR cur_ent IS SELECT Abstand(Entfernung.Stadt, Rheinland_Staedte.Stadtname)
      FROM Rheinland_Staedte;
    returnTab entfernungTab;
  BEGIN
    OPEN cur_ent;
    -- bulk collect straight into your collection
    FETCH cur_ent BULK COLLECT INTO returnTab;
    CLOSE cur_ent;
    RETURN returnTab;
  END;

Or if you have a lot of records you can add a limit:

  FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
  RETURN entfernungTab
  IS 
    -- call the function in the cursor query
    CURSOR cur_ent IS SELECT Abstand(Entfernung.Stadt, Rheinland_Staedte.Stadtname)
      FROM Rheinland_Staedte;
    returnTab entfernungTab;
  BEGIN
    OPEN cur_ent;
    LOOP
      -- bulk collect straight into your collection with limit
      FETCH cur_ent BULK COLLECT INTO returnTab LIMIT 10;
      EXIT WHEN cur_ent%NOTFOUND;
    END LOOP;
    CLOSE cur_ent;
    RETURN returnTab;
  END;