CODEWITHSUNDEEP
sqloracle-databaseplsql
Splunk
Splunk

Reputation: 311

PLS-00306: when calling a function from a PL/SQL block

I am getting the PLS-00306 error when I attempt to run a PL/SQL block that calls a variety of things including a function. The job of the function is to count how many cars belong to a certain model type. The function works if I call it in a SQL statement or it's own block, it just doesn't seem to work here.

This is the function:

CREATE OR REPLACE Function findtotalcarmodels(
  model_name_in IN varchar2)
RETURN NUMBER
IS
  counter NUMBER := 0;
  CURSOR car_count_cur IS
    SELECT model_name 
      FROM i_car 
     WHERE model_name = model_name_in;
  Rec_car_details car_count_cur%ROWTYPE;
BEGIN
  OPEN car_count_cur;
  LOOP
    FETCH car_count_cur INTO Rec_car_details;
    EXIT WHEN car_count_cur%NOTFOUND;
    counter := counter + 1;
  END LOOP;
  CLOSE car_count_cur;
  RETURN counter;
END;

This is the Block:

SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000 
Declare 
  v_model VARCHAR2(40);
  v_carcategory VARCHAR2(40);
  v_totalcars NUMBER;
  v_maxdate DATE:=TO_DATE(1, 'J');
  Cursor carcur IS 
    SELECT * 
      FROM i_car;
  CURSOR c1(v_car_registration VARCHAR2) IS 
    SELECT * 
      from i_booking a
     WHERE a.registration=v_car_registration;
Begin  
  For car_rec in carcur
  LOOP
    v_maxdate:=TO_DATE(1, 'J');
    for rec in c1(car_rec.registration)
    loop
      IF rec.date_reserved > v_maxdate 
      then
        v_maxdate:=rec.date_reserved ;
        If car_rec.Cost <=50000 
        THEN 
          v_carcategory := 'Budget Car';
        End IF;
        If car_rec.Cost BETWEEN 50000 AND 100000 
        THEN 
          v_carcategory := 'Standard Car';
        End IF;
        If car_rec.Cost >100000 
        THEN 
          v_carcategory := 'Premium Car';
        End If;
      end IF;
      v_totalcars := findtotalcarmodels;
    end loop;
    DBMS_OUTPUT.PUT_LINE('Registration:'|| ' '|| car_rec.registration); 
    DBMS_OUTPUT.PUT_LINE('Cost:'|| ' $' || car_rec.Cost); 
    DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '|| car_rec.model_name); 
    DBMS_OUTPUT.PUT_LINE('Car Category:'|| ' '||v_carcategory);
    DBMS_OUTPUT.PUT_LINE('Total number of Cars:'|| ' '||v_totalcars);
    DBMS_OUTPUT.PUT_LINE('Most Recent Rental Date: '|| ' '||v_maxdate);
    DBMS_OUTPUT.NEW_LINE; 
  END LOOP; 
END;
/

Before I get slammed for the style of the PL/SQL block, just keep in mind that it is written to requirement and everything works well with the exception of the function.

If someone could point me in the right direction to call this function without error I would be very grateful.

Upvotes: 0

Views: 280

Answers (2)

Dba
Dba

Reputation: 6639

It looks like you have missed to pass the IN parameter to the function.

Try like this,

v_totalcars := findtotalcarmodels('<model_name_in>');

Upvotes: 1

Dom84
Dom84

Reputation: 864

Well, when i see things right, you don't fillup the parameter model_name. When you don't have an overriden function wihtout parameter you need to fill it up.

AS you can also see the PLS-00306 is telling you something about wrong number of arguments.

Upvotes: 0

Related Questions