Oracle-如何处理变量中的32K +字符串长度 [英] Oracle - How to handle 32K+ string length in variables

问题描述

我正在使用oracle 11g.每当遇到大于varchar2大小限制的字符串时，在sql server中，我都会按照以下方式将数据拆分为多个变量，然后在执行时将它们联接.但是，Oracle似乎期望在执行之前合并大小为32K.我收到"ORA-20000:ORU-10028:行长溢出，每行限制32767字节"的错误.

I am using oracle 11g. Whenever I encountered strings larger than varchar2 size limit, In sql server I use to split the data into multiple variables as below and then join them while execution. However Oracle seems to be expecting 32K combined size before execution. I am getting "ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line" error.

我在oralce脚本(不存储过程)中使用这些变量.最后2条语句抛出上述错误，我可以单独显示该值. 预先感谢.

I am using these variables in an oralce script (not stored procs). Last 2 statements are throwing the above error and individually I am able to show the value. Thanks in advance.

DECLARE

sViewQuery varchar2(32000);
sViewSelectQuery varchar2(32000);

BEGIN

    ---Assign values of 32,000 letter string (dynamic query)
    sViewSelectQuery:='32K string...';
    sViewQuery:='32K string..';

    DBMS_OUTPUT.PUT_LINE(sViewQuery||sViewSelectQuery);

    EXECUTE IMMEDIATE sViewQuery||sViewSelectQuery;

END;

推荐答案

您可以为此使用DBMS_SQL程序包:

You can use DBMS_SQL Package for this:

DECLARE
stmt DBMS_SQL.VARCHAR2A;
c number;
res number;
BEGIN
stmt(1) := 'create view view_a (';
stmt(2) := 'col_a, ';
stmt(3) := 'col_b, ';
stmt(4) := 'col_c) as '
stmt(5) := 'select ';
stmt(6) := 'col_bb, ';
stmt(7) := 'col_cc + col_ee + DECODE(...), ';
stmt(8) := 'col_dd) ';
stmt(9) := 'from table_b ';
stmt(10) := 'where ... '; 
-- each element can have up to 32K characters, number of elements is (almost) unlimited
c := DBMS_SQL.open_cursor;
DBMS_SQL.parse(c, stmt, 1,10, TRUE, DBMS_SQL.NATIVE);
res := DBMS_SQL.execute(c);
DBMS_SQL.close_cursor(c);
  END;

这篇关于Oracle-如何处理变量中的32K +字符串长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！