Needs fixing a procedure for inserting values to the table dynamically

Question

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?

Just a side observation, I see that in your table you define all of the varchar2 columns with a (max) length of 1000. This is just lazy design. It is true that as varchar2, they won't take any more storage than the actual length of the data, However, when allocating _memory_ to handle them (like the internal handling of a simple SELECT) oracle will have to allocate for the max possible. Do you really need 1000 chars to indicate the car is 'blue' or that it has 4 doors? — EdStevens, May 01 '21 at 11:43

pmdba · Accepted Answer · 2021-05-01T03:10:30.923

You didn't define the length of p_values in your anonymous pl/sql block. But why use dynamic sql? This is a really poor use case for it. Why not this?

create or replace procedure insert_all_cars (
    p_manufacturer VARCHAR2,
    p_model VARCHAR2,
    p_year INTEGER,
    p_category VARCHAR2,
    p_mileage NUMBER,
    p_fueltype VARCHAR2,
    p_enginevolume NUMBER,
    p_drivewheels VARCHAR2,
    p_gearbox VARCHAR2,
    p_doors VARCHAR2,
    p_wheel VARCHAR2,
    p_color VARCHAR2,
    p_interiorcolor VARCHAR2,
    p_vin VARCHAR2,
    p_leatherinterior VARCHAR2,
    p_price VARCHAR2,
    p_clearence VARCHAR2) is
begin
    insert into cars (
        Manufacturer, 
        Model,
        Year,
        Category,
        Mileage,
        FuelType,
        EngineVolume,
        DriveWheels,
        GearBox,
        Doors,
        Wheel,
        Color,
        InteriorColor,
        VIN,
        LeatherInterior,
        Price,
        Clearence )
    values (
        p_manufacturer,
        p_model,
        p_year,
        p_category,
        p_mileage,
        p_fueltype,
        p_enginevolume,
        p_drivewheels,
        p_gearbox,
        p_doors,
        p_wheel,
        p_color,
        p_interiorcolor,
        p_vin,
        p_leatherinterior,
        p_price,
        p_clearence );
end;
/

And then this:

begin
    insert_all_cars (
        p_manufacturer    => 'new_manufacturer',
        p_model           => 'new_model',
        p_year            => 2000,
        p_category        => 'new_category',
        p_mileage         => 2000,
        p_fueltype        => 'new_fueltype',
        p_enginevolume    => 3.0,
        p_drivewheels     => 'new_drivewheels',
        p_gearbox         => 'new_gearbox',
        p_doors           => 'new_doors',
        p_wheel           => 'new_wheel',
        p_color           => 'new_color',
        p_interiorcolor   => 'new_interior_color',
        p_vin             => 'new_vin',
        p_leatherinterior => 'new_leather_interior',
        p_price           => 'new_price',
        p_clearence       => 'new_clearence'
    );
    commit;
end;
/

Needs fixing a procedure for inserting values to the table dynamically

1 Answers1