Oracle 12C PL/SQL新特性（上半部分）（7-15）

Oracle蓝莲花 2021-04-15

2513

- --Lerning Content :Oracle 12C PL/SQL新特性（上半部分）

- --Author :600团队

- --Description :PL/SQL Lanaguage Reference 关于Change in This Release For Oracle Database PLSQL

Language Reference有具体描述的特性清单

- --Remark :

-------------------------------------------------------------------------------------------------------

7.Extended Data Types在12c中的应用，在oracle 12c之前，某些数据类型在PL/SQL中有不同的阈值大小，比如

nvarchar2在sql中最大是4000字节，而在PL/SQL中是32767字节，从12c开始，varchar2,nvarchar2,raw数据类型在sql

和PL/SQL中最大都扩展到32767字节，如果要在sql中使用这些新特性阈值，需要设置max_string_size参数，必须为

extended方可使用新特性，以下为案例介绍：

7.1不要盲目在正常登录模式修改max_string_size参数，否则会报错ORA-14694 oracle官方给出如下错误提示

Database: 12c Release 1

Error code: ORA-14694

Description: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

Cause: An attempt was made to update the MAX_STRING_SIZE parameter to EXTENDED when the database was not in UPGRADE mode.

Action: Restart the database in UPGRADE mode, modify the parameter, run the utl32k.sql script in $ORACLE_HOME/rdbms/admin,

and restart the database in normal mode

7.2在Oracle 12c之前，无论使用什么字符语义，数据库中的VARCHAR2、NVARCHAR2和原始列的最大大小如下所示

VARCHAR2 : 4000 bytes

NVARCHAR2 : 4000 bytes

RAW : 2000 bytes

随着扩展数据类型的引入，Oracle 12c可选择性地增加这些最大大小

VARCHAR2 : 32767 bytes

NVARCHAR2 : 32767 bytes

RAW : 32767 bytes

注意：这些数字是以字节为单位的，而不是字符。可以存储的字符总数将取决于所使用的字符集

7.3模拟一下ORA-14694 ORA-14696报错信息，执行如下各种错误操作，帮助各位熟悉该参数如何修改，及其注意事项

SQL> conn as sysdba;

已连接。

SQL> show parameter max_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string STANDARD

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup upgrade;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> alter system set max_string_size=extended;

alter system set max_string_size=extended

第 1 行出现错误:

ORA-02097: 无法修改参数, 因为指定的值无效

ORA-02095: 无法修改指定的初始化参数

SQL> alter system set max_string_size=extended;

alter system set max_string_size=extended

第 1 行出现错误:

ORA-02097: 无法修改参数, 因为指定的值无效

ORA-02095: 无法修改指定的初始化参数

SQL> shutdown abort;

ORACLE 例程已经关闭。

SQL> startup;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter max_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string STANDARD

SQL> alter system set max_string_size=extended scope=spfile;

系统已更改。

SQL> start C:\app\wangrong\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

会话已更改。

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

第 1 行出现错误:

ORA-01722: 无效数字

从 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

C:\WINDOWS\system32>sqlplus nolog

SQL*Plus: Release 12.1.0.2.0 Production on 星期一 7月 23 19:10:36 2018

SQL> conn as sysdba;

已连接。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> staratup;

SP2-0042: 未知命令 "staratup" - 其余行忽略。

SQL> startup;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter max_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL> start C:\app\wangrong\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

会话已更改。

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

第 1 行出现错误:

ORA-01722: 无效数字

从 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

C:\WINDOWS\system32>sqlplus nolog

SQL*Plus: Release 12.1.0.2.0 Production on 星期一 7月 23 19:13:39 2018

SQL> conn as sysdba;

已连接。

SQL> alter pluggable database pdboracle open ;

alter pluggable database pdboracle open

第 1 行出现错误:

ORA-14694: 数据库必须处于 UPGRADE 模式下才能开始 MAX_STRING_SIZE 移植

SQL> conn as sysdba;

已连接。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup upgrade;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameter max_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL> @C:\app\wangrong\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

会话已更改。

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

未选定行

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database does not have compatible >= 12.0.0

DOC>

DOC> Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#

PL/SQL 过程已成功完成。

会话已更改。

已更新 0 行。

提交完成。

系统已更改。

PL/SQL 过程已成功完成。

提交完成。

系统已更改。

会话已更改。

PL/SQL 过程已成功完成。

没有错误。

会话已更改。

PL/SQL 过程已成功完成。

提交完成。

程序包已变更。

SQL> alter pluggable database all open upgrade;

插接式数据库已变更。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> alter pluggable database pdboracle open;

alter pluggable database pdboracle open

第 1 行出现错误:

ORA-14696: MAX_STRING_SIZE 移植对于可插入数据库 PDBORACLE 不完整

SQL> show user;

USER 为 "SYS"

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> set linesize 100;

