FAQ
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

Search Discussions

  • Reidy, Ron at Jun 6, 2005 at 8:14 pm
    The data dictionary is your friend ...

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


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

    -----Original Message-----
    From: Vergara, Michael (TEM)
    Sent: Monday, June 06, 2005 2:12 PM
    To: dbi-users@perl.org
    Subject: How can I...



    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


    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.
  • Steven Lembark at Jun 7, 2005 at 1:11 pm
    -- "Reidy, Ron" <Ron.Reidy@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
    lembark@wrkhors.com 1 888 359 3508
  • Reidy, Ron at Jun 6, 2005 at 8:15 pm
    Sorry, The query should read:

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

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


    -----Original Message-----
    From: Reidy, Ron
    Sent: Monday, June 06, 2005 2:12 PM
    To: Vergara, Michael (TEM); dbi-users@perl.org
    Subject: RE: How can I...


    The data dictionary is your friend ...

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


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

    -----Original Message-----
    From: Vergara, Michael (TEM)
    Sent: Monday, June 06, 2005 2:12 PM
    To: dbi-users@perl.org
    Subject: How can I...



    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


    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.


    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.
  • Peter Rabbitson at Jun 6, 2005 at 9:06 pm

    On Mon, Jun 06, 2005 at 01:11:30PM -0700, Vergara, Michael (TEM) wrote:
    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

    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 at Jun 7, 2005 at 1:07 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJun 6, '05 at 8:11p
activeJun 7, '05 at 1:11p
posts6
users5
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase