Create transform view by deleting or transforming DATE into VARCHAR in ORACLE

Question

Create transform view by deleting or transforming DATE into VARCHAR in ORACLE

Navigation

#1 by (0 votes)

0

I have a table with 58 different columns and would like to create a View that treats the DATE fields as VARCHAR , without my having which discriminate all columns in the View creation.

CREATE VIEW VW_TESTE AS SELECT * FROM TABELA

As in the example:

ID, BIGINT
NAME, VARCHAR
LOGIN, DATE
REGISTRATION, DATE
EMAIL, VARCHAR

And return this:

ID, BIGINT
NAME, VARCHAR
LOGIN, VARCHAR
INSCRICAO, VARCHAR
EMAIL, VARCHAR

OR

ID, BIGINT
NAME, VARCHAR
EMAIL, VARCHAR

I tried to make the command "Select * from Table where data_type = 'DATE'"

But it did not work.

sql oracle

asked by anonymous 17.05.2017 / 19:06

1 answer

Delete in sql - Inner Join CSS - inputs are not aligned

score 0 · Accepted Answer

The following example generates dbms_output, but a basic idea, generates the script

DECLARE
  VN_C NUMBER := 0;
  VN_COLUNAS NUMBER;
BEGIN
  SELECT COUNT(COLUMN_NAME) INTO VN_COLUNAS 
  FROM   USER_TAB_COLUMNS
  WHERE  TABLE_NAME = 'NOMETABELA';
  DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE VIEW V_NOMETABELA AS SELECT ');
  FOR R IN (SELECT (CASE WHEN DATA_TYPE = 'DATE' THEN 'TO_CHAR('  || COLUMN_NAME || ',' || '''DD/MM/YYYY''' || ') ' || COLUMN_NAME 
                         ELSE COLUMN_NAME END) COLUNA
            FROM   USER_TAB_COLUMNS
            WHERE  TABLE_NAME = 'NOMETABELA')
  LOOP
    VN_C := VN_C + 1;
    IF VN_C < VN_COLUNAS THEN
      DBMS_OUTPUT.PUT_LINE(R.COLUNA || ',');
    ELSE
      DBMS_OUTPUT.PUT_LINE(R.COLUNA);
    END IF;  
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('FROM NOMETABELA');
END;

You can do a generated script procedure or the view itself