0

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.

Improve this question
4
  • 1
    Works fine for me in SQL*Plus. Commented Apr 20, 2012 at 15:08
  • I have SQLPlus version which was added to Oracle 10g installation.. and it gives such a error on multiple computers (win7 or xp no matter). I run SQLPlus and then I call script with content as above by: @script.sql. Maybe my version is bugged? Commented Apr 21, 2012 at 12:43
  • It works fine in my Sql*plus too. Commented Apr 23, 2012 at 10:26
  • I had the same error in oracle 8i when i try to print more than 255 characters in sqlplus Commented Apr 25, 2012 at 12:12

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.