How to Use the dump function in Oracle Data Types

Source: Internet
Author: User

The following articles mainly analyze Oralce data types through examples. Based on Oracle 10 Gb, this article introduces the new data types introduced by Oracle 10 Gb. To introduce the actual operations related to Oracle Data Types and reveal the characteristics of Oracle data types.

Character Type

1.1 char

A fixed-length string is filled with spaces to reach its maximum length, up to 2000 bytes.

1. Create a test table test_char. With only one char column. Length: 10

SQL> create table test_char (colA char (10 ));

Table created

2. Insert some data into the table.

SQL> insert into test_char values ('A ');

1 row inserted

SQL> insert into test_char values ('A ');

1 row inserted

SQL> insert into test_char values ('aaa ');

1 row inserted

SQL> insert into test_char values ('aaa ');

1 row inserted

SQL> insert into test_char values ('aaaaaaaaaaa ');

1 row inserted

Note: A maximum of 10 bytes can be inserted. If no, an error is returned.

SQL> insert into test_char values ('aaaaaaaaaaa ');

Insert into test_char values ('aaaaaaaaaaa ')

ORA-12899: value too large for column "PUB_TEST". "TEST_CHAR". "COLA" (actual: 11, maximum: 10)

3. Use the dump function in Oracle data types to view the internal storage structure of each row.

SQL> select colA, dump (colA) from test_char;

Cola dump (COLA)

A Typ = 96 Len = 10: 97,32, 32,32, 32,32, 32,32, 32,32

Aa Typ = 96 Len = 10: 97,97, 32, 32, 32, 32, 32, 32

Aaa Typ = 96 Len = 10: 97,97, 97,32, 32, 32, 32, 32

Aaaa Typ = 96 Len = 10: 97,97, 97,97, 32, 32, 32, 32

Aaaaaaaaaa Typ = 96 Len = 10: 97,97, 97,97, 97,97, 97,97, 97,97

Note: Typ = 96 indicates the Data Type ID. Oracle numbers each data type. It indicates that the number of the char type is 96.

Len = 10 indicates the length of the internal storage (in bytes ). Although the first example only contains one character 'a', it still occupies 10 bytes of space.

97,32, 32,32, 32,32, 32,32, 32,32 indicates the internal storage mode. It can be seen that the internal storage of Oracle is stored in the database character set.

97 is the ASCII code of Character.

You can use the chr function to convert ASCII code into characters.

SQL> select chr (97) from dual;

CHR (97)

A

To know the ASCII code of a character, you can use the ascii function.

SQL> select ascii ('A') from dual;

ASCII ('A ')

97

32 is the ascii value of space.

Char is a fixed-length type. It is always filled with spaces to reach a fixed width.

Using the char type will waste storage space.

The length unit of Oracle data type is byte.

SQL> select dump ('hang') from dual;

DUMP ('han ')

Typ = 96 lename = 2: 186,186

It can be seen that a Chinese character occupies two bytes in Oracle.

English letters or symbols only occupy one byte.

Char (10) can store up to 5 Chinese characters.

The above content is an introduction to Oracle Data Types. I hope you will get some benefits.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.