Search code examples
sqloracle

How to get table name from dbms_tf.table_t in table macro


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);

Solution

  • 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
    );