PL/SQL: How to check if a table exists or not?
Michael A. Casillas
Yet another one:
How can I check in PL/SQL if a table exists or not. Is there a built in
function that can return TRUE or FALSE? I know the table name, I just
want to know if it's there or if I have to create it. Thanks in
advance.
Michael Casillas
Tim Witort
If you want to know if the table exists IN THE CURRENT USER'S
SCHEMA:
SELECT
DECODE(count(*),0,'F','T')
FROM
user_tables
WHERE
table_name = 'THE_TABLE_I_NEED';
If you don't care about who owns the table - just ANY table
in the database with the name (user must have select privs
on the dictionary tables):
SELECT
DECODE(count(*),0,'F','T')
FROM
sys.dba_tables
WHERE
table_name = 'THE_TABLE_I_NEED';
Or the all-around solution where you know the owner
explicitly:
SELECT
DECODE(count(*),0,'F','T')
FROM
sys.dba_tables
WHERE
owner = 'OWNER_NAME'
AND table_name = 'THE_TABLE_I_NEED';
-- TRW
--
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Tim Witort || He is not silent. He is not |
| trwATmedicalertDOTorg || whispering. We are not quiet |
| || we are not listening. |
| Pin: TZ, only one :^( || - Out of the Grey |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Philip E. Sevenants
Try this:
Select Count(Table_name) from table_object where Table_name = 'MyTable';
I've been using Oracle 7.1 for the past 7 days and have only some
familarity with Oracle. Maybe this will point you in the right direction.
--
Philip Sevenants
ComputerPeople Consulting Services
Seve...@NOWHERE.cp-consulting.com
To send me e-mail, delete the "nowhere."
Notice:
Usage of my email address for commercial purposes without my prior
written consent costs US$ 50 per message. By using this email address
you agree to this condition. Technical discussions of programming
problems and solutions are not "for commercial purposes" for the
purpose of this Notice.
------------------------------------------------------------------------
And for you automated email spammers out there, here is the current
board of the Federal Communications Commission.
Chairman Reed Hundt: rhu...@fcc.gov
Commissioner James Quello: jqu...@fcc.gov
Commissioner Susan Ness: sn...@fcc.gov
Commissioner Rachelle Chong: rch...@fcc.gov
And let's help you send some spam to the USPS, too:
cust...@email.usps.gov
Marc Billiet
Michael A. Casillas wrote:
>
> Yet another one:
>
> How can I check in PL/SQL if a table exists or not. Is there a built in
> function that can return TRUE or FALSE? I know the table name, I just
> want to know if it's there or if I have to create it. Thanks in
> advance.
>
There are two ways : you can check user_tables as specified in the other replies, or you
can use the exception handling :
DECLARE
NoTable Exception;
Pragma Exception_Init(NoTable,-942);
BEGIN
select *
from Some_Table
where ...;
EXCEPTION
when NoTable then
raise_application_error(-20000, 'Hey, Some_Table doesn''t exist');
END;
ORA-00942 is the Oracle error number for 'Table or view does not exist'.
This error message will also occur if the user can't access the table.
Marc
Mario 使ela
First I can think of is to trap the ORA error (ORA-00942 table or view does
not exist) in EXCEPTION
part of a program.
Jim Robertson
One Way would be to check the view "user_tab_privs"
This would Not only show if the table exists or not , but what
privileges the user has been granted on the table.
Philip E. Sevenants <Seve...@cp-consulting.com> wrote in article
<01bcb343$d626f7b0$aac970ce@the-machine>...
Ron Reidy
Try looking at the view ALL_TABLES (where table_owner = user) or the
view user_tables.
rr
Michael A. Casillas wrote:
> Yet another one:
>
> How can I check in PL/SQL if a table exists or not. Is there a built
> in
> function that can return TRUE or FALSE? I know the table name, I just
>
> want to know if it's there or if I have to create it. Thanks in
> advance.
>
> Michael Casillas
Alexander G. Andzelevitch
Michael A. Casillas <casi...@icepr.com> wrote in article
<34047555...@icepr.com>...
> Yet another one:
>
> How can I check in PL/SQL if a table exists or not. Is there a built in
> function that can return TRUE or FALSE? I know the table name, I just
> want to know if it's there or if I have to create it. Thanks in
> advance.
>
> Michael Casillas
>
>
write following
BEGIN
CREATE TABLE <My_Table> ...; -- always try to create table
EXCEPTION
WHEN OTHERS THEN -- Catch all possible exceptions
NULL; -- nothing to do.
END;
If My_Table is not constant then use dbms_sql package to execute "create
table ..." script.
--
Alexander G. Andzelevitch
john kline
Marc Billiet wrote in article <340551...@alcatel.be>...
>Michael A. Casillas wrote:
[text snipped]
>
>There are two ways : you can check user_tables as specified in the other
replies, or you
>can use the exception handling :
>
>DECLARE
> NoTable Exception;
> Pragma Exception_Init(NoTable,-942);
>BEGIN
> select *
> from Some_Table
> where ...;
>EXCEPTION
> when NoTable then
> raise_application_error(-20000, 'Hey, Some_Table doesn''t exist');
>END;
[snip]
>Marc
Unfortunately, the PL/SQL code above would not work. PL/SQL does
compile-time checks for objects referenced in the code. The code above would
not compile if some_table did not exist at compile time. If some_table
existed at compile time but was later removed then the code above would be
marked invalid and not execute...it would have to be recompiled at which
time it would fail.
You CAN do dynamic sql at runtime, but for the problem you described you are
much better off checking the USER_TABLE view as described earlier.
jk
john kline
Eric M. Smith
Since the possibility of the tablename being a variable exists, why not
obtain a count from user_tables. This way, the extra hassle of using the
dbms_sql package is eliminated.
SELECT count(*)
INTO RecCount
FROM user_tables
WHERE table_name = {VARCHAR2(50) table_name variable};
No exceptions would be thrown using this mechanism, therefore it also
reduces the number of begin...end blocks being handled. This reduction
makes the code far more readable and maintainable.
Eric M. Smith
Gibson Consulting, Inc.
Alexander G. Andzelevitch <a...@parad.ru> wrote in article
<01bcb6b3$13a01230$38a7d1c3@aga>...
> Michael A. Casillas <casi...@icepr.com> wrote in article
> <34047555...@icepr.com>...
<<Original Text Clipped>>
Dan Clamage
One problem with using USER_TABLES is that the table might not be in your
schema and that you don't have any kind of privileges on it. If you need to
test for the existence of a table in any schema, use DBA_TABLES or
DBA_OBJECTS. A PL/SQL package can easily be written, using any of the
methods the other folks have described, and compiled by the user SYS (see
your DBA!), with EXECUTE privilege granted to PUBLIC.
Have your friendly DBA compile this as SYS:
/* Stored function to test for the existence of a table
** Given a table name, returns TRUE/FALSE/NULL
** Written by Daniel J. Clamage dcla...@idcomm.com
** this software is Public Domain -- share freely!
*/
CREATE OR REPLACE FUNCTION
table_exists(Ptable_name IN DBA_TABLES.table_name%TYPE)
RETURN BOOLEAN IS
-- use cursor so that we only do a single fetch (more efficient)
CURSOR get_table(Ctable_name DBA_TABLES.table_name%TYPE) IS
SELECT owner
FROM DBA_TABLES
WHERE table_name = Ctable_name;
local_owner DBA_TABLES.owner%TYPE; -- need to fetch something
foundit BOOLEAN; -- did we find an entry?
BEGIN
OPEN get_table(UPPER(Ptable_name)); -- convert name to upper case,
execute
FETCH get_table INTO local_owner;
foundit := get_table%FOUND; -- cursor attribute tells us if entry was
found
CLOSE get_table; -- finished with cursor
RETURN (foundit); -- TRUE/FALSE
EXCEPTION -- an error occurred
WHEN OTHERS THEN
IF (get_table%ISOPEN) THEN
CLOSE get_table;
END IF;
RETURN (NULL); -- unknown result
END table_exists;
/
GRANT EXECUTE ON table_exists TO PUBLIC;
CREATE PUBLIC SYNONYM table_exists FOR SYS.table_exists;
Note how in the exception handler we cleanup the cursor and also
importantly, we have to return something -- otherwise at run-time we might
get 'ORA-06503: PL/SQL: Function returned without value'. We don't care
what error occurred, only that we can't determine whether the table exists.
Also, we make the SYS user's new function executable and more readily
accessible by any public user. Then all any user has to do in a PL/SQL
block is:
BEGIN
IF (table_exists('my_table')) THEN -- exists in data dictionary
...
ELSE -- not in the data dictionary!
...
You can generalize this function to test for the existence of *any*
database object -- index, sequence, view, etc. -- by selecting from
DBA_OBJECTS instead. Just supply the object_name and object_type, and
optionally the object's owner if you need to be that specific.
-Dan Clamage dcla...@idcomm.com
Gary England
Sandra Jones wrote:
>
> greg teets wrote:
> >
> > On Wed, 27 Aug 1997 15:43:33 -0300, "Michael A. Casillas"
> > <casi...@icepr.com> wrote:
> >
> > You can select against USER_TABLES
> >
> > Greg in Cincinnati
> > >Yet another one:
> > >
> > >How can I check in PL/SQL if a table exists or not. Is there a built in
> > >function that can return TRUE or FALSE? I know the table name, I just
> > >want to know if it's there or if I have to create it. Thanks in
> > >advance.
> > >
> > >Michael Casillas
> > >
> use
> describe table_name;
>
> if the table exist it will be described
>
> sincerely,
>
> fred jones
This question has been making the rounds. ORACLE has a built in set of
system tables one of which is USER_TABLES.
SELECT 'Y' FROM USER_TABLES WHERE TABLE_NAME = 'mytable'
Remember that literals are case-sensitive.
Sandra Jones
flux
Sandra Jones wrote:
try: select object_name from obj where object_type = 'TABLE' and object_name
= 'MY_TABLE_NAME';
Brent D Robinson
Sandra Jones <sfj...@bellsouth.net> wrote in article
<3415F8...@bellsouth.net>...
>
> greg teets wrote:
> >
> > On Wed, 27 Aug 1997 15:43:33 -0300, "Michael A. Casillas"
> > <casi...@icepr.com> wrote:
> >
> > You can select against USER_TABLES
> >
As far as I know, USER_TABLES would only show the tables which are own by
the same user who is currently connected, so this may not work for systems
where multiple user id's are used to connect and access tables owned by
another user id.
But if you are using PL/SQL you can do a SELECT statement on the table
(e.g. SELECT 'z' from TABLE1) and then catch the exception. If SQLCODE=
-904 in the exception (the code for "TABLE DOES NOT EXIST") then you can
take appropriate action.
BEGIN
SELECT 'x'
into x
from table1
where rownum = 1;
...
...
...
EXCEPTION
when others then
if SQLCODE = -904 then
-- do whatever action here
end if;
END;
> If you know the name of the table, why not just you
> use
> describe table_name;
>
> if the table exist it will be described
>
The person was asking about detecting the existence of the table in PL/SQL,
the "describe" is for SQL PLUS.
Cecil Shresta
Brent D Robinson wrote:
Instead of selecting from USERS_TABLES, why not try ALL_TABLES?