Re: Procedures

From: Gombos Bertalan <bgombos_at_freemail.c3.hu>
Date: Tue, 16 Mar 1999 09:26:18 +0000
Message-ID: <36EE23BA.3941B282@freemail.c3.hu>

Ed Lufker wrote:
>
> How do you pull out all procedures owned by one user and put
> the
> output into a file, so I can take it to another db and complie them
> there.
> I would like to use sql to do it and do it in a few steps rather than
> using OEM and doing the cut and paste thing.

Run this PL/SQL block under SYSTEM:

declare
  cursor c_source is
    select *

      from all_source
      where owner in (...)
      order by owner, name, decode(type, 'PACKAGE', 1, 'PACKAGE BODY',

2, 3), line;
  v_last_user varchar2(30):= 'x';
  v_first boolean:= true;
begin
  for v_source in c_source loop
    if v_last_user != v_source.owner then

      dbms_output.put_line('/');
      dbms_output.put_line('connect ' || v_source.owner || ';');
      v_last_user := v_source.owner;

    end if;
    if v_source.line = 1 then

      dbms_output.put_line('/');
      dbms_output.put('CREATE OR REPLACE ');

    end if;
    dbms_output.put_line(substr(v_source.text, 1, length(v_source.text)-1));
  end loop;
end;

Bye:
--

    .---,                            G o m b o s  B e r t a l a n
    :   :
    :---'-. ,---. ,--. ,-.-.       rendszerfejleszto, Oracle mernok
    :     : :--   :      ;          mailto:bgombos_at_freemail.c3.hu
   ,' '---' '---' '      `-'
-- : ------------------------- Az elet megis egy habostorta? --- '10^2-1

Received on Tue Mar 16 1999 - 03:26:18 CST