Skip to main content
Participant
Aug 3, 2016 10:23 AM UTC
Question

Coldfusion Standard and Stored Procedure

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 is closed to new replies. Start a new post to keep the conversation going.

3 replies

WolfShade
Legend
Aug 3, 2016 1:28 PM UTC

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,

^_^

Translate
GolemAuthor
Participant
Aug 3, 2016 1:39 PM UTC

What do you mean?

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

Can you link me some example?

Thank you very much.

Translate
WolfShade
Legend
Aug 3, 2016 2:07 PM UTC

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.

Translate