The problem isnt with Oracle.. I wrote 2 non-dynamic queries and
uploaded several documents in a row. So it has to be within the query
and the building of the dynamic query structure.
On Thu, 11 Nov 2004 07:07:20 -0600, Greg Morphis <[EMAIL PROTECTED]> wrote:
> I've got an upload utility I'm using to allow the client to upload a
> txt file which could go into 1 of 33 different tables.. the reports
> are structured so differently it's impossible to put them all into one
> table.
>
> After uploading the first document (304 rows), I try to upload another
> this one has 294. It gets to about 190-200 rows before crapping out on
> me.
> Cold Fusion reports "maximum opened cursors exceeded".
> I did some research and checked with our DBA.. Our cursor limit is set at
> 1500.
>
> I've looked over my code and cant see an issue with it.
> I uploaded 2 documents each with 1 row without a problem.
> I've uploaded 1 document with 800+ rows without a problem.
> I've uploaded a document of 1600 rows with no problem. It's real odd, when
> I get the Error, if I click on the link to take me back to the page
> and try again.. it works.. but then if I follow it with another file..
> it bombs out on me.
>
> Has anyone seen this before and can anyone suggest an idea.
> I'd like to ask for an increase of the cursor limit as a last resort.
>
> <code>
>
> <cfset isProblem = "no">
> <cftransaction action="begin">
> <cftry>
> <cfquery name="getSweepID" datasource="#application.config.getDSN()#">
> SELECT seq_sweep_id.nextval AS id FROM DUAL
> </cfquery>
> <!--- insert main record --->
>
> <cfquery name="sweepInsert" datasource="#application.config.getDSN()#">
> INSERT INTO SWEEP_MAIN
> (
> SWEEP_ID, PHONE_ID, ACTIONUSER, ACTIONDATE, CATEGORY_ID
> )
> VALUES
> (
> <cfqueryparam value="#getSweepId.id#"
> cfsqltype="cf_sql_numeric">,
> <cfqueryparam value="#form.phoneid#"
> cfsqltype="cf_sql_numeric">,
> <cfqueryparam value="#session.user.emplid#"
> cfsqltype="cf_sql_varchar">,
> sysdate,
> <cfqueryparam value="#form.category_id#"
> cfsqltype="cf_sql_numeric">
> )
> </cfquery>
>
> <!--- get table name --->
> <cfquery name="getTable" datasource="#application.config.getDSN()#">
> SELECT sweep_table AS tbl_name FROM CATEGORY_SWEEP
> WHERE category_id = <cfqueryparam value="#form.category_id#"
> cfsqltype="cf_sql_numeric">
> </cfquery>
> <!--- get table columns --->
> <cfquery name="getCols" datasource="#application.config.getDSN()#">
> SELECT collate(t.column_name) as columns
> FROM all_tab_columns t
> WHERE t.table_name= '#getTable.tbl_name#'
> ORDER BY column_id
> </cfquery>
> <!--- loop over record and insert into table --->
>
> <cfset strLen = ListLen(getCols.columns,',')>
> <cfset counter = 1>
> <cfoutput>Please Wait Loading...<br/></cfoutput>
> <cffile action="read"
> file="#cffile.serverDirectory#\#cffile.serverFile#"
> variable="fileData">
> <cfloop index="record" list="#fileData#"
> delimiters="#chr(10)##chr(13)#">
> <cfif counter gt 1>
> <cfquery name="qryInsert"
> datasource="#application.config.getDSN()#">
> INSERT INTO #getTable.tbl_name# (#getCols.columns#)
> VALUES
> (
> <cfqueryparam value="#getSweepId.id#"
> cfsqltype="cf_sql_numeric">,
> <cfloop from="1" to="#strLen-2#" index="i">
>
> <cfoutput>'#replace(evaluate("listgetat(record,#i#,chr(9))"),'"','',"all")#'</cfoutput>,
> </cfloop>
> '#evaluate("listgetat(record,#strLen-1#,chr(9))")#'
> )
> </cfquery>
>
> </cfif>
> <cfset counter = counter + 1>
> </cfloop>
>
> <CFCATCH type="Any">
> <cfoutput>
> <h3>Oops, There's been an error</h3>
> Please try again, if the problem persists <br/>
> notify the administrator<br/>
> Message :#CFCATCH.message#<p>
> Type :#CFCATCH.type#<p>
> <cfset isProblem = "yes">
> <p<i>Rolling back the attempted transaction</i></p>
> <cftransaction action="ROLLBACK" />
> <cfquery name="delFile"
> datasource="#application.config.getDSN()#">
> DELETE FROM DOCUMENTS WHERE DOCID = <cfqueryparam
> cfsqltype="cf_sql_numeric" value="#docid#">
> </cfquery>
> </cfoutput>
> </CFCATCH>
> </cftry>
> <cfif isproblem eq "no">
> <cftransaction action="COMMIT" />
> <script language="Javascript">
> <!--
> alert("Finished: <cfoutput>#counter-2#</cfoutput> rows added");
> location.href="../upload/"
> -->
> </script>
> </cfif>
> </cftransaction>
> </cfif>
>
> </code>
>
>
> --
> Auxilium meum a Domino
>
--
Auxilium meum a Domino
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183945
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
