create or replace FUNCTION fk_name  /* VERSION 1 */
	(fk_id_in IN INTEGER,
	 fk_table_in IN VARCHAR2,
	 fk_id_col_in IN VARCHAR2,
	 fk_nm_col_in IN VARCHAR2)
RETURN VARCHAR2
IS
	/* Declare and obtain a pointer to a cursor */
	cursor_handle INTEGER := DBMS_SQL.OPEN_CURSOR;

	/* Variable to receive feedback from package functions */
	dbms_sql_feedback INTEGER;
	/*
	|| The return value of the function. Notice that I have
	|| to hard-code a size in my declaration.
	*/
	return_value VARCHAR2(100) := NULL;
BEGIN
	/*
	|| Parse the query. I construct most of the SQL statement from
	|| the parameters with concatenation. I also include a single
	|| bind variable for the actual foreign key value.
	*/
	DBMS_SQL.PARSE
		(cursor_handle,
		 'SELECT ' || fk_nm_col_in ||
		 '  FROM ' || fk_table_in ||
 		 ' WHERE ' || fk_id_col_in || ' = :fk_value',
		 DBMS_SQL.NATIVE);

	/* Bind the variable with a specific value -- the parameter */
	DBMS_SQL.BIND_VARIABLE (cursor_handle, 'fk_value', fk_id_in);

	/* Define the column in the cursor for the FK name */
	DBMS_SQL.DEFINE_COLUMN (cursor_handle, 1, fk_nm_col_in, 100);
	
	/* Execute the cursor, ignoring the feedback */
	dbms_sql_feedback := DBMS_SQL.EXECUTE (cursor_handle);

	/* Fetch the row. If feedback is 0, no match found */
	dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cursor_handle);
	IF dbms_sql_feedback > 0
	THEN
		/* Found a match. Extract the value/name for the key */
		DBMS_SQL.COLUMN_VALUE (cursor_handle, 1, return_value);
	END IF;
	/*
	|| Close the cursor and return the description, which
	|| could be NULL if no records were fetched.
	*/
	DBMS_SQL.CLOSE_CURSOR (cursor_handle);
	RETURN return_value;
END;
	
FUNCTION fk_name /* VERSION 2 */
	(fk_id_in IN INTEGER,
	 fk_table_in IN VARCHAR2,
	 fk_id_col_in IN VARCHAR2 := '_ID',
	 fk_nm_col_in IN VARCHAR2 := '_NM',
	 max_length_in IN INTEGER := 100)
RETURN VARCHAR2
/* I will not repeat any comments from first version of fk_name. */
IS
	/*
	|| Local variables to hold column names, since I must construct
	|| those names based on the values provided. If the column names
	|| are NULL, then fall back on the defaults.
	*/
	fk_id_column VARCHAR2(60) := NVL (fk_id_col_in, '_ID');
	fk_nm_column VARCHAR2(60) := NVL (fk_nm_col_in, '_NM');

	cursor_handle INTEGER := DBMS_SQL.OPEN_CURSOR;
	dbms_sql_feedback INTEGER;
	/*
	|| The return value of the function. Notice that even though one
	|| of the parameters now specifies a maximum size for the return
	|| value, I still do have to hard-code a size in my declaration.
	*/
	return_value VARCHAR2(100) := NULL;

	/*---------------------- Local Module ---------------------------*/

	PROCEDURE convert_column (col_name_inout IN OUT VARCHAR2)
		RETURN VARCHAR2
	/*
	|| Construct the column name. If the argument begins with a "_",
	|| use as suffix to table name. Otherwise, substitute completely.
	*/
	IS
	BEGIN
		IF SUBSTR (col_name_inout, 1, 1) = '_'
		THEN
			col_name_inout := fk_table_in || col_name_inout;
		ELSE
			/* Default value on variable declaration already handles it */
			NULL;
		END IF;
	END;

