Listing 1: ColdFusion call to the procedure
<cfstoredproc procedure="prc_ins_data" datasource="#request.ds#">
<cfprocparam type="OUT"
cfsqltype="CF_SQl_NUMERIC"
variable="v_img_id" dbvarname="p_id" value ="">
<cfprocparam type="IN"
cfsqltype="CF_SQl_VARCHAR"
variable="v_img_title" dbvarname="p_img_title"
value ="#form.title#">
<cfprocparam type="IN"
cfsqltype="CF_SQl_VARCHAR"
variable="v_img_desc" dbvarname="p_img_desc"
value ="#form.description#">
<cfprocparam type="IN"
cfsqltype="CF_SQl_VARCHAR"
variable="v_img_categories" dbvarname="p_categories"
value ="#form.categories#">
</cfstoredproc>

Listing 2: Oracle code
CREATE OR REPLACE PROCEDURE prc_ins_data(p_id IN OUT INTEGER,
p_img_title IN VARCHAR2,
p_img_desc IN VARCHAR2,
p_categories IN VARCHAR2)IS 


v_key INTEGER := NULL;
v_cat_list VARCHAR2(4000) := ÔÕ;

BEGIN
-- get next primary key and assign to variable as it is 
needed for foreign keys
SELECT seq_img_id.nextval INTO v_key
FROM dual;

/* p_id This is an IN OUT parameter and therefore, 

will be returned to the calling application.*/
p_id := v_key;

-- insert into image_table (the master table)
INSERT INTO image_table(img_id,
img_title,
img_desc) 
VALUES
(v_key,
p_img_title,
p_img_desc);

-- p_categories is a comma delimited list of image categories
v_cat_list := p_categories;

WHILE (LENGTH (v_cat_list) > 0 ) LOOP
IF (INSTR(v_cat_list, Ô,Õ) > 0) THEN 
v_cat := SUBSTR(v_cat_list, 0 , INSTR(v_cat_list, Ô,Õ) Ð1 );

v_cat_list := SUBSTR(v_cat_list, INSTR(v_cat_list, Ô,Õ) +1 );
ELSE 
v_cat := v_cat_list;
v_cat_list := ÔÕ;
END IF;

-- insert categories and foreign key link to the image 
table 
INSERT INTO img_category(category_id,
category_name,
img_id)
VALUES (seq_category.nextval,
v_cat_name,
v_key);
END LOOP;

-- finally if there are no errors commit;
COMMIT;
EXCEPTION

WHEN others THEN
/* when others is a 
default exception type that will catch all types of 
exceptions.*/
ROLLBACK;

END;