Highlighted

Coldfusion Standard and Stored Procedure

New Here ,
Aug 03, 2016

Copy link to clipboard

Copied

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?

Views

385

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

Coldfusion Standard and Stored Procedure

New Here ,
Aug 03, 2016

Copy link to clipboard

Copied

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?

Views

386

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 03, 2016 0
LEGEND ,
Aug 03, 2016

Copy link to clipboard

Copied

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,

^_^

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
Reply
Loading...
Aug 03, 2016 0
New Here ,
Aug 03, 2016

Copy link to clipboard

Copied

What do you mean?

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

Can you link me some example?

Thank you very much.

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
Reply
Loading...
Aug 03, 2016 0
LEGEND ,
Aug 03, 2016

Copy link to clipboard

Copied

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.

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
Reply
Loading...
Aug 03, 2016 0