4 Replies Latest reply on Sep 2, 2015 3:43 AM by jfb00

    pass query to oracle

    jfb00 Level 3

      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. Re: pass query to oracle
          WolfShade Level 4

          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,

           

          ^_^

          • 2. Re: pass query to oracle
            jfb00 Level 3

            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,

            • 3. Re: pass query to oracle
              WolfShade Level 4

              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. Re: pass query to oracle
                jfb00 Level 3

                Thanks for you reply and help!

                Best,