大家好,又见面了,我是你们的朋友全栈君。
oracle迁移到sqlserver时,报错-如下图,
查找原因,发现是因为有些表number类型没有设精度导致的,解决方法如下,修改表结构加上精度
,加上之后就可以了。考虑到有些表有多个字段没有设精度,所以采取以下方式实现。
–split函数
create or replace type type_split as table of varchar2(4000);
/
create or replace function test_split(p_list VARCHAR2, p_sep VARCHAR2) return type_split
PIPELINED IS
l_idx PLS_INTEGER;
v_list VARCHAR2(32767) := p_list;
begin
LOOP
l_idx := instr(v_list, p_sep);
IF l_idx > 0 THEN
PIPE ROW(substr(v_list, 1, l_idx – 1));
v_list := substr(v_list, l_idx + length(p_sep));
ELSE
PIPE ROW(v_list);
EXIT;
END IF;
END LOOP;
RETURN;
end test_split;
/
–修改表加精度的存储过程
create or replace procedure changefield
(tablename in varchar2,
fieldname in varchar2
) as
CURSOR c1
IS
SELECT * FROM table(test_split(fieldname,’,’));
begin
execute immediate ‘create table temp_table as select * from ‘|| tablename;
execute immediate ‘DELETE FROM ‘|| tablename;
FOR x IN c1
LOOP
execute immediate ‘alter table ‘ || tablename || ‘ modify ‘ || x.column_value || ‘ NUMBER(38, 0)’;
END LOOP;
execute immediate ‘INSERT INTO ‘|| tablename||’ SELECT * FROM temp_table’;
execute immediate ‘DROP TABLE temp_table’;
end;
/
–执行例子需要手工改 参数为表名和字段名 字段名可以一个可以多个,多个字段以逗号分隔
exec changefield(‘A25′,’A2517,SORT’);
exec changefield(‘A36′,’A3617’);
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/163898.html原文链接:https://javaforall.cn