有个问题想请教大家,我需要在rpt_chgdata_custom表里面的sql_define(varchar)字段定义一段语句,并确保能select出两个结果值赋给变量object_type和sql_result,不知道下面语句为什么在sqlplus中第一次能运行,第二次会报ora-03113通信通道的文件结束的错误(虽然网上有人讨论,终究看不出所以然),各位有兴趣能帮我看看吗,谢谢set serveroutput on size 1000000;
declare
object_type varchar(3);
sql_def varchar(200);
col_name varchar(200);
sql_exec varchar(200);
sql_result number(5);
sql_tmp varchar(200);--sql_tmp是我测试才定义的,本来应该可以一直用sql_exec,但在这里如果把sql_tmp换成sql_exec,
--同样会报ora-03113的错误begin
select sql_define,column_name into sql_def,col_name from rpt_chgdata_custom;
dbms_output.put_line(sql_def);
dbms_output.put_line(col_name);
execute immediate sql_def into object_type,sql_result;
dbms_output.put_line(object_type);
dbms_output.put_line(sql_result);
sql_exec :='update rpt_general_data set ';
sql_tmp :=sql_exec||col_name;
--以上语法没什么问题
--本意是在rpt_general_data表里update修改col_name表的某个字段A001(number)
--sql_tmp:=sql_tmp||'='||col_name||to_char(sql_result)也会报
--ora-03113的错误
--如果解决过类似问题,希望能告诉我怎么直接把col_name拼接上去
sql_tmp:=sql_tmp||'=';
--下面这句sql_exec是我测试用的,可以运行,不用看的,(因为我无法把number类型的变量加入
--到该字符串变量里面,让oracle来帮我转换);
sql_exec:='update rpt_general_data set A001=83'
dbms_output.put_line(sql_tmp);
execute immediate sql_exec;
commit;
end;
/
declare
object_type varchar(3);
sql_def varchar(200);
col_name varchar(200);
sql_exec varchar(200);
sql_result number(5);
sql_tmp varchar(200);--sql_tmp是我测试才定义的,本来应该可以一直用sql_exec,但在这里如果把sql_tmp换成sql_exec,
--同样会报ora-03113的错误begin
select sql_define,column_name into sql_def,col_name from rpt_chgdata_custom;
dbms_output.put_line(sql_def);
dbms_output.put_line(col_name);
execute immediate sql_def into object_type,sql_result;
dbms_output.put_line(object_type);
dbms_output.put_line(sql_result);
sql_exec :='update rpt_general_data set ';
sql_tmp :=sql_exec||col_name;
--以上语法没什么问题
--本意是在rpt_general_data表里update修改col_name表的某个字段A001(number)
--sql_tmp:=sql_tmp||'='||col_name||to_char(sql_result)也会报
--ora-03113的错误
--如果解决过类似问题,希望能告诉我怎么直接把col_name拼接上去
sql_tmp:=sql_tmp||'=';
--下面这句sql_exec是我测试用的,可以运行,不用看的,(因为我无法把number类型的变量加入
--到该字符串变量里面,让oracle来帮我转换);
sql_exec:='update rpt_general_data set A001=83'
dbms_output.put_line(sql_tmp);
execute immediate sql_exec;
commit;
end;
/
create table rpt_chgdata_custom
(
sql_define varchar(200),
column_name varchar(20)
)create table rpt_general_data
(
A001 number(5)
)insert into rpt_chgdata_custom values('select ABCD,1 from dual','A001');
insert into rpt_general_data values(83);
commit;
insert into rpt_chgdata_custom values('select ABCD,1 from dual','A001');
应该是insert into rpt_chgdata_custom values('select ''ABCD'',1 from dual','A001');
因为我这里没相应环境,就随便给个字符串'ABCD'
2 object_type varchar(5);
3 sql_def varchar(200);
4 col_name varchar(200);
5 sql_exec varchar(200);
6 sql_result number(5);
7 sql_tmp varchar(200);
8 begin
9 select sql_define,column_name into sql_def,col_name from rpt_chgdata_custom;
10 dbms_output.put_line(sql_def);
11 dbms_output.put_line(col_name);
12 execute immediate sql_def into object_type,sql_result;
13 dbms_output.put_line(object_type);
14 dbms_output.put_line(sql_result);
15 sql_exec :='update rpt_general_data set ';
16 sql_exec :=sql_exec||col_name;
17 sql_exec:=sql_exec||'=';
18 sql_exec:=sql_exec || '83';
19 dbms_output.put_line(sql_tmp);
20 execute immediate sql_exec;
21 commit;
22 end;
23 /
select 'ABCD',1 from dual
A001
ABCD
1PL/SQL procedure successfully completed.SQL> select * from rpt_general_data
2 / A001
---------
83SQL> declare
2 object_type varchar(5);
3 sql_def varchar(200);
4 col_name varchar(200);
5 sql_exec varchar(200);
6 sql_result number(5);
7 sql_tmp varchar(200);
8 begin
9 select sql_define,column_name into sql_def,col_name from rpt_chgdata_custom;
10 dbms_output.put_line(sql_def);
11 dbms_output.put_line(col_name);
12 execute immediate sql_def into object_type,sql_result;
13 dbms_output.put_line(object_type);
14 dbms_output.put_line(sql_result);
15 sql_exec :='update rpt_general_data set ';
16 sql_exec :=sql_exec||col_name;
17 sql_exec:=sql_exec||'=';
18 sql_exec:=sql_exec || '85';
19 dbms_output.put_line(sql_tmp);
20 execute immediate sql_exec;
21 commit;
22 end;
23 /
select 'ABCD',1 from dual
A001
ABCD
1PL/SQL procedure successfully completed.SQL> select * from rpt_general_data
2 / A001
---------
85SQL> declare
2 object_type varchar(5);
3 sql_def varchar(200);
4 col_name varchar(200);
5 sql_exec varchar(200);
6 sql_result number(5);
7 sql_tmp varchar(200);
8 begin
9 select sql_define,column_name into sql_def,col_name from rpt_chgdata_custom;
10 dbms_output.put_line(sql_def);
11 dbms_output.put_line(col_name);
12 execute immediate sql_def into object_type,sql_result;
13 dbms_output.put_line(object_type);
14 dbms_output.put_line(sql_result);
15 sql_exec :='update rpt_general_data set ';
16 sql_exec :=sql_exec||col_name;
17 sql_exec:=sql_exec||'=';
18 sql_exec:=sql_exec || '100';
19 dbms_output.put_line(sql_tmp);
20 execute immediate sql_exec;
21 commit;
22 end;
23 /
select 'ABCD',1 from dual
A001
ABCD
1PL/SQL procedure successfully completed.SQL> select * from rpt_general_data
2 / A001
---------
100SQL>
object_type varchar(5);
sql_def varchar(200);
col_name varchar(200);
sql_exec varchar(200);
sql_result number(5);
sql_tmp varchar(200);
begin
select sql_define,column_name into sql_def,col_name from rpt_chgdata_custom;
dbms_output.put_line(sql_def);
dbms_output.put_line(col_name);
execute immediate sql_def into object_type,sql_result;
dbms_output.put_line(object_type);
dbms_output.put_line(sql_result);
sql_exec :='update rpt_general_data set ';
sql_exec :=sql_exec||col_name;
sql_exec:=sql_exec||'=';
sql_exec:=sql_exec || '100';
dbms_output.put_line(sql_tmp);
execute immediate sql_exec;
commit;
end;
/
太小了,改成
object_type varchar(5);
或者更大点。