CODEWITHSUNDEEP
mysqldatabaseoracle-database
Shivam Sharma
Shivam Sharma

Reputation: 1597

Can I see columns related to particular datatypes in oracle using query?

I am 90% sure that this is not possible but I need expert advice.

Is this possible to filter out columns on the basis of datatypes using any method in select list in oracle or in any other DBMS.

eg.

select datatype('DATE') from my_table;

To see all the columns in result having datatype DATE. I know above query is not possible according to me(specially the datatype function :) ) but acknowledge if it is.

Upvotes: 0

Views: 40

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

In Oracle, You may not directly run a query containing specific datatypes. However, you can create a procedure to construct a query with table name and datatype as arguments to contain specific columns and return it as a REF CURSOR.

CREATE OR REPLACE PROCEDURE proc_getsql(
    p_table_name IN VARCHAR2,
    p_datatype   IN VARCHAR2 ,
    p_query OUT SYS_REFCURSOR )
IS
  v_query VARCHAR2(1000) ;
BEGIN
OPEN p_query FOR
  SELECT 'SELECT '
    || LISTAGG(COLUMN_NAME,',') WITHIN GROUP (
  ORDER BY column_name ) 
    ||' FROM '||p_table_name query_to_run
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = p_table_name
  AND DATA_TYPE LIKE '%'
    ||p_datatype
    ||'%';
END;

You may then call or fetch from this CURSOR depending on your requirement.

VAR x REFCURSOR;
EXEC proc_getsql('EMPLOYEES','DATE', :x);
PRINT :x;

Output

QUERY_TO_RUN                                  
------------
SELECT HIRE_DATE,DATE_OF_BIRTH FROM EMPLOYEES

Upvotes: 1

Related Questions