Skip to main content
J
jfb00
Inspiring
August 24, 2015
Answered

pass query to oracle

  • August 24, 2015
  • 1 reply
  • 943 views

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.

    This topic has been closed for replies.
    Correct answer WolfShade

    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,

    ^_^

    1 reply

    WolfShade
    WolfShade
    Legend
    August 26, 2015

    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,

    ^_^

    J
    jfb00Author
    Inspiring
    August 26, 2015

    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
    WolfShadeCorrect answer
    Legend
    August 26, 2015

    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,

    ^_^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices