Re: SQL statement > 32Kb

W.van.der.Deijl_at_inter.nl.net (W. van der Deijl) wrote:

>Hi,
 

>We are developing some packages that are being used in some PL/SQL
>procedures. One of these packages contains about 5 procedures. But all
>of these procedures containts some large select-statements.
 

>When I create the package body (using a script) the create package
>body statement is larger then 32Kb, which causes:
>TNS packet writer failure
>No longer connected to Oracle
 

>when I exzecute the script from SQL*Plus.
 

>There might be to solutions:
>1) Make some buffer larger so that the statement may exceed 32Kb. But
>I don't know if this is possible and if it is, how I can do this....
 

>2) Create the package and add some procedures to it with separate
>statements. This way not all the code is included in one CREATE
>PACKAGE-statement. But I have looked in all the manuals and found no
>way to add a procedure to a package, then to create all of the package
>again (using one very large CREATE PACKAGE-statement)
 

>Doe anyone have the answer to this problem?
>Thanks!

Have you considered taking the really really large SQL queries and turning them into views? That way you take out the five biggest things (the really long queries turn into "select * from T", very short).

32k is a SQL*Plus limit, you could always write a small pro*c program to execute your "CREATE OR REPLACE package" statement instead of the above. Then the maximum length will be 64k.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Mon Oct 30 1995 - 00:00:00 CET