ORA-06550: line 5, column 31: PL/SQL: ORA-00904: "MAX_DATE": invalid identifier

Good morning friends,

I created a Bind variable called MAX_Date as follow. But when I try to use it on the next procedure I am getting invalid identifier error. Can some one help on how to pass the Bind variable inside the body of the program?

-- Declare a variable to store the maximum date
DECLARE
max_date DATE;
BEGIN
-- Calculate the maximum date from tbase.pay_ssnchk07 table
SELECT MAX(trunc(pay_period_end_date)) INTO max_date
FROM transbase_master.pay_ssnchk07;

END;

-- Declare the cursor for processing TG_PROD_PREDICT03WK_C
DECLARE
CURSOR data_cursor IS
SELECT *
FROM TG_PROD_PREDICT03WK_C
WHERE week_end_date > max_date; -- the issue is here

-- Define variables to match the array elements
data_rec data_cursor%ROWTYPE;

BEGIN
-- Loop through the cursor
FOR data_rec IN data_cursor LOOP
-- Set variables to NULL or 0 based on your logic
data_rec.prodwday := NULL;
data_rec.rvsrs := NULL;
data_rec.vsrs := NULL;
data_rec.rvsrplusvsr := NULL;
data_rec.othrs := NULL;
data_rec.othrsperwkday := NULL;
data_rec.othrsperwkrperwkday := NULL;
data_rec.rbprod := NULL;
data_rec.totwkhrsperwkday := NULL;
data_rec.othrsperwkrperwkdayasp := NULL;
data_rec.rvsr_othrsperwkrperwkdayasp := NULL;
data_rec.vsr_othrsperwkrperwkdayasp := NULL;
data_rec.fdcpct := NULL;
data_rec.x0006 := NULL;
data_rec.rfdcount := NULL;
data_rec.rfdwday := NULL;
data_rec.autorfdwday := NULL;
data_rec.othrsaspen := NULL;
data_rec.vsravlhrsperwkday := 0;
data_rec.rvsravlhrsperwkday := 0;
data_rec.xvsravlhrsperwkday := 0;
data_rec.dampfactor := 1;

    -- Update the dataset
    UPDATE TG_PROD_PREDICT03WK_C
    SET
        prodwday = data_rec.prodwday,
        rvsrs = data_rec.rvsrs,
        vsrs = data_rec.vsrs,
        rvsrplusvsr = data_rec.rvsrplusvsr,
        othrs = data_rec.othrs,
        othrsperwkday = data_rec.othrsperwkday,
        othrsperwkrperwkday = data_rec.othrsperwkrperwkday,
        rbprod = data_rec.rbprod,
        totwkhrsperwkday = data_rec.totwkhrsperwkday,
        othrsperwkrperwkdayasp = data_rec.othrsperwkrperwkdayasp,
        rvsr_othrsperwkrperwkdayasp = data_rec.rvsr_othrsperwkrperwkdayasp,
        vsr_othrsperwkrperwkdayasp = data_rec.vsr_othrsperwkrperwkdayasp,
        fdcpct = data_rec.fdcpct,
        x0006 = data_rec.x0006,
        rfdcount = data_rec.rfdcount,
        rfdwday = data_rec.rfdwday,
        autorfdwday = data_rec.autorfdwday,
        othrsaspen = data_rec.othrsaspen,
        vsravlhrsperwkday = data_rec.vsravlhrsperwkday,
        rvsravlhrsperwkday = data_rec.rvsravlhrsperwkday,
        xvsravlhrsperwkday = data_rec.xvsravlhrsperwkday,
        dampfactor = data_rec.dampfactor
    WHERE CURRENT OF data_cursor;
END LOOP;

END;
/

Hello john,

last time when you helped me on this Bind Var, You said, PL/SQL does not know "Date" datatype. So, you suggested to change it to VARCHAR as follow:

Declare :MAX_DATE as a VARCHAR2, and use TO_CHAR when setting its value. I have the following:

-- Declare a variable to store the maximum date
DECLARE
max_date VARCHAR2(20);
BEGIN
-- Calculate the maximum date from tbase.pay_ssnchk07 table
SELECT MAX(TO_CHAR(pay_period_end_date, 'YYYY-MM-DD')) INTO max_date
FROM transbase_master.pay_ssnchk07;
dbms_output.put_line (max_date);

END;

When I tried to use it on the next sets of instructions as

WHERE week_end_date > TO_DATE(max_date,'YYYY-MM-DD'); -- the issue is here

still I am getting invalid identifier error. dbms_output.put_line (max_date); has displayed 2023-10-07. So, the Bind Var is holding the correct issue.

when you have 2 separate anonymous blocks, like below, the 2nd one does not know anything about the first one's variables.

-- Declare a variable to store the maximum date
DECLARE
  max_date DATE;
BEGIN
  -- Calculate the maximum date from tbase.pay_ssnchk07 table
  SELECT MAX(trunc(pay_period_end_date)) INTO max_date
  FROM transbase_master.pay_ssnchk07;
END; -- MAX DATE is forgotten here!

-- Declare the cursor for processing TG_PROD_PREDICT03WK_C
DECLARE
  CURSOR data_cursor IS
  SELECT *
  FROM TG_PROD_PREDICT03WK_C
  WHERE week_end_date > max_date; **-- the issue is here**

Actually I said (or meant to say) that SQL*Plus does not know the DATE datatype. PL/SQL knows it just fine.

You don't need to select a value into a variable to be used in another SQL. Just combine the SQL, like this:

DECLARE
  CURSOR data_cursor IS
    SELECT *
    FROM TG_PROD_PREDICT03WK_C
    WHERE week_end_date > (SELECT MAX(trunc(pay_period_end_date)) FROM transbase_master.pay_ssnchk07);
...
...

Hello John,

This is beautiful. I do that, I do not need to use a bind variable. for some reason, I got the following error on the same program:

[Window Title]
Toad for Oracle - Error

[Main Instruction]
ORA-06550

[Content]
ORA-06550: line 65, column 26:
PLS-00404: cursor 'DATA_CURSOR' must be declared with FOR UPDATE to use with CURRENT OF

Copy to clipboard

[V] See details [Ignore] [Ignore All] [Ignore All ORA-06550] [Search Forums] [Cancel]

[Expanded Information]
ORA-06550: line 65, column 26:
PLS-00404: cursor 'DATA_CURSOR' must be declared with FOR UPDATE to use with CURRENT OF
ORA-06550: line 65, column 26:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 41, column 9:
PL/SQL: SQL Statement ignored

Just if you have time.

The error is telling you what you need. Add "For update" to your cursor declaration. You need it because you are using with the "CURRENT OF" in your code.

Thank you Very much John.

1 Like