Re: PERL/DBI: Finding view creation info

In article <72fjrb$on2$1_at_shell2.ba.best.com>, K. Krueger <kirbyk_at_best.com> wrote:
>I've been having a lot of success using the DBI module in Perl to connect
>to a small Oracle database. However, I'm stumped by this problem.
>

To follow up on my own post: Problem solved. It turns out that the field is a long data type, and adding in the line: $dbh->{LongReadLen} = 1000;

fixed the problem. (Which was found by turning on the DBI_TRACE, as suggested in perldoc DBI. Which I should have found the first time around, alas.)

>What I want to do is, given a view name, be able to tell how it was created
>(the where clause, essentially.) After digging around, we found the all_views
>table, which looks like where to look. (If I'm wrong, I'd love to know where
>I ought to go.) I can type into sqlplus:
>select TEXT from all_views where view_name = 'V_TEST';
>and it returns something reasonable. (I actually can't see the whole line,
>and I'm not sure how to wrap it properly, but at least there's clearly a
>result.)
>
>However, given this test script:
>
>#!/usr/local/bin/perl
>
>use DBI;
>$ENV{ORACLE_HOME} = "/oracle/app/oracle/product/7.3.4";
>
>
> $dbh = DBI->connect("dbi:Oracle:pophlth", "LOGIN", "PASSWORD")
> || die ("Could not connect to db: $!\n");
> $name = "TEST";
>
> $sql = "select TEXT from all_views where view_name = 'V_$name'";
> $getview = $dbh->prepare($sql);
> warn "prepare for getview failed" unless defined ($getview);
> $getview->execute || warn "Execute for getview failed";
> @viewresult = $getview->fetchrow_array;
> $view = ($viewresult[0] =~ /.*where(.*)/);
> print $view, @viewresult, $sql, "\n";
>
>
>The $sql statement gets set correctly to:
>select TEXT from all_views where view_name = 'V_TEST'
>
>and I don't get any errors, but the $view and @viewresult variables are
>empty. I'm baffled - any suggestions?
>
>(An email copy would be appreciated, as I'm not a regular reader of
>comp.databases.oracle.misc or comp.lang.perl.modules. Thanks.)
>--
>Kirby Krueger O- kirbyk_at_best.com
><*> "Most .sigs this small can't open their own jump gate."

-- 
Kirby Krueger      O-     kirbyk_at_best.com 
<*> "Most .sigs this small can't open their own jump gate."