1. 前言

oracle有很多特性在lightdb无法使用，使用该工具可以即时扫描某个在线数据库或本地文件夹中有哪些不兼容特性。 LightDB-A和LightDB-X对Oracle不兼容特性有所区别，扫描范围可参考后续表格。

2. 使用说明

2.1. 使用限制

  • 默认扫描存储过程，函数及包

  • 默认仅支持oracle数据库扫描

  • 默认排除系统用户’SYS’, ‘SYSTEM’, ‘SYSMAN’

2.2. 环境配置

  • 用户需要下载安装oracle客户端：instantClient-basic-Windows.x64-11.2.0.4.0.zip

  • 路径加入path环境变量中，如‘D:工具instantclient-basic-windows.x64-11.2.0.4.0instantclient_11_2’

2.3. 配置Oracle连接字符串

  • 检查并配置./conf/DBconfig.ini文件，必须有 ‘oracle’ 节点，且有 ‘connectstring’ 键值对

  • 配置中connectstring是连接字符串，格式为<用户名>/<密码>@<IP>:<端口>/<实例名>，例如：test/test123@127.0.0.1:1521/ORCL

  • OWNER字段为需要扫描的用户。可选，为空时扫描所有用户；大小写不敏感；用户不存在时会跳过，多用户之间用逗号或者空格分割

image-2023110100001

2.4. 开始扫描

配置好环境和连接信息后，找到工具包中CheckUnsupportOracle.exe右键以管理员身份运行，启动扫描。

注意：执行程序所在路径不能有中文

2.4.1. 选择兼容类型

image-2023110100002

  • 1-PL/oraSQL -X ：迁移到LightDB-X时选择

  • 2-PL/oraSQL -A ：迁移到LightDB-A时选择

输入1或2，回车进入下一步

2.4.2. 选择扫描源

image-2023110100003

  • 1-连接Oracle在线扫描 ：连接DBconfig.ini中配置的Oracle数据库，获取用户的所有存储过程，函数及包进行扫描

  • 2-本地文件 ：扫描放在./files文件夹下的所有文件，如果没有files文件夹可自行新建

建议输入1使用在线方式，回车开始扫描。（目前本地文件是逐行代码进行扫描的，需要进一步优化代码提取逻辑，暂不推荐使用）

2.5. 扫描报告

扫描结束后，会生成格式如“report_日期_时间”的文件夹，如下图：

image-2023110100004

文件夹内包含两类文件：

  • 扫描结果Excel，命名格式为Report_扫描类型_日期_时间.xlsx

  • 单个用户的log日志，内容与excel大致相同，最下方统计了各类不兼容项的数量

image-2023110100005

Excel报告如下，包含列：

  • 函数/过程/包名

  • 行号

  • 检测语句：原SQL语句

  • 不兼容问题：根据报错信息辨别不兼容信息

image-2023110100006

3. 检测项清单

本节列出了扫描检测项清单，随着LightD支持更多的特性，兼容的特性会被标记为“已支持”且不再扫描该项。

plsql2plorasql检测项清单

序号

检测项

lightdb-x

lightdb-a

报错信息

备注

1

scandeleteTable

×

×

==>Syntax check: perform an delete operation on a table,try ‘delete from’ for instead.

已支持

2

scanoperator

√

√

==>Syntax check: ~= or ^= not support

3

scantrunc

√

√

==>Syntax check: The return result of trunc() may be different Between oracle and lightdb!

4

scanNEXT

√

√

==>Syntax check: NEXT() not support

5

scanByPLS_INTEGER

×

×

==>Syntax check: type PLS_INTEGER not support

已支持

6

scandelete

√

√

==>Syntax check: delete() may not support

7

scanexists

√

√

==>Syntax check: exists() not support

8

scancreatetypeisrecord

√

√

==>Syntax check: create type * is record not support

9

scanEXCEPTION

√

√

==>Syntax check: type ‘exception’ not support

10

scanEXCEPTION_INIT

√

√

==>Syntax check: EXCEPTION_INIT() not support

11

scanPRAGMA

×

×

==>Syntax check: PRAGMA not support

已支持

12

scanNOCOPY

√

√

==>Syntax check: NOCOPY not support

13

scanAUTHID

√

√

==>Syntax check: AUTHID not support

14

scanconnectby

×

×

==>Syntax check: ‘connect by’ not support

已支持

15

scanVARRAY

√

√

==>Syntax check: VARRAY not support

16

scantable

×

×

==>Syntax check: table() not support

已支持

17

scanFunparam

√

√

==>Syntax check: String type not support

18

scanDeterministic

√

√

==>Syntax check: Deterministic not support

19

scanescape

√

√

==>Syntax check: q’ not support

20

scaneRelationOperation

×

×

==>Syntax check: > = or < = not support

已支持

21

scaneTYPEistableof

√

√

==>Syntax check: TYPE * is table of * not support

22

scangoto

×

×

