Converting SQL Server Query to Oracle Query

Question

I have SQL Server database query:-

          SELECT DISTINCT
                 info.COLUMN_NAME AS NAME,
                 info.TABLE_SCHEMA AS TABLESCHEMA,
                 info.TABLE_NAME AS TABLENAME,
                 info.ORDINAL_POSITION AS POSITION, 
                 info.IS_NULLABLE AS ISNULLABLE,
                 info.DATA_TYPE AS DATATYPE,
                 tc.CONSTRAINT_TYPE AS CONSTRAINTTYPE,
                 kcufk.TABLE_SCHEMA AS REFRENCESCHEMA, 
                 kcufk.TABLE_NAME AS REFRENCETABLE,
                 kcufk.COLUMN_NAME AS REFRENCECOLUMN
            FROM INFORMATION_SCHEMA.COLUMNS info
 LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
              ON kcu.COLUMN_NAME = info.COLUMN_NAME
             AND kcu.TABLE_NAME = info.TABLE_NAME
 LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
              ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
             AND tc.TABLE_NAME = kcu.TABLE_NAME
 LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rk 
              ON rk.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
 LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcufk 
              ON kcufk.CONSTRAINT_NAME = rk.UNIQUE_CONSTRAINT_NAME
           WHERE info.TABLE_NAME = 'Category'
             AND info.TABLE_SCHEMA = 'core' 
        ORDER BY info.ORDINAL_POSITION ASC;

How can I convert this SQL Server query into an Oracle query?

Answer 1

SELECT tc.column_name,
       tc.owner,
       tc.table_name,
       tc.column_id,
       tc.nullable,
       tc.data_type,
       c.constraint_type,
       c.r_owner AS reference_owner,
       rcc.table_name AS reference_table,
       rcc.column_name AS reference_column_name
FROM   ALL_TAB_COLUMNS tc
       LEFT OUTER JOIN ALL_CONS_COLUMNS cc
       ON ( tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name = cc.COLUMN_NAME )
       LEFT OUTER JOIN ALL_CONSTRAINTS c
       ON ( tc.owner = c.owner AND tc.table_name = c.table_name AND c.constraint_name = cc.constraint_name )
       LEFT OUTER JOIN ALL_CONS_COLUMNS rcc
       ON ( c.r_owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name )
WHERE  tc.table_name = 'CATEGORY'
AND    tc.OWNER      = 'CORE'
ORDER BY tc.column_id;