Skip to main content
Participant
August 3, 2016
Question

Coldfusion Standard and Stored Procedure

  • August 3, 2016
  • 1 reply
  • 623 views

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?

This topic has been closed for replies.

1 reply

WolfShade
Legend
August 3, 2016

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,

^_^

GolemAuthor
Participant
August 3, 2016

What do you mean?

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

Can you link me some example?

Thank you very much.

WolfShade
Legend
August 3, 2016

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.