Cloud Photo

Azure Data Architect | DBA

Oracle Function to Create CFForm

,

If you use table and column comments, you can create an Oracle function that will create insert and update forms for you complete with table formatting and data type form fields that are limited to the number of chars in a column.

I’ll update this post soon to explain and document the details of this function and it’s usage.
create or replace FUNCTION cfform(ftype IN NUMBER,   otname IN VARCHAR2,   fsize IN NUMBER := 50) RETURN CLOB IS cfform CLOB;
BEGIN
IF ftype = 0 THEN
FOR x IN
(SELECT decode(data_type,    ‘DATE’,    ‘<cf_dimg fname=”‘ || column_name || ‘” full=”1″ req=”1″ focus=”1″ value=”‘ || to_char(sysdate,    ‘dd-mon-rr’) || ‘” size=”‘ || fsize || ‘”>’,    ‘<cfinput type=”text” name=”‘ || column_name || ‘” maxlength=”‘ || data_length || ‘” ‘ || decode(nullable,    ‘N’,    ‘required=”YES” ‘) || decode(data_type,    ‘NUMBER’,    ‘validate=”float”‘) || ‘ size=”‘ || fsize || ‘”>’) AS
form_field
FROM dba_tab_cols dtc
WHERE owner || ‘.’ || TABLE_NAME = UPPER(otname)
ORDER BY column_id)
LOOP
BEGIN
cfform := cfform || CHR(10) || x.form_field;
END;
END LOOP;
ELSIF ftype = 1 THEN
FOR x IN
(SELECT decode(dcc.comments,    ‘HIDDEN’,    NULL,    ‘<tr>’ || CHR(10) || ‘     <td valign=”BOTTOM”>’ || dcc.comments || ‘</td>’ || CHR(10) || ‘     <td valign=”BOTTOM”>’ || decode(dtc.data_type,    ‘DATE’,    ‘<cf_dimg fname=”‘ || dtc.column_name || ‘” full=”1″ req=”1″ focus=”1″ value=”‘ || to_char(sysdate,    ‘dd-mon-rr’) || ‘” size=”‘ || fsize || ‘”>’,    ‘<cfinput type=”text” name=”‘ || dtc.column_name || ‘” maxlength=”‘ || dtc.data_length || ‘” ‘ || decode(dtc.nullable,    ‘N’,    ‘required=”YES” ‘) || decode(dtc.data_type,    ‘NUMBER’,    ‘validate=”float”‘) || ‘ size=”‘ || fsize || ‘”>’ || ‘</td>’ || CHR(10) || ‘</tr>’)) AS
form_field
FROM dba_tab_cols dtc,
dba_col_comments dcc
WHERE dtc.owner || ‘.’ || dtc.TABLE_NAME = UPPER(otname)
AND dcc.owner || dcc.TABLE_NAME || dcc.column_name = dtc.owner || dtc.TABLE_NAME || dtc.column_name
ORDER BY column_id)
LOOP
BEGIN
cfform := cfform || CHR(10) || x.form_field;
END;
END LOOP;
ELSIF ftype = 2 THEN
FOR x IN
(SELECT decode(dcc.comments,    ‘HIDDEN’,    ‘<input type=”hidden” name=”‘ || dcc.column_name || ‘” value=”#’ || dtc.column_name || ‘#”>’,    ‘<tr>’ || CHR(10) || ‘     <td valign=”BOTTOM”>’ || dcc.comments || ‘</td>’ || CHR(10) || ‘     <td valign=”BOTTOM”>’ || decode(dtc.data_type,    ‘DATE’,    ‘<cf_dimg fname=”‘ || dtc.column_name || ‘” full=”1″ req=”1″ focus=”1″  value=”#’ || dtc.column_name || ‘#” size=”‘ || fsize || ‘”>’,    ‘<cfinput type=”text” name=”‘ || dtc.column_name || ‘” value=”#’ || dtc.column_name || ‘#” maxlength=”‘ || dtc.data_length || ‘” ‘ || decode(dtc.nullable,    ‘N’,    ‘required=”YES” ‘) || decode(dtc.data_type,    ‘NUMBER’,    ‘validate=”float”‘) || ‘ size=”‘ || fsize || ‘”>’ || ‘</td>’ || CHR(10) || ‘</tr>’)) AS
form_field
FROM dba_tab_cols dtc,
dba_col_comments dcc
WHERE dtc.owner || ‘.’ || dtc.TABLE_NAME = UPPER(otname)
AND dcc.owner || dcc.TABLE_NAME || dcc.column_name = dtc.owner || dtc.TABLE_NAME || dtc.column_name
ORDER BY column_id)
LOOP
BEGIN
cfform := cfform || CHR(10) || x.form_field;
END;
END LOOP;
END IF;
RETURN cfform;
END;

Leave a Reply