Oracle SQL
  • LICENSE

Differences between integer(int) in SQL and PL/SQL

Posted on November 10, 2012 by Sayan Malakshinov Posted in oracle, undocumented

Very simple example:

create table t_integer(i integer);
insert into t_integer values(1e125);
select * from t_integer;

declare
  i1 integer;
begin 
  select i into i1 from t_integer;
end;
/
declare
  i2 t_integer.i%type;
begin 
  select i into i2 from t_integer;
end;
/

Although variable and the column specified as “integer”, but in both blocks you will get error: ORA-06502: PL/SQL: numeric or value error: number precision too large.
Same error will be with “int“.
Describe shows wrong datatype:

> desc t_integer;
 Name                        Null?    Type
 --------------------------- -------- ----------
 I                                    NUMBER(38)

Really it would be number without precision and scale=0. You can see it in dba_tab_columns.
sys.standard:

  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;

Also a couple simple good-known but often forgotten things:
1. integer as parameter type or return type:

SQL> create function f_integer(i integer)
  2    return integer is
  3  begin
  4    return i;
  5  end;
  6  /

Function created.

SQL> select f_integer(1/3) from dual;

F_INTEGER(1/3)
--------------
    .333333333

1 row selected.

2. Old finding from sql.ru – no casting in assignments (from 10.2 till 11.2, was found by Elic):

declare
  numberVar  number        := 1/3;
  numberVar2 number(20,10) := 1/3;
  procedure Test(Label varchar2, Value int)
  is
    LocalVar int := Value;
  begin
    dbms_output.put_line(Label || '         : ' || Value);
    dbms_output.put_line(Label || ' assigned: ' || LocalVar);
  end Test;
begin
  Test('|                 const', 1/3);
  Test('|            number var', numberVar);
  Test('|constrained number var', numberVar2);
end;
/
|                 const         : .3333333333333333333333333333333333333333
|                 const assigned: .3333333333333333333333333333333333333333
|            number var         : .3333333333333333333333333333333333333333
|            number var assigned: .3333333333333333333333333333333333333333
|constrained number var         : .3333333333
|constrained number var assigned: .3333333333

PL/SQL procedure successfully completed.
undocumented oracle
« The change in “dump sort statistics” trace (event 10032)
Just another version of Tom Kyte’s runstats (runstats_pkg) »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL