Highlighted

pass query to oracle

Advisor ,
Aug 24, 2015

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.

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,

^_^

Views

382

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

pass query to oracle

Advisor ,
Aug 24, 2015

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.

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,

^_^

Views

383

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 24, 2015 0
LEGEND ,
Aug 26, 2015

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 26, 2015 0
Advisor ,
Aug 26, 2015

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,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 26, 2015 0
LEGEND ,
Aug 26, 2015

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 26, 2015 0
jfb00 LATEST
Advisor ,
Sep 02, 2015

Copy link to clipboard

Copied

Thanks for you reply and help!

Best,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 02, 2015 1