pass query to oracle

[jfb00]

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.

[WolfShade]

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,

^_^

[jfb00]

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,

[WolfShade · Accepted solution]

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,

^_^

[jfb00]

Thanks for you reply and help!

Best,