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

From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
To: <Ethan.Post@xxxxxx>, "Steve Jelfs" <steve@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 22 Nov 2004 17:52:51 -0800
Two comments:
1) The code from Steve Jelfs is good, but I would add a "order by line"
clause to his "select text from all_source" to be safe;
2) If you are using Oracle 9.2 or above, the 256-character limit per
line of code doesn't apply any more, in dbms_sql for 9.2 there is a new
signature for dbms_sql.parse that uses a defined type called
"dbms_sql.varchar2a" which is a varchar2 (32767). In other words, change
the line
vCode dbms_sql.varchar2s;
to
vCode dbms_sql.varchar2s;
in Steve Jelfs' example.
Here's a proof of concept where I create a package with a source code
line of more than 256 characters.

declare
   sqlst dbms_sql.varchar2a ;
   c_dynsql pls_integer ;
   ignore pls_integer ;
begin
   sqlst (nvl (sqlst.last, 0) + 1) := 'create package p' ;
   sqlst (nvl (sqlst.last, 0) + 1) := 'as' ;
   sqlst (nvl (sqlst.last, 0) + 1) := null ;
   for i in 1..9
   loop
      sqlst (sqlst.last) := sqlst (sqlst.last) || '
var_w_name_of_30_characters' || to_char (i, 'FM000') || ' ;' ;
   end loop ;
   sqlst (nvl (sqlst.last, 0) + 1) := 'end p ;' ;
   c_dynsql := dbms_sql.open_cursor ;
   dbms_sql.parse (c => c_dynsql,
                   statement => sqlst,
                   lb => sqlst.first,
                   ub => sqlst.last,
                   lfflg => true,
                   language_flag => dbms_sql.native
                  ) ;
   ignore := dbms_sql.execute (c => c_dynsql) ;
   dbms_sql.close_cursor (c => c_dynsql) ;
exception
   when others
   then
      if dbms_sql.is_open (c => c_dynsql)
      then
         dbms_sql.close_cursor (c => c_dynsql) ;
      end if ;
      raise ;
end ;
/
set linesize 300
select length (text), text
 from user_source
 where name = 'P' and type = 'PACKAGE'
 order by line ;


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<BLOCKED::mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On Behalf Of Post,
Ethan
Sent: lundi, 22. novembre 2004 12:37
To: Steve Jelfs; oracle-l@xxxxxxxxxxxxx
Subject: 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
<BLOCKED::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



--
//www.freelists.org/webpage/oracle-l
RE: Get Package, Re-write, Re-compile...

Other related posts:
