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

pass query to oracle

  • August 24, 2015
  • 4 replies
  • 950 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 is closed to new replies. Start a new post to keep the conversation going.
    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,

    ^_^

    4 replies

    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