Is this the sanctioned way to ascertain a table's existence?

#21

On 18 Okt., 07:21, Noons <wizofo... (AT) yahoo (DOT) com.au> wrote:

Quote:

Maxim Demenko wrote,on my timestamp of 17/10/2009 11:24 PM:

I got the impression, he *is* checking metadata (more precise -
user_tables).

He is counting the rows of a table to check its existence, according to his own
words:

yes, he is counting the rows of one very specific table -
*user_tables* .
But i think, it is irrelevant as long as we agree on the subject - one
parse call should be more efficient than query the data dictionary.

Quote:

*>>> EXEC SQL SELECT COUNT(1) INTO :howMany FROM user_tables WHERE

I can't possibly understand how you got that impression...

Best regards

Maxim

#22

Well, I have voiced my opinion. People may agree or not. That's fine.
It's what I teach at any rate.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

#23

Maxim Demenko wrote,on my timestamp of 19/10/2009 6:51 PM:

Quote:

yes, he is counting the rows of one very specific table -
*user_tables* .
But i think, it is irrelevant as long as we agree on the subject - one
parse call should be more efficient than query the data dictionary.

Yes, absolutely. My apologies, I misread the OP's question.

#24

On Oct 19, 6:09*am, Noons <wizofo... (AT) yahoo (DOT) com.au> wrote:

Quote:

Maxim Demenko wrote,on my timestamp of 19/10/2009 6:51 PM:

yes, he is counting the rows of one very specific table -
*user_tables* .
But i think, it is irrelevant as long as we agree on the subject - one
parse call should be more efficient than query the data dictionary.

Yes, absolutely. *My apologies, I misread the OP's question.

Actually, you goofed up there, but OP also said:

Quote:

Internally, the command 'nrec' actually is a simple:

SELECT COUNT(1) from :someTable;

Which could potentially be disastrous.

There could perhaps be a discussion about context switching between
PL and SQL relevant to other parts of the thread
http://www.google.com/search?btnG=1&...xt+swit ching
..

jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stori...&zIndex=184764

#25

Palooka wrote,on my timestamp of 19/10/2009 10:58 AM:

Quote:

Assuming that your PL/SQL has half decent error handling, Serge's way is
much better IMHO. Anyway, as he he says, if the table does not exist/is
not visible, the block will not compile.

And the "is not visible" bit is the important point there, IMHO.
Using the view USER_TABLES presumes the table(s) to be checked are created by
the user firing off the SQL. In this day and age of dbs with multiple schemas
and cross-schema access (consolidation et all) it's much better to check on
visibility: the table might be there and accessible, just not owned by the
schema of the same name as the user. That means using ALL_TABLES, not
USER_TABLES, at the very least. And what happens then if it is a view, not a table?
I still think Serge's (and Maxim's) approach of checking via parsing is the
correct one: it covers everything. Either the object (be it table or view) is
accessible or not, doesn't matter from where and in what conditions.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Is this the sanctioned way to ascertain a table's existence?

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Is this the sanctioned way to ascertain a table's existence? - 10-19-2009 , 03:51 AM

Re: Is this the sanctioned way to ascertain a table's existence? - 10-19-2009 , 08:02 AM

Re: Is this the sanctioned way to ascertain a table's existence? - 10-19-2009 , 08:09 AM

Re: Is this the sanctioned way to ascertain a table's existence? - 10-19-2009 , 04:04 PM

Re: Is this the sanctioned way to ascertain a table's existence? - 10-20-2009 , 06:17 AM