Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!sjc72.webusenet.com!sjc70.webusenet.com!news.webusenet.com!sn-xit-02!sn-xit-06!sn-post-02!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: DA Morgan Newsgroups: comp.databases.oracle.misc Subject: Re: Nesting Procedures within aPprocedure Date: Mon, 03 Mar 2003 09:45:31 -0800 Organization: EXE Message-ID: <3E6394BB.1D4E00BE@exesolutions.com> X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@splish28.drizzle.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 52 Xref: newsfeed1.easynews.com comp.databases.oracle.misc:94293 X-Received-Date: Mon, 03 Mar 2003 10:46:03 MST (news.easynews.com) Lowell Izaguirre wrote: > I tried to create a procedure that populates a table , but checks to > make sure some dependant foreign keys exist first. > > The problem is, with the procedure trying to check and insert over > 1000 records, i got the error PLS-00123: program too large; > OK fine, so I choped the original stored procedure into 7 bitesized > procedures instead; each compiles successfully. > > it seems so simple to now just have a procedure that calls all the > others, but this is where I'm having trouble. > > The smaller procedures do not have any IN or OUT Parameters, they just > check for values and insert em if they are missing. > > Now I want a procedure to call each of the seven mini procedures, but > I'm having a terrible time with that getting to compile correctly. > > could someone point out what I'm doing wrong?; I've tried some > variations with EXECUTE IMMEDIATE without success; > > Here's my Procedure, which nests the other mini procedures: > > CREATE OR REPLACE PROCEDURE SP_MXNOAC > AS > BEGIN > /*because I hit the "error PLS-00123: program too large" I chopped > this up into smaller procedures, instead of one large procedure */ > BEGIN > EXECUTE SP_MXNOAC_1; > EXECUTE SP_MXNOAC_2; > EXECUTE SP_MXNOAC_3; > EXECUTE SP_MXNOAC_4; > EXECUTE SP_MXNOAC_5; > EXECUTE SP_MXNOAC_6; > EXECUTE SP_MXNOAC_7; > END; > > END; --PROC Lets go back to the error message PLS-00123: program too large. The chances of this are virtually nill. What version and edition of Oracle? And how many lines of PL/SQL in the procedure? But to answer your specific question ... drop the EXECUTE. Daniel Morgan