Error when calling an Oracle Stored Procedure

[Sid Wing]

Good Morning - 

I am using Lucee 4.5.1.000 final

I am trying to make a call to an Oracle Stored Procedure and keep getting the following error:

"PLS-00306: wrong number or types of arguments in call to XXX"

I had this same setup working under Railo 4 - but my implementation of that was last year.  I remember having this issue back then - but do not remember what steps I finally found to correct it.  Sample code is below:

The call to the stored procedure:

                <cfstoredproc procedure="MYAPP_APPL.ATTENDEE_CONFERENCE_SEARCH" datasource="#application.dsn#">

<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="in" value="#Trim(session.email)#" />

<cfprocparam cfsqltype="CF_SQL_NUMERIC" type="out" variable="out_count_records" />

<cfprocparam cfsqltype="CF_SQL_REFCURSOR" type="out" variable="out_result_cursor" />

</cfstoredproc>

The Oracle Stored Procedure:
----------------------------------------------

create or replace PROCEDURE "ATTENDEE_CONFERENCE_SEARCH" (

    in_attendee_email IN varchar,

    out_count_records OUT number,

    out_result_cursor OUT SYS_REFCURSOR

)

AS

    

BEGIN

SELECT count(*) into out_count_records from (SELECT * from OCFO_FOREIGN_CONF where ATTENDEE_EMAIL = in_attendee_email);

 

OPEN out_result_cursor FOR

        SELECT conf.RECORD_NUM, details.CONFERENCE_NUMBER, conf.STATUS as ATTENDEE_STATUS, details.CONF_START_DATE, details.CONF_END_DATE, details.CONFERENCE_NAME, details.STATUS as CONFERENCE_STATUS

        FROM OCFO_FOREIGN_CONF conf, OCFO_CONF_RECORD_DETAILS details

        WHERE 

        conf.RECORD_NUM = details.RECORD_NUMBER and

        ATTENDEE_EMAIL = in_attendee_email;

      

END;

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

Any help would be greatly appreciated!

NOTE:  If I run the query from inside a <cfquery> it returns the expected results.

[Sid Wing]

We have even gone as far as removing the COUNT from the Stored Procedure (and the cfprocparams) to simply get test with one "in" and one "out" - still the same error.

[Ronnie Otts]

I have the same issue.

[mmm mmm]

Here's what you posted on railo g-group.

Actually - found the issue.  We changed the <cfprocresult> to a <cfprocparam type=out> with the same name - and now it works.  

For some reason - ACF accepts the refcursor out from the SP as a proc result - but Railo does not.

[Ronnie Otts]

It seems like my issue is just calling an SP that is part of a package.

On Monday, April 27, 2015 at 9:17:17 AM UTC-5, Sid Wing wrote:

[Sid Wing]

Yes - and this did not work with Lucee

--
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
---
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/H22AKjWeDb8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/229c25e1-c239-49d5-a8db-3b28bd4b9a8d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ronnie Otts]

here is my package and procedure

create or replace package body TESTPACK as


  PROCEDURE testproc(testparam IN VARCHAR2)
  AS
  BEGIN
      insert
      into DEBUG_TABLE
      values(SYSDATE, testparam );
     
    DBMS_OUTPUT.PUT_LINE(testparam);
  END testproc;
END TESTPACK;

Below it shows the " call TESTPACK.TESTPROC() " with no params.

Lucee 4.5.1.000 Error (database)

Message

Detail ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'TESTPROC'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL { call TESTPACK.TESTPROC() }

CF code

<cfstoredproc procedure="TESTPACK.TESTPROC" datasource="xe" result="spRes">
 <cfprocparam type="in" value="hey from lucee">
</cfstoredproc>

On Monday, April 27, 2015 at 9:17:17 AM UTC-5, Sid Wing wrote: