速度！请教大家一问题：设置Dbms_Output.put_line()输出长度大小的命令是什么？

Question

 
  如题，
      我主要就是由于在过程中动态拼接SQL语句，我调试的时候，需要打印我这个SQL语句出来，但是会报错，长度只能255个字符，
      ORU-10028: line length overflow, limit of 255 chars per line
      精简后的sql_str长度：Length(sql_str)=995
      我如何设置打印的长度大点?
      命令我不清楚了，大家告诉一下。
      之前我也遇到过，但是精简打印语句后可以，但是这次确实拼接的SQL语句太长，只有来问问大家了
  Dbms_Output.put_line(sql_str);

Answer 1

备注：不想拆分成多个SQL 一个一个打印
要求能一次全部输入完

Answer 2

SET SERVEROUTPUT ON SIZE 5000 
--这样有效吗？

Answer 3

 --据说10g或以上版本可以这样
set serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

Answer 4

不行啊兄弟们
我是在过程中打印的，不是在SQLPLUS命令窗口

Answer 5

set long 100000
set linesize 10000
set serveroutput on size 1000000

Answer 6

而且我的过程在SQLPLUS下创建会报错，但是在我另一个工具SQLTOOLS中创建能成功，只是执行过程中打印语句会报错。
哪个兄弟帮我打印出来看看？--过程：     CREATE OR REPLACE PROCEDURE p_get_info(v_id IN  VARCHAR2, --直流交流类型
                                            v_cheindate IN  DATE ) --,  --时间
                                            --resuleSet OUT sys_refcursor)
     IS
       sql_str VARCHAR2(4000);
     BEGIN
          sql_str:='SELECT Decode(Grouping(equipName),1,''总计'',equipName) as equipName,Sum(Decode(To_Char(cheindate,''mm''),''01'',1,0)) mon1,Sum(Decode(To_Char(cheindate,''mm''),''02'',1,0))  mon2,Sum(Decode(To_Char(cheindate,''mm''),''03'',1,0))  mon3,Sum(Decode(to_char(cheindate,''q''),1,1,0)) jidu1,Sum(Decode(To_Char(cheindate,''mm''),''04'',1,0)) mon4,Sum(Decode(To_Char(cheindate,''mm''),''05'',1,0)) mon5,Sum(Decode(To_Char(cheindate,''mm''),''06'',1,0)) mon6,Sum(Decode(to_char(cheindate,''q''),2,1,0)) jidu2,Sum(Decode(To_Char(cheindate,''mm''),''07'',1,0)) mon7,Sum(Decode(To_Char(cheindate,''mm''),''08'',1,0)) mon8,Sum(Decode(To_Char(cheindate,''mm''),''09'',1,0)) mon9,Sum(Decode(to_char(cheindate,''q''),3,1,0)) jidu3,Sum(Decode(To_Char(cheindate,''mm''),''10'',1,0)) mon10,Sum(Decode(To_Char(cheindate,''mm''),''11'',1,0)) mon11,Sum(Decode(To_Char(cheindate,''mm''),''12'',1,0)) mon12,Sum(Decode(to_char(cheindate,''q''),4,1,0)) jidu4,Count(1) huizong FROM (SELECT * FROM tab1 WHERE id='||v_id||' AND cheindate <= '||v_cheindate||') GROUP BY rollup(equipName)'; 
            Dbms_Output.put_line(sql_str);
            --OPEN resuleSet FOR sql_str;
             
     END;
     /--执行：
  EXEC p_get_info('t001',To_Date('2010-06-12','yyyy-mm-dd'));

Answer 7

SQL> EXEC p_get_info('t001',To_Date('2010-06-12','yyyy-mm-dd'));
ERROR:
ORA-06502: PL/SQL: 数字或值错误 :  主机绑定数组太小
ORA-06512: 在 line 1PL/SQL procedure successfully completed.SQL>

Answer 8

--SQLPLUS命令窗口执行报错：Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set long 100000
SQL> set linesize 10000
SQL> set serveroutput on size 1000000
SQL> CREATE OR REPLACE PROCEDURE p_get_info(v_id IN  VARCHAR2, --直流交流类型
  2                                              v_cheindate IN  DATE ) --,  --时间
  3                                              --resuleSet OUT sys_refcursor)
  4       IS
  5         sql_str VARCHAR2(4000);
  6       BEGIN
  7            sql_str:='SELECT Decode(Grouping(equipName),1,''总计'',equipName) as equipName,Sum(De
