INSERT INTO error and PLS-00231 error Function may not be used in SQL

Question

I was trying to call a function from a procedure and encounter an error that need someone else with a fresh mind to have a look at. I have included the code for both the function and the error message below. I have an error with the INSERT INTO statement as well (SQL statement ignored).

PROCEDURE produceForecast AS
    insert_company_code VARCHAR2(25);
    insert_settlement_case_id NUMBER(25);
    insert_settlement_run_id NUMBER(25);
    insert_tni VARCHAR2(25);
    insert_metertype VARCHAR2(25);
    insert_frmp VARCHAR2(25);
    insert_lr VARCHAR2(25);
    insert_mdp VARCHAR2(25);
    insert_transaction_id VARCHAR2(25);
    insert_startdate DATE;
    insert_startdayname VARCHAR2(10);

    CURSOR c_ParseAllData IS
        SELECT DISTINCT company_code, settlement_case_id, settlement_run_id, tni, metertype, frmp, lr, mdp, transaction_id
        FROM v_nem_rm16;

BEGIN
    OPEN c_ParseAllData;
    FOR i IN 1..10 
    LOOP 
        FETCH c_ParseAllData
        INTO 
            insert_company_code,
            insert_settlement_case_id,
            insert_settlement_run_id,
            insert_tni,
            insert_metertype,
            insert_frmp,
            insert_lr,
            insert_mdp,
            insert_transaction_id;

            FOR insert_currentDay IN 1..14
            LOOP
                insert_startdate := TRUNC(sysdate + insert_currentDay);
                insert_startdayname := trim(TO_CHAR(TO_DATE(insert_startdate, 'DD-MON-YYYY'),'day'));

                FOR insert_currentWeek IN 1..48
                LOOP
                INSERT 
                INTO local_rm16 --Error occured here
                (
                    company_code,
                    settlement_case_id,
                    settlement_run_id,
                    statement_type,
                    tni,
                    metertype,
                    frmp,
                    lr,
                    mdp,
                    change_date,
                    DAY,
                    transaction_id,
                    hh,
                    volume
                )
                VALUES
                (
                    insert_company_code,
                    insert_settlement_case_id,
                    insert_settlement_run_id,
                    'FORECAST',
                    insert_tni,
                    insert_metertype,
                    insert_frmp,
                    insert_lr,
                    insert_mdp,
                    sysdate,
                    insert_startdate,
                    insert_transaction_id,
                    insert_currentWeek,
                    calculateAvgVolume(insert_startdayname, insert_currentWeek)   --Error occurred here
                );
            END LOOP;
        END LOOP;
    END LOOP;
CLOSE c_ParseAllData;

common.log('Forecasted values for the next 2 weeks have been written to LOCAL_RM16 table');

END produceForecast;

This is the error message

FUNCTION calculateAvgVolume
(
    p_dayname IN VARCHAR2,
    p_timeinterval IN NUMBER
)
    RETURN FLOAT
AS
    v_avg FLOAT(10);

BEGIN
    SELECT AVG(volume)
    INTO v_avg
    FROM tmpForecast
    WHERE currentDay = p_dayname
    AND dayInterval = p_timeinterval;

    RETURN v_avg;
END calculateAvgVolume;

What does "calculateAvgVolume" do?

Littlefoot
– Littlefoot

2020-06-05 08:41:03 +00:00
Commented Jun 5, 2020 at 8:41 — Littlefoot
– Littlefoot, Commented Jun 5, 2020 at 8:41
I have added the code for the calculateAvgVolume FUNCTION

tam nguyen
– tam nguyen

2020-06-05 09:16:56 +00:00
Commented Jun 5, 2020 at 9:16 — tam nguyen
– tam nguyen, Commented Jun 5, 2020 at 9:16

Tony Andrews · Accepted Answer · 2020-06-23 10:55:20Z

To be able to call a function from SQL it needs to be visible to SQL, i.e. public. Probably you have defined function calculateAvgVolume in the body of the package that contains produceForecast, but not in the spec to make it public.

In this instance you could avoid making the function public by assigning its result to a variable before doing the insert:

var := calculateAvgVolume(insert_startdayname, insert_currentWeek);

Then use the variable var in the insert statement in place of the function call.

This is how it would work in your code:

PROCEDURE produceForecast AS
    insert_company_code VARCHAR2(25);
    insert_settlement_case_id NUMBER(25);
    insert_settlement_run_id NUMBER(25);
    insert_tni VARCHAR2(25);
    insert_metertype VARCHAR2(25);
    insert_frmp VARCHAR2(25);
    insert_lr VARCHAR2(25);
    insert_mdp VARCHAR2(25);
    insert_transaction_id VARCHAR2(25);
    insert_startdate DATE;
    insert_startdayname VARCHAR2(10);
    v_average_volume local_rm16%TYPE;

    CURSOR c_ParseAllData IS
        SELECT DISTINCT company_code, settlement_case_id, settlement_run_id, tni, metertype, frmp, lr, mdp, transaction_id
        FROM v_nem_rm16;

BEGIN
    OPEN c_ParseAllData;
    FOR i IN 1..10 
    LOOP 
        FETCH c_ParseAllData
        INTO 
            insert_company_code,
            insert_settlement_case_id,
            insert_settlement_run_id,
            insert_tni,
            insert_metertype,
            insert_frmp,
            insert_lr,
            insert_mdp,
            insert_transaction_id;

            FOR insert_currentDay IN 1..14
            LOOP
                insert_startdate := TRUNC(sysdate + insert_currentDay);
                insert_startdayname := trim(TO_CHAR(TO_DATE(insert_startdate, 'DD-MON-YYYY'),'day'));

                v_average_volume := calculateAvgVolume(insert_startdayname, insert_currentWeek);

                FOR insert_currentWeek IN 1..48
                LOOP
                INSERT 
                INTO local_rm16 --Error occured here
                (
                    company_code,
                    settlement_case_id,
                    settlement_run_id,
                    statement_type,
                    tni,
                    metertype,
                    frmp,
                    lr,
                    mdp,
                    change_date,
                    DAY,
                    transaction_id,
                    hh,
                    volume
                )
                VALUES
                (
                    insert_company_code,
                    insert_settlement_case_id,
                    insert_settlement_run_id,
                    'FORECAST',
                    insert_tni,
                    insert_metertype,
                    insert_frmp,
                    insert_lr,
                    insert_mdp,
                    sysdate,
                    insert_startdate,
                    insert_transaction_id,
                    insert_currentWeek,
                    v_average_volume 
                );
            END LOOP;
        END LOOP;
    END LOOP;
CLOSE c_ParseAllData;

common.log('Forecasted values for the next 2 weeks have been written to LOCAL_RM16 table');

END produceForecast;

but the insert_startdayname and insert_currentweek are parameters inside the PROCEDURE produceForecast. is there another way around this without declaring the calculateAvgVolume in the spec and making it public?

Collectives™ on Stack Overflow

INSERT INTO error and PLS-00231 error Function may not be used in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related