Skip to main content
M
Mr_Roomi
Participant
March 9, 2009
Question

Error Accessing an Oracle Function

  • March 9, 2009
  • 2 replies
  • 2980 views
Hi There,
I am trying to access an oracle function and getting an error.

CREATE OR REPLACE FUNCTION simple RETURN VARCHAR2 IS
BEGIN
RETURN 'Simple Function';
END simple;

<cfstoredproc procedure="simple" datasource="MySource" returnCode ="no">
<CFPROCRESULT name="simple_function">
</cfstoredproc>

Error:
Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC]Syntax error or access violation.

I do have access to this function. I tried it from SQL Plus and it works fine.

any help in this will be highly appreciated.

Regards,
This topic has been closed for replies.

2 replies

M
Mr_RoomiAuthor
Participant
March 16, 2009
As per Adam Cameron advice, I changed my SQL function:
CREATE OR REPLACE FUNCTION simple RETURN VARCHAR2 IS
a varchar2(10) := '';
BEGIN
select 'Simple' into a from dual;
Return a;
END simple;

As per paross1 advice, I changed my code to:

<cfstoredproc procedure="simple" datasource="MySource" returnCode ="yes">
<CFPROCRESULT name="simple_function">
</cfstoredproc>

I am still getting the following error:
Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC]Syntax error or access violation.
The error occurred on line 53.

Any advice will be highly appreciated.
P
paross1
Participating Frequently
March 16, 2009
You can NOT return the value of a PL/SQL function using cfprocresult. Either change your PL/SQL function to a procedure, then return your value via an OUT parameter and cfprocparam, or do something like the following:

<cfstoredproc procedure="simple" datasource="MySource" returnCode ="yes">
</cfstoredproc>

<cfoutput>#cfstoredproc.statusCode#</cfoutput>

Your "return value" will be contained in cfstoredproc.statusCode variable. However, since you are using an ODBC connection to your database, your results may be unpredictable.

Phil
N
Newsgroup_User
Inspiring
March 9, 2009
I'm not sure why you're getting the syntax error, but your technique won't
work anyhow. simple() returns a simple value. <cfprocresult> expects a
recordset.

However you can probably achieve your desired result with <cfquery>:

<cfquery name="q" datasource="intranet">
select simple() as simple_function
from dual
</cfquery>

--
Adam
M
Mr_RoomiAuthor
Participant
March 9, 2009
Thank you much Adam...
P
paross1
Participating Frequently
March 11, 2009
You should restrict yourself to calling PL/SQL procedures from ColdFusion if you wish to use cfstoredproc rather than trying to call PL/SQL functions. You have to resort to trickery to access the values returned from Oracle functions, whereas if you strictly use procedures, you can return values via OUT parameters or result sets (ref cursors).

Having said the above, you could capture the funtion value returned by setting returncode="yes" and looking at #cfstoredproc.statusCode#

<cfstoredproc procedure="simple" datasource="MySource" returnCode ="yes">
</cfstoredproc>

<cfoutput>#cfstoredproc.statusCode#</cfoutput>

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices