Hello all,
I'm trying to write a program which inserts values into a table (table name is dynamic) using only one string, as number of columns is also dynamic.
I couldn't get it to work properly, as it seems that in an insert statement wrapped by execute immediate, each bind variable represents only one column.
My original program generates the "values" string and cannot seperate it into different variables
Here is a simplified code shownig my problem:
DECLARE
V_SQL VARCHAR2(4000);
V_VALUES VARCHAR2(4000);
BEGIN
V_SQL := 'INSERT INTO TBL_TST
(CARRIER,FLIGHTNO,FLIGHTDATE,LVL01_OCCURS,LVL13_OCCURS,LVL14_OCCURS,SURNAME,TYPE,QUANTITY,STATUS)
VALUES (:in_str)';
V_VALUES := '''XXX'', ''1'', ''20140106'', ''2'', ''1'', ''1'', ''SURNAME'', ''A'', '''', ''''';
DBMS_OUTPUT.PUT_LINE(V_VALUES);
EXECUTE IMMEDIATE V_SQL
USING V_VALUES;
END;
ORA-00947: not enough values
ORA-06512: at line 10
00947. 00000 - "not enough values"
Has anyone got a suggestion to solve my difficulty?