code(To_Char(cheindate,''mm''),''01'',1,0)) mon1,Sum(Decode(To_Char(cheindate,''mm''),''02'',1,0))  
mon2,Sum(Decode(To_Char(cheindate,''mm''),''03'',1,0))  mon3,Sum(Decode(to_char(cheindate,''q''),1,1
,0)) jidu1,Sum(Decode(To_Char(cheindate,''mm''),''04'',1,0)) mon4,Sum(Decode(To_Char(cheindate,''mm'
'),''05'',1,0)) mon5,Sum(Decode(To_Char(cheindate,''mm''),''06'',1,0)) mon6,Sum(Decode(to_char(chein
date,''q''),2,1,0)) jidu2,Sum(Decode(To_Char(cheindate,''mm''),''07'',1,0)) mon7,Sum(Decode(To_Char(
cheindate,''mm''),''08'',1,0)) mon8,Sum(Decode(To_Char(cheindate,''mm''),''09'',1,0)) mon9,Sum(Decod
e(to_char(cheindate,''q''),3,1,0)) jidu3,Sum(Decode(To_Char(cheindate,''mm''),''10'',1,0)) mon10,Sum
(Decode(To_Char(cheindate,''mm''),''11'',1,0)) mon11,Sum(Decode(To_Char(cheindate,''mm''),''12'',1,0
)) mon12,Sum(Decode(to_char(cheindate,''q''),4,1,0)) jidu4,Count(1) huizong FROM (SELECT * FROM tab1
 WHERE id='||v_id||' AND cheindate <= '||v_cheindate||') GROUP BY rollup(equipName)'; 
  8              Dbms_Output.put_line(sql_str);
  9              --OPEN resuleSet FOR sql_str;
 10               
 11       END;
 12  /Warning: Procedure created with compilation errors.SQL> show error
Errors for PROCEDURE P_GET_INFO:LINE/COL ERROR
-------- -----------------------------------------------------------------
7/20     PLS-00103: 出现符号 "SELECT
         Decode(Grouping(equipName),1,'总计',equipName) as
         equip"在需要下列之一时：
         ( - + case mod new not null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> avg
         count current exists max min prior sql stddev sum variance
         execute forall merge time timestamp interval date
         <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set sSQL> --SQLtools工具中创建成功
     CREATE OR REPLACE PROCEDURE p_get_info(v_id IN  VARCHAR2, --直流交流类型
                                            v_cheindate IN  DATE ) --,  --时间
                                            --resuleSet OUT sys_refcursor)
     IS
       sql_str VARCHAR2(4000);
     BEGIN
          sql_str:='SELECT Decode(Grouping(equipName),1,''总计'',equipName) as equipName,Sum(Decode(To_Char(cheindate,''mm''),''01'',1,0)) mon1,Sum(Decode(To_Char(cheindate,''mm''),''02'',1,0))  mon2,Sum(Decode(To_Char(cheindate,''mm''),''03'',1,0))  mon3,Sum(Decode(to_char(cheindate,''q''),1,1,0)) jidu1,Sum(Decode(To_Char(cheindate,''mm''),''04'',1,0)) mon4,Sum(Decode(To_Char(cheindate,''mm''),''05'',1,0)) mon5,Sum(Decode(To_Char(cheindate,''mm''),''06'',1,0)) mon6,Sum(Decode(to_char(cheindate,''q''),2,1,0)) jidu2,Sum(Decode(To_Char(cheindate,''mm''),''07'',1,0)) mon7,Sum(Decode(To_Char(cheindate,''mm''),''08'',1,0)) mon8,Sum(Decode(To_Char(cheindate,''mm''),''09'',1,0)) mon9,Sum(Decode(to_char(cheindate,''q''),3,1,0)) jidu3,Sum(Decode(To_Char(cheindate,''mm''),''10'',1,0)) mon10,Sum(Decode(To_Char(cheindate,''mm''),''11'',1,0)) mon11,Sum(Decode(To_Char(cheindate,''mm''),''12'',1,0)) mon12,Sum(Decode(to_char(cheindate,''q''),4,1,0)) jidu4,Count(1) huizong FROM (SELECT * FROM tab1 WHERE id='||v_id||' AND cheindate <= '||v_cheindate||') GROUP BY rollup(equipName)'; 
            Dbms_Output.put_line(sql_str);
            --OPEN resuleSet FOR sql_str;
             
     END;
     /
     Create procedure, executed in 0.109 sec.
     Total execution time 0.109 sec.     --执行时候，打印报错！
     EXEC p_get_info('t001',To_Date('2010-06-12','yyyy-mm-dd'));
          
           ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
           ORA-06512: 在 "SYS.DBMS_OUTPUT", line 35
           ORA-06512: 在 "SYS.DBMS_OUTPUT", line 115
           ORA-06512: 在 "UCNZP.P_GET_INFO", line 8
           ORA-06512: 在 line 2      Total execution time 0.016 sec.

