Re: Input truncated to 7499 characters

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 13 Jun 2003 10:27:55 -0700
Message-ID: <92eeeff0.0306130927.27a537aa_at_posting.google.com>


"news" <oscgonesp_at_hotmail.com> wrote in message news:<bcc3ht$q7368_at_cesio.mundo-r.com>...
> Hi everyone
>
> I have this problem with a script in Oracle 8.1.7. This script (script1)
> generates another script (script2) which contains command like:
> ...
> /
> drop table X
> create table X
> update Y set field1=".."
> /
>
> When script2 is running it show this error in a few commands:
>
> DROP TABLE REP_O_TTF
> /
> CREATE TABLE REP_O_TTF AS SELECT * FROM O_TTF WHERE 1=2
> /
> UPDATE MSCATALOG SET REPORTTABLE = 'REP_O_TTF' WHERE TABLENAME = 'O_TTF'
> /
> commit
> /
> DROP TABLE REP_O_UTENTI
> /
> CREATE TABLE REP_O_UTENTI AS SELECT * FROM O_UTENTI WHERE 1=2
> /
> UPDATE MSCATALOG SET REPORTTABLE = 'REP_O_UTENTI' WHERE TABLENAME =
> 'O_UTENTI'
> /
> commit
> /
> Input truncated to 7499 characters
> SP2-0027: Input is too long (> 2499 characters) - line ignored
> SP2-0027: Input is too long (> 2499 characters) - line ignored
> values
> *
> ERROR at line 10:
> ORA-00947: not enough values
>
> The script1 code is somethink like that:
>
> set serveroutput on
> set feed off
> spool rep_table.tmp
> BEGIN
> dbms_output.enable(20000000);
> for rec in
> (
> select tablename
> from mscatalog
> where tablename like 'O_%'
> )
> loop
> dbms_output.put_line('DROP TABLE REP_'||rec.tablename);
> dbms_output.put_line('/');
>
> dbms_output.put_line('CREATE TABLE REP_'||rec.tablename
> || ' AS SELECT * FROM ' || rec.tablename || ' WHERE
> 1=2');
> dbms_output.put_line('/');
> dbms_output.put_line('UPDATE MSCATALOG SET REPORTTABLE = ''REP_' ||
> rec.tablename || ''' WHERE TABLENAME = ''' || rec.tablename ||
> '''');
> dbms_output.put_line('/');
> dbms_output.put_line('commit');
> dbms_output.put_line('/');
>
> end loop;
> END;
> /
> spool off
> set feed on
> _at_rep_table.tmp
>
>
> Could you help me?
>
> Thanks anyway.

Couple of things,

  1. Dbms_output buffer limit is 1000000 and not 20000000.
  2. Instead of using dbms_output to create DDL/DML, I would use Sql statements to do that. This way you bypass buffer limit imposed by dbms_output. e.g.

SET PAGESIZE 0
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF
SPOOL rep_table.tmp
SELECT 'DROP TABLE REP_' || tablename || ';' FROM mscatalog
WHERE ....; Regards
/Rauf Sarwar Received on Fri Jun 13 2003 - 19:27:55 CEST

Original text of this message