3 Replies Latest reply on Sep 2, 2004 8:03 PM by 137783

    Package Arrays with Object Types

    137783
      Hi,
      I am working with Arrays on Interconnect Design
      I want to know if is posible to work with arrays that are declare as type on Package Specification.
      Regards
        • 1. Re: Package Arrays with Object Types
          137783
          I
          When I try to compile the Package generated by iStudio the following message appears
          PLS-00306 wrong number ot types of arguments

          Maybe is because iStudio creates Object Types and my store procedure have types inside package specification???

          Regards
          • 2. Re: Package Arrays with Object Types
            349404
            Ricardo,

            InterConnect only supports Object Types and Collections for this purpose. The best practice approach would be to include a serializer in your PL/SQL block that maps the object type/collection attributes to the PL/SQL type attributes.

            Here's an example. Let's assume the following package spec:

            PACKAGE EXAMPLE
            AS

            TYPE test_rec IS RECORD(
            a NUMBER
            ,b VARCHAR2(100)
            ,c DATE
            );

            TYPE test_arr IS TABLE OF test_rec;

            PROCEDURE create_test (input TEST_ARR);

            END;

            If you import the structure of EXAMPLE.create_test as your application view, iStudio will generate corresponding object types and collections, which you have to deploy together with the subscribing package stub.
            Rather than letting iStudio generate the object type and collection, I'd recommend to create these yourself as that gives you better control over the naming of the database objects.

            CREATE OR REPLACE TYPE global_test_rec AS OBJECT (
            a NUMBER
            ,b VARCHAR2(100)
            ,c DATE
            );

            CREATE OR REPLACE TYPE global_test_arr AS VARRAY(1000) OF global_test_rec;

            The iStudio generated subscribing procedure will have the object type/collection as an IN parameter and you will have to write a little serializer block before calling EXAMPLE.create_test, which will serialize the input_arr VARRAY into the local variable l_test_arr of type EXAMPLE.test_arr.

            PROCEDURE sub_<event_name> (
            input_arr GLOBAL_TEST_ARR
            )
            IS

            l_test_arr EXAMPLE.test_arr;
            i_idx NUMBER;

            BEGIN

            IF input_arr.EXISTS(1) THEN

            i_idx := input_arr.FIRST;

            WHILE i_idx IS NOT NULL LOOP

            l_test_arr(i_idx).a := input_arr(i_idx).a;
            l_test_arr(i_idx).b := input_arr(i_idx).b;
            l_test_arr(i_idx).c := input_arr(i_idx).c;

            i_idx := input_arr.NEXT(i_idx);

            END LOOP;

            END IF;

            EXAMPLE.create_test(l_test_arr);

            END;

            Hope this is helpful.

            Thanks,
            Markus

            ____________________________________________

            Markus Spohn
            Principal Product Manager
            OracleAS Integration

            Oracle Corporation
            Worldwide Headquarters
            500 Oracle Parkway m/s 1op627
            Redwood Shores, CA 94065
            U.S.A.

            e-Mail: markus.spohn@oracle.com
            ____________________________________________
            • 3. Re: Package Arrays with Object Types
              137783
              Thanks Markus for your help
              Thank you very much