SQL> spool G:\demo.txt

SQL> set serveroutput on size 999999;

SQL> declare

2 sql_stmt varchar2(100);

3 cursor c1 is select name, open_mode from v$pdbs where name ^=

4 'PDB$SEED' order by name;

5 begin

6 dbms_output.put_line('alter system set max_string_size=EXTENDED'||

7 ' scope=spfile;');

8 dbms_output.put_line('alter session set container = CDB$ROOT;');

9 dbms_output.put_line('shutdown immediate;');

10 dbms_output.put_line('startup upgrade;');

11 dbms_output.put_line('@?/rdbms/admin/utl32k.sql');

12 dbms_output.put_line('alter session set container = PDB$SEED;');

13 dbms_output.put_line('@?/rdbms/admin/utl32k.sql ;');

14 dbms_output.put_line('shutdown immediate;');

15 dbms_output.put_line('startup;');

17 for c1rec in c1 loop

18 dbms_output.put_line('alter session set container = '||c1rec.name|| ';');

19 dbms_output.put_line('alter pluggable database '||c1rec.name||

20 ' close immediate;');

21 dbms_output.put_line('alter pluggable database '||c1rec.name||

22 ' open upgrade;');

23 dbms_output.put_line('@?/rdbms/admin/utl32k.sql');

24 dbms_output.put_line('alter pluggable database '||c1rec.name||

25 ' close immediate;');

26 dbms_output.put_line('alter pluggable database '||c1rec.name||

27 ' open read write;');

29 end loop;

30 end;

31 /

alter system set max_string_size=EXTENDED scope=spfile;

alter session set container = CDB$ROOT;

shutdown immediate;

startup upgrade;

@?/rdbms/admin/utl32k.sql

alter session set container = PDB$SEED;

@?/rdbms/admin/utl32k.sql ;

shutdown immediate;

startup;

alter session set container = PDBORACLE;

alter pluggable database PDBORACLE close immediate;

alter pluggable database PDBORACLE open upgrade;

@?/rdbms/admin/utl32k.sql

alter pluggable database PDBORACLE close immediate;

alter pluggable database PDBORACLE open read write;

PL/SQL 过程已成功完成。

SQL> spool off;

SQL> show parameter max_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_string_size string EXTENDED

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup upgrade;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> @C:\app\wangrong\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

会话已更改。

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

未选定行

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database does not have compatible >= 12.0.0

DOC>

DOC> Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#

PL/SQL 过程已成功完成。

会话已更改。

已更新 0 行。

提交完成。

系统已更改。

PL/SQL 过程已成功完成。

提交完成。

系统已更改。

会话已更改。

PL/SQL 过程已成功完成。

没有错误。

会话已更改。

PL/SQL 过程已成功完成。

提交完成。

程序包已变更。

SQL> alter session set container=PDB$SEED;

会话已更改。

SQL> @C:\app\wangrong\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

会话已更改。

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

未选定行

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database does not have compatible >= 12.0.0

DOC>

DOC> Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#

PL/SQL 过程已成功完成。

会话已更改。

已更新 0 行。

提交完成。

系统已更改。

PL/SQL 过程已成功完成。

提交完成。

系统已更改。

会话已更改。

PL/SQL 过程已成功完成。

没有错误。

会话已更改。

PL/SQL 过程已成功完成。

提交完成。

程序包已变更。

SQL> shutdown immediate;

插接式数据库已关闭。

SQL> startup;

插接式数据库已打开。

SQL> alter session set container=pdboracle;

会话已更改。

SQL> alter gluggable database pdboracle close immediate;

alter gluggable database pdboracle close immediate

第 1 行出现错误:

ORA-00940: 无效的 ALTER 命令

SQL> alter pluggable database pdboracle close immediate;

alter pluggable database pdboracle close immediate

第 1 行出现错误:

ORA-65020: 可插入数据库 PDBORACLE 已关闭

SQL> alter pluggable database pdboracle open upgrade;

插接式数据库已变更。

SQL> @C:\app\wangrong\product\12.1.0\dbhome_1\RDBMS\ADMIN\utl32k.sql

会话已更改。

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database has not been opened for UPGRADE.

DOC>

DOC> Perform a "SHUTDOWN ABORT" and

DOC> restart using UPGRADE.

DOC>#######################################################################

DOC>#

未选定行

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database does not have compatible >= 12.0.0

DOC>

DOC> Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#

PL/SQL 过程已成功完成。

会话已更改。

已更新 53 行。

提交完成。

系统已更改。

PL/SQL 过程已成功完成。

提交完成。

系统已更改。

会话已更改。

PL/SQL 过程已成功完成。

没有错误。

会话已更改。

PL/SQL 过程已成功完成。

提交完成。

程序包已变更。

SQL> alter pluggable database pdboracle close immediate;

插接式数据库已变更。

