Java7
Posts tagged oracle
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
Creating Oracle “AUTO_INCREMENT” column for your surrogate key
Mar 9th
Lets face it, nobody tries to create primary keys out from natural keys in today’s context, maybe due to the sheer number of database tables an application or system today requires.
Creating surrogate keys is simple as it is common to most people. Yet Oracle has made this task a non trivial one.
I have come upon the chance to create a new table supporting new business requirements. The guide steps through using Oracle SQL Developer.
Step 1: Create Table
Create a new table of your choice.
** The steps to create a table are omitted.
For the purpose of this guide, we are interested to make column STATUS_ID of table GL_RECORD_STATUS_MATRIX an auto incremented surrogate key. STATUS_ID is also the primary key of the table.
Step 2: Create Sequence
Auto incremental numbers come in the form of sequence in Oracle. It is necessary to create a sequence object.
Name your sequence. For my sequence, i have chosen “SEQ_GL_RECORD_STATUS_MATRIX” in the naming convention “SEQ” + <table name>.
Create your sequence with the following properties:
Increment 1
Min value 0
Max value 999999999
Start with 1
Step 3: Create Trigger
In the configuration dialog, name your trigger and select the table you wish to apply this trigger to. You may choose to name your sequence trigger in the convention of <table name> + “SEQ” or “SEQ” + <table name> depending on the naming scheme you prefer.
Select the “Row Level” option as we are applying this trigger on a row level.
Once you have created your trigger, SQL Developer should display the “code” of the trigger that you have just created.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
NULL;
END;
Note that between the BEGIN and END keywords is a NULL literal. This is where you fill in the “how” of this trigger.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
END;
Note that i have replaced the string literal “NULL” with the following:
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
This statement means, select the next value from the sequence i have just created, and insert into onto STATUS_ID of my table.
Compile the trigger when done.
Complete! Testing…
Test that your auto number is working by adding a new row to your table without explicitly providing a value to the STATUS_ID field. Oracle should insert the value of STATUS_ID automatically for you.
Recap…
Usually, it is not necessary to add a prefix to the name of the object that you are creating, for example, it is not necessary to name an employee table tbl_employee or table_employee. However, because Oracle does not quite allow us to create a sequence object with the same name as the table object in the same schema, we have no choice but to select another more definitive name for our sequence.
(Unable to create a sequence with the same name as the table)
A Foreign Key constraint must define at least one column
Mar 7th
Oracle is right! A foreign key constraint must define at least one column (specifically, key), how else would referential integrity work.
While working on a CR, i was required to add a foreign key cascade update and another cascade delete. I am using Oracle SQL Developer over Oracle 10g DBMS, and naturally i tried to deliver this patch through point and click. The problem came as that there were no columns visible for selection in the UI dialog for the table i was referencing to.
(There are supposed to be columns i can select from in the “Referenced Constraint” drop down menu)
Although my first instinct was that Oracle must have screwed up (yet again), but it was not. It fell back on relational theory. Read on.
On inspecting the table i was trying to reference – SERVICE_TYPE, i found the culprit – there was no primary key defined in that table!
The solution – define the rightful primary key for that table.
Solution
Define the primary key for the master table, or the table you are trying to reference your foreign key to.
Go back to your referencing table, and try to add the foreign key again. It works this time around.
This does not apply to Oracle DBMS only, rather anywhere (DBMS) with a “safe” checking mechanism akin to Java as a strongly typed language.
What to check for when such happens?
1. Check the table you are referencing, that a primary key is defined.
To quote Oracle Database 11g The Complete Reference on one good and short coverage of foreign keys.
The Foreign Key
A foreign key is a combination of columns with values based on the primary key values from another table. A foreign key constraint, also known as a referential integrity constraint, specifies that the values of the foreign key correspond to actual values of the primary key in the other table. In the BOOKSHELF table, for example, the CategoryName column refers to values for the CategoryName column in the CATEGORY table:
create table BOOKSHELF( Title VARCHAR2(100) primary key, Publisher VARCHAR2(20), CategoryName VARCHAR2(20), Rating VARCHAR2(2), constraint CATFK foreign key (CategoryName) references CATEGORY(CategoryName));You can refer to a primary or unique key, even in the same table. However, you can’t refer to a table in a remote database in the references clause. You can use the table form (which is used earlier to create a PRIMARY KEY on the TROUBLE table) instead of the column form to specify foreign keys with multiple columns.
Sometimes you may want to delete these dependent rows when you delete the row they depend on. In the case of BOOKSHELF and CATEGORY, if you delete a CategoryName from CATEGORY, you may want to make the matching BOOKSHELF CategoryName column values NULL. In another case, you might want to delete the whole row. The clause on delete cascade added to the references clause tells Oracle to delete the dependent row when you delete the corresponding row in the parent table. This action automatically maintains referential integrity. For more information on the clauses on delete cascade and references, consult “Integrity Constraint” in the Alphabetical Reference of this book.
Note that i have always referenced Oracle, or any “database product” as DBMS (Database Management System), and not by the term “Database”. A database is a flat file, Oracle is not a flat file, it is a Database Management System. This distinction is highlighted in the book by Thomas Connally and notably subject veteran Carolyn Begg in “Database Systems, A practical approach to Design, Implementation, and Management”.
