Re: Stored procedure Bulk replace

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 3 May 2007 08:16:50 -0700
Message-ID: <1178205410.205102.76400@y5g2000hsa.googlegroups.com>

On May 3, 10:36 am, Laura <f.marchi..._at_pride.it> wrote:
> Hi all!
> I have the need to replace all occurrences of a string in all stored
> procedures of one user.
> is it possible to do it ?
>
> in practice we have an Oracle based application which uses Oracle
> cartidge to produce html
>
> Since Oracle cartridge will be dismissed we have to change all the
> cartridge Api with some other instructions.
>
> In this example :
>
> CREATE OR REPLACE PACKAGE BODY dummy AS
> .........) IS
> BEGIN
>
> htp.p('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//
> EN">');
> htp.p('<html><head><title>WIND R.D.C.P. MENU</title></head>');
> htp.p('<frameset rows="70,*" framespacing="0" frameborder="0"
> style="border-color : Silver
>
> I need to replace "ht.p" instructions with something else (f.e.
> displayHtml() )
>
> How to do it ?
> Thanks
> Francesco

Lots of alternatives. Tools such as Toad can bring you back the DDL to re-create a schema ... select the procedures and change them in an editor is one way.

You can use dbms_metadata to get the procedure code that you want ... write it to a file ... get it changed in that file ... use sqlplus to put it back in.

First you need to decide on a toolset and an approach. Lots of examples of using dbms_metadata over at http://asktom.oracle.com Received on Thu May 03 2007 - 10:16:50 CDT