Re: extracting DBA_SOURCE and recreating (HELP!)

In article <617_at_redrockgrp.win.net>,

   tlp_at_redrockgrp.win.net (Tracy Lynn Pendergast) wrote:
>
> I'm trying to write a procedure to extract source code
> for such things as packages to produce the ability of
> recreating them...
>
> for some reason the extracted code compiles with
> "compilation errors".

Without seeing the errors, I have to guess at your problem.

This script works fine for me:

set heading off
set feedback off
set pause off
spool temp.sql
select text
  from dba_source
 where name = 'TNAME'
   and owner = 'RBROWN'
 order by line
/
spool off
set heading on
set feedback on
set pause on

I have only used it for procecures and functions, but I expect that it would work for packages.

If you don't specify the owner, you risk getting more than one copy, or the wrong copy(not a problem if you're using User_Source).

If you don't sort it by line, there's no guarantee that it will come out in the correct order (although it always seems to be ok).

This is probably too obvious, but are you inserting the words 'CREATE OR REPLACE' at the beginning of your source? Are you adding a '/' at the end of the source? They never seem to be there when you use DBA_Source.

Out of habit, I usually strip the trailing blanks off each line with vi or sed. This really shouldn't make any difference, though.         

        sed -e 's/ *$//' < infile > outfile (from the FAQ)

Is it possible that you don't have all the privileges you need in order to create the package?

Anyway, I hope something here has been useful. Good luck with your problem. Received on Wed May 22 1996 - 00:00:00 CEST