Stored Procedure Problem

[vbdude13]

I am using CFServer4.5, CFStudio4.5.2 & Oracle8i.

My problem is that I have a stored procedure that runs perfectly when the one and only param is 23 chars or less. As soon as I try pass a larger param, from a calling cfm, CF Server has trouble bringing up the action cfm the storeproc resides in. I have check the size of the fields in my cfm's and my 8i database and they are the same. I have set a maxlength on the param equal to the db field size. I have no idea what could be causing this. Does CF have a problem with params over a certain size using the <CFSTOREDPROC> tag?

 

[GunJack]

I don't think there is a 23 character limit on stored proc parameters. Are you passing this parameter with <cfStoredProcParam> or within a regular <cfquery> block? Whichever way you are using, I would switch and try the other and see if the problem persists. There have been a lot of bugs with CF 4.5 so I would make sure you have the latest service pack installled as well. It could also be a bug in the Oracle ODBC driver so I would also check for any updates to your ODBC driver.

If you want to post your code, I or someone here can look it over for any syntax problems.

Good luck,
GJ

 

[vbdude13]

I am using <CFSTOREDPROC>.

Here's my CF code:
<CFSTOREDPROC PROCEDURE=&quot;APP_PKG.APP_DESC_CHECK&quot; DATASOURCE=&quot;#SESSION.DB#&quot; USERNAME=&quot;#SESSION.USERID#&quot; password=&quot;#SESSION.PASSWD#&quot;>
<cfprocparam type=&quot;IN&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; value=&quot;#form.description#&quot; maxlength=&quot;40&quot;>
<cfprocparam type=&quot;OUT&quot; cfsqltype=&quot;CF_SQL_REFCURSOR&quot; variable=&quot;resultset&quot;>
<cfprocresult name=&quot;qDescCheck&quot;>
</CFSTOREDPROC>

Here's my stored procedure:
PROCEDURE app_desc_check (p_description IN varchar2, p_list OUT app_table_rows)
IS
BEGIN
OPEN p_list FOR
SELECT DESCRIPTION
FROM APP_CODES
WHERE DESCRIPTION = p_description;
END app_ac_desc_check;

Here's the table desc for APP_CODES:
Column Name Null? Type
------------------------------ -------- ----
INPUT_BY VARCHAR2(10)
INPUT_DT DATE
ACTION_CODE NOT NULL VARCHAR2(5)
DESCRIPTION NOT NULL VARCHAR2(40)
UPDATED_BY VARCHAR2(10)
UPDATE_DT DATE

Here's the input field from from the cfm:
<input type=&quot;Text&quot; name=&quot;DESCRIPTION&quot; size=&quot;40&quot; maxlength=&quot;40&quot; tabindex=&quot;2&quot; class=&quot;text&quot; title=&quot;Description&quot;>

It seems too simple to screw up, but something's not right.

 

[vbdude13]

Correction to the procedure (I mistyped the END:

PROCEDURE app_desc_check (p_description IN varchar2, p_list OUT app_table_rows)
IS
BEGIN
OPEN p_list FOR
SELECT DESCRIPTION
FROM APP_CODES
WHERE DESCRIPTION = p_description;
END app_desc_check;

Any help would be great.

 

[GunJack]

Here's two things you can try but I'm somewhat guessing as I don't see anything immediately wrong.

1. <cfquery
name=&quot;resultset&quot;
DATASOURCE=&quot;#SESSION.DB#&quot;
USERNAME=&quot;#SESSION.USERID#&quot;
password=&quot;#SESSION.PASSWD#&quot;
>
call APP_PKG.APP_DESC_CHECK '#form.description#', 'resultSet'
</cfquery>

You may need to remove the &quot;call&quot; word and/or the single quotes.

2. Change &quot;CF_SQL_VARCHAR&quot; to &quot;CF_SQL_LONGVARCHAR&quot;

Let me know if either of these produce different results,
GJ