SQL & PL/SQL

Hi PL/SQL developers, i having issue in regard to the using PTF (Polymorphic Table Functions) and Binding here my current setup and what i want to achieve:

• I'm trying to globalize my company app in Oracle Apex, to do there are Oracle Apex did provide a way to do this by, which i did for some to test thing out. It work fine for stub that is not changeable by the End-User like navigation, header, button, etc…

• But for column value it an hassle, to change column value you need to use the Dynamic translation and wrap you col around it like this apex_lang.lang(type). This work for column that is not defined by the End-User but some are, for example employee role which can be added by the End-User as the company expand…

• So i split it out to different table that can be alter by the End-User

• 

• So to show a table base on the user language preference i can do this

• select emp.id,
  	   case :FSP_LANGUAGE_PREFERENCE
  	   	when 'en' then col_en
  	   	when 'vi' then col_vi
  	   end case as role
  from employee emp
  left join test
  	on test.id = emp.id__test;
  

• But each table of mine have several of those so it get really annoying really quick, not to mention that to add a new language support require a refactor of every query

My current solution and issue with it:

• CREATE OR REPLACE PACKAGE pkg_globalization_ptf AS
     FUNCTION describe (
        tab      IN OUT dbms_tf.table_t,
        col_name VARCHAR2,
        lang_id  VARCHAR2
     ) RETURN dbms_tf.describe_t;
  END pkg_globalization_ptf;
  /
  CREATE OR REPLACE PACKAGE BODY pkg_globalization_ptf AS
     FUNCTION describe (
        tab      IN OUT dbms_tf.table_t,
        col_name IN VARCHAR2,
        lang_id  IN VARCHAR2
     ) RETURN dbms_tf.describe_t AS
        l_col_name VARCHAR2(100) := '"' || upper(col_name);
        l_lang_id  VARCHAR2(2) := upper(lang_id);
     BEGIN
        dbms_output.put_line(l_lang_id);
        CASE l_lang_id
           WHEN 'VI' THEN
              l_col_name := l_col_name || '_VI"';
           ELSE -- default to en
              l_col_name := l_col_name || '_EN"';
        END CASE;
        dbms_output.put_line(l_col_name);
        -- assumed col 1 is id and alway pass it through
        tab.column(1).pass_through := TRUE;
        FOR i IN 2..tab.column.count() LOOP
           dbms_output.put_line(i);
           tab.column(i).pass_through := tab.column(i).description.name = l_col_name;
           IF tab.column(i).pass_through THEN
              tab.column(i).description.name := upper(col_name);
           END IF;
        END LOOP;
        return null;
     END;
  END pkg_globalization_ptf;
  /
  CREATE OR REPLACE FUNCTION func_get_globalized (
     tab      TABLE,
     col_name VARCHAR2,
     lang_id  VARCHAR2
  ) RETURN TABLE
     PIPELINED ROW POLYMORPHIC
  USING pkg_globalization_ptf;
  

• which work as i want

• SELECT * FROM func_get_globalized(test, 'col', 'vi' );
  

  

• SELECT * FROM func_get_globalized(test, 'col', 'en');
  

  

• But when i use Structure Binding like :FSP_LANGUAGUE_PREFERENCE and manually bind it to ‘vi’ then i got ‘en’ instead. After some printing debug later i realize the issue is my package wasn't receiving lang_id.

• I asked some AI and it said this is due to the describe() is running at compile time and Binding is running at run time so at compile time the Binding is null. It recommended fix which didn't work.