Needs fixing a procedure for inserting values to the table dynamically

I am trying to use dynamic SQL to insert values into my table. But I am struggling with it! This is my table

CREATE TABLE CARS(  ID INTEGER PRIMARY KEY,  Manufacturer VARCHAR2(1000),  Model VARCHAR2(1000),  Year INTEGER NOT NULL,  Category VARCHAR2(1000) NOT NULL,  Mileage NUMBER,  FuelType VARCHAR2(1000),  EngineVolume NUMBER,  DriveWheels VARCHAR2(1000),  GearBox VARCHAR2(1000),  Doors VARCHAR2(1000),  Wheel VARCHAR2(1000),  Color VARCHAR2(1000),  InteriorColor VARCHAR2(1000),  VIN VARCHAR2(1000),  LeatherInterior VARCHAR2(1000) NOT NULL,  Price VARCHAR2(1000) NOT NULL,  Clearence VARCHAR2(1000) NOT NULL)  

And I have created a trigger that will increment the id column automatically.

CREATE SEQUENCE cars_seq START WITH 93100;    CREATE OR REPLACE TRIGGER cars_id_inc   BEFORE INSERT ON cars FOR EACH ROW  BEGIN      :NEW.ID := CARS_SEQ.nextval;  END;  

Then I have created a procedure that will insert values into the cars table.

CREATE OR REPLACE PROCEDURE insert_all_cars (p_values VARCHAR2) IS      v_stmt VARCHAR2(10000);  BEGIN      v_stmt := 'INSERT INTO CARS '  || ' VALUES ' || p_values;      EXECUTE IMMEDIATE v_stmt;  END;  

When I am trying to insert values to the cars table using a procedure like this:

DECLARE       p_values VARCHAR2 := '(''new_manufacturer'', ''new_model'', ' || '2000' || ' ,''new_category'', ' || '2000' ||' ,''new_fueltype'', ' || '3.0' ||       ' ,''new_drivewheels'',''new_gearbox'',''new_doors'',''new_wheel'',''new_color'',      ''new_interior_color'',''new_vin'',''new_leather_interior'',''new_price'',''new_clearence'')';  BEGIN      insert_all_cars(p_values);  END;  

I am getting this kind of error:

Error starting at line : 60 in command -  DECLARE       p_values VARCHAR2 := '(''new_manufacturer'', ''new_model'', ' || '2000' || ' ,''new_category'', ' || '2000' ||' ,''new_fueltype'', ' || '3.0' ||       ' ,''new_drivewheels'',''new_gearbox'',''new_doors'',''new_wheel'',''new_color'',      ''new_interior_color'',''new_vin'',''new_leather_interior'',''new_price'',''new_clearence'')';  BEGIN      insert_all_cars(p_values);  END;  Error report -  ORA-06550: line 2, column 14:  PLS-00215: String length constraints must be in range (1 .. 32767)  06550. 00000 -  "line %s, column %s:\n%s"  *Cause:    Usually a PL/SQL compilation error.  *Action:  

Also tried to put numbers without quotes got the same kind error. How I can fix it?

https://stackoverflow.com/questions/67342165/needs-fixing-a-procedure-for-inserting-values-to-the-table-dynamically May 01, 2021 at 09:35AM