Just a completely different question; can you refer to a variable in a SP by name with Oracle? I know that the variables I pass it, need to be in order they are in the SP, as you can't refer to them by name with CF5 and less, as far as I know. And that's with SQL Server and CF5 or less..
Taco -----Original Message----- From: Mark M [mailto:[EMAIL PROTECTED] Sent: Thursday, 26 June 2003 11:09 AM To: CFAussie Mailing List Subject: [cfaussie] Oracle sproc weirdness... update works, but not insert? Hey everyone - this has been driving me nuts all day yesterday and so far this morning. I have 2 stored procs for dealing with a table, one to insert data, and one to update the data. The procs are called from ColdFusion MX (with updater3). (Oracle is 8i, for those that care) Code is at the bottom of the email (so as to not clutter) When inserting the 'data' using a the t_value = "", everything works fine. When updating the value using a string of 350 characters long for t_value, the update sproc works fine. When inserting the data with t_value being the same 350 character long string, it throws a: ORA-01460: unimplemented or unreasonable conversion requested can anyone suggest anything? I'm totally stuck :oP Thanks, Mark -------- calling insert sproc:----------- <cfstoredproc procedure="Request.insertData" datasource="#this.datasource#" debug="No"> <cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" dbvarname="t_lnkRequestID" value="#this.lnkRequestID#" null="No"> <cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" dbvarname="t_lnkElementID" value="#this.lnkElementID#" null="No"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="t_value" value="#this.value#" null="No"> <cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" dbvarname="t_isChanged" value="#IIF(this.isChanged, DE("1"), DE("0"))#" null="No"> <cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" dbvarname="t_versionNumber" value="#this.versionNumber#" null="No"> <cfprocparam type="Out" cfsqltype="CF_SQL_NUMERIC" variable="ID" null="No"> </cfstoredproc> -------- insert sproc:----------- FUNCTION insertData ( t_lnkrequestid IN NUMBER, t_lnkelementid IN NUMBER, t_value IN VARCHAR2, t_ischanged IN NUMBER, t_versionnumber IN NUMBER ) RETURN NUMBER IS BEGIN DECLARE CURSOR c_ID IS SELECT req_data_seq.CURRVAL FROM DUAL; t_retVal NUMBER; BEGIN INSERT INTO TBL_REQ_DATA ( iddata, lnkrequestid, lnkelementid, data_value, data_ischanged, data_versionnumber ) VALUES ( req_data_seq.NEXTVAL, t_lnkrequestid, t_lnkelementid, t_value, t_ischanged, t_versionnumber ); OPEN c_ID; FETCH c_ID INTO t_retVal; CLOSE c_ID; RETURN t_retVal; END; END insertData; -------- calling update sproc:----------- <cfstoredproc procedure="Request.updateData" datasource="#this.datasource#"> <cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" dbvarname="t_IDElement" value="#this.id#" null="No"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="t_value" value="#this.value#" null="No"> </cfstoredproc> -------- update sproc:----------- PROCEDURE updateData ( t_IDData IN NUMBER, t_Value IN VARCHAR2 ) IS BEGIN UPDATE TBL_REQ_DATA SET data_value = t_value WHERE IDData = t_IDData; END updateData; ----------------------------------- [EMAIL PROTECTED] ICQ: 3094740 Safe From Bees [www.safefrombees.com] --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ ---------------------------------------------------------------------------- This email, together with any attachments, is intended for the named recipient(s) only and may contain privileged and confidential information. If received in error, please inform the sender as quickly as possible and delete this email and any copies from your computer system network. If not an intended recipient of this email, you must not copy, distribute or rely upon it and any form of disclosure, modification, distribution and/or publication of this email is prohibited. Unless stated otherwise, this email represents only the views of the sender and not the views of the Queensland Government. ---------------------------------------------------------------------------- --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
