1

getting an error

SQL> select calculator.add(10,10) from dual;

select calculator.add(10,10) from dual

ERROR at line 1:

ORA-01747: invalid user.table.column, table.column, or column specification

error screenshot

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 24 13:58:08 2019

SQL> ed
Wrote file *afiedt.buf*

  1  create or replace package body calculator
  2  as
  3      function add(x number, y number) return number;
  4      function subtract(x number, y number) return number;
  5      function multiply(x number, y number) return number;
  6      function divide(x number, y number) return number;
  7* end;

SQL> /

Warning: Package Body created with compilation errors.

SQL>

SQL> get pkg_bdy_cal

  1  create or replace package body calculator
  2  as
  3     function add(x number, y number) return number
  4     is
  5     begin
  6         return (x + y);
  7     end;
  8     function subtract(x number, y number) return number
  9     is
 10     begin
 11         return (x - y);
 12     end;
 13     function multiply(x number, y number) return number
 14     is
 15     begin
 16         return (x * y);
 17     end;
 18     function divide(x number, y number) return number
 19     is
 20     begin
 21         return (x / y);
 22     end;
 23* end calculator;

SQL> /

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY CALCULATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated

1/14     PLS-00304: cannot compile body of 'CALCULATOR' without its
         specification

1/14     PLS-00905: object SYSTEM.CALCULATOR is invalid
Vishal Kumar
  • 21
  • 5
  • You have declared a package body twice. The first section should be `create or replace package calculator` (not `package body`). – William Robertson Nov 24 '19 at 10:28
  • It results 'Warning: Package created with compilation errors.` – Vishal Kumar Nov 24 '19 at 10:54
  • 3
    The earlier version did, because of the error I mentioned. The updated version does not. The package now compiles without errors, but you get runtime errors due to the keyword `add` being used as a function name and a missing `:` where you try to use a host variable. It would be clearer if you posted your code in a shareable format, as I tried to do for you but you reverted my edit. Am I wasting my time? – William Robertson Nov 24 '19 at 11:06
  • exactly as what you mentioned. the problem raises due to missing prefix `:` for `res`, and `add` being a reserved keyword. But it's interesting that Oracle **ignores** the function name `add` within `exec :res := calculator.add(10,10);`, whereas hurls for `select calculator.add(10,10) from dual` @WilliamRobertson (*and allows to create such a function with a reserved keyword*). Btw, Vishal you can replace the name `add` with `addition` as a result. – Barbaros Özhan Nov 24 '19 at 12:08

0 Answers0