create table t ( day_of_week integer , val number ); insert into t values ( 1, 100 ); insert into t values ( 3, 300 ); insert into t values ( 4, 400 ); insert into t values ( 5, 500 ); commit; create table t3 ( key varchar2(3) , qty integer ); insert into t3 values ( 'a', null ); insert into t3 values ( 'b', 0 ); insert into t3 values ( 'c', 1 ); insert into t3 values ( 'd', 2 ); insert into t3 values ( 'e', 3 ); commit; create table t4 ( key varchar2(3) ); insert into t4 values ( 'a' ); insert into t4 values ( 'b' ); commit; create table days_of_the_week ( day_of_week integer , name varchar2(10) ); insert into days_of_the_week values ( 0, 'Sunday' ); insert into days_of_the_week values ( 1, 'Monday' ); insert into days_of_the_week values ( 2, 'Tuesday' ); insert into days_of_the_week values ( 3, 'Wednesday' ); insert into days_of_the_week values ( 4, 'Thursday' ); insert into days_of_the_week values ( 5, 'Friday' ); insert into days_of_the_week values ( 6, 'Saturday' ); commit; create table t2 ( c1 varchar2(6) , c2 varchar2(6) , c3 number ); insert into t2 values ( 'x', 'y', 42 ); commit; create type integer_table_type as table of integer ; -- see Note 1 /
Notes
1. If you do not have privileges to create a custom type like INTEGER_TABLE_TYPE you can use any pre-existing nested table or varray type with a numeric data type big enough to hold the integers you wish to generate. A query like the following will help you find one.
select type_name, case coll_type when 'VARYING ARRAY' then 'VARRAY(' || upper_bound || ')' when 'TABLE' then 'NESTED TABLE' else '?' end as collection_type, elem_type_name, precision, scale from all_coll_types where elem_type_name in ( 'NUMBER', 'INTEGER' ) and rownum <= 10 ;
TYPE_NAME COLLECTION_TYPE ELEM_TY PRECISION SCALE ------------------------------ -------------------- ------- ---------- ---------- SCHEDULER$_INT_ARRAY_TYPE VARRAY(1000) INTEGER INTEGER_TABLE_TYPE NESTED TABLE INTEGER ODCINUMBERLIST VARRAY(32767) NUMBER ODCIGRANULELIST VARRAY(65535) NUMBER AWRRPT_NUM_ARY VARRAY(21) NUMBER SQL_OBJECTS VARRAY(2000) NUMBER bind-bucket-lengths143_COLL VARRAY(2147483647) NUMBER 10 0 SDO_ORDINATE_ARRAY VARRAY(1048576) NUMBER SDO_ELEM_INFO_ARRAY VARRAY(1048576) NUMBER SDO_VPOINT_TYPE VARRAY(64) NUMBER