ColdFusion using Oracle's "INSERT ... RETURNING ... INTO" statement

Question

I am building a simple chat application with ColdFusion and I am trying to get a message ID when I run my insert. Here is what I have so far

<cffunction name="putMessage" access="remote" returnformat="JSON">

     <cfargument name="message" />
     <cfset LOCAL.id = 0 />

     <cfquery name="insertquery" datasource="myDS"> 
          insert into 
               chat (message) 
          values 
               (<cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.message#">)
          RETURNING
               id
          INTO
               <cfqueryparam cfsqltype="cf_sql_numeric" value="#LOCAL.id#">
     </cfquery>

     <cfreturn LOCAL.id />
        
</cffunction>

The error I am getting is

Error Executing Database Query.

Not all return parameters registered

I know there are workarounds with stored procedures, and running multiple queries. I would like to know how to do it this way.

Answer 1

This is an old question so apologies in advance for the necromancy. In my opinion the best (and maybe only) way to accomplish this is by wrapping the INSERT statement in a stored procedure and returning the values from the RETURNING ... INTO clause in an output parameter.

CREATE OR REPLACE PROCEDURE chat_insert
  ( p_message IN VARCHAR2, r_id OUT NUMBER )
AS
BEGIN
  INSERT INTO chat ( message )
  VALUES ( p_message )
  RETURNING id INTO r_id;
END;
/

You can then invoke this stored procedure from ColdFusion as follows:

<cffunction name="putMessage" access="remote" returnformat="JSON">
    <cfargument name="message" />
    <cfset LOCAL.id = 0 />

    <cfstoredproc procedure="chat_insert" datasource="myDS">
        <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="in" value="#ARGUMENTS.message#" />
        <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="LOCAL.id" />
    </cfstoredproc>

    <cfreturn LOCAL.id />
</cffunction>

Hope this helps.