Databases Reference
In-Depth Information
CREATE TABLE FRIEND_0NF (
ID NUMBER NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR2(30) NOT NULL,
LASTNAME VARCHAR2(30) NOT NULL,
PHONES TAB_PHONES)
NESTED TABLE PHONES STORE AS FRIEND_PHONES;
In this example, we have created a table which stores an unlimited number of phone numbers
for every friend in the table. Structures like this are to be used only when we don't query
details in the nested table alone. Otherwise, we will spend a lot of time in un-nesting and
nesting the table, which isn't good from a performance point of view.
Avoiding dynamic SQL
The title of this recipe should be extended to say "… when you can do your stuff without using
it". In this recipe, we will see when and how to use dynamic SQL.
Dynamic SQL is the only choice when:
F
We want to execute DDL statements in our application.
F
We have to code different queries depending on user input, for example, a search
form with different search criteria that the user can choose from. This leads to
different predicates in the
WHERE
clause.
F
We want to code generic procedures, which can act on any table, for example, a
generic "print" procedure, which shows the content of a table in a certain format.
For each of these situations, there are drawbacks to be taken care of.
How to do it...
To execute DDL statements in our application, we cannot use static SQL inside PL/SQL code.
So, if we want to grant the
RESOURCE
role to the user
SH
, we have to do something similar to
the following:
BEGIN
EXECUTE IMMEDIATE 'GRANT RESOURCE TO SH'
END;
