calling an Oracle stored procedure

[r0nke]

I am looking to call an Oracle stored procedure, this is what I have been given and I dont know what to do with it.

DECLARE
o_firstcommdt VARCHAR2(4000);
o_covlvl VARCHAR2(4000);
o_covdesc VARCHAR2(4000);
o_sitestate VARCHAR2(4000);
o_amodel VARCHAR2(4000);
o_salechannel VARCHAR2(4000);
o_paycode VARCHAR2(4000);
o_errmsg VARCHAR2(4000);
BEGIN


svcasur_qry('#form.value#',o_firstcommdt,o_covlvl,o_covdesc,o_sitestate,o_amodel,o_salechannel,o_paycode,o_errmsg);
dbms_output.put_line(o_firstcommdt||o_covlvl||o_covdesc);


Form.value is the value I will be passing to the stored procedure and the other values should be returned by the stored procedure.

I dont know what cftags to use or the way to get the values back from the stored procedure.

Please help, its an oracle stored procedure.

Many thanks

 

[ecobb]

You would use cfstoredproc to call the procedure, cfprocparam to pass values to the procedure, and cfprocresult if you're returning results like a query.

Start here:

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00000338.htm#2607555

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[r0nke]

Thanks ECAR:

This is what I have but for some reason I get a syntax error.

<cfstoredproc procedure="procname" datasource="dsn">

<cfprocparam type="in" dbvarname="@i_san" value=#form.value# cfsqltype="cf_sql_varchar2">


<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="firstcommdt" dbvarname="@o_firstcommdt">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="covlvl" dbvarname="@o_covlvl">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="covdesc"
dbvarname="@o_covdesc">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="sitestate" dbvarname="@o_sitestate">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="amodel" dbvarname="@o_amodel">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="salechannel"
dbvarname="@o_salechannel">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="paycode"
dbvarname="@o_paycode">

<cfprocparam type="out" cfsqltype="cf_sql_varchar2" variable="errmsg"
dbvarname="@o_errmsg">

</cfstoredproc>

I didnt write the Oracle SP, but this is what the dba sent me:

DECLARE
o_firstcommdt VARCHAR2(4000);
o_covlvl VARCHAR2(4000);
o_covdesc VARCHAR2(4000);
o_sitestate VARCHAR2(4000);
o_amodel VARCHAR2(4000);
o_salechannel VARCHAR2(4000);
o_paycode VARCHAR2(4000);
o_errmsg VARCHAR2(4000);
BEGIN

-- Now call the stored program
procname('#form.value#',o_firstcommdt,o_covlvl,o_covdesc,o_sitestate,o_amodel,o_salechannel,o_paycode,o_errmsg);



PROCEDURE procname(
i_san VARCHAR2,
o_firstcommdt OUT VARCHAR2,
o_covlvl OUT VARCHAR2,
o_covdesc OUT VARCHAR2,
o_sitestate OUT VARCHAR2,
o_amodel OUT VARCHAR2,
o_salechannel OUT VARCHAR2,
o_paycode OUT VARCHAR2,
o_errmsg OUT VARCHAR2
)

 

[ecobb]

Try changing CF_SQL_VARCHAR2 to CF_SQL_VARCHAR. I don't think cfprocparam has a specific sqltype of VARCHAR2, it just has VARCHAR as a "catch-all". It should pass the value to the database however the database requires it.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher