CODEWITHSUNDEEP
sqloracle-database
user6774488
user6774488

Reputation:

Inserting date-type values into the table in Oracle: ORA-00984

I have two tables created according to the following diagram:

diagram

I am now trying to populate those tables with the test data and I'm having trouble inserting date-type column into the PositionLogEntry table getting an error: PL/SQL: ORA-00984: column not allowed here.

declare



noOfFlights   constant int:= 10;
noOfLog       constant int:=100;



begin



for m in 1..noOfFlights loop



insert into Flight (aircraftRegistration, DateAndTimeDeparture,pilotInCommand)
values              (
                    'Aircraft Registration' || to_char(sqFlight.nextval),
                    to_date('2000-01-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
                    + to_ymInterval(to_char(floor(dbms_random.value(1,30))) || '-' || to_char(floor(dbms_random.value(1,12)))),
                    'Pilot in Command' || to_char(sqFlight.currval));
                    end loop;



for c in 1..noOfLog loop
    for m in (select AIRCRAFTREGISTRATION, DATEANDTIMEDEPARTURE from Flight) loop



insert into POSITIONLOGENTRY (AIRCRAFTREGISTRATION, DATEANDTIMEDEPARTURE, logTime, positionLatitude, positionLongtitude, courseDegrees, headingDegree, horizontalSpeedKnots,
  verticalSpeedKnots, altitudeFeet, altitudeFlightLevels) values
  (
    AIRCRAFTREGISTRATION,
    DATEANDTIMEDEPARTURE,
    to_date('2000-01-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
    + to_ymInterval(to_char(floor(dbms_random.value(1,30))) || '-' || to_char(floor(dbms_random.value(1,12)))),
    round(dbms_random.value(7,0)),
    round(dbms_random.value(7,0)),
    round(dbms_random.value(3,0)),
    round(dbms_random.value(3,0)),
    round(dbms_random.value(3,0)),
    round(dbms_random.value(3,0)),
    round(dbms_random.value(4,0)),
    round(dbms_random.value(4,0)));
    end loop;



end loop;



/commit;/



end;
/

P.S. The Flight table gets populated correctly. Could you please suggest any possible fixes? Thanks!

Upvotes: 0

Views: 53

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

You cannot use column names: AIRCRAFTREGISTRATION, DATEANDTIMEDEPARTURE etc in VALUES clause.

use 

m.AIRCRAFTREGISTRATION,
m.DATEANDTIMEDEPARTURE

instead.

Upvotes: 2

Related Questions