Re: SQL statement > 32Kb

tkyte_at_us.oracle.com (Thomas J Kyte) wrote:

>>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
 

>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).
Well, actually I suggested this to the developers (in another division). But they are determined not to allow views. In the entire database with about 150 tables there not one view..... So this option is out of the question.

>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.

Hmmm. Again something that was not accepted by the developers.

I "solved" the problem by making two packages. This way the group of procedures that belong together are now split in two packages. Not the best option but the only one I could think of and that was allowed by the developers.

You see I'm just hired for 2-3 months on this project, so I have to live by the rules of the developers that designed the system :-(

Thanks for the answer anyway!

Bye,
Wilfred. Received on Fri Nov 03 1995 - 00:00:00 CET