CODEWITHSUNDEEP
sqloracle-databaseplsqlsqlplussequences
jcup505
jcup505

Reputation: 5

SQL Insert values sequence.NEXTVAL

Relatively new to pl/sql and I'm trying to figure a way to utilize sequence.NEXTVAL in my loop. When I run what I currently have, I get "ORA-01722: invalid number" Is there sometime I'm missing?

DECLARE 
  CURSOR SALESDATA
  IS
    SELECT vin,
           sale_date,
           SF.plan_ID,
           SP.salepersons_ID,
           COUNT (*) AS Vehicles_Sold,
           SUM (S.gross_sales_price) AS Gross_Sales_Amt
    FROM sales S, sales_financing SF, salepersons SP
    WHERE S.sale_ID = SF.sale_ID AND S.salepersons_ID = SP.salepersons_ID
   GROUP BY vin,
          sale_date,
          SF.plan_ID,
          SP.salepersons_ID
     HAVING COUNT (*) > 0;
  BEGIN
    FOR VEHICLE_REC IN SALESDATA
    LOOP
      INSERT INTO sale_facts (sale_fact_ID,
                              sale_day,
                              vehicle_code,
                              plan_code,
                              dealer_ID,
                              vehicle_sold,
                              gross_sales_amt)
          VALUES (salefactID_seq.NEXTVAL,
                  vehicle_rec.sale_date,
                  vehicle_rec.vin,
                  vehicle_rec.plan_id,
                  vehicle_rec.salepersons_ID,
                  vehicle_rec.vehicles_sold,
                  vehicle_rec.gross_sales_amt);

     COMMIT;
    END LOOP;

    COMMIT;
  END;
 /

Here's the tables' structures.

SQL> DESC sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_ID                                   NOT NULL NUMBER(10)
 VIN                                                VARCHAR2(17)
 CUST_ID                                            NUMBER(10)
 GROSS_SALES_PRICE                         NOT NULL NUMBER(10)
 MILEAGE                                   NOT NULL VARCHAR2(10 CHAR)
 SALE_DATE                                 NOT NULL DATE
 VEHICLE_STATUS                            NOT NULL VARCHAR2(25 CHAR)
 SALEPERSONS_ID                                     NUMBER(10)

SQL> DESC salepersons
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALEPERSONS_ID                            NOT NULL NUMBER(10)
 TITLE                                     NOT NULL VARCHAR2(25 CHAR)
 NAME                                      NOT NULL VARCHAR2(50 CHAR)
 HIRE_DATE                                 NOT NULL DATE

SQL> DESC sales_financing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_ID                                            NUMBER(10)
 PLAN_ID                                            NUMBER(10)
 DOWN_PAY                                  NOT NULL FLOAT(10)
 LOAN_TERM                                 NOT NULL NUMBER(3)

SQL> DESC sale_facts
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_FACT_ID                              NOT NULL NUMBER(5)
 SALE_DAY                                  NOT NULL DATE
 VEHICLE_CODE                              NOT NULL NUMBER(5)
 PLAN_CODE                                 NOT NULL NUMBER(10)
 DEALER_ID                                 NOT NULL NUMBER(10)
 VEHICLE_SOLD                              NOT NULL NUMBER(10)
 GROSS_SALES_AMT                           NOT NULL NUMBER(15)

Upvotes: 0

Views: 2749

Answers (1)

Ashraf Iqbal
Ashraf Iqbal

Reputation: 424

your 'vehicle_code' in 'sale_facts' is NUMBER(5), whereas 'VIN' in 'Sales' is VARCHAR2(17). Are you sure all values for VIN can be converted to NUMBER(5)? i.e., there are no characters in there?

Upvotes: 1

Related Questions