CODEWITHSUNDEEP
oracle11gcoldfusion-2016
MGL
MGL

Reputation: 145

Calling Oracle procedure with error

I'm calling a procedure written in Oracle 11g from ColdFusion2016. I've been debugging since yesterday unsuccessfully, a little help is appreciated. I don't know if this error is caused my oracle procedure or Coldfusion so I did some test by calling the procedure from my oracle sql developer:

variable x refcursor;
variable y varchar2(200);
variable z varchar2(200);
exec um_check_alloc_bene_loadv2(v_session_id => 1, v_summary => :y, v_continue => :z, cv_1 => :x );
print x;
print y;
print z;

This get me some result. So, my assumption is that the error must be due to the ColdFusion call. But I can't see what's wrong with my call:

        <cfstoredproc procedure="um_check_alloc_bene_loadV2" datasource="#Trim(TESTDB)#">
          <cfprocparam type="in" cfsqltype="cf_SQL_INTEGER" variable="session_id" value="#Trim(max_session_id)#" MAXLENGTH="4">
          <cfprocparam type="out" cfsqltype="CF_SQL_VARCHAR" variable="summary" value="">
          <cfprocparam type="out" cfsqltype="CF_SQL_VARCHAR" variable="continue" value="">
        <cfprocresult name="errors" resultset="1">
        </cfstoredproc>

The beginning of the procedure looks like this:

create or replace PROCEDURE um_check_alloc_bene_loadV2 (
  v_session_id IN     NUMBER DEFAULT NULL,
  v_summary       OUT VARCHAR2                                /* DEFAULT ' '*/
                          ,
  v_continue      OUT VARCHAR2                                /* DEFAULT ' '*/
                          ,
  cv_1         IN OUT SYS_REFCURSOR)
AS
  v_rowcount   NUMBER (10, 0);
  v_errorcount NUMBER (5, 0);
BEGIN
  -- clean errors
  UPDATE um_allocation_beneficiary_ldV2
     SET errors = ' '
   WHERE session_id = v_session_id;

and when I call this proc from my ColdFusion I got this error that I cannot figure out:

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 
'UM_CHECK_ALLOC_BENE_LOADV2' ORA-06550: line 1, column 7: PL/SQL: Statement 
ignored
........................................
138 :             <cfprocparam type="out" cfsqltype="CF_SQL_VARCHAR" 
variable="summary" value="">
139 :             <cfprocparam type="out" cfsqltype="CF_SQL_VARCHAR" 
variable="continue" value="">
140 :           <cfprocresult name="errors" resultset="1">
141 :           </cfstoredproc>
142 :           <table align="center" width="90%">

SQLSTATE      HY000
DATASOURCE    TESTDB
VENDORERRORCODE   6550
SQL   {call um_check_alloc_bene_loadV2( (param 1) , (param 2) , (param 3) )}
Resources:

Upvotes: 0

Views: 211

Answers (1)

jose_bacoy
jose_bacoy

Reputation: 12684

Your procedure is expecting four parameters such as below but you pass only three parameters. Thanks.

PROCEDURE um_check_alloc_bene_loadV2 (
  v_session_id ,   
  v_summary    ,   
  v_continue   ,   
  cv_1         )

Upvotes: 2

Related Questions