--howdy:
--thanks for the reply and the assist.
--background and overview:
--i am trying to port some tables to
--Oracle 9i on my linux box. With PostgreSQL,
--i can do a pg_dump and get the table structure
--and data ... but because there are some differences
--between the two DBs, I can't just dump the structure
--and load it to Oracle for all of the tables.
--so, my idea is this: i'd like to write a query
--to get the a list of tables and the structure
--type and append it into some *.ddl format file
--per user. that way, i can use Oracles' sqlldr
--program to load the DDL file and then insert
--the data from PostgreSQL in the two scripts.
--in the below script, i am trying to test to see
--if i can get the tablename and owner and use
--a place holder to get a list of users. the
--reason i think this is useful is if i try to
--run a query where i do a subquery, the results
--take a long time - but i don't know if there
--is a real difference in speed.
--regardless, in the below query, i guess i expect
--that the 'execute' query should get a list of
--users and place the results into the place holder
--in the EOQ loop. but what i actually see is
--only some of the tables and those tables are
--owned by one person.
--what i don't understand is:
* what should the 'execute' part actually
bring back (select distinct tableowner from pg_tables)
if not the entire list of unique users?
* should the place holder portion (the EOQ part)
only take one user and do the query?
* to omit the 'execute' part, how can i write
a query to get the tablename and structure of
each table per user? i did the psql -E to echo
what PostgreSQL sees to get a list, but i got
a list that only produced the structure (column
names) and users ... but i missed the tablenames.
--that's what i have so far.
--suggestions? thanks!
-X
-----Original Message-----
From: K Old
To: Johnson, Shaunn
Sent: 6/17/03 10:56 AM
Subject: RE: example of placeholders
Shaunn,
Can you give a description of what you are trying to do step by step.
I'm very familiar with Pg and do many complex queries at a time as it
seems you're trying to do.
I can offer help if you explain exactly what you're trying to do.
Kevin
On Tue, 2003-06-17 at 10:32, Johnson, Shaunn wrote:
> --howdy.
>
> --i think i am getting closer to solving this, but
> --i have ran into a problem.
>
> --the below program works to the point where it
> --will return the data, but not ALL of the data.
>
> --for example, i get only about 50 or so lines when
> --if i do the queries by hand, i get over 200 with
> --different $tables and $users. in the outfile,
> --i only get the first person and some of her tables.
> --is it my query? how can i tell where this script
> --is failing since Perl has not generated an error?
>
> --thanks again!
>
> -X
>
>
> [snip script]
>
> #!/usr/bin/perl -w
>
> # created: 16 June 03 -X
>
> # script to get table information and
> # append it to a new file (DDL format)
> # and prep for use by Oracle
> #
>
> use strict;
> use diagnostics;
> use DBI;
> use Cwd;
>
> ###
> ### i create a list of users to
> ### abuse later
> ###
>
> my $list="userlist.txt";
> my $file="newlist.txt";
> my $local_dir=cwd;
>
> open (DIR, $local_dir) or die "can nae open local directory: $!";
>
>
> my $date=`date '+%d %B %Y'`;
> chomp ($date);
>
> # should be the address of sender and users
> my $addr='[EMAIL PROTECTED]';
>
> # when all else fails ... cheat ...
>
> # get a list of users best way you
> # can and keep a text copy.
>
> # connect to database
>
> my $dbh=DBI->connect('dbi:Pg:dbname='test_db', 'joe_user')
> or die "Can not connect: $!";
>
> # get list to use later
>
> my $sql =<<EOQ;
> select tablename, tableowner
> from pg_tables
> where tableowner = ?
> EOQ
>
> # set up error checking
> my $sth=$dbh->prepare($sql) or die "Error =", DBI::errstr;
>
> $sth->execute($dbh->selectrow_array('select distinct tableowner from
> pg_tables'));
>
> #
> # this works; now will test the sql part
> #
> #$sth->execute ('joe_user');
>
> unless ($sth->execute) {
> print"\n\tExecute failed for stmt:\n\t$sql\nError = ",
> DBI::errstr;
> $sth->finish;
> $dbh->disconnect;
> die "\n\t\tClean up finished\n";
> }
>
> open FILE, ">$file" or die "can nae open text file: $!";
>
> while ( my($table, $user)=$sth->fetchrow_array ) {
> print FILE "$table", "$user", "\n";
> }
>
> close (DIR);
> close (FILE);
>
> $sth->finish();
>
> $dbh->disconnect;
>
> __END__
>
>
> [/snip script]
--
K Old <[EMAIL PROTECTED]>
