limit on length of strings passed to dbms_sql?

From: Bill Mepham <mepham_at_newscorp.com>
Date: Fri, 24 Jul 1998 12:02:56 -0400
Message-ID: <35B8B030.BE65EB67@newscorp.com>

Hello,

I am having difficulty passing a character string with a length longer than 1023 characters to the dbms_sql or utl_file packages. The string can be sent to our own stored procedures without difficulty. How can I increase the size?

Consider the following pl/sql block:

declare

   aFile utl_file.file_type;

   stmt varchar2(4000) :=
'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234\

56789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345\

67890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456\

78901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567\

89012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678\

90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789\

01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890\

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901\

23';

   procedure put (

      stmt  in varchar2,
      aFile in utl_file.file_type)

   as
   begin

      utl_file.put_line(aFile, length(stmt));
      utl_file.put_line(aFile, stmt);         -- dies here when

length(stmt) > 1023

   end;

begin

   aFile := utl_file.fopen('...some pathname...', 'test.out','W');

   put(stmt, aFile);

   stmt := stmt||'X'; --length is now 1024

   put(stmt, aFile);

   utl_file.fclose(aFile);

exception

   when others then

      utl_file.fclose(aFile);
      raise;

end;
/

When I comment out the line "utl_file.put_line(aFile, stmt)" the code works fine, which I think means that I can pass charater strings of length greater than 1023 to my own procedures but not Oracle's. Is there a way of increasing the allowable size that can be passed to dbms_sql or utl_file?

Thanks in advance,
-Bill Mepham Received on Fri Jul 24 1998 - 11:02:56 CDT