Hello there
I have a DB with a table PRODOTTO (products) and some specialization like SMARTPHONE etc. I need to insert a product like a new smartphone using only 1 procedure NEW_PROD
because of the specialization I create a view SPECIALIZZAZIONE joining all the spec tables then I create the procedure NEW_PROD and at the end a trigger instead of
CREATE OR REPLACE PROCEDURE NEW_PROD(
NOME1 VARCHAR2, VAL1 VARCHAR2,
NOME2 VARCHAR2, VAL2 VARCHAR2,
NOME3 VARCHAR2, VAL3 VARCHAR2,
NOME4 VARCHAR2, VAL4 VARCHAR2,
NOME5 VARCHAR2, VAL5 VARCHAR2,
NOME6 VARCHAR2, VAL6 VARCHAR2,
NOME7 VARCHAR2 DEFAULT NULL, VAL7 VARCHAR2 DEFAULT NULL,
NOME8 VARCHAR2 DEFAULT NULL, VAL8 VARCHAR2 DEFAULT NULL,
NOME9 VARCHAR2 DEFAULT NULL, VAL9 VARCHAR2 DEFAULT NULL,
NOME10 VARCHAR2 DEFAULT NULL, VAL10 VARCHAR2 DEFAULT NULL,
NOME11 VARCHAR2 DEFAULT NULL, VAL11 VARCHAR2 DEFAULT NULL,
NOME12 VARCHAR2 DEFAULT NULL, VAL12 VARCHAR2 DEFAULT NULL,
NOME13 VARCHAR2 DEFAULT NULL, VAL13 VARCHAR2 DEFAULT NULL,
NOME14 VARCHAR2 DEFAULT NULL, VAL14 VARCHAR2 DEFAULT NULL,
NOME15 VARCHAR2 DEFAULT NULL, VAL15 VARCHAR2 DEFAULT NULL,
NOME16 VARCHAR2 DEFAULT NULL, VAL16 VARCHAR2 DEFAULT NULL,
NOME17 VARCHAR2 DEFAULT NULL, VAL17 VARCHAR2 DEFAULT NULL,
NOME18 VARCHAR2 DEFAULT NULL, VAL18 VARCHAR2 DEFAULT NULL,
NOME19 VARCHAR2 DEFAULT NULL, VAL19 VARCHAR2 DEFAULT NULL,
NOME20 VARCHAR2 DEFAULT NULL, VAL20 VARCHAR2 DEFAULT NULL)
IS
SQLSTM VARCHAR2(400);
BEGIN
SQLSTM := 'INSERT INTO SPECIALIZZAZIONE('|| NOME1 ||','||NOME2||','||NOME3||','||NOME4||','||NOME5||','||NOME6||','||NOME7||','||NOME8||','||NOME9||','||NOME10||','||NOME11||','||NOME12||','||NOME13||','||NOME14||','||NOME15||','||NOME16||','||NOME17||','||NOME18||','||NOME19||','||NOME20||')
VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14; :15, :16, :17, :18, :19, :20)';
EXECUTE IMMEDIATE SQLSTM USING VAL1, VAL2, VAL3, VAL4, VAL5, VAL6, VAL7, VAL8, VAL9, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20;
END;
NOME20 AND VAL20 because when I insert a product I have to enter a maximum of 20 values
the procedure complies correctly but when I execute got this error:
EXECUTE NEW_PROD('BARCODE','1234567890','MODELLO','KINGSTON G4 DATATRAVELER 32GB','PRODUTTORE','KINGSTON','GARANZIA','IT','COLORE','BLACK','PREZZO_LIST','22','VEL_TRASF','400MBS','MEMSIZE','32GB');
ERROR AT LINE 1:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "PROVA2.NEW_PROD", line 27
ORA-06512: at line 1
line 27 is the EXECUTE IMMEDIATE...
what can I do?
