Re: [DDL-Oracle-users] View DDL-s

[Richard S. <rvs...@ya...>]

Christian,

The problem WAS in DDL::Oracle, but has since been corrected.  Please
upgrade to the current version (1.10), and see if this fixes your output.

Thanks.  And please report any other issues you find -- we try to fix all
reported bugs.

Richard

--- Christian Mondrup <sc...@bi...> wrote:
> 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
> 
> _______________________________________________
> DDL-Oracle-users mailing list
> DDL...@li...
> http://lists.sourceforge.net/lists/listinfo/ddl-oracle-users


=====
Richard Sutherland
rvs...@ya...

A GURU HAIKU
Drug testing you say?
Think I'll go to Joe's Tavern
And do my homework.

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/