Answer 9

SQL> EXEC p_get_info('t001',To_Date('2010-06-12','yyyy-mm-dd'));
SELECT Decode(Grouping(equipName),1,'总计',equipName) as
equipName,Sum(Decode(To_Char(cheindate,'mm'),'01',1,0))
mon1,Sum(Decode(To_Char(cheindate,'mm'),'02',1,0))
mon2,Sum(Decode(To_Char(cheindate,'mm'),'03',1,0))
mon3,Sum(Decode(to_char(cheindate,'q'),1,1,0))
jidu1,Sum(Decode(To_Char(cheindate,'mm'),'04',1,0))
mon4,Sum(Decode(To_Char(cheindate,'mm'),'05',1,0))
mon5,Sum(Decode(To_Char(cheindate,'mm'),'06',1,0))
mon6,Sum(Decode(to_char(cheindate,'q'),2,1,0))
jidu2,Sum(Decode(To_Char(cheindate,'mm'),'07',1,0))
mon7,Sum(Decode(To_Char(cheindate,'mm'),'08',1,0))
mon8,Sum(Decode(To_Char(cheindate,'mm'),'09',1,0))
mon9,Sum(Decode(to_char(cheindate,'q'),3,1,0))
jidu3,Sum(Decode(To_Char(cheindate,'mm'),'10',1,0))
mon10,Sum(Decode(To_Char(cheindate,'mm'),'11',1,0))
mon11,Sum(Decode(To_Char(cheindate,'mm'),'12',1,0))
mon12,Sum(Decode(to_char(cheindate,'q'),4,1,0)) jidu4,Count(1) huizong FROM
(SELECT * FROM tab1 WHERE id=t001 AND cheindate <= 12-6月 -10) GROUP BY
rollup(equipName)PL/SQL 过程已成功完成。

Answer 10

