Details

    • Type: Technical task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: OTHER
    • Labels:

      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

              People

              • Assignee:
                Unassigned
                Reporter:
                bar Alexander Barkov
              • Votes:
                5 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated: