解决oracle 10g sql *plus 用dbms_output.put_line()输出时的长度限制问题 ORA-20000: ORU-10028: line le

解决oracle 10g
sql *plus 用dbms_output.put_line()输出时的长度限制问题。。。。。

错误提示：

*
第 1 行出现错误:
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: 在 line 7

 

参见下法解决（摘自《Oracle Database 10g完全参考手册》第47章）：《《《《《《
《《《《《《《《

You can access XSU via Java and PL/SQL APIs (application programming 
interfaces). The XSU
Java classes are part of the Oracle kernel software. The PL/SQL API is a 
wrapper that publishes
the Java classes to PL/SQL (see Chapter 39). Because the XSU classes are 
stored in the database,
you can write new stored procedures to directly access XSU’s Java classes. 
Because a PL/SQL
API is available, XSU’s functionality can be accessed directly through 
SQL.
For example, you can generate the XML version of the RATING table via the 
XSU PL/SQL
procedures. The following listing creates a procedure named PRINTCLOBOUT 
that will be called
as part of the data-output process. The data will be read in as a CLOB.

create or replace procedure PRINTCLOBOUT(result IN OUT NOCOPY CLOB)
is
xmlstr varchar2(32767);
line varchar2(2000);
begin
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
/

Now that the PRINTCLOBOUT procedure exists, run the following anonymous 
PL/SQL block.
This block queries the RATING table, so that table must be accessible from 
your schema (see the
accompanying CD-ROM).
NOTE
You must set serveroutput on prior to executing this PL/SQL block.

 

declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin
-- set up the query context...
queryCtx := DBMS_XMLQuery.newContext('select * from rating');
-- get the result..
result := DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query..
end;
/

》》》》》》》》》》》》》》》》》

 

 

 

注：在存储过程中使用存储过程时，则不需要加“exec”。。。如：printClobOut(result);

收藏于 2011-03-15