Re: More Oracle/CF fun!
shouldn't it be: mailInfo IN OUT types.cursorType The ref cursor should be 'IN OUT' not just 'OUT'. -Adam -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 11:53 PM To: 'CF-Talk' Subject: More Oracle/CF fun! Consider this stored procedure (part of package DLC): PROCEDURE dlc_mail_countMsg ( studentID IN number, mailInfo OUT types.cursorType ) AS BEGIN OPEN mailInfo FOR SELECT count(*) AS msgCount FROM tblMail WHERE mIndividID = studentID AND mToType = 1 AND mRecipActive = '1' AND mRead IS NULL; END dlc_mail_countMsg; ...and this CFSTOREDPROC call: cfstoredproc datasource=DLCampus procedure=dlc.dlc_mail_countMsg cfprocparam type=in value=#cookieID# cfsqltype=cf_sql_integer cfprocresult name=msgCount /cfstoredproc ...and, finally, this error message: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DLC_MAIL_COUNTMSG' ORA-06550: line 1, column 7: PL/SQL: Statement ignored In tblMail, the datatypes are: mIndividID number mToType number mRecipActive char(1) mRead date I can't see the error.It looks like all of the paramters are being passed, and nothing unusual is going on.I've Googled but couldn't find anything that looks relevant. Anyone have any ideas? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: More Oracle/CF fun!
Why are you using a cursor?Won't the select statement return a single value, the count of records matching the criteria specified in the where clause? Janet. At 01:39 PM 3/12/2004 +, you wrote: shouldn't it be: mailInfo IN OUT types.cursorType The ref cursor should be 'IN OUT' not just 'OUT'. -Adam -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 11:53 PM To: 'CF-Talk' Subject: More Oracle/CF fun! Consider this stored procedure (part of package DLC): PROCEDURE dlc_mail_countMsg ( studentID IN number, mailInfo OUT types.cursorType ) AS BEGIN OPEN mailInfo FOR SELECT count(*) AS msgCount FROM tblMail WHERE mIndividID = studentID AND mToType = 1 AND mRecipActive = '1' AND mRead IS NULL; END dlc_mail_countMsg; ...and this CFSTOREDPROC call: cfstoredproc datasource=DLCampus procedure=dlc.dlc_mail_countMsg cfprocparam type=in value=#cookieID# cfsqltype=cf_sql_integer cfprocresult name=msgCount /cfstoredproc ...and, finally, this error message: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DLC_MAIL_COUNTMSG' ORA-06550: line 1, column 7: PL/SQL: Statement ignored In tblMail, the datatypes are: mIndividIDnumber mToTypenumber mRecipActivechar(1) mReaddate I can't see the error.It looks like all of the paramters are being passed, and nothing unusual is going on.I've Googled but couldn't find anything that looks relevant. Anyone have any ideas? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: More Oracle/CF fun!
are you using type IV driver? in my openion.. oracle REF CURSORs are supported by type IV drivers(i.e. CFMX 6.1 enterprise edition) -sampath nellutla Adrocknaphobia wrote: shouldn't it be: mailInfo IN OUT types.cursorType The ref cursor should be 'IN OUT' not just 'OUT'. Thanks for the suggestion.I tried it, but got the same error. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
More Oracle/CF fun!
Consider this stored procedure (part of package DLC): PROCEDURE dlc_mail_countMsg ( studentID IN number, mailInfo OUT types.cursorType ) AS BEGIN OPEN mailInfo FOR SELECT count(*) AS msgCount FROM tblMail WHERE mIndividID = studentID AND mToType = 1 AND mRecipActive = '1' AND mRead IS NULL; END dlc_mail_countMsg; ...and this CFSTOREDPROC call: cfstoredproc datasource=DLCampus procedure=dlc.dlc_mail_countMsg cfprocparam type=in value=#cookieID# cfsqltype=cf_sql_integer cfprocresult name=msgCount /cfstoredproc ...and, finally, this error message: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DLC_MAIL_COUNTMSG' ORA-06550: line 1, column 7: PL/SQL: Statement ignored In tblMail, the datatypes are: mIndividID number mToType number mRecipActive char(1) mRead date I can't see the error.It looks like all of the paramters are being passed, and nothing unusual is going on.I've Googled but couldn't find anything that looks relevant. Anyone have any ideas? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
