ColdFusion - cfproc param : How do I call stored proc with db defined variable %TYPE?

Question

I have a stored proc in Oracle. One of the input params is a defined type (see below). The error I get whenever I call the cfstoredproc is "expression is of wrong type ORA-06550". It's a formatted varchar, it's a table column. There's no CFPROCPARAM type that matches that.

The type is a package-defined specific format (table column): EMAIL_ADDRESSES_TABLE.USER_ID%TYPE. The table column USER_ID is: USER_ID VARCHAR2(8 BYTE)

The USER_ID is a 0-padded numeric, like '00001234'

my call:

<cfstoredproc procedure="PK_EMAIL.get_emails" datasource="#MYDSN#">
  <cfprocparam type="in" cfsqltype="CF_SQL_VARCHAR" variable="p_user_id" value="#formattedUserId#">
  <cfprocparam type="in" cfsqltype="CF_SQL_NUMERIC" variable="p_active_only" value="1">
  <cfprocresult name="spResult">
</cfstoredproc>

the stored proc function:

FUNCTION get_emails(
  p_user_id        IN EMAIL_ADDRESSES_TABLE.USER_ID%TYPE,
  p_active_only    IN SIMPLE_INTEGER
) RETURN EMAIL_ADDRESSES_TABLE;

The error I get from the system is:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 18: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored 

Answer 1

@DanBracuk helped me figure this out.

These functions were originally set up to be exposed as Java API calls.

Short answer: It's a "FUNCTION", not a "PROCEDURE". ColdFusion cannot call a DB Function directly.

The work-around (info provided by someone here who did this once): Need to create an Oracle PROCEDURE that calls the function. It just acts as intermediary between CF and the Oracle function.