How would we go about getting the table name as a varchar2 when using table macros?
Oracle 19c database
Example table macro, where we would like to get the table name of the variable 't':
CREATE FUNCTION take (n NUMBER, t DBMS_TF.TABLE_T)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
-- TODO do something with the table name
-- if (t.name = 'foo') then
RETURN 'SELECT * FROM t FETCH FIRST take.n ROWS ONLY';
END;
/
The table_t definition does not include the name of the table:
TYPE TABLE_T IS RECORD(
column TABLE_COLUMNS_T,
schema_name DBMS_id,
package_name DBMS_id,
ptf_name DBMS_id);
CREATE OR REPLACE FUNCTION gettablename (t DBMS_TF.table_t)
RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
RETURN 'SELECT '''|| t.table_name || ''' as table_name FROM DUAL';
END;
/
The doc is lying: there is a "table_name" field in the record:
TYPE TABLE_T IS RECORD
(
column TABLE_COLUMNS_T,-- Column information, TABLE_COLUMNS_T is a TABLE OF COLUMN_T
schema_name dbms_quoted_id, -- the schema name OF ptf
package_name dbms_quoted_id, -- the package name OF ptf
ptf_name dbms_quoted_id, -- the ptf name invoked
table_schema_name dbms_quoted_id, -- schema name table
table_name dbms_quoted_id -- table name
);