ok - I do something very similar to this, I think, and it doesn't involve writing out files and using sql*plus. It goes something like:- (and I'm sure you'll be able to adapt it to your use. create or replace function run_statement(pCode in dbms_sql.varchar2s) return integer is begin dbms_output.put_line('Opening Cursor'); vCursor:=dbms_sql.open_cursor; dbms_sql.parse(vCursor,pCode,1,pCode.count,true,dbms_sql.native); dbms_sql.close_cursor(vCursor); return 1; exception etc etc end; Declare vCode dbms_sql.varchar2s; vEmptyCode dbms_sql.varchar2s; vCount pls_integer:=0; vCursor integer; vRun pls_integer; vCount pls_integer:=1; begin for i in (select text from all_source@main_server where name=<procedure name> and type='PROCEDURE' and owner=<owner>) loop vCount:=vCount+1; vCode(vCount):=i.text; end loop; vCode(1):='create or replace '||vCode(1); vRun:=run_statement(vCode); end; / Works perfectly for me - I use it to replicate code changes down to some 100 salesforce laptops who have a local contacts database and diary which they access via the web. It does, however, insert an additional blank line between each line - never really got round to figuring out why or how to avoid it but it doesn't cause a problem. One caveat though is that each line of code can't exceed 256 characters. Cheers Steve -- -----Original Message----- From: "Post, Ethan" <Ethan.Post@xxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Date: Fri, 19 Nov 2004 13:56:18 -0600 Subject: RE: Get Package, Re-write, Re-compile... > OK, this one is going to AskTom as soon as his queue clears up. Would > anyone actually want his job? We need to put Tom in for a Nobel prize > or something, he exhibits an amazing amount of patience and effort with > ol AskTom. > > Will keep you posted. > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Post, Ethan > Sent: Thursday, November 18, 2004 2:38 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: Get Package, Re-write, Re-compile... > > Here is the situation, I have written a PL/SQL package that grabs > source > code for other packages from DBA_SOURCE, goes through each line, > inserts > instrumentation among other things, and then inserts that source into a > temporary table. This can all be accomplished using a simple call to > the package. However, to recompile the new package I spool out the new > source to a file and run the file, this happens via SQL*Plus.=3D20 > > Ideally I would like to recompile the new package using PL/SQL however > I > think DBMS_SQL has a limit of 32K and I think varchar2 limit is also > 32K > which I could use for execute immediate. Some of these packages are > > 1MB in size. Besides writing some sort of java/c wrapper can anyone > think of a way to do this? > > Thanks! > -- > //www.freelists.org/webpage/oracle-l > -- > //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l