Skip to main content
A
Anonymous
January 23, 2007
Question

oracle Stored Procedure access from CF 6.1

  • January 23, 2007
  • 1 reply
  • 342 views
Hi,
I am using Oracel 10g, Oracle 6.1, i want to use Stored Procedures in my project. I am getting some problems while using this one.. please help me.. Is there any configuration setting in the Cold Fusion administration...

Oracle Package/Procedure:

CREATE OR REPLACE PACKAGE one_function AS
FUNCTION GetOSUser RETURN VARCHAR2;
END one_function;
/
CREATE OR REPLACE PACKAGE BODY one_function AS
FUNCTION getosuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
select prog_num into vOSUser from gcadmin.tbl_program where prog_num=1000;
RETURN vOSUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNK';
END getosuser;
END one_function;
/

CFML Sytax:
<cfstoredproc procedure="one_fucntion.getosuser" returncode="yes" datasource="gcadmin" debug="yes">

<cfprocresult name="rs1">
<cfprocparam cfsqltype="cf_sql_integer" type="IN" value="2" dbvarname="@number_in">
<cfprocparam type = "OUT" cfsqltype="cf_sql_varchar" variable ="vOSUser">

</cfstoredproc>
<cfoutput query="rs1">
#vOSUser#
</cfoutput>

Please help me.....
This topic has been closed for replies.

1 reply

P
paross1
Participating Frequently
January 23, 2007
Better to use a PL/SQL stored procedure than a function, as "older" versions of ColdFusion could not handle the retun value from an Oracle function. Not sure about 6.1, but you can use them with MX7, but you have to capture your return code in a variable named cfstoredproc.StatusCode, such as below:

<cfstoredproc procedure="one_fucntion.getosuser" returncode="yes" datasource="gcadmin" debug="yes">
<cfprocparam cfsqltype="cf_sql_integer" type="IN" value="2" >
</cfstoredproc>

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

I would rewrite your PL/SQL SP as a procedure with an OUT parameter that you could capture in a CFPROCPARAM.

Also, you would only use CFPROCRESULT if you were returning the results of a query that you had selected into a referrence cursor in your proc (OPEN out_ref_cursor FOR SELECT...... etc.), but you would have to contain your procedure in a package and have a ref cursor type declared globally , etc. Your use of CFPROCRESULT is not necessary to return a single value from a procedure, only a CFPROCPARAM of type="out".

Bottom line, as written, what you have isn't really even close... sorry.

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