CODEWITHSUNDEEP
oracle-databaseplsql
user3258814
user3258814

Reputation: 1

ORACLE PL/SQL: Table or view does not exist

I'd appreciate some help on this topic since I am making my first steps into PL/SQL:

When trying to create a function, I get an error: "Table or view does not exist" for all 5 Select statements (and for the statement in the cursor declaration as well). I am sure all tables exist.

Many thanks in advance.

CREATE OR REPLACE FUNCTION ZINSKALK (vtid IN NUMBER, intart IN INTEGER)
   RETURN NUMBER
IS
   j         INTEGER := 0;
   p         NUMBER := 1;
   q         NUMBER := 0;
   t         NUMBER := 1;
   r         NUMBER := 0;
   lz        INTEGER := 0;
   modusvn   INTEGER := 0;
   w1        NUMBER := 0;
   w2        NUMBER := 0;
   i         INTEGER := 0;

   TYPE ARRAY_TYP IS VARRAY (200) OF NUMBER (10, 2);

   RARRAY    ARRAY_TYP;

   CURSOR C1
   IS
        SELECT vtobslpos.anzahl, vtobslpos.betrag
          FROM vtobsl, vtobslpos
         WHERE     vtobsl.sysid = vtobslpos.sysvtobsl
               AND vtobsl.sysid IN
                      (SELECT MAX (vtobsl.sysid)
                         FROM vtobsl
                        WHERE vtobsl.sysvt = vtid AND vtobsl.rang = 100)
      ORDER BY vtobslpos.rang ASC;

BEGIN
   SELECT SUM (vtobslpos.anzahl)
     INTO lz
     FROM vtobslpos, vtobsl
    WHERE     vtobslpos.sysvtobsl = vtobsl.sysid
          AND vtobsl.sysvt = vtid
          AND vtobsl.sysid IN (SELECT MAX (sysid)
                                 FROM vtobsl
                                WHERE sysvt = vtid AND rang = 100);

   SELECT vtobsl.modus
     INTO modusvn
     FROM vtobsl
    WHERE vtobsl.sysid IN (SELECT MAX (sysid)
                             FROM vtobsl
                            WHERE sysvt = vtid AND rang = 100);

   SELECT   bgextern
          - sz
          + (CASE intart
                WHEN 1 THEN 0
                WHEN 2 THEN - (sz2 + disagio)
                WHEN 3 THEN (sz2 + disagio - provision)
                ELSE 0
             END)
     INTO W1
     FROM vt
    WHERE sysid = vtid;

   SELECT rw + rsv
     INTO w2
     FROM vt
    WHERE sysid = vtid;

   i := (CASE WHEN modusvn = 0 THEN 1 ELSE 0 END);

   RARRAY (1) := -w1;
   RARRAY (lz + 1) := w2;

   FOR x IN C1
   LOOP
      FOR y IN 1 .. x.anzahl
      LOOP
         i := i + 1;
         RARRAY (i) := RARRAY (i) + x.betrag;
      END LOOP;
   END LOOP;



   WHILE ABS (p) >= 0.001
   LOOP
      p := 0;
      q := 0;
      j := 0;

      FOR z IN 1 .. lz + 1
      LOOP
         j := j + 1;
         p := p + RARRAY (j) * POWER (t, j - 1);
         q := q + (j - 1) * RARRAY (j) * POWER (t, j - 2);
      END LOOP;

      t := t - p / q;
   END LOOP;

   r := 1200 / t - 1200;
   RETURN r;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;

Upvotes: 0

Views: 2301

Answers (3)

balaaagi
balaaagi

Reputation: 512

Try describing the tablename with same credentials in the same schema with which you are trying to compile/execute the function.

desc tablename

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40489

I assume the table belong to another schema than the schema into which your function is compiled. If you have granted select on these table via role rather than directly, Oracle will give you an ORA-00942.

See this stackoverflow question for more information.

Upvotes: 1

bdn02
bdn02

Reputation: 1500

Did you compile the procedure on the same schema of the tables? If you use toad try to press F4 button when cursor is on table name or try to launch the command "desc tablename"

Upvotes: 0

Related Questions