|
|
|
|
|
|
|
|
PL/SQL 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. -
Audit DML on Oracle tables using autonomous triggers:Actions taken within DML triggers on tables will be rolled back if the triggering transaction is rolled back. This can cause problems if you want your trigger to fire regardless of the success or failure of the triggering statement. A case in point would be when a trigger is designed to log insert attempts to a table (from, say, ColdFusion) into an audit table. Obviously we would want even failed inserts to be logged, but this won't happen if regular triggers are used. Autonomous transactions save the day. These are, essentially, transactions within transactions. The inner (autonomous) transaction can be committed independently of the outer transaction. The inner transaction allows us to log failed inserts (outer transaction) Below is an example using the ubiquitous EMP table from the scott/tiger schema.
Log in to Oracle as scott and create an audit table EMP_HISTORY. This table is intended to capture all attempted inserts into EMP.
create table emp_history(
empno number(4),
ename varchar2(10),
deptno number(2),
insert_date date
);
Next create the autonomous trigger on EMP.
create or replace trigger before_insert_emp
before insert on emp for each row
declare
pragma autonomous_transaction;
begin
insert into
emp_history
values
(:new.empno, :new.ename, :new.deptno, sysdate);
commit;
end before_insert_emp;
Notes:
1) The pragma signals that the body of the trigger is an autonomous transaction.
2)The commit at the end is required to complete the autonomous transaction.
Now try inserting an illegal entry into EMP from CF or any other tool.
insert into
emp
values
(7521, 'AWATI', 'SIDEKICK', 7839, sysdate, 4000,null,10);
This statement fails with a key constraint violation (assuming that you have the original data in EMP).
Finally, do a select * on EMP_HISTORY to see a log of the failed insert attempt. To convince yourself of the necessity of this approach, you may want to attempt the insert after recompiling the trigger without the pragma declaration and the commit. -
Define your own database exceptions within Oracle:Oracle allows you to define and raise custom application exceptions. This affords a nice route to trapping application errors which occur in the database. Here's a procedure template that initialises (defines) an exception and raises it if the error condition is encountered. The exception can then be handled within CF like any other database error.
create or replace your_proc as
error_condition number := 0;
pragma exception_init(e_exception_name;-20001);
begin
--your processing goes here. It should set the variable error_condition to
--0 or 1
if (error_condition) then
raise e_exception_name;
end if;
--exception block
exception
when e_exception_name then
raise_application_error(-20001,'Your error message text goes here');
end your_proc;
Notes:
1) Oracle reserves error numbers between -20999 and -20000 for user defined exceptions.
2)The pragma in the third line simply associates an exception name with the number, allowing you to raise the exception by name within the code.
3) The invocation of raise_application_error stops processing of the current code block and returns the error number and message to CF. You can then trap and display the error message to the user via the usual <CFTRY>. <CFCATCH> mechanism. -
Use Oracle referenced cursors to return result sets (ColdFusion 4.5+, Oracle 7.3+):As of version 4.5+, ColdFusion allows you to retrieve result sets from Oracle via referenced cursor objects. To do this, you would first create a package containing the referenced cursor type definition and a procedure which creates the specific instance of the cursor for the data you want to return to ColdFusion. Here's the PL/SQL:
Package spec
create or replace package rc as
type rs_type is ref cursor return table1%rowtype;
procedure rset (p_rs in out rs_type);
end rc;
Package body
create or replace package body rc is
procedure rset (p_rs in out rs_type) is
begin
open p_rs for select * from table1;
end rset;
end rc;
Then in ColdFusion you would use the cfstoredproc tag to retrieve the data in table1.
<cfstoredproc procedure="rc.rset"The result set, stored in the variable table1, can be processed as a ColdFusion query. Note that in ColdFusion 5.0 you must set maxrows to -1 in cfprocresult if you want all rows returned (the default is set to 1), whereas in version 4.5 you must set maxrows to the actual number of rows you want returned.
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
<cfprocparam type="out"
cfsqltype="cf_sql_refcursor"
variable="v" maxrows="-1">
<cfprocresult name="table1Data">
</cfstoredproc>
-
Running Anonymous PL/SQL blocks:Anonymous PL/SQL blocks can be executed through cfquery with ODBC drivers - you simply enclose your code block within a matching pair of cfquery tags with the appropriate data source attributes. If you try this with native drivers, however, you will get a ORA-06550 error. There is a workaround which was pointed out to me by Mike Morris of KLA - Tencor (Thanks, Mike!) : assign the code block to a ColdFusion variable and then output the variable contents within cfquery, as shown below.
< cfset plsql_code=
"declare
cursor c_table is
select
*
from
your_table;
begin
for r_table in c_table loop
null;
end loop;
end;">
<cfquery name="yourQuery"
datasource="yourNativeDSN"
username="yourUsername"
password="yourPwd">
#plsql_code#
</cfquery>
-
Use the "maxlength" attribute of cfprocparam for strings longer than 255 bytes:CF truncates strings longer than 255 characters passed to a stored procedure through cfstoredproc and cfprocparam. To avod this use the little known "maxlength" attribute of cfprocparam. Here's an example:
<cfstoredproc procedure="yourProcedure"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
<cfprocparam type="in"
value=#long_variable#"
maxlength=4000
cfsqltype="cf_sql_varchar">
</cfstoredproc>