In PL/SQL code I am trying to get counts of records that have a certain column with values stored in a SQL table containing the lookup values of interest.
Here is my attempt:
PROCEDURE my_procedure
...
CURSOR del_method_crsr IS
SELECT delivery_method -- delivery_method is VARCHAR2 column
FROM delivery_method_lookup;
...
TYPE del_method_cnts_table_type IS TABLE OF NUMBER
INDEX BY VARCHAR2(32);
del_method_counts del_method_cnts_table_type;
del_method_char VARCHAR2(32);
cat_count LONG;
...
BEGIN
...
FOR del_method IN del_method_crsr
LOOP
del_method_char := CAST(del_method AS VARCHAR2); --**** Error occurs on this line ****
SELECT COUNT(*)
INTO cat_count
FROM (...);
del_method _counts(del_method) := cat_count
END LOOP;
...
END;
When I try to compile I get error "PLS-00382: expression is of wrong type"
I have tried several methods to overcome this problem, this CAST attempt is just the last, but all attempts result in the same error.
I am assuming that the problem is that del_method is not of type VARCHAR2 (is it something like a pointer?) so I am trying to convert it the VARCHAR2 so that it can be used for the key to the table.
My original attempt for the select statement in the Cursor For loop was of the form:
SELECT COUNT(*)
INTO del_method_counts(del_method)
FROM (...);
Is my assumption correct or is there some other problem I am not aware of?
How do I make this work?
Thanks.