ORA-01006: bind variable does not exist

[jayel.villamin]

I have an Oracle package with 2 procedures. The 1st one is just an insert
statement and the 2nd one takes care of updates/deletes.

I can run the 1st one without any problem. But on the 2nd one, I keep on
getting ORA-10006 error on the variable "p_return_code" if I ran it using
Coldfusion.

I have checked the variable names several times and as you can see, the 1st
"p_return_code" is exactly the same as the 2nd one. But the 2nd procedure just
won't run.

I can compile the package without any problems. and <b>I can run the 2nd
procedure from an Oracle client without any problems</b>.

Any ideas?

Please see the code below. Thank you.
==========
ONLY the header part so I wouldn't clutter my message.

CREATE OR REPLACE PACKAGE MAINTAIN_CLASSES AS

PROCEDURE ADD_CLASS (
p_class IN celc.classes.class%TYPE,
p_update_by IN celc.classes.updated_by%TYPE,

p_return_code OUT INT);

PROCEDURE UPDATE_DELETE_CLASS (
p_class_id_list IN VARCHAR2,
p_class_list IN VARCHAR2 DEFAULT NULL,
p_updated_by IN celc.classes.updated_by%TYPE,

p_return_code OUT INT);

END MAINTAIN_CLASSES;
======================

If my CFM file, I have the following cfstoredproc statements with
corresponding cdprocparam statements

<cfstoredproc procedure="MAINTAIN_CLASSES.ADD_CLASS" datasource="celc">
<cfprocparam variable="p_class" type="in" cfsqltype="cf_sql_varchar"
value="#form.class#">
<cfprocparam variable="p_updated_by" type="in" cfsqltype="cf_sql_varchar"
value="vill0042">
<cfprocparam variable="p_return_code" type="out"
cfsqltype="cf_sql_integer">
</cfstoredproc>


<cfstoredproc procedure="MAINTAIN_CLASSES.UPDATE_DELETE_CLASS"
datasource="celc">
<cfprocparam variable="p_class_id_list" type="in"
cfsqltype="cf_sql_varchar" value="#classIDList#">
<cfprocparam variable="p_class_list" type="in" cfsqltype="cf_sql_varchar"
value="#classList#">
<cfprocparam variable="p_updated_by" type="in" cfsqltype="cf_sql_varchar"
value="vill0042">
<cfprocparam variable="p_return_code" type="out"
cfsqltype="cf_sql_integer">
</cfstoredproc>

[jayel.villamin]

I have an Oracle package with 2 procedures. The 1st one is just an insert
statement and the 2nd one takes care of updates/deletes.

I can run the 1st one without any problem. But on the 2nd one, I keep on
getting ORA-10006 error on the variable "p_return_code" if I ran it using
Coldfusion.

I have checked the variable names several times and as you can see, the 1st
"p_return_code" is exactly the same as the 2nd one. But the 2nd procedure just
won't run.

I can compile the package without any problems. and <b>I can run the 2nd
procedure from an Oracle client without any problems</b>.

Any ideas?

Please see the code below. Thank you.
==========
ONLY the header part so I wouldn't clutter my message.

CREATE OR REPLACE PACKAGE MAINTAIN_CLASSES AS

PROCEDURE ADD_CLASS (
p_class IN celc.classes.class%TYPE,
p_update_by IN celc.classes.updated_by%TYPE,

p_return_code OUT INT);

PROCEDURE UPDATE_DELETE_CLASS (
p_class_id_list IN VARCHAR2,
p_class_list IN VARCHAR2 DEFAULT NULL,
p_updated_by IN celc.classes.updated_by%TYPE,

p_return_code OUT INT);

END MAINTAIN_CLASSES;
======================

If my CFM file, I have the following cfstoredproc statements with
corresponding cdprocparam statements

<cfstoredproc procedure="MAINTAIN_CLASSES.ADD_CLASS" datasource="celc">
<cfprocparam variable="p_class" type="in" cfsqltype="cf_sql_varchar"
value="#form.class#">
<cfprocparam variable="p_updated_by" type="in" cfsqltype="cf_sql_varchar"
value="vill0042">
<cfprocparam variable="p_return_code" type="out"
cfsqltype="cf_sql_integer">
</cfstoredproc>


<cfstoredproc procedure="MAINTAIN_CLASSES.UPDATE_DELETE_CLASS"
datasource="celc">
<cfprocparam variable="p_class_id_list" type="in"
cfsqltype="cf_sql_varchar" value="#classIDList#">
<cfprocparam variable="p_class_list" type="in" cfsqltype="cf_sql_varchar"
value="#classList#">
<cfprocparam variable="p_updated_by" type="in" cfsqltype="cf_sql_varchar"
value="vill0042">
<cfprocparam variable="p_return_code" type="out"
cfsqltype="cf_sql_integer">
</cfstoredproc>

[CF_Oracle]

Please put code of 2nd procedure. Also, how did you provide IN parameters when tested in Oracle? What are their source in ColdFusion?

[paross1]

Since you are declaring the OUT parameters in your PL/SQL as type INT (ANSI),
which is actually a subtype of Oracle type NUMBER, you might try using
CFSQLType = CF_SQL_DECIMAL instead of CF_SQL_INTEGER. Not sure if it will make
a difference, but I'd give it a shot. (Also, you don't need the variable
attribute in your cfprocparam tags for type="in", as this attribute is only
required for OUT or INOUT types. )

How does your 2nd PL/SQL procedure run if you EXECUTE it from SQL Plus? Are
any of your IN parameters NULL?

Phil