2 Replies Latest reply: Sep 16, 2013 8:06 AM by user4928701 RSS

    OCI doc says Cursor and Nested table have the same bind type SQLT_RSET but they don't

    user4928701

      5 Binding and Defining in OCI

      PL/SQL REF CURSORs and Nested Tables in OCI

      says SQLT_RSET is passed for the dty parameter.

       

      If I use SQLT_RSET for the return value of a function that returns a table and pass a statement handle's address for the OCI parameter data pointer, I expected that the statement handle will be instantiated as a result of executing the function on which I can further perform fetch, similar to a cursor. But it throws exception PLS-00382: expression is of wrong type ORA-06550: line 2, column 3. Is the above documentation wrong?

      From the OCI header file I see that for varray and nested table it mentions to use SQLT_NCO. I could find no example in the OCI documentation on how to pass or receive as return value a nested value when using SQLT_NCO.

      Please help before I shoot myself.

        • 1. Re: OCI doc says Cursor and Nested table have the same bind type SQLT_RSET but they don't
          Vincent Rogier

          Hi,

           

          Oracle doc is not wrong. Nested table was used to also refer to kind of cursors.

           

          SQLT_NCO is not meant to be used for binding host variable.

          It it only used in type descriptors to indicate that an UDT is a Collection.

           

          To bind a host variable that must receive a SQL collection (varray, nested table), use SQLT_NTY.

          Have a look at the OCILIB source code for more information related to binding collections.

           

          Regards,

          • 2. Re: OCI doc says Cursor and Nested table have the same bind type SQLT_RSET but they don't
            user4928701

            So the Nested table I quoted in the doc is not actually used to mean a table type below?

            create type t_resultsetdata as object (

            i int, d decimal, c varchar(10)

            );

             

            create type t_nested_resultsetdata as table of t_resultsetdata;

             

            create function Blah return t_nested_resultsetdata  is . . .

             

            For this you are saying to use SQL_NTY and not SQL_NCO. Can you tell where this usage is documented, because ocidfn.h says

            #define SQLT_NTY  108                               /* named object type */
            #define SQLT_NCO  122  /* named collection type (varray or nested table) */

             

            Another question - Because of the original document I said I followed, I thought I could treat cursor and nested table similarly in the calling application, i.e. I could repeatedly do a fetch on the OCIStmt* which will be bound for nested table. Now from what you say I understand I can't really bind a OCIStmt* for nested table but have an object type. That means it will get all the data of that collection in one go, right? LIke I said, lack of examples is making this tough. I don't want to look into OCI source code, as that will be too much.