I’ve heard for years that using Oracle’s stored procedures is both more efficient and more secure than writing queries against the database. It turns out, not everyone agrees with that and there is quite a bit of room for debate (most of the articles focus on MS-SQL Server and T-SQL, not Oracle and PL\/SQL, although some general principles still apply):<\/p>\n
I was still interested in how<\/em> to use Oracle stored procedures with ColdFusion, though. Then I added a stored procedure to Oracle:<\/p>\n It took me a while to get the stored procedure to compile, but with some Google-sleuthing I eventually cobbled together something that worked.<\/p>\n Now, how to get the data out via ColdFusion? First I added the proper data source to the CF administrator. Then I put the following code in a CFM page:<\/p>\n Voila!<\/em><\/strong> It worked–3 rows returned.<\/p>\n But what if I wanted to select a subset of rows (or otherwise include a parameter in the query)? First I needed to update the stored procedure to accept a parameter:<\/p>\n Next, I needed to include a parameter in my stored procedure call:<\/p>\n Voila!<\/em><\/strong> Only a single row returned.<\/p>\n Now that I’ve figured out how<\/em> to use them, I just need to consider the many arguments for and against using stored procedures and decide whether or not I should<\/em> use them.<\/p>\n","protected":false},"excerpt":{"rendered":" I’ve heard for years that using Oracle’s stored procedures is both more efficient and more secure than writing queries against the database. It turns out, not everyone agrees with that and there is quite a bit of room for debate (most of the articles focus on MS-SQL Server and T-SQL, not Oracle and PL\/SQL, although … Continue reading Oracle stored procedures and ColdFusion<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[349,278,274,275],"class_list":["post-828","post","type-post","status-publish","format-standard","hentry","category-coldfusion","tag-coldfusion","tag-database","tag-oracle","tag-stored-procedures"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/828","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=828"}],"version-history":[{"count":10,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/828\/revisions"}],"predecessor-version":[{"id":843,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/828\/revisions\/843"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=828"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=828"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=828"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n
\nFirst I created a simple table and populated it with some data:<\/p>\n\n\n
\n <\/tfoot>\n\nid<\/id><\/p>\n name<\/th>\n<\/tr>\n<\/thead>\n \n 1<\/td>\n Chris<\/td>\n<\/tr>\n \n 2<\/td>\n Ben<\/td>\n<\/tr>\n \n 3<\/td>\n Dave<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n CREATE OR REPLACE PROCEDURE testselect \r\n (recordset OUT SYS_REFCURSOR) IS\r\nBEGIN\r\n OPEN recordset FOR\r\n SELECT id,name\r\n FROM myschema.name_table;\r\nEND;\r\n\/<\/code><\/pre>\n<cfstoredproc datasource=\"sptest\" procedure=\"testselect\">\r\n <cfprocresult name=\"data\"\/>\r\n<\/cfstoredproc>\r\n<cfdump var=\"#data#\"><\/code><\/pre>\nCREATE OR REPLACE PROCEDURE myschema.testselect \r\n (p_name IN varchar2, recordset OUT SYS_REFCURSOR) IS\r\nBEGIN\r\n OPEN recordset FOR\r\n SELECT id,name\r\n FROM myschema.test_table\r\n WHERE name = p_name;\r\nEND;\r\n\/<\/code><\/pre>\n<cfstoredproc datasource=\"sptest\" procedure=\"testselect\">\r\n <cfprocresult name=\"data\"\/>\r\n <cfprocparam value=\"Chris\" dbvarname=\"p_name\" cfsqltype=\"cf_sql_varchar\" maxlength=\"50\"\/>\r\n<\/cfstoredproc>\r\n<cfdump var=\"#data#\"><\/code><\/pre>\n