Error: PL/SQL: Compilation unit analysis terminated?

Question

Keep Getting the same error on both codes!!

DROP TABLE Date_Dimension CASCADE CONSTRAINTS ;

CREATE TABLE Date_Dimension
  (
    date_key             NUMBER NOT NULL ,
    full_date            DATE ,
    day_of_week          NUMBER ,
    day_num_in_month     NUMBER ,
    day_num_overall      NUMBER ,
    day_name             VARCHAR2 (9) ,
    day_abbrev           VARCHAR2 (3) ,
    week_num_in_year     NUMBER ,
    week_num_overall     NUMBER ,
    week_begin_date      DATE ,
    MONTH                NUMBER ,
    month_number_overall NUMBER ,
    month_name           VARCHAR2 (9) ,
    month_abbrev         VARCHAR2 (3) ,
    quarter              NUMBER ,
    YEAR                 VARCHAR2 (20) ,
    century              NUMBER
  ) ;
ALTER TABLE Date_Dimension ADD CONSTRAINT Date_Dimension_PK PRIMARY KEY ( date_key ) ;

Create or replace PROCEDURE sp_DATE_DIMENSION(v_STARTDATE IN INT, v_END_YEAR IN INT) IS

v_STARTDATE DATE;
v_ENDDATE DATE;

v_STARTDATE Date := to_date('2005/01/01' || v_START_YEAR, 'YYYY/MM/DD');
v_ENDDATE Date := to_date('2020/12/31' || v_END_YEAR,'YYYY/MM/DD');

BEGIN

INSERT INTO

  Date_Dimension 
  (date_key,full_date, day_of_week, day_num_in_month, day_num_overall, day_name, day_abbrev, week_num_in_year, week_num_overall, month, month_name, month_abbrev, quarter, year, century)

VALUES
  (
 '1',TO_DATE(v_STARTDATE, 'yyyy/mm/dd'), TO_NUMBER(v_STARTDATE, 'D'), TO_NUMBER(v_STARTDATE, 'DD'), TO_NUMBER(v_STARTDATE, 'DDD'), TO_CHAR(v_STARTDATE, 'DAY'), TO_CHAR(v_STARDATE, 'DY'), TO_NUMBER(v_STARTDATE, 'IW'), TO_NUMBER(v_STARTDATE, 'WW'), TO_NUMBER(v_STARTDATE, 'MM'), TO_CHAR (v_STARTDATE, 'MONTH'), TO_CHAR (v_STARTDATE, 'MON'), TO_NUMBER (v_STARTDATE, 'Q'), TO_CHAR (v_STARTDATE, 'YEAR'), TO_NUMBER (v_STARTDATE, 'CC') 
  )
;

IF v_STARTDATE > v_ENDDATE THEN

  DBMS_OUTPUT.PUT_LINE ('ERROR IN CODE REGARDING DATES CHOSEN');

ELSE

    WHILE v_STARTDATE <= V_ENDDATE LOOP 
        DBMS_OUTPUT.PUT_LINE ('Date : '||to_char(v_StartDate,'YYYY / MM / DD'));

        v_STARTDATE := v_STARTDATE + 1;

    END LOOP;
END IF;
END;

Solution

Your code gets

PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted

You have duplicated the name startdate from the procedure's format argument list as a local variable; and then repeated both of them again. I think you meant the formal argument to just be the year, since it's a number. Your conversion to a date is also then wrong:

to_date('2005/01/01' || v_START_YEAR, 'YYYY/MM/DD')

... doesn't make sense, as you already have the year 2005 hard-coded.

I think for that part you want something more like:

create or replace PROCEDURE sp_DATE_DIMENSION(p_START_YEAR IN NUMBER, p_END_YEAR IN NUMBER) IS
  l_START_DATE Date := to_date(p_START_YEAR ||'-01-01', 'YYYY-MM-DD');
  l_END_DATE Date := to_date(p_END_YEAR ||'-01-01', 'YYYY-MM-DD');
BEGIN

with other variables references tweaked to match. In your insert you're passing the first value as the string '1' instead of the number 1. You then call to_date() against variables which are already dated; and you call to_number() with a format mask for a date element - for those you need to convert to a string, and then to a number. So that insert woudl become more like:

  INSERT INTO Date_Dimension (date_key, full_date, day_of_week, day_num_in_month,
    day_num_overall, day_name, day_abbrev, week_num_in_year, week_num_overall,
    month, month_name, month_abbrev, quarter, year, century)
  VALUES (1,
    l_START_DATE,
    TO_NUMBER(TO_CHAR(l_START_DATE, 'D')),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'DD')),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'DDD')),
    TO_CHAR(l_START_DATE, 'DAY'),
    TO_CHAR(l_START_DATE, 'DY'),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'IW')),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'WW')),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'MM')),
    TO_CHAR(l_START_DATE, 'MONTH'),
    TO_CHAR(l_START_DATE, 'MON'),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'Q')),
    TO_CHAR (l_START_DATE, 'YEAR'),
    TO_NUMBER(TO_CHAR(l_START_DATE, 'CC'))
  );

It isn't good practice to use dbms_output for error messages, as (a) a calling program has no other indication that something is wrong, and (b) even in a simple client call a user may well not even have capture or display of those enabled. It's better to throw an exception:

  IF l_START_DATE > l_END_DATE THEN
    RAISE_APPLICATION_ERROR (-20001, 'ERROR IN CODE REGARDING DATES CHOSEN');
  END IF;

  WHILE l_START_DATE <= l_END_DATE LOOP
    DBMS_OUTPUT.PUT_LINE ('Date : ' || to_char(l_START_DATE, 'YYYY / MM / DD'));
    l_START_DATE := l_START_DATE + 1;
  END LOOP;
END;
/

The exception will cause the procedure to terminate early, so you don't need the else part as nothing beyond that is reached anyway if the dates are wrong.

Even so, you probably really want to do the insert inside the loop so you create all the relevant rows; and it would make sense to do the check and exception throw right at the start (maybe comparing the years rather than the dates, but doesn't really matter. And probably other things I've forgotten - hopefully this will put you more towards the right track. You don't really need a procedure for this, or even PL/SQL, as it could be done in plain SQL and a single insert, but hopefully this is an exercise.

Comments

[Dmitriy]

What is the error and where does it appears?

[Alex Poole]

Which client are you using? There should be some way to see the actual error being raised, beyond the initial termination message - possibly in another window or section of your IDE; or via something like SQL*Plus' show errors; or as a fall-back by querying user_errors, which holds all outstanding errors against all of your stored PL/SQL code. If you can see that one you ought to be able to see the other errors too...

[Coder101]

Oracle SQL Developer returns the error message, doesn't allow me to see exactly where the error is located

[Alex Poole]

SQL Developer shows the PLS-00410 in the compiler log window, or if you do show errors in the worksheet. It points to line 1 for that error - essentially it gives up trying to parse the PL/SQL very early I guess. The error message is available though, you then just need to interpret it *8-)

[Coder101]

Thanks issues appear to be resolved! appreciate all the help