Re: selecting from all_source

From: Kevin Bass <akil1_at_mindspring.com>
Date: Thu, 9 Mar 2000 06:29:44 -0500
Message-ID: <8a81vf$t6g$1@slb6.atl.mindspring.net>

Try using the following shell script and sql statement to dump package headers and bodies. This will allow you to dump your code from the Unix command line instead of going into SQL*PLUS (the script will do it for you). You can execute both of them or modify them into one shell script. Please change information where appropriate.

---

SHELL SCRIPT - dump_pkg.sh

---

PKG_OWNER=$1
PKG_NAME=$2 sqlplus <<EOF
$USER_ID/$USER_PASS
@dump_pkg $PKG_OWNER $PKG_NAME
EOF echo "CREATE OR REPLACE \c" > $PKG_OWNER.$PKG_NAME.spstmp cat $PKG_OWNER.$PKG_NAME.sps | sed "s/[ ][ ]*$//g" >> $PKG_OWNER.$PKG_NAME.spstmp
mv $PKG_OWNER.$PKG_NAME.spstmp $PKG_OWNER.$PKG_NAME.sps echo "/" >> $PKG_OWNER.$PKG_NAME.sps
echo "show errors" >> $PKG_OWNER.$PKG_NAME.sps

echo "CREATE OR REPLACE \c" > $PKG_OWNER.$PKG_NAME.spbtmp cat $PKG_OWNER.$PKG_NAME.spb | sed "s/[ ][ ]*$//g" >> $PKG_OWNER.$PKG_NAME.spbtmp
mv $PKG_OWNER.$PKG_NAME.spbtmp $PKG_OWNER.$PKG_NAME.spb echo "/" >> $PKG_OWNER.$PKG_NAME.spb
echo "show errors" >> $PKG_OWNER.$PKG_NAME.spb

---

SQL STATEMENT - dump_pgk.sql

---

set pagesize 0
set heading off
set linesize 300
set feedback off
set verbose off
set termout off
spool &&1\.&&2\.sps
select text
  from sys.dba_source

where owner = upper('&&1')
   and name = upper('&&2')
   and type = 'PACKAGE'

order by line;

spool off
spool &&1\.&&2\.spb
select text
  from sys.dba_source

where owner = upper('&&1')
   and name = upper('&&2')
   and type = 'PACKAGE BODY'

order by line;
spool off
set termout on

Kevin

Tom Zamani <tomz_at_redflex.com.au> wrote in message news:8a4dcn$hr3$1_at_perki.connect.com.au...
> Hi there could you please help me with this problem.
> attached is the procedure which creates a file for given package body and
> given user.
> The owner of this procedure has DBA privilage.
>
> BUT it does not select and records, this is only for package body.
> Is there any way that I could do this.
> Thanks tom
>
> Procedure COMP_PACKAGE_BODY(v_filename in varchar2 default null,
> v_user in varchar2 default null,
> v_pack_name in varchar2 default null )
> IS
> OUTPUT VARCHAR2(32000);
> v_FileHandle UTL_FILE.FILE_TYPE;
> v_FileDir varchar2(100) default '/u02/oradata/SID/orafile';
>
> begin
> V_FileHandle := UTL_FILE.FOPEN(v_FileDir,v_filename,'w');
> for c1 in
>
> select
> a.text
> from
> all_source a
> where
> a.OWNER = upper(v_user)
> and a.name = v_pack_name
> and a.TYPE = 'PACKAGE BODY'
> order by a.line)
> loop
> --UTL_FILE.PUT(v_FileHandle,c1_rec.text);
> dbms_output.put_line('hi');
> utl_file.put(v_FileHandle,c1.text);
> end loop;
> UTL_FILE.fclose(v_FileHandle);
> EXCEPTION WHEN OTHERS THEN
> dbms_output.put_line(SUBSTR(SQLERRM,1,1000));
> END
>
>
>
>
Received on Thu Mar 09 2000 - 05:29:44 CST