Details

    Description

      Add support for TABLE OF data type declaration, for scalar data types.

      If TYPE declaration appears in AS or DECLARE section of a stored routine, then the data type is created temporary.
      A CREATE TYPE statement creates types permanently.

      The below example:

      • Declares a data type table_of_int_t as TABLE OF INT
      • Declares a table variable tbl of this type
      • Loads data from the table t1 into the table variable tbl
      • Appends one more record to tbl using methods tbl.extend and tbl.count
      • Iterates through all records in tbl and inserts data into t2

      DROP TABLE t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (10);
      INSERT INTO t1 VALUES (20);
      DROP TABLE t2;
      CREATE TABLE t2 (a INT);
      DROP PROCEDURE p1;
      CREATE PROCEDURE p1
      AS
        TYPE table_of_int_t IS TABLE OF INT;
        tbl table_of_int_t;
      BEGIN
        SELECT * BULK COLLECT INTO tbl FROM t1;
        tbl.extend;
        tbl(tbl.count):=1;
       
        FOR Y IN 1 .. tbl.count
        LOOP
          EXECUTE IMMEDIATE 'INSERT INTO t2 VALUES(:param)' USING tbl(Y)+1;
        END LOOP;
      END;
      /
      CALL p1();
      SELECT * FROM t2;
      

      	 A
      ----------
      	11
      	21
      	 2
      

      Passing TABLE-type values to stored procedures

      DROP PACKAGE g1;
      DROP TABLE t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1);
      CREATE PACKAGE g1 IS
      TYPE table_t IS TABLE OF INT;
      PROCEDURE consume(t IN table_t);
      PROCEDURE test_t;
      END;
      /
      CREATE PACKAGE BODY g1 IS
      PROCEDURE consume(t IN table_t) IS
      BEGIN
      FOR c IN 1 .. t.count
      LOOP
      EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(:p1)' USING t(c);
      END LOOP;
      NULL;
      END;
      PROCEDURE test_t IS
      t table_t;
      BEGIN
      SELECT * BULK COLLECT INTO t FROM t1;
      consume(t);
      END;
      END;
      /
      CALL g1.test_t();
      SELECT * FROM t1;

       

      	 A B
      ---------- --------------------
      	 1 b
      	 2 bb
      

      Attachments

        Issue Links

          Activity

            Attached contains sample procedure using table of scalar data type.

            vamsimaddi Vamsi Krishna Maddi added a comment - Attached contains sample procedure using table of scalar data type.

            The attached file has been deleted and sent to the respective engineer via email.

            vamsimaddi Vamsi Krishna Maddi added a comment - The attached file has been deleted and sent to the respective engineer via email.

            Should probably be 10.3

            elenst Elena Stepanova added a comment - Should probably be 10.3

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              5 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.