Data Binding in Oracle
This is a suppliment to the previous blog on data binding without cfqueryparam. I was asked for a syntax example using Oracle. I came up with the following:
In oracle a PL/SQL block has a specific syntax that goes:
DECLARE
local variable declarations
BEGIN
Events and query code
EXCEPTION
error handling code. Note it is "inside" the begin/end block
END
Here's a sample derived from this link.
<Cfquery name="get" datasource="#dsn#">
DECLARE
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER := 0.72;
samp_num CONSTANT NUMBER := 132;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;
the_ratio := numerator/denominator;
IF the_ratio > lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);
ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
</cfquery>
Note: The fact that Oracle requires local vars to be declared within a declaration block would seem to me to innoculate the code from SQL injection attack. I'm speculating. I have not tested it.
DECLARE
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER := 0.72;
samp_num CONSTANT NUMBER := 132;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;
the_ratio := numerator/denominator;
IF the_ratio > lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);
ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
</cfquery>
0 Comments
Sites to Watch
Aggregators
RSS
