This is the 1st. time I'm working with Oracle 11 and ColdFusion 8.

It used to be very easy when I did it in MSSQL, pretty straight forward but in Oracle I need to create a package for a str. procedure that returns a query result

So I created a package procedure in oracle successfully but when calling it from CF it threw me error and I have no clue as what the error is refering to.

(I googled and found similar problem, I followed and still did not work)

Can anyone shed some light please?

I called it this way and it threw error at me:

<cfstoredproc procedure="DUP_PKG.DUP_NEW" datasource="#Trim(application.dsn)#" returncode="True"> 

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="ins_name" value="#Trim(session.instname)#">

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="aca_year" value="#Trim(ayr)#">

              <cfprocresult name="MyResult">   <------------------ ERROR POINTING TO THIS LINE          

</cfstoredproc>

OR

If I did it this way:

<cfstoredproc procedure="DUP_PKG.DUP_NEW" datasource="#Trim(application.dsn)#" returncode="True"> 

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="ins_name" value="#Trim(session.instname)#">

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="aca_year" value="#Trim(ayr)#">

              <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable="MyResult">  <------------------ ERROR POINTING TO THIS LINE          

</cfstoredproc>

The error was:

My package looks like this:

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

create or replace
PACKAGE DUP_PKG
AS

  TYPE mypkg IS REF CURSOR;

  PROCEDURE DUP_NEW (ins_name IN VARCHAR2, aca_year IN VARCHAR2, MyResult OUT mypkg);

END;

create or replace
PACKAGE BODY DUP_PKG
AS

PROCEDURE DUP_NEW (ins_name IN VARCHAR2,aca_year IN VARCHAR2, MyResult OUT mypkg)

IS
  BEGIN

// a bunch of INSERT statements

    INSERT INTO....

    INSERT INTO....

    etc

//After all insert are done, get some data using select statement and return the query result to the calling CF codes for further processing

   OPEN MyResult FOR

      SELECT columns

      FROM a table

      WHERE .....

  END;

END;