Integer Series Generators

Setup

Run the code on this page in SQL*Plus to create the sample tables, data, etc. used by the examples in this section.

Be sure to read Using SQL Snippets ™ before executing any of these setup steps.

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
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11816.html]SQL Snippets: Integer Series Generators - Setup[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11816.html">SQL Snippets: Integer Series Generators - Setup</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Integer Series Generators - Setup
  • URL (href): http://www.sqlsnippets.com/en/topic-11816.html