Stan,
I don't completely understand your application, but here is the query I'd
use.
SELECT COUNT(*) FROM ALL_OBJECTS O, ALL_TAB_COLUMNS C
WHERE
O.OWNER = 'FOO'
AND OBJECT_NAME = 'BAR'
AND COLUMN_NAME IN ('BLAH', 'RAH')
AND OBJECT_TYPE IN ('TABLE', 'VIEW')
AND O.OWNER = C.OWNER
AND TABLE_NAME = OBJECT_NAME
Oracle treats views the same way it treats tables for the purposes of
keeping track of the columns. So, you can find the columns associated with
a view by looking ALL_TAB_COLUMNS. At any rate, if the count comes back as
zero, the table or view does not contain the columns in your query (or does
not exist at all).
If this doesn't address your question, feel free to mail me directly. I
don't know if this topic is too Oracle specific for this list.
-dpf-
-----------------------
David P. Fannin
Database Administrator [EMAIL PROTECTED]
UM-Rolla Computing and Information Services FAX (573) 341-4216
URL http://www.umr.edu/~dpf PHONE (573) 341-4841
-----------------------
-----Original Message-----
From: Stan Brown [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 1:53 PM
To: [EMAIL PROTECTED]
Subject: How to safetly check an SQL statement?
I'm modifying a script that connects to Oracle to collect data. Previously
it could only get data from a table, and now I'm adding ghe ability to get
data from a view.
Because of the environment it runs in, and the fact that it's configuration
table driven, on the fly. I need a LOT of error checking.
So that's where it gets interesting. Previously I was able to check for
existence of the requested table, and collumn, by checking the appropriate
Now that I'allowing views, the SQL statement can be retrived from the
"TEXT" collumn of the "ALL_VIEWS" view. However, that does not garunteee
that the sattement still points to valid table/collumn pairs.
What I have in mind is doing a prepare on this statement, and checking the
return code. Does this sound like it will catch invalis statements? If so,
how do I do this safetly without risk of causing the script to bail out on
an error?
Oravle system tables.
table tha
--
Stan Brown [EMAIL PROTECTED]
843-745-3154
Charleston SC.
--
Windows 98: n.
useless extension to a minor patch release for 32-bit extensions and
a graphical shell for a 16-bit patch to an 8-bit operating system
originally coded for a 4-bit microprocessor, written by a 2-bit
company that can't stand for 1 bit of competition.
-
(c) 2000 Stan Brown. Redistribution via the Microsoft Network is
prohibited.
