开始还以为是execute IMMEDIATE 这个不能执行太长字符串的问题。

后来才意识到是在拼成的SQL字符串调用P70201020 的一个参数太长，而存储过程P70201020 的那个参数类型是VARCHAR2.

-- Created on 2013-3-29 by ADMINISTRATOR

declare

-- Local variables here

i integer;

l_strsql LONG;

l_dummy dual.dummy%type;

l_strsql2 varchar2(32765);

l_str long;

l_clob LONG;

l_dummy2 VARCHAR2(500);

v_rt_cursor types.cursorType;

v_o_return_code INTEGER;

v_o_return_msg VARCHAR2(2000);

v_or_outparam INTEGER DEFAULT 2;

i_count INTEGER DEFAULT 0;

begin

DELETE FROM bblu_test_t;

l_str := rpad(' ', 32000, ' ') || 'select ''Hello World'' from dual';

dbms_output.put_line('l_dummy2 ===' || l_dummy2);

l_str := ' call ';

l_str := l_str || 'P70201020' || '(:param1,';

FOR rec IN (SELECT t.value, t.in_out

FROM kmms.tb_his_query_input_detail t

WHERE t.query_id = 135

ORDER BY t.input_seq) LOOP

IF rec.in_out = 0 THEN

l_str := l_str || '''' ||

dbms_lob.substr(rec.value, length(rec.value), 1) || ''',';

i_count := i_count + 1;

ELSIF rec.in_out = 1 THEN

l_str := l_str || ':param' || to_char(v_or_outparam) || ',';

v_or_outparam := v_or_outparam + 1;

i_count := i_count + 1;

END IF;

END LOOP;

l_str := substr(l_str, 0, length(l_str) - 1);

l_str := l_str || ')';

insert into bblu_test_t values (l_str);

COMMIT;

select NAME into l_clob from bblu_test_t;

dbms_output.put_line('length of the long variable type ==' ||

length(l_clob));

execute IMMEDIATE l_clob ---ORA-01704: string literal too long ORA-06512: at line 54

USING OUT v_rt_cursor, out v_o_return_code, OUT v_o_return_msg;

end;

ITPub博客

在测试PLSQL值得吸取的教训

最新文章