Problems with Oracle Stored Function (Procedure)

[Rob@PSA]

I am trying to call a function in an Oracle database package using
cfstoredporc. I keep getting the following error:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value
error: character to number conversion error ORA-06512: at line 1

When I turn on robust debugging, it tells me the error occurred in the
following line:

<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="viewschdocs_in"
value="N" null="no">

As far as I can tell, everything is correct. Can anyone help me understand
what might be going on here?

Thx,
--Rob

[javabuzz]

check the datatype expected in the ORACLE PL/SQL Statement and make
sure your passing the appropriate datatype from cfstoredporc (This is
an error occuring at the db level-hence the ORA-06502 error code).

[javabuzz]

javabuzz wrote:

> check the datatype expected in the ORACLE PL/SQL Statement and make
> sure your passing the appropriate datatype from cfstoredporc (This is
> an error occuring at the db level-hence the ORA-06502 error code).

Verbose-VALUE_ERROR

An arithmetic, conversion, truncation, or size-constraint error occurs.
For example, when your program selects a column value into a character
variable, if the value is longer than the declared length of the
variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In
procedural statements, VALUE_ERROR is raised if the conversion of a
character string into a number fails. (In SQL statements,
INVALID_NUMBER is raised.)

[javabuzz]

javabuzz wrote:

> check the datatype expected in the ORACLE PL/SQL Statement and make
> sure your passing the appropriate datatype from cfstoredporc (This is
> an error occuring at the db level-hence the ORA-06502 error code).

Verbose-VALUE_ERROR

An arithmetic, conversion, truncation, or size-constraint error occurs.
For example, when your program selects a column value into a character
variable, if the value is longer than the declared length of the
variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In
procedural statements, VALUE_ERROR is raised if the conversion of a
character string into a number fails. (In SQL statements,
INVALID_NUMBER is raised.)

[Nicholas 11]

As far as I know, you must specify ALL Oracle SP or Function parameters in a
proper order.
If you are missing one or more 'cfprocparam' tags, you will be getting an
error.

In your case maybe on ther place of number you have a character parameter, so
Oracle cannot conver it.

[paross1]

You used the term Function and Procedure in your topic, so I'm not sure if you
are using the terms interchangeably or not. However, if you really meant
function, then you can NOT call PL/SQL functions from ColdFusion, as CF can not
handle the return value of a function. You can only call stored PL/SQL
procedures, which would return any values back to CF as parameters or return
sets. Period.

Phil

[jonwrob]

Function call:

<cfquery name="call_function" dsn="#mydsn#">
select function_name() return_value
from dual
</cfquery>

To access:

<cfoutput>#call_function.return_value#</cfoutput>

Just like from sql*plus.

JR

[Nicholas 11]

The only problem in this call is that you cannot use named parameters - you
have to provide all parameters.

Does anybody knows how to execute Oracle's Stored Procedure or Function in
ColdFusion MX by specifying naming parameters?

[paross1]

[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=p art_cfm.htm


[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=p art_cfm.htm

Phil

[Nicholas 11]

Yes, I know, it's impossible with cfstoredproc...
I was thinking about calling SP with cfquery tag, and it actually works with
naming parameters. The only problem is how to return an output parameter from
within Oracle BEGIN-END block as a query?

[paross1]

JR, you are correct about calling a function within a CFQUERY by SELECTing it
from DUAL, but the oroginal poster was using CFSTOREDPROC, which can not be
used to execute Oracle functions. I should have been more specific in my
previous post that I was referring to using CFSTOREDPROC. Also, if you have
functions that return values as OUT parameters, your method won't work,
although it probably isn't a very sound practice to design functions that have
OUT parameters anyway. :D

Phil

[jonwrob]

Phil, I'm sorry you misunderstood me, but you said that you can't call a
function from CF. That's just not true. Rob@PSA (the OP) was trying to call a
function using CFSTOREDPROC. It doesn't work. I provided a way to call a
function. Yes, you cannot access OUT parameters this way, but then you and I
both know that it is VERY bad programming practice to return OUT parameters
from functions for this very reason.

Nicholas 11 is talking about named parameters which is a different, and
unrelated problem. To him I would say named parameters are not supported by
CFMX. Code around them. You have several options here, the best probably
being changing the procedure or function to check the parameter values for
null, and replacing them with the default value (p1 := nvl (p1, 'default
value'))

JR