Procedure: PL/SQL: ORA-00904: “QUARTER”: invalid identifier

I’m new to Oracle PL/SQL and I’m attempting to create a procedure, called pop_date_dim, which populates an already-existing date dimension called date_dim. It does so by accepting both a date (DDMMYYYY) and a number (n) as parameters. The date dimension is then populated by using a FOR loop. Unfortunately, I keep getting two errors. Error(142,9): PL/SQL: SQL Statement ignore and Error(152,13): PL/SQL: ORA-00904: “QUARTER”: invalid identifier.

This is the code:

CREATE OR REPLACE PROCEDURE pop_date_dim (DDMMYYYY IN number, n IN number)
IS 
start_date date;
BEGIN
start_date := TO_DATE(DDMMYYYY, 'DDMMYYYY');
    FOR i IN 1..n LOOP
        INSERT INTO date_dim
            VALUES ( 
            start_date + i, 
            dayofweek(start_date + i), 
            dayofmonth(start_date + i), 
            dayofyear(start_date + i),
            dayname(start_date + i),
            month(start_date + i),
            monthname(start_date + i) + i,
            year(start_date + i),
            quarter(start_date + i)
            ); 
    END LOOP;
END;

It seems to have something to do with Oracle’s quarter() function, which ought to return an integer in the range 1 to 4. I can’t see what the problem is at all. Any advice or pointers?

Answer

All those functions, presumably from MySQL DB, within the VALUES list don’t exists within the Oracle DB, and should be translated.

Indeed no need to populate a table’s columns with those converted values, since you can get them all by using suitable TO_CHAR() conversion for each within a SELECT statement, but for sake of your case that procedure might be rewritten as considering Oracle DB

CREATE OR REPLACE PROCEDURE pop_date_dim(DDMMYYYY IN VARCHAR2, n IN INT) IS
  start_date DATE;
BEGIN
  start_date := TO_DATE(DDMMYYYY, 'DDMMYYYY');
  FOR i IN 1 .. n LOOP
    INSERT INTO date_dim
    VALUES
      (start_date + i,
       TO_CHAR(start_date + i, 'D', 'NLS_DATE_LANGUAGE=English'),
       TO_CHAR(start_date + i, 'DD'),
       TO_CHAR(start_date + i, 'DDD'),
       TO_CHAR(start_date + i, 'Day', 'NLS_DATE_LANGUAGE=English'),
       TO_CHAR(start_date + i, 'MM'),
       TO_CHAR(start_date + i, 'Month', 'NLS_DATE_LANGUAGE=English'),
       TO_CHAR(start_date + i, 'YYYY'),
       TO_CHAR(start_date + i, 'Q'));
  END LOOP;
END;
/

where you should make the first parameter to be of string type rather than a numeric one against for the cases of having that values with leading zero

Demo