Why are you passing a query to a stored procedure? Stored procedures are great for _running_ queries, but the query should already exist in the stored procedure. You can pass variables in the cfprocparam for dynamic content.
Thanks for your reply and help.
I am trying to do a bulk import of 100K records into Oracle.
Doing a cfquery with a loop insert takes too long.
Do you know a better way of doing bulk import?
Get your records and put them into a query object (if they are not already), call it getRecs, and:
<cfquery name="bulkInsert" datasource="#application.yourDSN#"> INSERT ALL <cfoutput query="getRecs"> INTO schema.tableName(columnA, columnB, columnC, etc) VALUES (<cfqueryparam value="#getRecs.column1#" />,<cfqueryparam value="#getRecs.column2#" />,<cfqueryparam value="#getRecs.column3#" />,<cfqueryparam value="#getRecs.column4#" />) </cfoutput> SELECT * FROM DUAL </cfquery>
You must include the "SELECT * FROM DUAL" after the output loop. This will make one connection to your database (Oracle), insert all the rows, and disconnect. Place it within a CFTRANSACTION tag to make sure they are all inserted, or all rolled back.
Thanks for you reply and help!