Error when calling an Oracle Stored Procedure

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#">



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 it returns the expected
results.

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.

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

Actually - found the issue. We changed the to a
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.On Monday, April 27, 2015 at 12:36:55 PM UTC-5, Sid Wing wrote:

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.

I have the same issue.On Monday, April 27, 2015 at 9:17:17 AM UTC-5, Sid Wing wrote:

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#">



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 it returns the expected
results.

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

On Monday, April 27, 2015 at 9:17:17 AM UTC-5, Sid Wing wrote: > > 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: > > procedure="MYAPP_APPL.ATTENDEE_CONFERENCE_SEARCH" > datasource="#application.dsn#"> > value="#Trim(session.email)#" /> > variable="out_count_records" /> > variable="out_result_cursor" /> > > > > 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 it returns the expected > results. >

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:

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#">



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 it returns the expected
results.