Oracle Forms and Reports development suite is still hanging in there. Most applications are moving to a web browser front end, but there are many corporations around the world that have built custom Oracle applications against an Oracle database using Oracle Forms and Reports. It has been more difficult to find help even from Ask TOM, so I thought I would start sharing some of my tips and tricks.
Blanking out an NBT Field in Oracle Forms that is not Mandatory
Hi everyone, here are steps required in Oracle Forms to be able to
blank out an ID field linked to an NBT field that gets values from an LOV that is not mandatory.
Add a POST-TEXT-ITEM trigger to the NBT field with the following
code as an example:
IF :BLOCK_NAME.NBT_FIELD IS NULL AND
:BLOCK_NAME.FIELD_ID IS NOT NULL THEN
:BLOCK_NAME.FIELD_ID := NULL;
END IF;
This will NULL out the NBT fields corresponding ID field so
that the value gets blanked out and commit/save.
Lookup the unique key value of any id field in any table based on foreign keys constraints
One of our clients has an audit table that shows the change in a value (before and after) with user and date and time along with the table name and column name. The problem is that some of the table columns are IDs which are meaningless numbers. This function I created will use Oracles data dictionary tables to lookup the value of the ID to display on the Audit screen by passing the originating table name, column name, and the value. If the foreign key table has multiple unique columns, the function will concatenate all of them into one value.
Hopefully this function is useful to you, and you are welcome to tweak if needed.
FUNCTION fnGetIDValue(pIDTabName_In IN VARCHAR2, pIDColName_In IN VARCHAR2, pIDValue_In IN NUMBER) RETURN VARCHAR2 IS
CURSOR crFKTabIDCol (cpIDTabName_In IN VARCHAR2, cpIDColName_In IN VARCHAR2) IS
SELECT c.table_name, c.column_name
FROM user_cons_columns a
JOIN user_constraints b ON a.owner = b.owner AND a.constraint_name = b.constraint_name
JOIN user_cons_columns c ON b.r_constraint_name = c.constraint_name
WHERE a.owner = 'OWNER'
AND b.constraint_type = 'R'
AND a.column_name = upper(cpIDColName_In)
AND a.table_name = upper(cpIDTabName_In);
CURSOR crFKValCol (cpFKTabName_In IN VARCHAR2) IS
SELECT a.column_name
FROM user_cons_columns a
JOIN user_constraints b ON a.owner = b.owner AND a.constraint_name = b.constraint_name
WHERE a.owner = 'OWNER'
AND b.constraint_type = 'U'
AND a.table_name = upper(cpFKTabName_In);
lvQuery VARCHAR2(1000);
TYPE ref_cursor IS REF CURSOR;
lvResultCursor ref_cursor;
TYPE ref_record IS RECORD ( FKNAME VARCHAR2(255) );
rcResultsRec ref_record;
lvFKTabName VARCHAR2(50);
lvFKIDColName VARCHAR2(50);
lvFKValColName VARCHAR2(255);
lvValue VARCHAR2(255);
BEGIN
lvFKTabName := NULL;
lvFKIDColName := NULL;
lvFKValColName := ' ';
lvValue := NULL;
OPEN crFKTabIDCol (cpIDTabName_In => pIDTabName_In, cpIDColName_In => pIDColName_In);
FETCH crFKTabIDCol INTO lvFKTabName, lvFKIDColName;
CLOSE crFKTabIDCol;
IF lvFKTABName IS NOT NULL THEN
FOR frFKValCol IN crFKValCol (cpFKTabName_In => lvFKTABName) LOOP
IF lvFKValColName= ' ' THEN
lvFKValColName := frFKValCol.column_name;
ELSE
lvFKValColName := lvFKValColName || ' || ' || '''-''' || ' || ' || frFKValCol.column_name;
END IF;
END LOOP;
END IF;
IF pIDValue_In IS NOT NULL THEN
lvQuery := 'SELECT (' || lvFKValColName || ') FKName FROM ' || lvFKTabName || ' WHERE ' || lvFKIDColName || ' = ' || pIDValue_In || ' ';
debug_rec(lvQuery);
OPEN lvResultCursor for lvQuery;
FETCH lvResultCursor INTO rcResultsRec;
CLOSE lvResultCursor;
IF rcResultsRec.fkname IS NOT NULL THEN
lvValue := rcResultsRec.fkname;
END IF;
END IF;
RETURN lvValue;
END fnGetIDValue;
