Is there anyway to test the function in SQL?

I have following problem. I’ve made an function which checks dates, warehouse if it’s full, etc..

Here is the code and I’ve added comments so it is more clear and understanble:

SET SERVEROUTPUT ON
CREATE OR REPLACE
PROCEDURE Anlieferung (P_Artikelbezeichnung VARCHAR, P_Datum date, P_Stueck INT) AS
_inputQuantity int;
_currentQuantity int;
_inputItem int;
_articleNummber int;
_quanitityToInput int:=P_Stueck;
_maximumLnr int;

BEGIN 
  FOR v_rec IN (SELECT * FROM lager) LOOP -- implicit cursor
  _currentQuantity:=0;
  
  SELECT artikel.anr -- Adding our article nummber in our variable called "_articleNummber"
  INTO _articleNummber
  FROM artikel
  WHERE artikel.bezeichnung = P_Artikelbezeichnung;
  
  FOR buchung IN(SELECT * FROM lagerbuchung lag --Checking the lnr and the date
  WHERE lag.lnr = v_rec.lnr AND P_Datum >= lag.datum)
  LOOP
  --Menge in _currentQuantity einfügen
  _currentQuantity:=_currentQuantity + buchung.stueck; --Adding our current quantity in our variable called "_currentQuantity"
  END LOOP;
  
  
  IF(v_rec.stueckkap > _currentQuantity OR _quanitityToInput >0) --Checking our capacity
  THEN
  _inputItem:=v_rec.stueckkap - _currentQuantity;
  
  IF(_inputItem <= _quanitityToInput)
  THEN
  _inputQuantity:= _inputItem;
  _quanitityToInput:=_quanitityToInput - _inputItem;
  ELSE
  _inputQuantity:= _quanitityToInput;
  _quanitityToInput:=0;
  END IF;
  
  SELECT COALESCE(MAX(lfndnr),0) -- If we have null in our table, we will also get null in return 
  INTO _maximumLnr
  FROM lagerbuchung;
  
  _maximumLnr:=_maximumLnr +1;
  
  --Inserting our new values in our table
  INSERT INTO lagerbuchung (lfndnr, datum, stueck, anr, lnr) VALUES (_maximumLnr, P_Datum, _inputQuantity, _articleNummber, v_rec.lnr);
  
  END IF;
  END LOOP;
END Anlieferung;
/

SELECT l.LNR, SUM(lb.STUECK) FROM LAGER l JOIN LAGERBUCHUNG lb on l.lnr = lb.lnr group by l.lnr order by l.lnr;
EXEC Anlieferung('Apfel', '27.11.2015', 160);
SELECT l.LNR, SUM(lb.STUECK) FROM LAGER l JOIN LAGERBUCHUNG lb on l.lnr = lb.lnr group by l.lnr order by l.lnr;

So basically if I run only the function I don’t get any error. But when I run it with the test date I get following error:

Fehler beim Start in Zeile : 166 in Befehl -
EXEC Anlieferung('Apfel', '27.11.2015', 160)
Fehlerbericht -
ORA-06550: line 1, column 7:
PLS-00905: object IF4EBIHORACM.ANLIEFERUNG is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I’ve tried to debbug it and also input different test dates but still, same error.

Answer

After you compile the procedure either run:

SHOW ERRORS

or (assuming you are compiling it as the owning user and not as a DBA user; if you are not the owning user then use ALL_ERRORS):

SELECT * FROM USER_ERRORS;

Either will give you a list of the errors in the procedure and you can then debug it and fix the errors so the procedure works.

---

Update

From the comment:

2/1 PLS-00103: Encountered the symbol "_" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language

Could it be because of my variables? They start with underscore

From the Database Object Names and Qualifiers documentation:

Database Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

• A quoted identifier begins and ends with double quotation marks (“). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
• A nonquoted identifier is not surrounded by any punctuation.

…

6. Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

You have many identifiers for variables that begins with an underscore. This tells you that the solution is to change the variable names to start with an alphabetic character (or to use a quoted identifier and surround the identifier with double quotes; but don’t use quoted identifier, just use a letter as a prefix before the underscore):

CREATE OR REPLACE PROCEDURE Anlieferung (
  P_Artikelbezeichnung VARCHAR,
  P_Datum              date,
  P_Stueck             INT
)
AS
  v_inputQuantity    int;
  v_currentQuantity  int;
  v_inputItem        int;
  v_articleNummber   int;
  v_quanitityToInput int:=P_Stueck;
  v_maximumLnr       int;
BEGIN
...

Also, don’t use:

SELECT COALESCE(MAX(lfndnr),0) -- If we have null in our table, we will also get null in return 
INTO _maximumLnr
FROM lagerbuchung;
  
_maximumLnr:=_maximumLnr +1;

As it may generate duplicate values if the procedure is called twice at the same time.

Instead, create a sequence for the column and get the next value from the sequence.

CREATE SEQUENCE lagerbuchung__lfndnr__seq START WITH 213 INCREMENT BY 1;

(Start with whatever is 1 more than your current maximum value.)

Then in your procedure use:

INSERT INTO lagerbuchung (
  lfndnr,
  datum,
  stueck,
  anr,
  ln
) VALUES (
  lagerbuchung__lfndnr__seq.NEXTVAL,
  P_Datum,
  v_inputQuantity,
  v_articleNummber,
  v_rec.lnr
);