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

Asked

Viewed 188 times

0

I have a table with 58 different columns and would like to create a View treat the fields DATE as SWEEP, without me having to discriminate all the columns in the creation of View.

CREATE VIEW VW_TESTE AS SELECT * FROM TABELA

As follows in the example:

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

And return this:

ID, BIGINT
NAME, VARCHAR
LOGIN, VARCHAR
INSCRIPTION, VARCHAR
EMAIL, VARCHAR

OR

ID, BIGINT
NAME, VARCHAR
EMAIL, VARCHAR

I tried to command "Select * from Table Where data_type = 'DATE' "

But it didn’t work.

sql database oracle

  • https://forum.imasters.com.br/topic/261439-resolver%C2%A0procedure-trigger-trigger-of-audit/ I once did this process that generates a Trigger for auditing the basic idea could be used to generate the view that would "to-char" when the field was date

  • But I confess I see no reason for this work

  • @Motta in this case I do not have DBA permission in DB Oracle with my user. Using the to_char I would have to make a view having to put all the columns. But that’s not necessarily a problem now. However, as I am working on a bigdata project, this could soon be a problem with even wider tables. In addition, I will analyze what you sent and exchange an idea with DBA. The problem is that what I am doing is only for testing.

  • But why the dates have to be char in the view ?

  • @Motta sorry the delay. So, I’m making an import to a cluster Hadoop and is giving a strange error with a selected dataset. That’s why I would like to turn it into a string, to avoid the date format that causes incompatibility.

1 answer

0


Follows example generating 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 make a script generated process or view itself

Browser other questions tagged sql database oracle

You are not signed in. Login or sign up in order to post.