CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Zhamshidbek Abdulkhamidov
Zhamshidbek Abdulkhamidov

Reputation: 45

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?

Upvotes: 0

Views: 58

Answers (1)

pmdba
pmdba

Reputation: 7033

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;
/

Upvotes: 1

Related Questions