CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
Gumowy Kaczak
Gumowy Kaczak

Reputation: 1499

Long PL/SQL output in SQLPLUS

Got problem with executing my script under SQLPLUS. Under SQL Developer it works well.

set serveroutput on size 1000000
declare
yyy varchar2(32000):='';
begin
yyy := 'XxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzz';
yyy := yyy || 'XxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzz';
yyy := yyy || 'XxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzzXxxYyyZzz';
dbms_output.put_line(yyy);
end;
/

Under SQLPLUS I get error: 

ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

Any solution how to force running scripts with long output under SQLPLUS? I need output on because I spool results to file.

Upvotes: 0

Views: 1457

Answers (1)

psaraj12
psaraj12

Reputation: 5072

You are using a Oracle client older than Oracle 10g against an Oracle 10g Database

Please find the quote from ASKTOM below

The problem is NOT during runtime, you are apparently using an old client against a 10.2 or above database. The database code runs fine

It is when the client goes to PICK UP THE STRING from the database that you are hitting the exception.

sqlplus is doing something like this:

a) call procedure/code - it runs and fills a dbms_output buffer, an array in a package.

b) then sqlplus calls dbms_output.GET_LINES to get the buffered output to print. THIS is the call that fails.

For more information Kindly refer

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1011431134399

Upvotes: 1

Related Questions