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?
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,
^_^
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.
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.