Java7
Oracle SQL
Finding tables containing two or more columns in Oracle Database
Nov 17th
It is really easy to find out the table that has the column that you are looking for, for example, to find out the table that has the column ‘EMPLOYEE’:
select distinct(table_name)
from all_tab_cols
where column_name = 'EMPLOYEE';
But it is not quite as straightforward if you are trying to find tables containing two columns which you are looking for.
For example, you would like to find out a table which has both employee_id and employee_name, (maybe not such a good example, but you understand!). It requires some SQL crafting as you will see!
SELECT table_name
FROM all_tab_cols
WHERE column_name IN ('EMP_ID', 'EMP_NAME')
GROUP BY table_name, owner
HAVING COUNT(column_name) = 2
Credit goes to Ponies from Stackoverflow!
ORA-06502: PL/SQL: numeric or value error (How to safeguard your stored procedures from requirement led changes?)
Jun 30th
Today we faced a particular interesting problem, we found that Oracle was throwing “ORA-06502: PL/SQL: numeric or value error”, when we did a Google quick check, it seemed that an attempt was made in the DBMS to assign a value larger than the declared size of the holder. i.e. trying to assign a 20 characters String to a 10 character varchar.
ORA-06502: PL/SQL: numeric or value error
What happened was, there was a particular table and a few fields in it with more or less fixed datatype and size, then one fine day owing to changing requirements, one of the fields had its size lengthened. I.e. from varchar2(20) to varchar2(30). The change impacted an adjacent stored procedure that was still expecting varchar2(20) size. The error “ORA-06502: PL/SQL: numeric or value error” was subsequently thrown.
Stored procedure declaring variable “pegasus_NPP” as VARCHAR(20):
Table the above stored procedure was drawing data from:
Note that the data length for the column is VARCHAR(30).
To effectively eliminate this issue is not to re-declare the variable in the stored procedure as VARCHAR(30), yes it solves the above gap but does not safeguard you from future changes.
The solution
Declare your variables as TYPEs based on the column definitions in your tables.
Use:
dept_name dept.dept_name%TYPE;Instead of:
dept_name VARCHAR2(50);Cheers!
Reference
How to minimize the coupling/dependency between physical DDL changes and PL/SQL changes?
What does “%Type” mean in Oracle sql?
History
July 5, 2011 Added link What does “%Type” mean in Oracle sql?
Selecting column_name from cols returns nothing
May 27th
In Oracle if you want to find out the table properties you are dealing with in the correct way or if you need to *search* for the table that has certain columns that you are looking for, then you probably want look at the COLS table.
select * from cols;Most of the time, the statement works and you get a result set of column names, their properties along with the tables they belong to. However, sometimes you may not.
When the above statement fails and you are very certain that you have the column name of the table you are looking for correct, this could be due to that you are connected as a user that owns no tables (or views).
In some editions of Oracle, it recommends that you do not use the table COLS as it is maintained only for compatibility.
COLS is a synonym for USER_TAB_COLUMNS and is maintained for compatibility, it should not be used where possible.
The ALL_TAB_COLUMNS table
select * from ALL_TAB_COLUMNS;
This should really be used, and it should work most of the time.
Example of showing all tables with the column like “EMP”
select table_name from ALL_TAB_COLUMNS where column_name like 'EMP%';
Reference
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2134.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2135.htm
Oracle show tables
Apr 23rd
The ‘SHOW TABLES’ is a useful command in MySQL that does not really work in or for Oracle DBMS. Here are two ways to show tables in your schema or username.
You may choose from either of the two steps below to retrieve information of your schema.
1. select * from dba_tables
You may however use this only if you have DBA privileges which i find most of the time many application developers (like myself) should not have unless in other various environments where security settings are stepped down.
SELECT owner, table_name FROM dba_tables;For example, i am connected as user hr, which is configured not to have DBA access. Oracle prompts me that the table or view does not exist. In this case, this alternative does not work for me.
2. select * from all_tables
This method of retrieving information of your tables may be used if the particular user has not been given DBA rights.
SELECT owner, table_name FROM all_tables;SELECT owner, table_name FROM all_tables WHERE owner = 'your schema or username';
Note that a schema is not a user.
Reference
http://stackoverflow.com/questions/205736/oracle-get-list-of-all-tables
Revision
April 24, 2011. Minor elaboration.
