cfprocparam and ORACLE

[tee4]

I need help with an error I am getting;

I am calling an Oracle DB procedure in a package(people)

PROCEDURE version_info (version OUT VARCHAR2) IS
BEGIN
version := people.ask_version;
END version_info;
*****The procedure calls a function which also returns a VARCHAR2*****

On calling the procedure from my CF page

<cfstoredproc procedure="people.version_info" datasource="#thedsn#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="version">
</cfstoredproc>

I get the following error:

Error Executing Database Query. - ORA-01008: not all variables bound

I have just read live docs (6.1) and the binding should work. Can anyone help
please

[tee4]

I need help with an error I am getting;

I am calling an Oracle DB procedure in a package(people)

PROCEDURE version_info (version OUT VARCHAR2) IS
BEGIN
version := people.ask_version;
END version_info;
*****The procedure calls a function which also returns a VARCHAR2*****

On calling the procedure from my CF page

<cfstoredproc procedure="people.version_info" datasource="#thedsn#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="version">
</cfstoredproc>

I get the following error:

Error Executing Database Query. - ORA-01008: not all variables bound

I have just read live docs (6.1) and the binding should work. Can anyone help
please

[paross1]

Nothing obvious, but a couple of things to consider. Does this procedure return
the correct value when called from SQL Plus? Also, what happens if you replace
the function call in your procedure with a constant, so that you are setting
the value of version with a known value? Is the function inside the procedure
being called accross a dblink? When I searched Oracle Metalink I got quite a
few hits on this error, and below ia just one sample:

Symptoms
A stored procedure calls an overloaded function that is in a package. Some of
the tables use a dblink to access data in an 8.1.7.4 remote database. The first
time the stored procedure calls the function, it works fine. Subsequent calls
to the same function results in the error:

ORA-01008: not all variables bound

In the absence of very much information, if the answer to my first question is
no, and the procedure returns a good value when using a constant, then I would
suspect the function call as being the problem area.

Phil

[paross1]

See [url]http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=1151346&enterthre ad=y[/url]

Phil

[tee4]

Many thanks for your response.

I have actually written another procedure that calls the same function, but it
returns a VARCHAR instead of a VARCHAR2 and it works!

PROCEDURE get_version (p_version OUT VARCHAR) IS
BEGIN
p_version := cfi_person_org.ask_version;
END get_version;

INSTEAD OF

PROCEDURE get_version (p_version OUT VARCHAR2) IS
BEGIN
p_version := cfi_person_org.ask_version;
END get_version;

But isn't VARCHAR deprecated in ORACLE

[paross1]

VARCHAR is technically a subtype of VARCHAR2, but I would consider it
depricated and not use it if possible. Perhaps the cfi_person_org.ask_version
function is old and uses the VARCHAR data type. If you can't alter the
function, then try casting its return value to VARCHAR2 before setting p_version

Does this work?

PROCEDURE get_version (p_version OUT VARCHAR2) IS
BEGIN
p_version := CAST(cfi_person_org.ask_version AS VARCHAR2);
END get_version;

Phil

[tee4]

Sorry, what I was actually trying to point our is that the version of the
procedure that returns a VARCHAR2 doesn't work but the version that returns a
VARCHAR works but the problem is VARCHAR is deprecated.

I can' t understand why there is a binding error with the version that returns
VARCHAR2 though!

[paross1]

Well, it looks like the data type of cfi_person_org.ask_version is VARCHAR, and
for whatever reason, something is objecting to that value being passed as an
OUT parameter of your procedure as a VARCHAR2. My suggestion is just to CAST
the return value of cfi_person_org.ask_version to a VARCHAR2 so that your
procedure can return a VARCHAR2. If that isn't what you want to do, then
whatever.

Phil

[tee4]

Thanks

[paross1]

....or you could "fix" cfi_person_org.ask_version, you know, find out why its return value datatype is VARCHAR.

Phil