scott@YPCOST> /过程已创建。scott@YPCOST> EXEC p_get_info('t001',To_Date('2010-06-12','yyyy-mm-dd'));
SELECT Decode(Grouping(equipName),1,'总计',equipName) as equipName,Sum(Decode(To_Char(cheindate,'mm'
mon1,Sum(Decode(To_Char(cheindate,'mm'),'02',1,0))  mon2,Sum(Decode(To_Char(cheindate,'mm'),'03',1,0
jidu1,Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) mon4,Sum(Decode(To_Char(cheindate,'mm'),'05',1,0
mon6,Sum(Decode(to_char(cheindate,'q'),2,1,0)) jidu2,Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) m
mon8,Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) mon9,Sum(Decode(to_char(cheindate,'q'),3,1,0)) ji
mon10,Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) mon11,Sum(Decode(To_Char(cheindate,'mm'),'12',1,
jidu4,Count(1) huizong FROM (SELECT * FROM tab1 WHERE id=t001 AND cheindate <= 2010-06-12 00:00:00) PL/SQL 过程已成功完成。

Answer 11

--成哥，我要的是在过程中dbms_output.put_line(sql_str)打印出来的结果
--我主要是要看我拼接的SQL语句
--单独执行，肯定是成功的
--如：Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput ON
SQL> --创建过程：
SQL> CREATE OR REPLACE PROCEDURE p_get_info(resuleSet OUT sys_refcursor)
  2  IS 
  3  BEGIN 
  4       OPEN resuleSet FOR 
  5            SELECT
  6            Decode(Grouping(equipName),1,'总计',equipName) "设备种类",
  7            Sum(Decode(To_Char(cheindate,'mm'),'01',1,0)) "1月",
  8            Sum(Decode(To_Char(cheindate,'mm'),'02',1,0))  "2月",
  9            Sum(Decode(To_Char(cheindate,'mm'),'03',1,0))  "3月",
 10            Sum(Decode(to_char(cheindate,'q'),1,1,0)) "一季度",
 11            Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) "4月",
 12            Sum(Decode(To_Char(cheindate,'mm'),'05',1,0)) "5月",
 13            Sum(Decode(To_Char(cheindate,'mm'),'06',1,0)) "6月",
 14            Sum(Decode(to_char(cheindate,'q'),2,1,0)) "二季度",
 15            Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) "7月",
 16            Sum(Decode(To_Char(cheindate,'mm'),'08',1,0)) "8月",
 17            Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) "9月",
 18            Sum(Decode(to_char(cheindate,'q'),3,1,0)) "三季度",
 19            Sum(Decode(To_Char(cheindate,'mm'),'010',1,0)) "10月",
 20            Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) "11月",
 21            Sum(Decode(To_Char(cheindate,'mm'),'12',1,0)) "12月",
 22            Sum(Decode(to_char(cheindate,'q'),4,1,0)) "四季度",
 23            Count(1) "总计"
 24            FROM tab1 GROUP BY rollup(equipName);
 25  END;
 26  /Procedure created.SQL> --测试过程
SQL> var cur refcursor
SQL> exec p_get_info(:cur);PL/SQL procedure successfully completed.SQL> --结果：
SQL> print cur    --结果：
设备种类        1月 2月 3月 一季度 4月 5月 6月 二季度 7月 8月 9月 三季度 10月 11月 12月 四季度 总计
---------------------------------------------------------------------------------------------------------
交流电动转辙机  1    1   0   2      0   1   0    1     0   0   1    1     0    0    1    1      5
直流电动转辙机  2    1   1   4      0   1   2    3     0   1   0    1     0    0    0    0      8
总计            3    2   1   6      0   2   2    4     0   1   1    2     0    0    1    1      13

Answer 12

奇怪,plsql developer的command windows可以顺利执行,sqlplus就不成.
都是设置了long linesize 和serveroutput

Answer 13

对啊，我也奇怪了，我之前在SQLPLUS中也设置了long linesize 和serveroutput
但是创建时候就会报错
所以就要在SQLTOOLS中创建不会报错，但是执行过程时，打印就会报错，SQLTOOLS下无法设置long linesize 和serveroutput还有Aspen  你打印出来的SQL语句咋少了呢？ 是不是没复制完全？

Answer 14

我的是在sqplus里啊 set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 160
set pagesize 9999

Answer 15

恩，是我复制少了
scott@YPCOST> EXEC p_get_info('t001',To_Date('2010-06-12','yyyy-mm-dd'));
SELECT Decode(Grouping(equipName),1,'总计',equipName) as equipName,
Sum(Decode(To_Char(cheindate,'mm'),'01',1,0)) mon1,
Sum(Decode(To_Char(cheindate,'mm'),'02',1,0))  mon2,
Sum(Decode(To_Char(cheindate,'mm'),'03',1,0))  mon3,
Sum(Decode(to_char(cheindate,'q'),1,1,0)) jidu1,
Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) mon4,
Sum(Decode(To_Char(cheindate,'mm'),'05',1,0)) mon5,
Sum(Decode(To_Char(cheindate,'mm'),'06',1,0)) mon6,
Sum(Decode(to_char(cheindate,'q'),2,1,0)) jidu2,
Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) mon7,
Sum(Decode(To_Char(cheindate,'mm'),'08',1,0)) mon8,
Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) mon9,
Sum(Decode(to_char(cheindate,'q'),3,1,0)) jidu3,
Sum(Decode(To_Char(cheindate,'mm'),'10',1,0)) mon10,
Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) mon11,
Sum(Decode(To_Char(cheindate,'mm'),'12',1,0)) mon12,
Sum(Decode(to_char(cheindate,'q'),4,1,0)) jidu4,Count(1) huizong 
    FROM (SELECT * FROM tab1 WHERE
id=t001 AND cheindate <= 2010-06-12 00:00:00) 
    GROUP BY rollup(equipName)PL/SQL 过程已成功完成。

Answer 16

谢谢大家了，我主要是看拼接的SQL语句是否正确，结果遇到这么写麻烦
还好，成哥和Aspen的打印结果证实了我拼接是正确的哦对了 minitoy  你打印不成，报错是不是和我再SQLTOOLS下的一样？

Answer 17

初始意向是想得到拼接后的SQL语句，想打印出来看看执行是否正确，已从成哥和Aspen得到答案。
至于其它创建不成功，打印报错等，先结贴，先把肚子填饱再说。

Answer 18

成哥，其实，这个问题是这个帖子的，里面有测试数据：http://topic.csdn.net/u/20101206/15/057718f2-987d-470c-a77a-283ebc1629ea.html