BEGIN
	/* Convert the column names as necessary based on arguments */
	convert_column (fk_id_column);
	convert_column (fk_nm_column);

	/* Parse statement using converted column names */
	DBMS_SQL.PARSE
		(cursor_handle,
		 'SELECT ' || fk_nm_column ||
		 '  FROM ' || fk_table_in ||
 		 ' WHERE ' || fk_id_column || ' = :fk_value',
		 DBMS_SQL.NATIVE);

	/* This code is same as in version 1 */
	DBMS_SQL.BIND_VARIABLE (cursor_handle, 'fk_value', fk_id_in);
	DBMS_SQL.DEFINE_COLUMN (cursor_handle, 1, fk_nm_column, max_length_in);
	dbms_sql_feedback := DBMS_SQL.EXECUTE (cursor_handle);
	dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cursor_handle);
	IF dbms_sql_feedback > 0
	THEN
		DBMS_SQL.COLUMN_VALUE (cursor_handle, 1, return_value);
	END IF;
	DBMS_SQL.CLOSE_CURSOR (cursor_handle);
	RETURN return_value;
END;

create or replace FUNCTION fk_name /* VERSION 3 */
   (fk_id_in IN INTEGER,
    fk_table_in IN VARCHAR2,
    fk_id_col_in IN VARCHAR2 := '_ID',
    fk_nm_col_in IN VARCHAR2 := '_NM',
    max_length_in IN INTEGER := 100,
    where_clause_in IN VARCHAR2 := NULL)
RETURN VARCHAR2
/* I will not repeat any comments from first version of fk_name. */
IS
   /*
   || Local variables to hold column names, since I must construct
   || those names based on the values provided. If the column names
   || are NULL, then fall back on the defaults.
   */
   where_clause VARCHAR2(1000);
   perform_bind BOOLEAN := TRUE;

   fk_id_column VARCHAR2(60) := NVL (fk_id_col_in, '_ID');
   fk_nm_column VARCHAR2(60) := NVL (fk_nm_col_in, '_NM');

   cursor_handle INTEGER := DBMS_SQL.OPEN_CURSOR;
   dbms_sql_feedback INTEGER;
   /*
   || The return value of the function. Notice that even though one
   || of the parameters now specifies a maximum size for the return
   || value, I still do have to hard-code a size in my declaration.
   */
   return_value VARCHAR2(100) := NULL;

   /*---------------------- Local Module ---------------------------*/

   PROCEDURE convert_column (col_name_inout IN OUT VARCHAR2)
   /*
   || Construct the column name. If the argument begins with a "_",
   || use as suffix to table name. Otherwise, substitute completely.
   */
   IS
   BEGIN
      IF SUBSTR (col_name_inout, 1, 1) = '_'
      THEN
         col_name_inout := fk_table_in || col_name_inout;
      ELSE
         /* Default value on variable declaration already handles it */
         NULL;
      END IF;
   END;

BEGIN
   /* Convert the column names as necessary based on arguments */
   convert_column (fk_id_column);
   convert_column (fk_nm_column);

   IF UPPER (where_clause_in) LIKE 'AND%' OR
      UPPER (where_clause_in) LIKE 'OR%'
   THEN
      /* Append the additional Boolean expressions to default */
      where_clause :=
         ' WHERE ' || fk_id_column || ' = :fk_value ' || where_clause_in;

   ELSIF where_clause_in IS NOT NULL
   THEN
      /* Substitute completely the WHERE clause */
      where_clause := ' WHERE ' || where_clause_in;
   	  perform_bind := FALSE;

   ELSE
      /* Just stick with default */
       where_clause := ' WHERE ' || fk_id_column || ' = :fk_value';
   END IF;

   /* Now the call to PARSE uses the pre-processed WHERE clause */
  dbms_output.put_line (where_clause);
   DBMS_SQL.PARSE
      (cursor_handle,
       'SELECT ' || fk_nm_column || '  FROM ' || fk_table_in || where_clause,
       DBMS_SQL.NATIVE);

      /* This code is same as in version 1 */
   IF perform_bind
   THEN
      DBMS_SQL.BIND_VARIABLE (cursor_handle, 'fk_value', fk_id_in);
   END IF;
   DBMS_SQL.DEFINE_COLUMN (cursor_handle, 1, fk_nm_column, max_length_in);
   dbms_sql_feedback := DBMS_SQL.EXECUTE (cursor_handle);
   dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cursor_handle);
   IF dbms_sql_feedback > 0
   THEN
      DBMS_SQL.COLUMN_VALUE (cursor_handle, 1, return_value);
   END IF;
   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   RETURN return_value;
END;
/