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;

/

Justin Cave Justin Cave · Accepted Answer · 2013-04-16T15:57:10

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.

Function/Package issue PL/SQL

3 Answers