Copy link to clipboard
Copied
HI All,
How can I pass a query to oracle using cfstoredprod?
Try this:
<cfstoredproc datasource="#application.dsn#" procedure="#importTable#">
<cfprocparam type="in" cfsqltype="cf_sql_refcursor" value="#myQuery#">
</cfstoredproc>
Oracle code:
Procedure importTable ( v_table IN SYS_REFCURSOR) is
Getting error: [Oracle JDBC Driver]Unable to determine the type of the specified object.
Any ideas?
Thanks in advanced.
1 Correct answer
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>
Copy link to clipboard
Copied
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.
V/r,
^_^
Copy link to clipboard
Copied
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?
Best,
Copy link to clipboard
Copied
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.
HTH,
^_^
Copy link to clipboard
Copied
Thanks for you reply and help!
Best,

