Coldfusion Standard and Stored Procedure

[Golem]

Hi, I would like to know if Oracle stored procedure are supported in Coldfusion 2016 Standard (exept for reference cursors).

I try to call a simply stored procedure like this:

CREATE OR REPLACE PROCEDURE MYDATABASE.TEST_PROC

( FIRSTNAME IN VARCHAR2,

LASTNAME IN VARCHAR2,

FULLNAME OUT VARCHAR2)

AS

BEGIN

  FULLNAME := FIRSTNAME || '  ' || LASTNAME;

END;

/

With this coldfusion code:

<cfstoredproc procedure="TEST_PROC" datasource="mydatasource" returncode="No">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname=":FIRSTNAME" value="John">

  <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname=":LASTNAME" value="Doe">

  <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" dbvarname=":FULLNAME" variable="fullname">

</cfstoredproc>

But I receive this error:

ErrorCode 17041

Message Missing IN or OUT parameter at index:: 4

Anyone can help me?

[WolfShade]

All of the online examples for Oracle Stored Procedures use "SELECT".  Perhaps prepending your seventh line with SELECT might return the value?

I'm not familiar with Oracle SPs, so this is complete speculation.

HTH,

^_^

[Golem]

What do you mean?

Something about "SELECT FULLNAME := FIRSTNAME || '  ' || LASTNAME;"?

Can you link me some example?

Thank you very much.

[WolfShade]

This uses SELECT for returning value from Oracle Stored Procedures:

https://docs.oracle.com/cd/E28280_01/bi.1111/e16630/app_ds_from_sp.htm#BIRDG629

I've also seen DBMS_OUTPUT.put_line():

https://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/

I did find one example using the same method as you have used in your example, and cannot understand why it isn't working.

I did also find this blog entry (hope it helps).  https://blogs.oracle.com/jdevotnharvest/entry/the_infamous_missing_in_or

V/r,

^_^

UPDATE:  In the CF examples of CFSTOREDPROC that I've seen, the variable names were not prepended with a colon.