PL/SQL Cold Fusion MX error

From: silverback <silverback_at_photobooks.com>
Date: Tue, 03 Jun 2003 14:35:02 -0400
Message-ID: <3aqpdv8gavrtoh7fd0hq0rk12lmjvb0p3e@4ax.com>

I have a wierd one dealing with Cold Fusion and Oracle 8.1.7.4. I know that this is not the best place to ask this, but I am hoping for some wisdom from other DBA's who have had this kind of experience.

The user can run this procedure fine from SQLPLUS, but it fails with the error below on CF MX.

We are using a 8.1.7.4 database and an apache web server.

Ask any questions you need to to solve this one.

Thanks in advance,

Evan

------------------Package definition
CREATE OR REPLACE  PACKAGE "ESDR"."PACK_ISREFCUR_ARRAY"  AS
      TYPE refcArray IS REF CURSOR RETURN Empe%ROWTYPE ;

PROCEDURE PROC_ISREFCUR_ARRAY (outEMP OUT refcArray) ; END ; CREATE OR REPLACE PACKAGE BODY "ESDR"."PACK_ISREFCUR_ARRAY" AS PROCEDURE PROC_ISREFCUR_ARRAY (outEMP OUT refcArray) IS

BEGIN
OPEN outEMP FOR select * from empe where rownum < 20 ; -- implicit
cursor

END ;
END ;

----------------------SQLPLUS to run package

set serveroutput on;

declare
l_cursor PACK_ISREFCUR_ARRAY.refcArray; l_empe_rec esdr.Empe%ROWTYPE;
begin

PACK_ISREFCUR_ARRAY.PROC_ISREFCUR_ARRAY ( l_cursor );

LOOP
/* Fetch from cursor variable. */

FETCH l_cursor INTO l_empe_rec;
dbms_output.put_line( 'empe_i = '||l_empe_rec.empe_i ); EXIT WHEN l_cursor%NOTFOUND; -- exit when last row is fetched

process data record END LOOP;

end;

-----------------------------Error when executed from CFMX

Error Executing Database Query.

Parameter Type Conflict: sqlType=2006

The error occurred in
/disks/web/oak-dev/htdocs/oak-dev/oak/ais/esd/testit2.cfm: line 18

 16 :
 17 :       <CFPROCRESULT NAME="rs1">
 18 : </CFSTOREDPROC>
 19 :
 20 : <CFTABLE QUERY ="rs1" COLHEADERS>


 SQL
                  {call PACK_ISREFCUR_ARRAY.PROC_ISREFCUR_ARRAY(

(param
1)

                )}
 DATASOURCE
                  ESDWEB
 VENDORERRORCODE
                  17012

Please try the following:

Check the CFML Reference Manual to verify that you are using the correct syntax.

Search the Knowledge Base to find a solution to your problem. Browser

Mozilla/4.77 [en] (Windows NT 5.0; U) Remote Address

999.999.999.999
Referrer
Date/Time

03-Jun-03 12:02 PM

Stack Trace
at

cftestit22ecfm1654795671.runPage(/disks/web/oak-dev/htdocs/oak-dev/oak/ais/esd/testit2.cfm:18)

-----------------------Correct response from SQLPLUS
empe_i = 1
empe_i = 2
empe_i = 3
empe_i = 4
empe_i = 5
empe_i = 6
empe_i = 7
empe_i = 8
empe_i = 9
empe_i = 10
empe_i = 11
empe_i = 12
empe_i = 13
empe_i = 14
empe_i = 15
empe_i = 16
empe_i = 17
empe_i = 18

empe_i = 19
empe_i = 19 Received on Tue Jun 03 2003 - 13:35:02 CDT