Function/Package issue PL/SQL

Question

I am trying to create a package with four functions. Each function will add a set of numbers and subtract one from the total. I have been having lots of trouble getting the syntax correct. The functions below work on their own, and i try calling the first function at the end.

When I try to create the package i get an error where on line 7, " Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior 0.05 seconds"

In the package body it says "name is already in use by an existing object". I don't understand as it has to be declared in the specification of the package anyway, and create or replace should solve this if the error is that there is already a package named functionbyfour.

And finally, when I try to use a function in the package, it says " Encountered the symbol "BEGIN" when expecting one of the following: := . ( @ % ; not null range default character The symbol ";" was substituted for "BEGIN" to continue. ORA-06550: line 5, column 43: PLS-00103: Encountered the symbol "FROM" when expecting one of the following: . ( * % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset me".

I am using ORACLE EXPRESS edition 11g and am new to PL/SQL(4 weeks).

Any input is greatly appreciated.

CREATE OR REPLACE FUNCTION functionbyfour AS

 FUNCTION functone( first number, second number) RETURN NUMBER ;
 FUNCTION functtwo( first number, second number, third number) RETURN NUMBER ;
 FUNCTION functthree(first number, second number, third number, fourth number) RETURN     NUMBER ;
 FUNCTION functfour( first number, second number, third number, fourth number,fifth   number) RETURN NUMBER ;

END  functionbyfour;
/

CREATE OR REPLACE PACKAGE functionbyfour AS

FUNCTION functone (first number, second number ) RETURN number AS total number;
 BEGIN
total:=first + second – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functone;


FUNCTION functtwo (first number, second number, third number ) RETURN number AS  total     number;
BEGIN
total:=first + second + third – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functtwo;

FUNCTION functthree (first number, second number,third number, fourth number )     RETURN     number AS total number;
BEGIN
total:=first + second + third + fourth – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functthree;


FUNCTION functfour (first number, second number, third number, fourth number, fifth     number ) RETURN number AS total number;
BEGIN
total:=first + second + third + fourth + fifth – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functfour;

/

BEGIN

SELECT functionbyfour.functone(1,2) FROM DUAL;

END;

/​

Solution 1

You would need to create a package named FunctionByFour (CREATE OR REPLACE PACKAGE)

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PACKAGE functionbyfour AS
  2   FUNCTION functone( first number, second number) RETURN NUMBER ;
  3   FUNCTION functtwo( first number, second number, third number) RETURN NUMBER ;
  4   FUNCTION functthree(first number, second number, third number, fourth number) RETURN     NUMBER ;
  5   FUNCTION functfour( first number, second number, third number, fourth number,fifth   number) RETURN NUMBER ;
  6* END  functionbyfour;
  7  /

Package created.

and then a corresponding package body (CREATE OR REPLACE PACKAGE BODY). You'll also need an END for the package body (right now, your code ends at the end of the fourth function)

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PACKAGE BODY functionbyfour AS
  2    FUNCTION functone (first number, second number ) RETURN number AS total number;
  3    BEGIN
  4      total:=first + second - 1;
  5      RETURN total;
  6      DBMS_OUTPUT.PUT_LINE(total);
  7    END functone;
  8    FUNCTION functtwo (first number, second number, third number ) RETURN number AS  total     number;
  9    BEGIN
 10      total:=first + second + third - 1;
 11      RETURN total;
 12      DBMS_OUTPUT.PUT_LINE(total);
 13    END functtwo;
 14    FUNCTION functthree (first number, second number,third number, fourth number )     RETURN     number AS total number;
 15    BEGIN
 16      total:=first + second + third + fourth - 1;
 17      RETURN total;
 18      DBMS_OUTPUT.PUT_LINE(total);
 19    END functthree;
 20    FUNCTION functfour (first number, second number, third number, fourth number, fifth     number ) RETURN number AS total number;
 21    BEGIN
 22      total:=first + second + third + fourth + fifth - 1;
 23      RETURN total;
 24      DBMS_OUTPUT.PUT_LINE(total);
 25    END functfour;
 26* END functionbyfour;
SQL> /

Package body created.

Once you've done that, you can use the function

SQL> SELECT functionbyfour.functone(1,2) FROM DUAL;

FUNCTIONBYFOUR.FUNCTONE(1,2)
----------------------------
                           2

If you want to put the SELECT statement in a PL/SQL block, you'd need to declare a local variable and do a SELECT INTO to populate the local variable with the result of the function (you could also just assign the local variable the result of the function call without needing to use a SELECT).

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    l_result NUMBER;
  3  BEGIN
  4    -- First approach
  5    l_result := functionByFour.functOne(1,2);
  6    dbms_output.put_line( l_result );
  7    -- Second approach
  8    SELECT functionByFour.functOne(1,2)
  9      INTO l_result
 10      FROM dual;
 11    dbms_output.put_line( l_result );
 12* END;
 13  /
2
2

PL/SQL procedure successfully completed.

Also, be aware that putting a DBMS_OUTPUT.PUT_LINE after your RETURN statement is pointless. That code can never be reached. If you want to print the result to the DBMS_OUTPUT buffer, that would need to come before the RETURN.

Solution 2

1. The line

   CREATE OR REPLACE FUNCTION functionbyfour AS
   

   should be

   CREATE OR REPLACE PACKAGE functionbyfour AS
   

2. The line

   CREATE OR REPLACE PACKAGE functionbyfour AS
   

   should be

   CREATE OR REPLACE PACKAGE BODY functionbyfour AS
   

3. The word second is a key word and you can't use it as a parameter name

4. You need an

   END functionbyfour;
   

   After your END functfour to end the package body

5. Your dbms_outputs will never be executed as they are after the return

6. You could do all this in one function

    FUNCTION functall(FIRST NUMBER
                        ,sec NUMBER DEFAULT 0
                        ,third NUMBER DEFAULT 0
                        ,fourth NUMBER DEFAULT 0
                        ,fifth NUMBER DEFAULT 0)
     RETURN NUMBER
   AS
     total NUMBER;
   BEGIN
     total := first + sec + third + fourth + fifth - 1;
   
     dbms_output.PUT_LINE(total);
   
     RETURN total;
   END functall;
   

7. What a strange thing to want to do? :-)

Solution 3

You are confused about standalone programs and packages.

CREATE FUNCTION can only be used to create a standalone functiion. What you have there should be:

CREATE OR REPLACE PACKAGE functionbyfour AS

A package consists of two parts, a specification and a body. The spec is the public face of the API, the body is the implementation. What you have as the package (spec) is the package body. So change that second chunk of code to start

CREATE OR REPLACE PACKAGE BODY functionbyfour AS

and at least you'll have your program structured correctly.

The Oracle PL/SQL documentation is online, comnprehensive and free. I urge you to read it. Find out more.