Oracle中“类型表"中varchar2的限制是什么? [英] What is the limit for varchar2 in a Table of Type in Oracle?

问题描述

就oracle中的类型表而言，我正在寻找varchar2类型的限制.请注意，我不是在谈论物理表(varchar2的限制是4000)，而是一种类型的表.

I am looking for the limit of the varchar2 type as far as a Table of type is concerned in oracle. Note that I am not talking about a physical table (for which the limit for varchar2 is 4000) but the table of a type.

谢谢

推荐答案

这取决于上下文.可以在任何地方创建 VARCHAR2(32767)表.但是32767只能在PL/SQL中使用. SQL中的限制仍然是4000.

It depends on the context. A table of VARCHAR2(32767) can be created anywhere. But 32767 can only be used in PL/SQL. The limit in SQL is still 4000.

SQL> --#1: SQL, 4000.
SQL> create or replace type varchar2_limit is table of varchar2(4000);
  2  /

Type created.

SQL> create table varchar2_limit_table1
  2  (
  3     a varchar2_limit
  4  ) nested table a store as my_nt;

Table created.

SQL> --#2: SQL, 4001.
SQL> drop table varchar2_limit_table1;

Table dropped.

SQL> create or replace type varchar2_limit is table of varchar2(4001);
  2  /

Type created.

SQL> create table varchar2_limit_table2
  2  (
  3     a varchar2_limit
  4  ) nested table a store as my_nt;
create table varchar2_limit_table2
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column A
ORA-00910: specified length too long for its datatype


SQL> --#3: PL/SQL, 32767.
SQL> declare
  2     type varchar2_limit is table of varchar2(32767);
  3  begin
  4     null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> --#4: PL/SQL, 32768
SQL> declare
  2     type varchar2_limit is table of varchar2(32768);
  3  begin
  4     null;
  5  end;
  6  /
        type varchar2_limit is table of varchar2(32768);
                                                 *
ERROR at line 2:
ORA-06550: line 2, column 43:
PLS-00215: String length constraints must be in range (1 .. 32767)

这篇关于Oracle中“类型表"中varchar2的限制是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！