Hello everyone,
I am currently trying to improve the import of data in our company. We offer products with different prices and these are in our database. Now there is the possibility to change the prices by different conditions.
In order to offer alternative prices for a customer, an enormous number of insert statements have to be executed on the database. This sometimes leads to programs such as PL/SQL freezing and being unusable for 10 minutes.
Our statements currently look like this:
insert into T_PRICE (ID, DUTY_FREE_AMOUNT, DUTY_FREE_CURRENCY, PERCENTAGE, TAX_INCL_CURRENCY, TAX_INCLUDED_AMOUNT, TAX_RATE, END_DATE_TIME, START_DATE_TIME)
values ((select max(id) + 1 from T_PRICE ), -400, 'EUR', null, 'EUR', -400, null, null, '01.12.22 10:00:00,000000');
insert into T_PRICE_ALTERATION (ID, APPLICATION_DURATION, DESCRIPTION, NAME, PRICE_TYPE, PRIORITY, RECURRING_CHARGE_PERIOD, UNIT_OF_MEASURE, END_DATE_TIME, START_DATE_TIME, PRICE_ID, PRODUCT_OFFERING_PRICE_ID)
values ((select max(id) + 1 from T_PRICE_ALTERATION ), 12, 'TMP', 'TMP', 0, 1, null, null, null, '01.12.22 10:00:00,000000', (select max(id) from T_PRICE), 1535);
insert into T_PRICE_CONDITION (ID, CONDITION_TYPE, VALUE, PRICE_ALTERATION_ID)
values ((select max(id) + 1 from T_PRICE_CONDITION ), 'CHAR_BANDWIDTH', '2', (select max(id) from T_PRICE_ALTERATION));
insert into T_PRICE_CONDITION (ID, CONDITION_TYPE, VALUE, PRICE_ALTERATION_ID)
values ((select max(id) + 1 from T_PRICE_CONDITION ), 'CUSTOMER_SPECIFIC', '999999', (select max(id) from T_PRICE_ALTERATION));
insert into T_PRICE_CONDITION (ID, CONDITION_TYPE, VALUE, PRICE_ALTERATION_ID)
values ((select max(id) + 1 from T_PRICE_CONDITION ), 'CHAR_TARIFF_ZONE', 'Country', (select max(id) from T_PRICE_ALTERATION));
My first approach here was to replace the select max(id) statements with sequences. I worked out the following as a test:
DECLARE
max_price_id NUMBER;
BEGIN
SELECT MAX(ID) INTO max_price_id FROM SVC_PRODUCT_CATALOG_ONE.T_PRICE;
EXECUTE IMMEDIATE 'CREATE SEQUENCE sq1 START WITH ' || (max_price_id + 1) || ' INCREMENT BY 1';
EXECUTE IMMEDIATE 'insert into T_PRICE (ID, DUTY_FREE_AMOUNT, DUTY_FREE_CURRENCY, PERCENTAGE, TAX_INCL_CURRENCY, TAX_INCLUDED_AMOUNT, TAX_RATE, END_DATE_TIME, START_DATE_TIME)values (sq1.NEXTVAL, -400, ''EUR'', null, ''EUR'', -400, null, null, TO_TIMESTAMP(''01.12.22 10:00:00,000000''));';
EXECUTE IMMEDIATE 'DROP SEQUENCE sq1';
END;
Unfortunately, I cannot execute this block successfully because the insert statement does not work. I suspect it has something to do with the sequence. The other alternative would be to use a loader file. I have now worked out the following, but I am not sure where to execute it and whether the sequence can be found in this case.
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'data.csv'
INTO TABLE SCHEMA.T_PRICE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID "sq1.NEXTVAL", DUTY_FREE_AMOUNT, DUTY_FREE_CURRENCY, PERCENTAGE, TAX_INCL_CURRENCY, TAX_INCLUDED_AMOUNT, TAX_RATE, END_DATE_TIME, START_DATE_TIME)