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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 22 Nov 2004 18:04:52 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87B50@irvmbxw02>

Right when I was pressing the "Send" button I noticed a mistake in my e-mail, which has been corrected below.

---

From: Jacques Kilchoer

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.varchar2a; --<<<----- correction 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org
<BLOCKED::mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Post, Ethan
Sent: lundi, 22. novembre 2004 12:37
To: Steve Jelfs; oracle-l_at_freelists.org
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_at_trolltec.co.uk <BLOCKED::mailto:steve_at_trolltec.co.uk> ] Sent: Friday, November 19, 2004 3:42 PM
To: Post, Ethan; oracle-l_at_freelists.org
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

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

Received on Mon Nov 22 2004 - 20:03:32 CST