Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed in Oracle Referenced Column DataType (oracle 19c)
Help Needed in Oracle Referenced Column DataType [message #685878] Mon, 11 April 2022 21:58 Go to next message
born2achieve
Messages: 9
Registered: July 2012
Junior Member
Hello, using the below query i can get list of all the referenced tables, Colum name and i would like to get column DataType and Datalength as well. Please help me on the query how to get the Data Type.
SELECT DISTINCT C.TABLE_NAME CHILD_TABLENAME,C.COLUMN_NAME
FROM (SELECT A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
B.COLUMN_NAME,
A.R_CONSTRAINT_NAME,
B.POSITION
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE = 'R') C,
USER_CONS_COLUMNS D
WHERE D.CONSTRAINT_NAME = C.R_CONSTRAINT_NAME
AND D.TABLE_NAME = 'Member'
AND D.POSITION = C.POSITION;
Re: Help Needed in Oracle Referenced Column DataType [message #685879 is a reply to message #685878] Tue, 12 April 2022 00:14 Go to previous messageGo to next message
jury68000
Messages: 33
Registered: October 2019
Member
SELECT DISTINCT C.TABLE_NAME CHILD_TABLENAME,C.COLUMN_NAME, c.data_type, c.data_length
FROM (SELECT A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
B.COLUMN_NAME,
A.R_CONSTRAINT_NAME,
B.POSITION,
c.data_type,
c.data_length
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B, user_tab_columns c
WHERE B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE = 'R'
--and b.owner = c.owner
and b.table_name = c.table_name
and b.column_name = c.column_name) C,
USER_CONS_COLUMNS D
WHERE D.CONSTRAINT_NAME = C.R_CONSTRAINT_NAME
--AND D.TABLE_NAME = 'Member'
AND D.POSITION = C.POSITION;




MC: Using the formatter
SELECT DISTINCT C.table_name CHILD_TABLENAME,
                C.column_name,
                c.data_type,
                c.data_length
FROM   (SELECT A.constraint_name,
               A.constraint_type,
               A.table_name,
               B.column_name,
               A.r_constraint_name,
               B.position,
               c.data_type,
               c.data_length
        FROM   user_constraints A,
               user_cons_columns B,
               user_tab_columns c
        WHERE  B.constraint_name = A.constraint_name
               AND A.constraint_type = 'R'
               --and b.owner = c.owner
               AND b.table_name = c.table_name
               AND b.column_name = c.column_name) C,
       user_cons_columns D
WHERE  D.constraint_name = C.r_constraint_name
       --AND D.TABLE_NAME = 'Member'
       AND D.position = C.position; 

[Updated on: Wed, 13 July 2022 05:40] by Moderator

Report message to a moderator

Re: Help Needed in Oracle Referenced Column DataType [message #685880 is a reply to message #685878] Tue, 12 April 2022 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query, if you don't know how to do it, learn it using SQL Formatter.

SQL> desc USER_TAB_COLUMNS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                       NOT NULL VARCHAR2(30)
 COLUMN_NAME                      NOT NULL VARCHAR2(30)
 DATA_TYPE                                 VARCHAR2(106)
 DATA_TYPE_MOD                             VARCHAR2(3 CHAR)
 DATA_TYPE_OWNER                           VARCHAR2(30)
 DATA_LENGTH                      NOT NULL NUMBER
 DATA_PRECISION                            NUMBER
 DATA_SCALE                                NUMBER
 NULLABLE                                  VARCHAR2(1 CHAR)
 COLUMN_ID                                 NUMBER
 DEFAULT_LENGTH                            NUMBER
 DATA_DEFAULT                              LONG
 NUM_DISTINCT                              NUMBER
 LOW_VALUE                                 RAW(32)
 HIGH_VALUE                                RAW(32)
 DENSITY                                   NUMBER
 NUM_NULLS                                 NUMBER
 NUM_BUCKETS                               NUMBER
 LAST_ANALYZED                             DATE
 SAMPLE_SIZE                               NUMBER
 CHARACTER_SET_NAME                        VARCHAR2(44 CHAR)
 CHAR_COL_DECL_LENGTH                      NUMBER
 GLOBAL_STATS                              VARCHAR2(3 CHAR)
 USER_STATS                                VARCHAR2(3 CHAR)
 AVG_COL_LEN                               NUMBER
 CHAR_LENGTH                               NUMBER
 CHAR_USED                                 VARCHAR2(1 CHAR)
 V80_FMT_IMAGE                             VARCHAR2(3 CHAR)
 DATA_UPGRADED                             VARCHAR2(3 CHAR)
 HISTOGRAM                                 VARCHAR2(15 CHAR)

Quote:
i would like to get column DataType and Datalength

Join with this view and add them in SELECT clause.

If you want to get further help don't forget to feedback in your topics.

[Updated on: Tue, 12 April 2022 00:21]

Report message to a moderator

Re: Help Needed in Oracle Referenced Column DataType [message #685881 is a reply to message #685880] Tue, 12 April 2022 09:27 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
AND D.TABLE_NAME = 'Member'
So you have a table where you have forced the name to be mixed-case? This is a very bad practice in oracle, as it breaks the default case-insensitivity of resolving object names.

SQL> show user
USER is "SCOTT"
SQL> -- -------- create the tables
SQL> create table Case_Insensitive
  2     (fname varchar2(10)
  3     )
  4  ;

Table created.

SQL> create table "Case_Sensitive"
  2     (fname varchar2(10)
  3     )
  4  ;

Table created.

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
Case_Sensitive
CASE_INSENSITIVE

2 rows selected.

SQL> select table_name
  2  from user_tables
  3  where table_name = 'case_sensitive';

no rows selected

SQL>
SQL> select table_name
  2  from user_tables
  3  where table_name = 'Case_Sensitive';

TABLE_NAME
--------------------------------------------------------------------------------
Case_Sensitive

1 row selected.

SQL>
SQL> select * from CaSe_InSeNsItIvE;

no rows selected

SQL> select * from case_sensitive;
select * from case_sensitive
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "Case_Sensitive";

no rows selected

SQL> -- -------- clean up
SQL> drop table case_insensitive purge;

Table dropped.

SQL> drop table Case_Sensitive purge;
drop table Case_Sensitive purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table "Case_Sensitive" purge;

Table dropped.
Previous Topic: Oracle JOIN tables
Next Topic: question on solution provided
Goto Forum:
  


Current Time: Thu Mar 28 13:20:45 CDT 2024