decfloat转换成oracle种类型,Oracle到DB2的移植挑战和问题总结.doc

最新推荐文章于 2021-04-12 13:48:48 发布

意呆

最新推荐文章于 2021-04-12 13:48:48 发布

阅读量212

点赞数

文章标签： decfloat转换成oracle种类型

TO 17．DBMS_SQL.PARSE()函数里的SQL语句前面不能有空格。

18．range between后面不能加变量。

19．给结构体赋值不支持结构体(x,y,z)的函数。

修改方法：模拟实现该函数，比如

CREATE OR REPLACE FUNCTION TYP_ACCT (

Acct_No Varchar2(18),

CURRTYPE Varchar2(3),

DRA_F Varchar2(1)

)

RETURN PA_TYPES.TYP_ACCT

X PA_TYPES.TYP_ACCT;

BEGIN

X.Acct_No := Acct_No;

X.CURRTYPE := CURRTYPE;

X.DRA_F:= DRA_F;

RETURN X;

END;

20．decode里long类型的参数如果值过大不能和string进行比较，否则会有SQLN302的异常抛出

修改前：

PreparedStatement stmt = conn.prepareStatement("Select c1,c2 from test where 1 = 1 and c1 = decode(?, 0,1, ? ) ");

Object o = new Long(21149);

System.out.println(((Long)o).longValue());

stmt.setObject(1, o);

stmt.setObject(2, o);

ResultSet rs = stmt.executeQuery();

修改后：

PreparedStatement stmt = conn.prepareStatement("Select c1,c2 from test where 1 = 1 and c1 = decode(?, 0,1, ? ) ");

Object o = new Long(21149);

System.out.println(((Long)o).longValue());

stmt.setObject(1, o);

stmt.setObject(2, o);

ResultSet rs = stmt.executeQuery();

21．不能将oracle.sql.ROWID类型转成com.ibm.db2.jcc.DB2RowID类型

修改后：可以转成byte[]或者Object，然后toString()

22．join的连接条件不能使用子查询语句

修改前：

select * from test t1 left outer join test2 t2 on t1.c1=t2.c1 and t2.c2 in (select 11 from dual)

修改后：

select * from test t1 left outer join test2 t2 on t1.c1=t2.c1 where t2.c2 in (select 11 from dual)

23．不能将数据库里的Date类型转成java.sql.Date

修改前：

java.sql.Date d = (java.sql.Date)rs.getObject(2);

修改后：

java.util.Date d = (java. util.Date)rs.getObject(2);

或者

Timestamp d = (Timestamp)rs.getObject(2);

24．DB2不支持like后面跟函数

修改前：

SELECT * from test where c1 like NVL(?, c1)

修改后：

SELECT * from test where locate (NVL(?, c1), c1) <> 0

附录2：获取Oracle对象的依赖关系

1. get referenced procedures

select distinct(referenced_name) from user_dependencies where referenced_owner=LOAN_ADMIN and referenced_type=PROCEDURE and NAME <> REFERENCED_NAME

2. get referenced functions

select distinct(referenced_name) from user_dependencies where referenced_owner=LOAN_ADMIN and referenced_type=FUNCTION and NAME <> REFERENCED_NAME

3. get referenced packges from code

select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in

(

select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in

(

select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in (select pkgname from usedpackages) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG)

) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG)

union

select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in

(

) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG)

union

select pkgname from usedpackages

4. Level 1 Package