SQL> alter pluggable database pdboracle open read write;

alter pluggable database pdboracle open read write

第 1 行出现错误:

ORA-65054: 无法在所需模式下打开可插入数据库。

SQL> show con_name;

CON_NAME

------------------------------

PDBORACLE

SQL> alter session set container=CDB$ROOT;

会话已更改。

SQL> alter pluggable database pdboracle open read write;

alter pluggable database pdboracle open read write

第 1 行出现错误:

ORA-65054: 无法在所需模式下打开可插入数据库。

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup;

ORACLE 例程已经启动。

Total System Global Area 3221225472 bytes

Fixed Size 3050800 bytes

Variable Size 1862271696 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13725696 bytes

数据库装载完毕。

数据库已经打开。

SQL> alter pluggable database pdboracle open read write;

插接式数据库已变更。

SQL>

注意，对应的demo.txt输出如下所示：

SQL> set serveroutput on size 999999;

SQL> declare

2 sql_stmt varchar2(100);

3 cursor c1 is select name, open_mode from v$pdbs where name ^=

4 'PDB$SEED' order by name;

5 begin

6 dbms_output.put_line('alter system set max_string_size=EXTENDED'||

7 ' scope=spfile;');

8 dbms_output.put_line('alter session set container = CDB$ROOT;');

9 dbms_output.put_line('shutdown immediate;');

10 dbms_output.put_line('startup upgrade;');

11 dbms_output.put_line('@?/rdbms/admin/utl32k.sql');

12 dbms_output.put_line('alter session set container = PDB$SEED;');

13 dbms_output.put_line('@?/rdbms/admin/utl32k.sql ;');

14 dbms_output.put_line('shutdown immediate;');

15 dbms_output.put_line('startup;');

17 for c1rec in c1 loop

18 dbms_output.put_line('alter session set container = '||c1rec.name|| ';');

19 dbms_output.put_line('alter pluggable database '||c1rec.name||

20 ' close immediate;');

21 dbms_output.put_line('alter pluggable database '||c1rec.name||

22 ' open upgrade;');

23 dbms_output.put_line('@?/rdbms/admin/utl32k.sql');

24 dbms_output.put_line('alter pluggable database '||c1rec.name||

25 ' close immediate;');

26 dbms_output.put_line('alter pluggable database '||c1rec.name||

27 ' open read write;');

29 end loop;

30 end;

31 /

alter system set max_string_size=EXTENDED scope=spfile;

alter session set container = CDB$ROOT;

shutdown immediate;

startup upgrade;

@?/rdbms/admin/utl32k.sql

alter session set container = PDB$SEED;

@?/rdbms/admin/utl32k.sql ;

shutdown immediate;

startup;

alter session set container = PDBORACLE;

alter pluggable database PDBORACLE close immediate;

alter pluggable database PDBORACLE open upgrade;

@?/rdbms/admin/utl32k.sql

alter pluggable database PDBORACLE close immediate;

alter pluggable database PDBORACLE open read write;

PL/SQL 过程已成功完成。

SQL> spool off;

7.4扩展以后我们来做如下验证：

CREATE TABLE T_600_EXTENDED(

COL1 NUMBER,

COL2 VARCHAR2(32767),

COL3 NVARCHAR2(10000),

COL4 RAW(32767)

)

SEGMENT CREATION IMMEDIATE

;

SELECT SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024||'K' FROM USER_SEGMENTS

WHERE SEGMENT_NAME = Q'{T_600_EXTENDED}'

SEGMENT_NAME SEGMENT_TYPE BYTES/1024||'K'

1 T_600_EXTENDED TABLE 64K

ALTER TABLE T_600_EXTENDED MODIFY COL3 NVARCHAR2(16383);

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(T_600_EXTENDED,4) */ INTO T_600_EXTENDED

SELECT 1,

RPAD('X', 32767, 'X') AS VARCHAR2_DATA,

RPAD('X', 16383, 'X') AS NVARCHAR2_DATA,

UTL_RAW.CAST_TO_RAW(RPAD('X', 32767, 'X')) AS RAW_DATA

FROM DUAL;

ALTER SESSION DISABLE PARALLEL DML;

SELECT COL1,

LENGTH(COL2),

LENGTH(COL3),

LENGTH(COL4),

DUMP(COL2),

DUMP(COL3),

DUMP(COL4)

FROM T_600_EXTENDED;

, COL1, LENGTH(COL2), LENGTH(COL3), LENGTH(COL4), DUMP(COL2), DUMP(COL3), DUMP(COL4)

......

结果就不展示了

-------------------------------------------------------------------------------------------------------

12C PL/SQL新特性下半部分明日在分享，请继续关注

-------------------------------------------------------------------------------------------------------

oracle

文章转载自Oracle蓝莲花，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 12C PL/SQL新特性（上半部分）（7-15）

评论