Here's the example I was trying to follow because I want to return a
recordset.
CREATE OR REPLACE PACKAGE pkg_Search
AS
TYPE CUSTOM_REF_CURSOR IS REF CURSOR;
PROCEDURE By_Title (
arg_Title Books.Title%type,
arg_Cursor IN OUT CUSTOM_REF_CURSOR);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_Search AS
PROCEDURE By_Title (
arg_Title Books.Title%type,
arg_Cursor IN OUT CUSTOM_REF_CURSOR)
IS
BEGIN
OPEN arg_Cursor FOR
SELECT
BookID,
Title,
Price,
PublishDate
FROM
Books
WHERE
Title LIKE '%' || arg_Title || '%';
END;
END;
/
<cfset ds = "oratst">
<cfset sp = "pkg_Search.By_Title">
<!---
<cfset ds = "sqltst">
<cfset sp = "Search_By_Title">
--->
<cfoutput>
<form action="#cgi.SCRIPT_NAME#" method="post">
<input type="text" name="criteria" size="20" />
<input type="submit" value="Search"/>
</form>
</cfoutput>
<cfif isDefined("form.criteria") AND form.criteria NEQ "">
<cfstoredproc procedure="#sp#" datasource="#ds#">
<cfprocparam
cfsqltype="cf_sql_varchar"
value="#form.criteria#">
<cfprocresult
name="searchResults">
</cfstoredproc>
<cfoutput>
<h3>Search results for '#form.criteria#'</h3>
</cfoutput>
<cfdump var="#searchResults#" label="Search Results">
</cfif>
I have tried passing the dbvarname="arg_Title" and it does the same
thing.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of [EMAIL PROTECTED]
Sent: Wednesday, March 17, 2004 11:36 AM
To: [EMAIL PROTECTED]
Subject: RE: [KCFusion] Stored Procedures
I experienced very very similar problems with Oracle & calling the
stored procedures. And as Doug mentioned, making sure you pass in/out
all of the correct variables is paramount. Remember, CF has a problem
with naming the parameters in a stored procedure call. You have to make
sure that you pass the variables in in the correct order that the
package/stored procedure expects them in. If you pass them in out of
order, say, passing in a string when it expects a numeric, it doesn't
matter if you 'named' the variable, it won't work.
=========================================================
Kansas City ColdFusion User Group's website & listserv is hosted through
the generous support of Clickdoug.com To send email to the list, email
[EMAIL PROTECTED] To (un)subscribe, email [EMAIL PROTECTED] with
your request.
For hosting solutions http://www.clickdoug.com Featuring Win2003
Enterprise, RedHat Linux, CFMX 6.1.
======================================================
========================================================Kansas City ColdFusion User
Group's website & listserv is
hosted through the generous support of Clickdoug.com
To send email to the list, email [EMAIL PROTECTED]
To (un)subscribe, email [EMAIL PROTECTED] with your request.
For hosting solutions http://www.clickdoug.com
Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1.
=====================================================
