Oracle Function to Create CFQuery

Rather than creating insert and update queries in ColdFusion against an Oracle database, this function can generate the code for you. I haven’t added cfqueryparam to check the datatypes, but that would be ideal at helping prevent SQL Injection attacks.

I’ll update this post later to add documentation and details on usage as well as add the cfqueryparam for datatype enforcement.
create or replace FUNCTION cfquery(ftype IN NUMBER,   otname IN VARCHAR2,   qname IN varchar2 := null)
RETURN CLOB IS cfquery CLOB;
BEGIN
/**
ftype := 0 — insert
ftype := 1 — update
**/
IF ftype = 0 THEN
FOR x IN
(SELECT column_name AS form_field FROM all_tab_cols dtc WHERE owner || ‘.’ || TABLE_NAME = UPPER(otname) order by column_id)
LOOP
BEGIN
cfquery := cfquery || ”’#form.’||x.form_field||’#”,’||chr(10);
END;
END LOOP;
cfquery := cfquery||’zzz’;
cfquery := ‘insert into ‘||otname||’ values ( ‘||replace(cfquery,’,’||chr(10)||’zzz’)||’ )’;
ELSIF ftype = 1 THEN
FOR x IN
(SELECT column_name AS form_field FROM all_tab_cols dtc WHERE owner || ‘.’ || TABLE_NAME = UPPER(otname))
LOOP
BEGIN
cfquery := cfquery || CHR(10) || x.form_field||’=”#form.’||x.form_field||’#”,’;
END;
END LOOP;
cfquery := cfquery||’zzz’;
cfquery := ‘update ‘||otname||’ set ‘||replace(cfquery,’,zzz’)||’ where ‘||qname||’=”#form.’||qname||’#”’;
END IF;
RETURN cfquery;
END;