oracle Stored Procedure access from CF 6.1

hi,
i using oracel 10g, oracle 6.1, want use stored procedures in project. getting problems while using one.. please me.. there configuration setting in cold fusion administration...

oracle package/procedure:

create or replace package one_function
function getosuser return varchar2;
end one_function;
/
create or replace package body one_function
function getosuser return varchar2
vosuser user_users.username%type;
begin
select prog_num vosuser gcadmin.tbl_program prog_num=1000;
return vosuser;
exception
when others
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 me.....

better use pl/sql stored procedure function, "older" versions of coldfusion not handle retun value oracle function. not sure 6.1, can use them mx7, have capture return code in variable named cfstoredproc.statuscode, such 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 rewrite pl/sql sp procedure out parameter capture in cfprocparam.

also, use cfprocresult if returning results of query had selected referrence cursor in proc (open out_ref_cursor select...... etc.), have contain procedure in package , have ref cursor type declared globally , etc. use of cfprocresult not necessary return single value procedure, cfprocparam of type="out".

bottom line, written, have isn't close... sorry.

phil

More discussions in Database Access

adobe