Yes you can - but they still have to be in the order they are in in the SP, otherwise it doesn't understand what you are doing.
> Taco Fleur <[EMAIL PROTECTED]> wrote: > > > 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/ > --------------------------------------------------------------------- > ------- > --------------------------------------------------------------------- > ------- ----------------------------------- [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/
