RE: Get Package, Re-write, Re-compile...

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

for i in (select text from all_source_at_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.

> 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_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Post, Ethan> Sent: Thursday, November 18, 2004 2:38 PM> To: oracle-l_at_freelists.org> 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!> --> http://www.freelists.org/webpage/oracle-l> --> http://www.freelists.org/webpage/oracle-l