[DDL-Oracle-users] View DDL-s

[Christian M. <sc...@bi...>]

Dear list members.

I'm a new user of the DDL utility which I've taken into use for a cron
job collecting a.o. table and view DDL from the data dictionary of my
developer's work station. 

I have, however, a problem which I haven't been able to solve: how do I
achieve the column name definition part of a create view statement being
reflected by the DDL utility? What I get when I execute the below script
with 'view' as argument for my option -t are statements consisting of
'create or replace view as ' followed by the select and where clauses in
question but no list of column names reflecting those I explicitly
declared myself.

As above mentioned I'm a new user and have almost no experience with
writing DBI syntax (I've been using Oracle4 & and oraperl for years) and
therefore presume that the problem is with the programmer (me) and not
the programming tool (DDL). Butwhat *is* the problem? Will someone
please enlighten me?

#!/usr/local/bin/perl
require "app_name.pl";
use Getopt::Long;
use DBI;
use DDL::Oracle;

sub read_opts {
    my($usage);
    $script = &app_name;
    $usage = "Usage: ${script}.pl -opt ..\n";
    $usage .= " -l user/password\n";
    $usage .= " -t object type\n";
    $usage .= " [ -n object name ]\n";
    $usage .= " [ -o object owner ]\n";

    &GetOptions('l=s','t=s','n:s','o:s') || die "$usage\n";
    $opt_l || die "$usage\n";

# split oracle user and password from login parm
    ($user,$passwd) = split(/\//,$opt_l);

    die "$usage\n" if ! ($user && $passwd);

    $object_type = ($opt_t eq "table") ? 'table family' : $opt_t;
    $OBJECT_TYPE = "\U$opt_t\E";
    $object_name = "\U$opt_n\E";
    $object_owner = "$opt_o";
}

read_opts;

$oracle_home = "/export/home/app/oracle/product/7.3.4";
$ENV{ORACLE_HOME} = "$oracle_home";
$ENV{ORA_NLS32} = "${oracle_home}/ocommon/nls/admin/data";

my $dbh = DBI->connect(
		       "dbi:Oracle:SC",
		       $user,
		       $passwd,
		       {
			PrintError => 0,
			RaiseError => 1
		       }
		     );
DDL::Oracle->configure( 
		       dbh    => $dbh,
		       schema => "0",
		     );
$select =
  "SELECT
     owner,
     object_name
   FROM dba_objects
   WHERE owner = '$object_owner'
   AND object_type = '$OBJECT_TYPE'";

if (length($object_name) > 0) {
  $select .=
    "\n  AND object_name = '$object_name'";
}
my $sth = $dbh->prepare("$select");
$sth->execute;
my $list = $sth->fetchall_arrayref;
my $obj = DDL::Oracle->new(
			   type  => $object_type,
			   list  => $list
			 );
my $ddl = $obj->create; 

Best regards
-- 
Christian Mondrup, Computer Programmer
Scandiatransplant, Skejby Hospital, University Hospital of Aarhus
Brendstrupgaardsvej, DK 8200 Aarhus N, Denmark
Phone: +45 89 49 53 01 - http://www.scandiatransplant.org