3 Replies Latest reply on Mar 20, 2017 4:54 PM by rp0428

    pl/sql in out types.cursortype

    user12198870

      Hi,

      I created an object type as:

      CREATE OR REPLACE TYPE HPOWNER.T_WEBMATERIALS AS TABLE OF O_WEBMATERIALS;

       

      and then a TABLE of the object:

      CREATE OR REPLACE TYPE HPOWNER.O_WEBMATERIALS AS OBJECT

      (

        PRODUCTNUMBER VARCHAR2(32),

        PRODUCTTYPE VARCHAR2(60),

        PRODUCTVARIANT NUMBER(1)

      )

       

      The procedure call is

      CREATE OR REPLACE PROCEDURE PRODUCTS (RESULTS IN OUT TYPES.CURSORTYPE)  IS

       

      How to send values in and get values from the procedure?

      Please give the syntax.

       

        • 1. Re: pl/sql in out types.cursortype
          BluShadow

          Well the TYPEs you've created are essentially a collection of objects (each object having 3 attributes)

           

          The procedure doesn't relate to those types in any way, it's expecting a cursor type (by the looks of it), so what are you trying to do exactly?  A cursor is not a collection of objects, don't be mislead by the name "TABLE" used to create a collection, it's not a table in the database table sense.

          • 2. Re: pl/sql in out types.cursortype
            Solomon Yakobson

            And what is TYPES.CURSORTYPE?

            Did you mean CREATE OR REPLACE PROCEDURE PRODUCTS (RESULTS IN OUT HPOWNER.T_WEBMATERIALS)  IS?

            If so:

             

            BEGIN

                PRODUCTS(

                         HPOWNER.T_WEBMATERIALS(

                                                HPOWNER.O_WEBMATERIALS(

                                                                       1,

                                                                       'MILK',

                                                                       1

                                                                      ),

                                                HPOWNER.O_WEBMATERIALS(

                                                                       2,

                                                                       'BEER',

                                                                       3

                                                                      )

                                                ...

                                               )

                        );

            END;

            /

             

            SY.

            • 3. Re: pl/sql in out types.cursortype
              rp0428

              How to send values in and get values from the procedure?

              Please give the syntax.

               

              The Oracle documentation provides ALL of the syntax for creating procedures and has working examples.

              https://docs.oracle.com/database/121/LNPLS/create_procedure.htm#LNPLS01373