CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Michał M
Michał M

Reputation: 618

Oracle, generate Where clause based on table names

Scenario: I'm passing 2 table names (f.e. Employees and departments) to pl/sql procedure. I want to this procedure generate WHERE clause like 'WHERE employees.department_id = departments.department_id' so basically this procedure must find key relations.

I tried to use all_constraints table to find constraint_type='P' or 'F' and then find associated column, but in this table there is no information about which column is used in constraint. Does anybody have an idea how can I do this (mayby in diffrent way)?

Upvotes: 0

Views: 226

Answers (2)

James
James

Reputation: 3411

How about using a Natural Join?

If the column names are consistent across two tables, the Natural Join clause will join the tables on each common column name.

For example, from the documentation:

https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html

If the tables COUNTRIES and CITIES have two common columns named
COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES

SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)

Upvotes: 0

starko
starko

Reputation: 1149

Try this code:

SELECT UC.OWNER
,      UC.CONSTRAINT_NAME
,      UCC1.TABLE_NAME||'.'||UCC1.COLUMN_NAME "CONSTRAINT_SOURCE"
,      'REFERENCES'
,      UCC2.TABLE_NAME||'.'||UCC2.COLUMN_NAME "REFERENCES_COLUMN"
FROM USER_CONSTRAINTS uc
,    USER_CONS_COLUMNS ucc1
,    USER_CONS_COLUMNS ucc2
WHERE UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
  AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
  AND UCC1.POSITION = UCC2.POSITION -- Correction for multiple column primary keys.
  AND UC.CONSTRAINT_TYPE = 'R'
ORDER BY UCC1.TABLE_NAME
,        UC.CONSTRAINT_NAME;

Upvotes: 1

Related Questions