Host Bind Array Too Small and Formatting

[getjbb]

I did not see a forum for SQLPLUS, so I hope this is the correct forum to post my question to.

After running the following script (did not put in all columns, because it is too many):

set termout on
set echo off
set feedback off
spool &directory
declare
cursor get_fore
is
select fortype, forarea, forlocal, ..., ...
from forcast;
begin
dbms_output.put_line
( rpad('fortype', 20 ) ||
rpad('forarea', 20) ||
rpad('forlocal' ,20) ||
...
...
rpad...);

dbms_output.put_line
( rpad( '-', 20, '-') ||
rpad(' -',20,'-') ||
rpad(' -',20,'-')
...
...
rpad...);

for for_rec in get_fore loop
dbms_output.put_line
( rpad( for_rec.fortype, 20 ) ||
rpad( for_rec.forarea, 20 ) ||
rpad( for_rec.forlocal, 20 )
...
...
rpad...);
end loop;
end;
/
spool off
set serveroutput off


I received the following error: ORA-06502: PL/SQL: numeric or value error: host bind array too small.

I understand this happens due to restrictions of 255 characters on DBMS_OUTPUT.PUT_LINE.

So, I changed to

set termout on
set echo off
set feedback off
spool &directory
declare
cursor get_fore
is
select fortype, forarea, forlocal, ..., ...
from forcast;
var1 varchar2(4000);
var2 varchar2(4000);
begin
var1 := rpad('Forecast Type', 20 ) ||
rpad('Forecast Area',20) ||
rpad('Forecast Local',20)||
...
...
rpad( '-', 10, '-') ||
rpad(' -',10,'-') ||
rpad(' -',10,'-')
...
...
rpad...);

str_len := length(var1);
while loop_cnt < str_len
loop
dbms_output.put_line(substr(var1, loop_count +1, 255));
end loop;

str_len := 0;
loop_count := 0;

for for_rec in get_fore loop
var2 := rpad( for_rec.fortype, 10 ) ||
rpad( for_rec.forarea, 10 ) ||
rpad( for_rec.forlocal, 10 ) ||
...
...
rpad... );
str_len := length(var1);
while loop_cnt < str_len
loop
dbms_output.put_line(substr(var1, loop_count +1, 255));
end loop;
end loop;
end;
/
spool off
set serveroutput off


So, the problem is that the formatting is really off.
Before the change the results looked like:

Forecast Type Forecast Area Forecast Local ... ....
-----------------------------------------------------------
xxxxxxx xxxxxxx xxxxxxxxxx


Now the headings, underscores and data wrap:

Forecast Type Forecast Area
Forecast Local ... ....
-----------------------------------------------------------
xxxxxxx xxxxxxx
xxxxxxxxxx xxxx xxxx


I am new at formatting using SQLPLUS and if anyone could give me some answers, I would appreciate it.

Thanks.

getjbb

 

[karluk]

In Oracle 10.2 there is no longer a 255 character limit on the length of an output line in dbms_output.put_line. But in order to take advantage of the longer line length, both your database and your sqlplus client must be at release 10.2 or higher. So, if your database is 10.2 but you are using an older Oracle client, you should be able to get dbms_output to work simply by upgrading your client software.

If this isn't feasible, you will either have to live with the wrapped output, or rewrite your report to avoid using the dbms_output package.

 

[getjbb]

I found out the version of the Oracle SQL Plus is 9.2.0.1.0. It is not 10g as I thought. My end result is to get my data into a flat file and using UTL is not an option. I do not know any other way of spooling the data in a script except using dbms. So, I guess I will have to live with wrapped data.

 

[dkyrtata]

Have you tried setting the line size in SQL*PLUS? I believe it affects line-wrapping of dbms_output.put_line(). So try this line at the top of your script:

SET LINESIZE 9999

 

[getjbb]

I have the linesize at 32767 and that seem to have no effect. I also put serveroutput on size 1000000 and my data still wrap. I could have something to do with the while loop I put in to handle the bind problem.

getjbb

 

[karluk]

Sqlplus has powerful formatting capabilities built in, so I doubt that you really need to resort to dbms_output to accomplish your goal. It looks as if you are simply trying to make all of your columns 20 characters long. You can do this in sqlplus with something similar to

set termout on
set echo off
set feedback off
set linesize 9999
column fortype  format a20
column forarea  format a20
column forlocal format a20
...
spool &directory
select fortype, forarea, forlocal, ..., ... 
from forcast
 /
spool off
set serveroutput off

 

[getjbb]

I added:

column fortype format a20
column forarea format a20
column forlocal format a20

and did not get back any data at all. Was I even took out the while loop related to the headers and underscores and received no data.

getjbb

 

[karluk]

Here is a sample script with formatted output using the first three columns of your forcast table.

SQL> create table forcast (fortype varchar2(100), forarea varchar2(100), forlocal varchar2(100));

Table created.

SQL> insert into forcast values ('asldkfj', 'owueqfpu', 'oiafaoiuf');

1 row created.

SQL> insert into forcast values ('oiaeufuoi', 'owiuerfiowueru', 'oiuuoiuouiu');

1 row created.

SQL> insert into forcast values ('iwueioeuoiur', 'owieurfiowu', 'weufiu');

1 row created.

SQL> commit;

Commit complete.

SQL> set termout on
SQL> set echo off
SQL> set feedback off
SQL> set linesize 9999
SQL> column fortype  format a20
SQL> column forarea  format a20
SQL> column forlocal format a20
SQL> select fortype, forarea, forlocal from forcast;

FORTYPE              FORAREA              FORLOCAL
-------------------- -------------------- --------------------
asldkfj              owueqfpu             oiafaoiuf
oiaeufuoi            owiuerfiowueru       oiuuoiuouiu
iwueioeuoiur         owieurfiowu          weufiu

 

[getjbb]

I added a spool command to your script and executed the scripts. The content of the text file was:

FORTYPE FORAREA FORLOCAL

-------------------- -------------------- --------------

xxxx xxxx xxxxx

xxxx xxxx xxxxx

There was space between each line. Is there any command I can set to get rid of the lines in between.

getjbb

 

[getjbb]

I got rid of the spaces (used, set trimspool) between each line of output in my text file, but now the content of my text file is printed twice:

FORTYPE FORAREA FORLOCAL
-------------------- -------------------- --------------xxxx xxxx xxxx
xxxx xxxx xxxx

FORTYPE FORAREA FORLOCAL
-------------------- -------------------- --------------xxxx xxxx xxxx
xxxx xxxx xxxx

Below are my set parameters:

set termout on
set echo off
set feedback off
set linesize 9999
set trimspool on

I put pagesize of 80, but still received double output.

Am I missing a parameter?

getjbb

 

[Dagon]

Check you haven't got any breaks set. Typing "break" at the SQL prompt will show you if you have any breaks set.

For Oracle-related work, contact me through Linked-In.

 

[getjbb]

It responded with no break(s) defined.

getjbb

 

[getjbb]

I found out what the problem was. I put a / before the set spool off command. After removing it I did not get the duplicate output.

Thanks all for your help.

getjbb