How can I...

Michael Vergara

unread,

Jun 6, 2005, 4:11:30 PM6/6/05

to dbi-...@perl.org

Hi Everyone:

I want to connect to an Oracle database and test for the existence of
a table. My first thought was to SELECT COUNT(*) FROM TABLE, but
DBI catches the ORA-942 error and I don't know how to catch that.
It throws the error during the $dbh->execute step; how do I catch
an Oracle error returned from that step?

Or...is there a better way?

Thanks,
Mike

Ron Reidy

unread,

Jun 6, 2005, 4:11:53 PM6/6/05

to Vergara, Michael (TEM), dbi-...@perl.org

The data dictionary is your friend ...

SELECT 'x'
FROM all_tables
WHERE table_name = UPPER(:name);

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.

Hi Everyone:

Thanks,
Mike

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Ron Reidy

unread,

Jun 6, 2005, 4:13:19 PM6/6/05

to Reidy, Ron, Vergara, Michael (TEM), dbi-...@perl.org

Sorry, The query should read:

SELECT 'x'
FROM all_tables
WHERE table_name = UPPER(:name)

AND owner = UPPER(:owner);

Peter Rabbitson

unread,

Jun 6, 2005, 5:06:09 PM6/6/05

to dbi-...@perl.org

Yes there is, and it is even portable (or so they say) across different
vendors. Look for the table_info () method at
http://search.cpan.org/~timb/DBI-1.48/DBI.pm#Database_Handle_Methods

Peter

Michael A Chase

unread,

Jun 7, 2005, 9:06:54 AM6/7/05

to mver...@guidant.com, dbi-...@perl.org

On 06/06/2005 02:06 PM, Peter Rabbitson said:

> On Mon, Jun 06, 2005 at 01:11:30PM -0700, Vergara, Michael (TEM) wrote:

>> I want to connect to an Oracle database and test for the existence of
>> a table. My first thought was to SELECT COUNT(*) FROM TABLE, but
>> DBI catches the ORA-942 error and I don't know how to catch that.
>> It throws the error during the $dbh->execute step; how do I catch
>> an Oracle error returned from that step?
>>
>> Or...is there a better way?

> Yes there is, and it is even portable (or so they say) across different

> vendors. Look for the table_info () method at
> http://search.cpan.org/~timb/DBI-1.48/DBI.pm#Database_Handle_Methods

Note that the same manual page also describes how to catch errors. You
can also see the same page by running `perldoc DBI` from the command
prompt. perldoc works for most other Perl modules and you can see
everything about the modules in CPAN (http://search.cpan.org/).

--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.

Steven Lembark

unread,

Jun 7, 2005, 9:20:12 AM6/7/05

to dbi-...@perl.org, Reidy, Ron, Vergara, Michael $TEM$

-- "Reidy, Ron" <Ron....@arraybiopharma.com>

> The data dictionary is your friend ...
>
> SELECT 'x'
> FROM all_tables
> WHERE table_name = UPPER(:name);

Depending on access you might be better off selecting
from user_tables: less likely to fail and the table
name is more likely to be useful in the current user's
context (i.e., fewer false hits).

--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lem...@wrkhors.com 1 888 359 3508