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

Thanks, it appears one of the parameters in the call to dbms_parse
inserts the blank lines, it can be set to false.  The line limit is a
bit of a bummer but this does appear to be working great!
 
Thanks
________________________________

From: Steve Jelfs [mailto:steve@xxxxxxxxxxxxxx] 
Sent: Friday, November 19, 2004 3:42 PM
To: Post, Ethan; oracle-l@xxxxxxxxxxxxx
Subject: 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
 
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!
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: