Incomplete/Malformed cursor

Question

I have a PL/SQL script to perform some aggregation tasks. I am having compilation errors, logic is not top priority at this moment as that can be changed once the errors are resolved. The script is as follows :

SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-MON-yy';
CREATE OR REPLACE PROCEDURE updateFeaturePerformanceTable
    (noOfDays IN NUMBER, runDate IN DATE, timeSpan IN VARCHAR2)
AS
    CURSOR c_feature_performance IS 
        SELECT distinct mkt_id,dow,device_type,feature_name 
        FROM gfmdev.feature_performance 
        WHERE timespan = 'ONE_DAY' 
        AND feature_performance_day >= TO_DATE('17-AUG-15','dd-MON-yy');
    rowsExtracted c_feature_performance%ROWTYPE;
    extractDate DATE;
    timespan_test varchar2(20);
BEGIN
    OPEN c_feature_performance;
    extractDate := runDate - noOfDays;
    timespan_test := timeSpan;
    LOOP
        FETCH c_feature_performance INTO rowsExtracted;
        EXIT WHEN c_feature_performance%NOTFOUND;

        dbms_output.put_line(extractDate || ' ' || timespan_test);

        INSERT INTO gfmdev.feature_performance
        SELECT  
            rowsExtracted.mkt_id,
            rowsExtracted.dow,
            rowsExtracted.device_type,
            rowsExtracted.feature_name,
            SUM(OPS),
            SUM(GV_COUNT),
            runDate,
            timespan_test
    FROM    gfmdev.feature_performance   
    WHERE   feature_performance_day BETWEEN extractDate AND runDate 
    AND timespan = 'ONE_DAY'
    AND mkt_id = rowsExtracted.mkt_id
    AND dow = rowsExtracted.dow
    AND device_type = rowsExtracted.device_type
    AND feature_name = rowsExtracted.feature_name
    group by mkt_id, dow, device_type, feature_name, timespan;      

END LOOP;
CLOSE c_feature_performance;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line('Trying to insert too many rows in SELECT...INTO');
        ROLLBACK;
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('No rows returned in SELECT...INTO');
        ROLLBACK;
    WHEN STORAGE_ERROR THEN
        dbms_output.put_line('Too much data to handle! Storage error');
        ROLLBACK;
    WHEN OTHERS THEN
        dbms_output.put_line('Oops! Something went wrong');
        ROLLBACK;
        RAISE;      

END updateFeaturePerformanceTable;

Show compilation errors:

SHOW ERRORS PROCEDURE updateFeaturePerformanceTable;

Run the procedure:

DECLARE
    runDate DATE;
BEGIN
FOR j IN 0 .. 5 LOOP
    SELECT (TO_DATE('17-AUG-15','dd-MON-yy') + j) INTO runDate FROM dual;
    updateFeaturePerformanceTable(6,runDate, 'ONE_WEEK');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' WEEKLY RECORDS UPDATED ');
FOR j IN 0 .. 28 LOOP
    SELECT (TO_DATE('17-AUG-15','dd-MON-yy') + j) INTO runDate FROM dual;
    updateFeaturePerformanceTable(29,runDate, 'ONE_MONTH');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' MONTHLY RECORDS UPDATED ');
COMMIT;
END;
/

When I execute it I get the following error message :

Errors for PROCEDURE UPDATEFEATUREPERFORMANCETABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/9      PLS-00341: declaration of cursor 'C_FEATURE_PERFORMANCE' is
     incomplete or malformed

5/3      PL/SQL: SQL Statement ignored
6/15     PL/SQL: ORA-00942: table or view does not exist
8/16     PL/SQL: Item ignored
16/3     PL/SQL: SQL Statement ignored
16/36    PLS-00320: the declaration of the type of this expression is
     incomplete or malformed

21/3     PL/SQL: SQL Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
36/22    PL/SQL: ORA-00904: "ROWSEXTRACTED"."FEATURE_NAME": invalid
     identifier

36/22    PLS-00320: the declaration of the type of this expression is
     incomplete or malformed

            updateFeaturePerformanceTable(6,runDate, 'ONE_WEEK');
            *

Any help on where I am going wrong is highly appreciated?

Answer 1

PL/SQL is a framework for running SQL statements programmatically. So often we find that PL/SQL errors are caused by the SQL errors in our code.

That is the case here. You have several PL/SQL errors indicating that the Cursor declaration is invalid. And why is it invalid? This line holds the answer:

6/15     PL/SQL: ORA-00942: table or view does not exist

So the problem is, the owner of the procedure UPDATEFEATUREPERFORMANCETABLE (ugly name by the way) does not have rights on gfmdev.feature_performance.

To solve, the table owner GFMDEV needs to grant SELECT and INSERT directly to the account which owns this procedure.

SQL>  conn gfmdev/password
SQL>  grant select, insert on feature_performance to whoever;

Note that granting privileges through a view won't cut it. The Oracle security model only permits us to build objects - PL/SQL programs, views - with privileges granted directly to our user.