sql_mode=ORACLE: TYPE .. TABLE OF for scalar data types

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