cfstoredproc: PL/SQL: Statement ignored

[cscottucd]

Hoping someone can tell me what I'm doing wrong. I'm getting the error:

ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in
call to 'P_REG_PAY_CITES' ORA-06550: line 1, column 7: PL/SQL:
Statement ignored

The error occurred in D:\www\website\Scheduled\API_pay_citations.cfm: line 28

26 : <cfprocparam type="out"
dbvarname="o_re_id" variable="o_re_id" cfsqltype="cf_sql_numeric">
27 : <cfprocparam type="out"
dbvarname="o_error_code" variable="o_error_code" cfsqltype="cf_sql_numeric">
28 : <cfprocparam type="out" dbvarname="o_error_msg"
variable="o_error_msg" cfsqltype="cf_sql_varchar">
29 : </cfstoredproc>
30 :


--------------------------------------------------------------------------------

SQL { (param 1) = call p_reg_pay_cites( (param 2) , (param 3) , (param 4) ,
(param 5) , (param 6) , (param 7) , (param 8) )}
DATASOURCE TAPSTest
VENDORERRORCODE 6550
SQLSTATE 65000

Please try the following:
<<snip>>

I've established default vaules for testing purposes.: Code included



Thanks.

<cfparam name="citations" default="I050012421">
<cfparam name="amount" default="30.00">
<cfparam name="pmid" default="">
<cfparam name="o_re_id" default="">
<cfparam name="o_error_code" default="">
<cfparam name="o_error_msg" default="">
<cfparam name="return_receipt" default="">
<cfparam name="return_error" default="">
<cfparam name="return_message" default="">

<cfset return_receipt = o_re_id>
<cfset return_error = o_error_code>
<cfset return_message = o_error_msg>


<cfstoredproc procedure="p_reg_pay_cites" datasource="dbname"
username="APIuser" password="xxxxxxx" dbtype="dynamic" returncode="yes">
<cfprocparam type="in"
dbvarname="i_citation_list" value='#citations#' cfsqltype="cf_sql_varchar">
<cfprocparam type="in"
dbvarname="i_amt_paid" value="#amount#" cfsqltype="cf_sql_decimal" scale="2">
<cfprocparam type="in"
dbvarname="i_itid" value="1" cfsqltype="cf_sql_numeric">
<cfprocparam type="in"
dbvarname="i_pm_id" value="9" cfsqltype="cf_sql_numeric">

<cfprocparam type="out"
dbvarname="o_re_id" variable="o_re_id" cfsqltype="cf_sql_numeric">
<cfprocparam type="out"
dbvarname="o_error_code" variable="o_error_code" cfsqltype="cf_sql_numeric">
<cfprocparam type="out" dbvarname="o_error_msg"
variable="o_error_msg" cfsqltype="cf_sql_varchar">
</cfstoredproc>

[paross1]

First off, you might need to make sure that your CFPROCPARAMs are declared in
the same order as the parameters in your PL/SQL procedure declaration, as
dbvarname has been deprecated since MX 6.x and has no effect whatsoever. In
other words, you can't use dbvarname to match up your CF parameters with your
stored procedure parameters. Also, the parameters need to match in number and
type. What does your PL/SQL procedure declaration (header) look like?

Phil

[s6868]

A application I will be working on need to use PL/SQL in my Coldfusion
development, could anyone recommend any reference book on this? Or something
like using Oracle procedure in Coldfusion developemnt will be very helpful as
well.

Thanks a lot.
Bill

[CF_Oracle]

There is no PL/SQL specifically for ColdFusion or PL/SQL for Dummies:). But
you can choose from big variaty on
[url]http://www.amazon.com/gp/search.html/ref=pd_lpo_k2k_3/104-7117063-5508729?%5Fenc[/url]
oding=UTF8&keywords=oracle%20pl%2Fsql&index=blende d. Any of them would be
usefull. I would recommend O'reily series. Also, free online documentation is
available:
[url]http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/toc[/url].
htm

[paross1]

I've got the 2nd and 3rd editions of this book
[url]http://www.amazon.com/gp/product/0596009771/qid=1142457131/sr=2-3/ref=pd_bbs_b_2[/url]
_3/104-3020004-4357542?s=books&v=glance&n=283155 and they are excellent. Steven
Feuerstein is the PL/SQL guru, as far as I'm concerned.

As for the ColdFusion end, just study up on using CFSTOREDPROC, CFPROCPARAM,
and CFPROCRESULT tags. There are a few quirks regarding returning result sets
by having to use reference cursors, etc., especially with different versions of
ColdFusion and different Oracle drivers, and some CF books have sections about
using stored procedures, like the various Forta ColdFusion Constructin Kit
books. Happy hunting.

Phil