Procedure: PL/SQL: ORA-00904: "QUARTER": invalid identifier-CodePudding

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?

CodePudding user response：

None of the functions used are built in Oracle functions, they may user defined but Oracle does provide to_char(some_date,'fmt') where the fmt code can provide each of the values you looking need, including quarter. See to_char. Further there is no need to loop through dates. You can do this is a single statement, or build it into a procedure.

create or replace procedure pop_date_dim(
                            start_date     in date
                          , number_of_days in number
                          )
is    
begin 
     insert into date_dim(
                <list of columns> 
               ) 
     with date_list (each_date) as 
          ( select start_date   (level-1) 
              from dual connect by level <= number_of_days
          ) 
    select <list of values> 
      from date_list;
end pop_date_dim;

See demo. The demo only does some of the fields, be should be enough show the process.

CodePudding user response：

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 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,
       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;
/

Demo