Highlighted

cfprocparam : How do I call stored proc with db defined variable %TYPE?

New Here ,
Aug 08, 2014

Copy link to clipboard

Copied

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

Views

175

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfprocparam : How do I call stored proc with db defined variable %TYPE?

New Here ,
Aug 08, 2014

Copy link to clipboard

Copied

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

Views

176

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 08, 2014 0

Have something to add?

Join the conversation