DBLinks and oracle_fdw

[denoley]

In Oracle a have View to access to other table via dblink.

CREATE OR REPLACE FORCE VIEW BLANKS_V
(
    BLANK_ID,  BLANK_SERIES,    BLANK_NUMBER,    BLANK_TYPE_ID,    OLD_BLANK_STATUS,
    NEW_BLANK_STATUS,    CHANGE_DATE,    CONTRACT_NUMBER,    RECORD_DATE,
    OPER_TYPE,    OPER_SYST,    RECORD_STATE,    VERSION,    BRANCH_ID,    CONTRACT_ID,
    HOLDING_COMPANY_ID
)
AS
    SELECT BLANK_ID,           BLANK_SERIES,           BLANK_NUMBER,           BLANK_TYPE_ID,
           OLD_BLANK_STATUS,           NEW_BLANK_STATUS,           CHANGE_DATE,
           CONTRACT_NUMBER,           RECORD_DATE,           OPER_TYPE,           OPER_SYST,
           RECORD_STATE,           VERSION,           BRANCH_ID,           CONTRACT_ID,
           HOLDING_COMPANY_ID
      FROM BLANK_LOG4RSA@DL_BLANK_RSA
    UNION ALL
    SELECT BLANK_ID,           BLANK_SERIES,           BLANK_NUMBER,           BLANK_TYPE_ID,
           OLD_BLANK_STATUS,           NEW_BLANK_STATUS,           CHANGE_DATE,
           CONTRACT_NUMBER,           RECORD_DATE,           OPER_TYPE,           OPER_SYST,
           RECORD_STATE,           VERSION,           BRANCH_ID,           CONTRACT_ID,
           HOLDING_COMPANY_ID
      FROM BLANK_LOG4RSA@DL_BLANK_EKIS;

Ora2Pg generates DDL:

CREATE OR REPLACE VIEW blanks_v (blank_id, blank_series, blank_number, blank_type_id, old_blank_status, new_blank_status, change_date, contract_number, record_date, oper_type, oper_syst, record_state, version, branch_id, contract_id, holding_company_id) AS 
  SELECT  BLANK_ID,          BLANK_SERIES,          BLANK_NUMBER,          BLANK_TYPE_ID,
          OLD_BLANK_STATUS,          NEW_BLANK_STATUS,          CHANGE_DATE,
          CONTRACT_NUMBER,          RECORD_DATE,          OPER_TYPE,          OPER_SYST,
          RECORD_STATE,          VERSION,          BRANCH_ID,          CONTRACT_ID,
          HOLDING_COMPANY_ID
      FROM BLANK_LOG4RSA@DL_BLANK_RSA
UNION ALL
   SELECT BLANK_ID,          BLANK_SERIES,          BLANK_NUMBER,          BLANK_TYPE_ID,
          OLD_BLANK_STATUS,          NEW_BLANK_STATUS,          CHANGE_DATE,
          CONTRACT_NUMBER,          RECORD_DATE,          OPER_TYPE,          OPER_SYST,
          RECORD_STATE,          VERSION,          BRANCH_ID,          CONTRACT_ID,
          HOLDING_COMPANY_ID
     FROM BLANK_LOG4RSA@DL_BLANK_EKIS;

If i'm using oracle_fdw, correct steps would be:

1. Create foreign tables:

   
   create foreign table blank_log4rsa_dl_blank_rsa
   ( 
   BLANK_ID            VARCHAR(25),
   BLANK_SERIES        VARCHAR(50),
   BLANK_NUMBER        VARCHAR(50),
   BLANK_TYPE_ID       VARCHAR(1),
   OLD_BLANK_STATUS    VARCHAR(3),
   NEW_BLANK_STATUS    VARCHAR(3),
   CHANGE_DATE         timestamp,
   CONTRACT_NUMBER     VARCHAR(50),
   RECORD_DATE         timestamp,
   OPER_TYPE           VARCHAR(1),
   OPER_SYST           integer,
   RECORD_STATE        integer,
   VERSION             integer,
   BRANCH_ID           VARCHAR(50),
   CONTRACT_ID         VARCHAR(25),
   HOLDING_COMPANY_ID  VARCHAR(25) 
   )  server dl_blank_rsa options (table 'BLANK_LOG4RSA');

create foreign table blank_log4rsa_dl_blank_ekis ( BLANK_ID VARCHAR(75), BLANK_SERIES VARCHAR(150), BLANK_NUMBER VARCHAR(150), BLANK_TYPE_ID VARCHAR(3), OLD_BLANK_STATUS VARCHAR(9), NEW_BLANK_STATUS VARCHAR(9), CHANGE_DATE timestamp, CONTRACT_NUMBER VARCHAR(150), RECORD_DATE timestamp, OPER_TYPE VARCHAR(3), OPER_SYST integer, RECORD_STATE integer, VERSION integer, BRANCH_ID VARCHAR(75), CONTRACT_ID VARCHAR(75), HOLDING_COMPANY_ID VARCHAR(25) ) server dl_blank_ekis options (table 'BLANK_LOG4RSA');

2. Create result view:

CREATE OR REPLACE VIEW blanks_v (blank_id, blank_series, blank_number, blank_type_id, old_blank_status, new_blank_status, change_date, contract_number, record_date, oper_type, oper_syst, record_state, version, branch_id, contract_id, holding_company_id) AS SELECT BLANK_ID, BLANK_SERIES, BLANK_NUMBER, BLANK_TYPE_ID, OLD_BLANK_STATUS, NEW_BLANK_STATUS, CHANGE_DATE, CONTRACT_NUMBER, RECORD_DATE, OPER_TYPE, OPER_SYST, RECORD_STATE, VERSION, BRANCH_ID, CONTRACT_ID, HOLDING_COMPANY_ID FROM blank_log4rsa_dl_blank_rsa UNION ALL SELECT BLANK_ID, BLANK_SERIES, BLANK_NUMBER, BLANK_TYPE_ID, OLD_BLANK_STATUS, NEW_BLANK_STATUS, CHANGE_DATE, CONTRACT_NUMBER, RECORD_DATE, OPER_TYPE, OPER_SYST, RECORD_STATE, VERSION, BRANCH_ID, CONTRACT_ID, HOLDING_COMPANY_ID FROM blank_log4rsa_dl_blank_ekis;

Is it possible to automate creation of foreign table, which accessed by dblinks (not only in views, but in plsql code too)? The foreign table name would be `tableName_dblinkName`

[darold]

It could be possible to automate if we can obtain the definition of the remote table. Do you know if it is possible to use at least DESC BLANK_LOG4RSA@DL_BLANK_RSA?

[denoley]

Yes, it is possible. You can execute this statement:

Select * from user_tab_columns@DL_BLANK_RSA
where table_name = 'BLANK_LOG4RSA'
order by column_id;

and so on.