Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Help with calling Oracle Procedure

New Here ,
Oct 19, 2007 Oct 19, 2007
First, let me state that I'm new to Oracle and using it with ColdFusion, but I've used CF on and off for the past 6 years. So this might be a simple "newbie" problem with Oracle and CF.

My boss wrote the following simple Oracle procedure to concatenate three variables together (I know I could do this in CF directly, but I need to be able to use his existing procedures in Oracle)

SPEC:
function ShowCaption(inprecaption in long,
incaption in long,
inpostcaption in long,
method in varchar2,
xcoord in number,
ycoord in number)
return long;

BODY:
function ShowCaption(
inprecaption in long,
incaption in long,
inpostcaption in long,
method in varchar2,
xcoord in number,
ycoord in number)
return long
is
begin
return inprecaption || incaption || inpostcaption;

end ShowCaption;

I'm trying to call that procedure using the attached code but when I run this code I receive the error "ORA-01008: not all variables bound" and I'm not sure why. Searching Google hasn't come up with anything and I've used other procedures, but can't seem to make this one work. I know the procedure works within Oracle as I'm able to run it.

I would really appreciate any ideas.
TOPICS
Database access
977
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Oct 22, 2007 Oct 22, 2007
Since he wrote his PL/SQL as a FUNCTION instead of a PROCEDURE, it complicates returning the result to ColdFusion. You haven't provided any means of capturing the return value of your function call in your CFSTOREDPROC call. If he had declared this a procedure, then you probably would have added an OUT parameter to the PL/SQL and an OUT CFPROCRESULT tag, but since it is a function, you are going to have to capture the result differently.

Try adding a returncode="yes" parameter to your CFSTOREDPROC tag, then check the cfstoredproc.statusCode variable for your returned result value:

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

Normally, I avoid calling Oracle functions from ColdFusion because in older versions of CF, there was no way of capturing the return value, so I rarely ever do it this way. In other words, I strictly write PL/SQL procedures for use with ColdFusion. However, if you are required to work with an existing function, then the method mentioned above may work for you.

Phil
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 22, 2007 Oct 22, 2007
Could I create a procedure that calls this function and has an OUT variable to return the value?

If we are going to go with CF to replace our existing web front-end, I have to be able to re-use as much of my bosses existing Oracle code, no matter what.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Oct 22, 2007 Oct 22, 2007
quote:

Could I create a procedure that calls this function and has an OUT variable to return the value?
That is always an option if you can't alter the original function.

Phil
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 25, 2007 Oct 25, 2007
LATEST
There is actually a rather straightforward method to call an Oracle PL/SQL function which I would recommend as an alternative to the already suggested fix.

You can actually call any Oracle function (whether provided by Oracle or user defined) directly inside of a SQL query. So, instead of using <cfstoredproc.... Just do a straight <cfquery... It would look something like this:

<cfquery name="myquery" datasource=......>
select ShowCaption(param1, param2, param3...) from dual
</cfquery>

<cfdump var="#myquery#">


Couple notes:
1. The query form "Select function_call from dual" is actually a very standard way to call some functions in Oracle. The "dual" table is a dummy table which is guaranteed to be inside of every Oracle database and contains exactly 1 record.

2. I would suggest always prefacing all table names, user defined functions, user defined packages, and user defined procedures with the Schema name that they reside in.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources