DataWindows, Oracle and Arrays: Anyone got this working?

[Former Member]

I am trying to use a stored procedure that accepts an array as a parameter as a datawindow data source.  I am receiving an ORA-06550.

(2b85c10): PROCEDURE PREPARE: (2b85c10): execute pkg_findtest.getTest;0 an_memberArray = 785145,785146,785147,785148, adtm_effective = TO_TIMESTAMP('1-02-2015 00:00:00','DD-MM-YYYY HH24:MI:SS'), adtm_limit = TO_TIMESTAMP('6-08-2015 23:59:59','DD-MM-YYYY HH24:MI:SS') (3.266 MS / 678.704 MS)

(2b85c10): *** ERROR 6550 ***(rc -1) : ORA-06550: line 1, column 15: PLS-00306: wrong number or types of arguments in call to 'GETTEST' ORA-06550: line 1, column 15: PL/SQL: Statement ignored

(2b85c10): CANCEL: (0.017 MS / 678.721 MS)

The package/procedure are defined as:

CREATE OR REPLACE PACKAGE pkg_findTest

AS

   TYPE num_tabType IS TABLE OF NUMBER;

   FUNCTION f_getTest(an_Array IN num_tabType, adtm_effective IN date, adtm_limit IN date)

     RETURN SYS_REFCURSOR;

PROCEDURE getTest(an_Array IN num_tabType, adtm_effective IN date, adtm_limit IN date, test_data OUT SYS_REFCURSOR);

END pkg_findTest;

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

CREATE OR REPLACE PACKAGE BODY pkg_findtest AS

   FUNCTION f_getTest(an_Array IN num_tabType, adtm_effective IN date, adtm_limit IN date)

     RETURN SYS_REFCURSOR

   AS

     test_data     SYS_REFCURSOR;

   BEGIN

       OPEN test_data FOR

     SELECT DISTINCT

            data_element1

       FROM test_table

     WHERE data_element2

                IN (SELECT column_value

                                               FROM TABLE(an_Array));

     RETURN test_data;

   END f_getTest;

   PROCEDURE getTest(an_Array IN num_tabType, adtm_effective IN date, adtm_limit IN date, test_data OUT SYS_REFCURSOR)

   AS

   BEGIN

     test_data       := f_getTest(an_Array, adtm_effective, adtm_limit);

   END getTest;

END pkg_findTest;

[Former Member]

Bump...

Bruce,

Did you see my reply to your response?

Thanks in Advance

[former_member190719]

Yes.  I've haven't done that in the past, and haven't had time to try it.

[former_member190719]

Yes.  Take a look at my Oracle samples (oracle.zip) on my Google Drive:  https://goo.gl/VXouQ

It contains a demo of passing string arrays and number arrays.

[Former Member]

Thanks Bruce,

I downloaded it before I posted and what I found was not a direct match for what I am trying to accomplish.  I saw d_roles, d_constrained_stored_procedure and d_unconstrained_stored_procedure as datawindows with stored procedure sources.  None of these are using an array.  You have an inline code example using an RPC for NUMBER_ARRAY_ARGS.

Is the RPC route my only choice?  I've never used one as a dataWindow source.  Also, Oracle's indexed by tables can't be used in a TABLE() cast, so it would require extra code to convert from an associative array to a nested table.

Christopher

[Former Member]

Forgot to include argument definition on PB side.