Function to return COUNT value without COUNT

Question

The requirements are as follows:

Create a PL/SQL function called findtotalcarmodels to return the total number of cars belonging to a particular model. The function should have a single IN parameter as model_name. You should then use an explicit cursor to count the number of cars belonging to that car model and return the final count. You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) to create this function.

The code I have come up with so far is:

CREATE OR REPLACE Function findtotalcarmodels
(model_name IN varchar2)
RETURN NUMBER
IS
CURSOR car_count_cur IS
SELECT model_name FROM i_car;
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;
END LOOP;
CLOSE car_count_cur;
RETURN Rec_car_details;
END;

I am getting the following errors:

Errors for FUNCTION FINDTOTALCARMODELS:
LINE/COL     ERROR
15/1     PL/SQL: Statement ignored
15/8     PLS-00382: expression is of wrong type

What am I doing wrong here?

Who is telling you that you must use a cursor instead of the right way (a single SELECT statement)? If it's for a class, find a new instructor. Seriously. — siride
– siride, Commented Oct 9, 2013 at 2:51
Ha! I agree. I emailed him at told him I fail to see any real world application for this method. But it is what it is... — Splunk
– Splunk, Commented Oct 9, 2013 at 3:08
Agreed that this is a pretty silly assignment. Especially because almost any 'in code' implementation of COUNT() is probably going to be in C/C++, be looking at indices/table statistics, etc. Note that I do think there is value in doing 'base' implementations of some common library functions (like, say, ArrayList in Java) for lessons... it's just that the SQL stuff is so integral to it, it feels like trying to implement an integer class. Now, an implementation of BigInteger I could understand... at least the exception list shows the prof knows this is a strange way to do this. — Clockwork-Muse
– Clockwork-Muse, Commented Oct 9, 2013 at 3:27

score 1 · Accepted Answer · 2013-10-09 02:14:43Z

1

You are trying to return the cursor from the inline function, and the function is looking to return an integer.

You need an (integer) counter to increment each time you iterate through the cursor... and then you can return that. I didn't test this, but this should work:

CREATE OR REPLACE Function findtotalcarmodels
(model_name_in IN varchar2)
RETURN NUMBER
IS

DECLARE counter INTEGER := 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;

edited Oct 9, 2013 at 2:14

answered Oct 9, 2013 at 1:50

user410039

Sign up to request clarification or add additional context in comments.

3 Comments

Splunk Over a year ago

I got rid of 'DECLARE' and it compiles well. Thanks for the reply.

Splunk Over a year ago

Hmm sadly the function returns nonsense. It is returning a value of 20 for everything...

user410039 Over a year ago

I updated the code to add a WHERE clause to your SQL... You need to filter the result set based on the model car passed in (I also changed the parameter name.

Noel · Accepted Answer · 2013-10-09 04:22:00Z

1

CREATE OR REPLACE FUNCTION findtotalcarmodels (vc_model_name IN VARCHAR2)
   RETURN NUMBER
AS
   CURSOR c1
   IS
      SELECT *
        FROM i_car
       WHERE UPPER (model_name) = UPPER (vc_model_name);

   cnt   NUMBER;
BEGIN
   cnt := 0;

   FOR i IN c1 LOOP
      cnt := cnt + 1;
   END LOOP;

   RETURN cnt;
END;

You can check in dual table such as,

SELECT findtotalcarmodels('SUV1') FROM DUAL;  -- here suv1 is your modelname

edited Oct 9, 2013 at 4:22

Noel

10.6k30 gold badges53 silver badges74 bronze badges

answered Oct 9, 2013 at 4:18

Arun Narang

211 silver badge4 bronze badges

Collectives™ on Stack Overflow

Function to return COUNT value without COUNT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related