Hello folks,
I'm running CF MX6.1 on Win2003 and running against Oracle 9i DB on HPUX 11.11.
Database connection is via oracle JDBC thin driver.
An Oracle developer wrote a stored procedure for us that is 17 pages long in
very small type and I am having a hard time with it. The purpose is to take
name and email values from a form and add them to the Oracle DB and create
accounts for the new subscribers in the Marketing module. The problem that is
hanging me up is the following error message:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 1
The error occurred in E:\inetpub\wwwroot\iStore_dev\emailSignup.cfm: line 59
57 : type="out"
58 : cfsqltype="cf_sql_varchar"
59 : variable="return_status">
60 : </cfstoredproc>
61 :
------------------------
SQL {call XXSC_IBE_USER_IMPORT.REGISTER_USER_NEW( (param 1) , (param 2) ,
(param 3) , (param 4) , (param 5) , (param 6) , (param 7) )}
DATASOURCE TEST_ON_WDBCLONE
VENDORERRORCODE 1858
The procedure executes perfectly. I can view the data (new user) in the DB
right after it is called. I want to get the return_status from the procedure
so that I can say "welcome to your account" but I always get the error above.
You can see from the CF call below that my data types match up. Promo_code
(param 5) is passed from an input tag with Hidden attribute.
The package spec contains this:
PROCEDURE register_user_new (
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email_address IN VARCHAR2,
p_promo_code IN VARCHAR2,
p_language_preference IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_error_message OUT VARCHAR2
);
and my call to the procedure looks like this.
<cfstoredproc
procedure="XXSC_IBE_USER_IMPORT.REGISTER_USER_NEW"
datasource="TEST_ON_WDBCLONE"
returncode="no">
<!--- param 1 --->
<cfprocparam
type="In"
maxlength="20"
cfsqltype="cf_sql_varchar"
value="#FORM.first_name#">
<!--- param 2 --->
<cfprocparam
type="in"
maxlength="20"
cfsqltype="cf_sql_varchar"
value="#FORM.last_name#">
<!--- param 3 --->
<cfprocparam
type="in"
maxlength="45"
cfsqltype="cf_sql_varchar"
value="#FORM.email_address#">
<!--- param 4 --->
<cfprocparam
type="in"
maxlength="5"
cfsqltype="cf_sql_varchar"
value="#promocode#">
<!--- param 5 --->
<cfprocparam
type="in"
maxlength="5"
cfsqltype="cf_sql_varchar"
value="#FORM.language_preference#">
<!--- param 6 - capture return_status output parameter --->
<cfprocparam
type="out"
cfsqltype="cf_sql_varchar"
variable="return_status">
</cfstoredproc>
In the end the procedureis successful in adding the new user but I cannot get
the return_status value back to evaluate for Success of Failure.
Any help or suggestions are greatly appreciated.
Thanks, Jerry
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:258903
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
