How to loop over array and insert records in Oracle database?

Question

I have form that has an option for adding extra fields. The user can add as many fields as they want. Once they submit the form I need to loop over from scope and insert records in ORACLE database. This is example of my code:

<cfquery name="insertRec" datasource="dbs">
    INSERT INTO myTbl(
        RecordID, First, Last, Email, Subject, Description, ActionDt
    ) VALUES
    <cfset count = 1>
    <cfloop from="1" to="#arrayLen(arrData)#" index="i">
    (
        SYS_GUID(),
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">, 
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">, 
        CURRENT_TIMESTAMP
    )
    <cfif count NEQ arrayLen(arrDpr)>,</cfif>
    <cfset count++>
</cfloop>

The first problem when I tried to test the code above is with my Oracle Insert statement. I'm getting error message:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended

I looked over the error message and Oracle insert code looks like this:

INSERT INTO myTbl(
    RecordID, First, Last, Email, Subject, Description, ActionDt
) VALUES ( 
    SYS_GUID(), 
    (param 1) , 
    (param 2) , 
    (param 3) , 
    (param 4) , 
    (param 5) , 
    CURRENT_TIMESTAMP 
) , 
( 
    SYS_GUID(), 
    (param 1) , 
    (param 2) , 
    (param 3) , 
    (param 4) , 
    (param 5) , 
    CURRENT_TIMESTAMP
) , 
( 
    SYS_GUID(), 
    (param 1) , 
    (param 2) , 
    (param 3) , 
    (param 4) , 
    (param 5) , 
    CURRENT_TIMESTAMP
)

The code above is obviously wrong and SQL insert string is not built the right way. I'm wondering how this can be fixed? What is the best way to do this in ColdFusion for ORACLE Database?

Also the other problem I have is related to ColdFusion and outputting array index in cfqueryparam. As you can see above values for Subject and Description columns are hardcoded. I'm trying to find the way to output array index value. So for example if my array has this values [1,3,2], then in cfloop I would need to get this in cfqueryparam:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column#i#)#" maxlength="50"> 
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr#i#)#" maxlength="500">

Code above is wrong and I can not have ## inside of another ##. Is there a way to get actual value of i appended to column and datadescr? They would look like:

column1   datadescr1
column3   datadescr3
column2   datadescr2

I'm new in Oracle world and this is something that I can't figure it out. Any help would be greatly appreciated.

Answer 1

For outputting correct values in cfqueryparam, you can use this:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Evaluate('form.column#i#')#" maxlength="50">

Or

<cfset tempCol = FORM["column#i#"]>

And then use this variable in your query. Also regarding your query: You need to set your Insert Into inside the loop or use from dual. This question should help Best way to do multi-row insert in Oracle?

    INSERT INTO myTbl(
    RecordID, First, Last, Email, Subject, Description, ActionDt
)
    <cfset count = 1>
        <cfloop from="1" to="#arrayLen(arrData)#" index="i">
        (
           select SYS_GUID(),
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.column1)#" maxlength="100">, 
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.datadescr1)#" maxlength="4000">, 
            CURRENT_TIMESTAMP
        ) from dual
        <cfif count NEQ arrayLen(arrDpr)> union all </cfif>
        <cfset count++>
    </cfloop>