No data found error in PL/SQL with the PROCEDURE

Question

I am new with PLSQL and I have issue with this procedure. I don't know what the error mean at the same time I am sure the table and data are created successfully.

the procedure should receive the start and end date with the invoice number to show the details

create or replace PROCEDURE Invoicedetails (Fromdate IN DATE , Todate IN DATE , InvoiceNum NUMBER)
IS 
INV_info invoicetable%ROWTYPE;
BEGIN
SELECT * 
INTO INV_info
FROM invoicetable
WHERE InvoiceNum = INV_info.InvoiceNum AND INV_info,InvoiceDate betwen Fromdate And Todate;
dbms_output.put_line('ID:'|| INV_info.InvoiceNum);
dbms_output.put_line('Amount:'|| INV_info.Invoiceamount);
dbms_output.put_line('Date:'|| INV_info.InvoiceDate);
END Invoicedetails;

When I call the procedure like this

BEGIN
Invoicedetails('01-JAN-2020','05-JAN-2020',200651)
END;

Error report ORA-01403 :no data found ORA-06512 : at "APPS.Invoicedetails",line 5 ORA-06512 : at line 2 01403. 00000 - "no data found"

Answer 1

If you say you are new then you have done a good job.

Let's dig into the problem,

If you are learning then put into TODO list, the next topic about exception in PLSQL and how to handle.

The error you get say ORA-01403 :no data found which is self explanatory and mean we are searching for something and whatever code you have written didn't find it as expected which leads us to the select statement,

SELECT * 
  INTO INV_info
  FROM invoicetable
 WHERE InvoiceNum = INV_info.InvoiceNum 
   AND INV_info,InvoiceDate betwen Fromdate And Todate;

In the above if you see,

• First small problem is syntactical which is INV_info,InvoiceDate which should be INV_info.InvoiceDate (this is anyway not correct as per the expectations of result which I will clarify below)

• Second and most important problem is you are trying to compare the value of InvoiceNum with the rowtype variable which is InvoiceNum = INV_info.InvoiceNum and you have to understand here INV_info.InvoiceNum is a variable and doesn't hold any value at this very time. So you should compare the table value with the input you provided via parameter as WHERE invoicetable.InvoiceNum = invoiceNum. Left side is the table column and right side is the parameter you passed.

• Similarly the condition AND INV_info,InvoiceDate betwen Fromdate And Todate should change to AND invoicetable.InvoiceDate betwen Fromdate And Todate.

Having said all these there are some things you also need to consider interms of naming convention of variables and also usage of alias for table. (Which can be seen what changes I made to the procedure below)

Accumulating all points the procedure can be further modified as,

create or replace procedure invoicedetails 
 ( 
   p_fromdate in date 
 , p_todate in date 
 , p_invoicenum number)
is 
  inv_info invoicetable%rowtype;
begin
  select * 
    into inv_info
    from invoicetable i
   where i.invoicenum = p_invoicenum 
     and i.invoiceDate between p_fromdate and p_todate;
  dbms_output.put_line('ID:'|| inv_info.invoicenum);
  dbms_output.put_line('Amount:'|| inv_info.invoiceamount);
  dbms_output.put_line('Date:'|| inv_info.invoicedate);
end invoicedetails;
/

Here is db<>fiddle for your reference. I have to do a little trick by calling dbms.output to print the result while calling the procedure which you don't need when you try in your machine

Answer 2

First thing out of the chute, you declare to input parameters as DATE, but then when you call the procedure you supply a CHARACTER STRING. Just because the input looks like a date to you does not mean that oracle interprets as a DATE. DATE is an internal, binary data type. Where will the input values actually originate? As per your example, you need to convert the input string to a DATE:

create or replace PROCEDURE Invoicedetails (Fromdate IN VARCHAR2 , Todate IN VARCHAR2 , InvoiceNum NUMBER)
IS 
v_fromdate date;
v_todate date;
INV_info invoicetable%ROWTYPE;
BEGIN
v_fromdate := to_date(fromdate,'dd-Mon-yyyy');
v_todoate := to_date(todate,'dd-Mon-yyyy');

Then, in the rest of your code, reference v_fromdate and v_todate instead of your input parms.

As an aside, you should also develop the habit of being consistent in your coding style. Unlike some other rdbms products, oracle really doesn't support MixedCaseNames. (Well, you can jump through some hoops to force it to, but that is going against the grain and you really don't want to go there.) Instead of MixedCaseNames, the oracle standard is underscore_separated_names.

Answer 3

You need to:

• Not name the procedure's arguments the same as columns in your table; it's confusing to debug and can confuse the SQL parser into comparing the column to itself rather than comparing the column to the parameter's argument.
• Handle the NO_DATA_FOUND exception.
• Handle the TOO_MANY_ROWS exception.

Using something like this:

CREATE PROCEDURE InvoiceDetails (
  p_FromDate   IN InvoiceTable.InvoiceDate%TYPE, -- Use the types from the table
  p_ToDate     IN InvoiceTable.InvoiceDate%TYPE,
  p_InvoiceNum IN InvoiceTable.InvoiceNum%TYPE
)
IS 
  inv_info invoicetable%ROWTYPE;
BEGIN
  SELECT * 
  INTO   INV_info
  FROM   invoicetable
  WHERE  InvoiceNum = p_InvoiceNum -- Don't have the same variable name as the
                                   -- column name. One practice is to prefix the
                                   -- parameter names with p_ to distinguish
                                   -- that they were passed from outside the
                                   -- procedure.
  AND    InvoiceDate BETWEEN p_FromDate AND p_ToDate;

  dbms_output.put_line('ID:     ' || INV_info.InvoiceNum);
  dbms_output.put_line('Amount: ' || INV_info.InvoiceAmount);
  dbms_output.put_line('Date:   ' || TO_CHAR( INV_info.InvoiceDate, 'YYYY-MM-DD' ) );
EXCEPTION
  WHEN NO_DATA_FOUND THEN -- Handle the exception when no rows are found.
    dbms_output.put_line('No Invoices exist.');
  WHEN TOO_MANY_ROWS THEN -- Handle the exception when multiple rows are found.
    dbms_output.put_line('Multiple invices exist.');
END InvoiceDetails;
/

So, if you have the table:

CREATE TABLE invoicetable (
  invoicenum    NUMBER(10,0),
  invoiceamount NUMBER(17,2),
  invoicedate   DATE
);

and then execute your anonymous PL/SQL block:

BEGIN
  Invoicedetails( DATE '2020-01-01',DATE '2020-01-05',200651);
END;
/

There will be no rows to match and the NO_DATA_FOUND exception will be raised and you get the output:

No Invoices exist.

If you then insert a row:

INSERT INTO  invoicetable (invoicenum, invoiceamount, invoicedate )
VALUES ( 200651, 200, DATE '2020-01-04' );

and run the same anonymous PL/SQL block, you now get the output:

ID:     200651
Amount: 200
Date:   2020-01-04

and, if you insert a second row:

INSERT INTO  invoicetable (invoicenum, invoiceamount, invoicedate )
VALUES ( 200651, 300, DATE '2020-01-05' );

and run the anonymous PL/SQL block again, you get the output:

Multiple invices exist.

db<>fiddle here