==>Syntax check: goto not support

已支持

23

scanlablename

×

×

==>Syntax check: <<lable_name>> not support

已支持

24

scankeep

√

√

==>Syntax check: keep() not support

25

scanunpivot

√

√

==>Syntax check: unpivot not support

26

scanDBLink

×

×

==>Syntax check: DBLink not support

已支持

27

scangetclobval

√

√

==>Syntax check: getclobval() not support

28

scanxmlparse

×

×

==>Syntax check: xmlparse() not support

已支持

29

scant_table

√

√

==>Syntax check: t_table() not support

30

scangather_table_stats

√

√

==>Syntax check: gather_table_stats() not support

31

scanUTL_SMTP

√

√

==>Syntax check: UTL_SMTP not support

32

scanUTL_RAW

×

×

==>Syntax check: UTL_RAW not support

已支持

33

scanUTL_ENCODE

√

√

==>Syntax check: UTL_ENCODE not support

34

scanprompt

×

×

==>Syntax check: prompt not support

35

scansynonym

√

√

==>Syntax check: create|drop synonym not support

36

scanconstant

×

×

==>Syntax check: constant not support

已支持

37

scanNOCACHE

√

√

==>Syntax check: NOCACHE not support

38

scanuser_tab_columns

×

×

==>Syntax check: user_tab_columns not support

已支持

39

scancol_relation

×

×

==>Syntax check: relation col not support

已支持

40

scantableandconstraint

√

√

==>Syntax check: The same table name and constraint name are not support

41

scantableusingindex

√

√

==>Syntax check: alter table using index not support

42

scancharType

√

√

==>Syntax check: char type not support

43

scantableanddropindex

√

√

==>Syntax check: alter table drop index not support

44

scanspoolc

√

√

==>Syntax check: spool c not support

45

scanspooloff

√

√

==>Syntax check: spool off not support

46

scanexecuteimmediatecommit

√

√

==>Syntax check: execute immediate commit not support

47

scanwrapped

√

√

==>Syntax check: wrapped not support

48

scaneisarrayof

√

√

==>Syntax check: is array of * not support

49

scaneforceview

√

√

==>Syntax check: FORCE VIEW not support

50

scanistableof

只在包中检测

√

==>Syntax check: Nested table not support

51

scandbms_scheduler

×

√

==>Syntax check: dbms_scheduler package not support

52

scanmergeinto

×

√

==>Syntax check: merge into not support

53

scanINSERTALL

×

√

==>Syntax check: INSERT ALL not support

54

scanRAISE

×

√

==>Syntax check: RAISE error not support

55

scandbms_registry_sys

×

√

==>Syntax check: dbms_registry_sys package not support

56

scandbms_registry

×

√

==>Syntax check: dbms_registry package not support

57

scanexternalconnection

×

√

==>Syntax check: (+) external connection not support

58

scanrowid

×

√

==>Syntax check: rowid not support

59

scanrownum

×

√

==>Syntax check: rownum not support

60

scanupdatesetmulticol

×

×

==>Syntax check: UPDATE SET multiple columns, need parentheses

已支持

61

scandoendwhile

√

√

==>Syntax check: DO END WHILE not support

62

scanexecuteinout

×

×

==>Syntax check: EXECUTE IN OUT not support

已支持

63

scanelse

√

√

==>Syntax check: ELSE not support

64

scancolumnvalue

√

√

==>Syntax check: ‘column_value’ not support

65

scancommenton

√

√

==>Syntax check: ‘comment on’ not support, try alter table

66

scanselectasinto

√

√

==>Syntax check: ‘SELECT … AS INTO’ not support, try ‘SELECT … INTO’

67

scangreaterthanorequal

√

√

==>Syntax check: No spaces allowed between >= or <=

68

scancncomma

√

√

==>Syntax check: chinese comma not support

69

scanunpairedcommentsI

√

√

==>Syntax check: Code comments /* */ must be paired

70

scanunpairedcommentsII

√

√

==>Syntax check: Code comments /* */ must be paired

71

scansystimestamp

√

√

==>Syntax check: function systimestamp do not support precision

72

scanemptylob

√

√

==>Syntax check: empty_clob() empty_blob() not support

73

scaninteger

√

√

==>Syntax check: int/integer(n) do not support, only int/interger

74

scanexecuteimmediateusing

√

√

==>Syntax check: execute immediate using not support

75

scansys_refcursor

√

√

==>Syntax check: sys_refcursor do not support as param, try refcursor instead

76

scanto_date

√

√

==>Syntax check: two params in to_date not match

77

scanxmlagg

√

√

==>Syntax check: xmlagg() not support

78

scanrowidtochar

√

√

==>Syntax check: ROWIDTOCHAR() not support

79

scanforupdateof

√

√

==>Syntax check: for update of coulumn_name not support

80

scanaes_decrypt

√

√

==>Syntax check: AES_DECRYPT() AES_ENCRYPT() not support