|
|
|
|
|
|
|
|
Miscellaneous Tips:
Note: The Oracle-related material presented here is largely ColdFusion-version independent. However,
please note that most of the ColdFusion code
has been developed and tested on older versions of ColdFusion (4.0 through 6.0).
Consequently, some CFML code snippets may need to be modified to work optimally on more recent versions of the
product. -
Use Oracle's fine-grained access control to implement row-level security:Oracle offers a fine-grained security framework to control access at the level of individual table rows. A detailed example is described in my article on Oracle row level security.
-
Use USER_ views to get information on database objectsOracle provides several views that allow you to extract information on database objects (tables, views, procedures etc). Three important ones are USER_OBJECTS, USER_SOURCE and USER_DEPENDENCIES. These views contain information on database objects owned by the currently logged in user (schema).
1) USER_OBJECTS: Querying this view yields information about object names, types, status (valid or invalid) and (most usefully in my experience), the last time the object was compiled or modified. Here's an example query on this view:
select
object_name, object_type, status, last_ddl_time
from
user_objects
order by
4
This query gives the name, type, status and the last modification date of all objects owned by the current user. This can be useful when, for instance, you want to know the last time a procedure was recompiled. There are several other columns in this view - see the Oracle docs for details.
2) USER_SOURCE: This view is useful when you want to search your compiled code (procedures and functions) for a particular keyword. The column TEXT contains the first two thousand characters of all procedures and functions. Here's an example query that returns all procedure and function names that contain the string 'EMPLOYEE'.
select
distinct name, type
from
user_source
where
upper(text) like '%EMPLOYEE%'
The source for triggers and views are contained in the views USER_TRIGGERS and USER_VIEWS respectively. The column TRIGGER_BODY contains the trigger text in the former and the column TEXT contains the view text in the latter. These columns cannot be searched because they are of type LONG, not VARCHAR!
3) USER_DEPENDENCIES: This view is invaluable when you need to find out which objects depend on a particular object. For instance, you may want to know which objects may be invalidated when you recompile a procedure called CALCULATE_REPORTS. Here's the query:
select
referenced_name, referenced_type
from
user_dependencies
where
name='CALCULATE_REPORTS'
The query will return a list of all database objects referenced by the object CALCULATE_REPORTS. USER_DEPENDENCIES contains other columns of interest- check the docs for details.
Notes:
1) USER_ views return information pertaining to objects owned by the current schema. Two other families of views exist: ALL_ and DBA_. Generally, for each USER_ view, there are corresponding ALL_ and DBA views (eg: ALL_OBJECTS, DBA_OBJECTS). The ALL_ views give information on all objects to which the current user has access (i.e. objects owned by the user+objects to which the user has been granted access), and the DBA_ views give information on database objects across all schemas. DBA_ views can contain more columns than the corresponding ALL_ or USER_ views. 2) There are several other USER_ views than those dealt with here, and it is useful to know about these when you want to investigate database issues. Check the Oracle docs for details. The pertinent book is the Oracle 8i (or 9i) Reference.
-
Use CFTRY - CFCATCH to trap Oracle error messagesThe ubiquitous Oracle error "ORA-00942 table or view does not exist", normally indicates that the user does not have appropriate privileges on the object. This, though, isn't immediately obvious from the message. It is best to trap the error using CFCATCH, and display a more meaningful message to your users. The CF code should be enclosed in a matching pair of <cftry>..</cftry> tags, and the cfcatch statement should appear just prior to the closing </cftry>. Here's an example to clarify the above verbiage
<!---Code that does not need error trapping goes here--->
<cftry>
<!---Code that needs error trapping goes here--->
<cfcatch type="database">
<cfif cfcatch.nativeerrorcode is "942">
<!---Your user friendly message goes here--->
</cfif>
</cfcatch>
</cftry>
Obviously you can cfcatch other errors (database and non-database) as needed. See the CF docs for details.
-
Use sequences and triggers to automatically generate primary key values:MS Access provides the autonumber facility which automatically generates an increasing sequence of numbers to serve as the primary key of a table. In Oracle you have to build this yourself. The steps involved are:
1) Create a table with the columns you need including an "id" column to hold the primary key value.
2) Create a sequence.
3) Create a trigger that will fire whenever an insert is made into the table. The trigger will increment the sequence and insert the next available number into the "id" column.
In the example below I assume that the table specs are insert_test(id number not null, remarks varchar2(64)):
Create the sequence
create sequence pk_seq nocache;
Create the trigger
create or replace trigger tib_insert_test
before insert on insert_test
referencing new as new old as old
for each row
begin
select
pk_seq.nextval
into
:new.id
from
dual;
end tib_insert_test;
Test to see that this works
insert into
insert_test
(remarks)
select
object_name
from
all_objects;
-
Specify the IP address of DB server rather than the hostname in tnsnames.ora:Specifying the IP address rather than the hostname prevents the occurrence of an ORA-12154 error (TNS: Could not resolve hostname.) in case the domain name server stutters.