Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Antoine.

Asked: December 03, 2002 - 5:43 pm UTC

Last updated: February 06, 2011 - 11:50 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would like to have your point of view on naming convention?
For exemple, do you limit the number of characeter (let say 20) for the name of a table?

Does Oracle provide a template for the naming convention in the database?

Thanks,


Antoine

and Tom said...

I have no naming conventions personally. I don't like to type really long names, but I have hit the 30 character limit from time to time.

Oracle per-say does not. Tools like Designer can help you enforce one. It is a matter of choice more then anything.


The only naming conventions I have are in PLSQL.

local variables must start with L_
parameters must start with P_
global package variables must start with G_

That is so a Parameter ENAME won't get confused with a column named ENAME in a query -- I never would have a column p_ename -- so there is no ambiguity.


Beyond that (and beyond disliking prefixing column names with their table names -- talk about pedantic) I haven't any.



Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

But...

A reader, December 03, 2002 - 9:24 pm UTC

Tom,
Thanks for sharing your thoughts. But don't you think that there should be some recommended guidelines or standard practices from Oracle. Oracle has come a long way now, and the time has come to put atleast some guidelines. I am sure that people will atleast think in this direction. Otherwise, it is really a difficult task especially for consultant who is running between projects, and can not make any educated guess that table names may be starting with tb or ending with tb etc., or code columns are starting/ending with cde, code or cd. Date columns is a typical example, some sites have names having dte, date, dt etc., and views are hard to identify by name until and unless you check for object type and list goes on. One of the good example is about OFA. Most of the sites are following OFA, and DBA need not to spend much time in understanding how the file system is organised etc.
Thanks


Tom Kyte
December 04, 2002 - 7:55 am UTC

But.... the same is true of C, of VB (remember MS trying to get us to use "hungarian notation" -- variables were named after their datatypes and case was important and all that -- i despised that when I was forced to program in windows) and anything.

The military has naming conventions out the wazoo -- mil std 8320.1 for example. For all the good it did.

naming conventions are a very personal thing. You either love them or they are foisted on you. It is upto a company or group to come to some conclusion as to what is best for them.



Could you comment on this convention please?

Ramakrishna. V, December 03, 2002 - 11:35 pm UTC

Dear Tom,
I have been waiting to post this for a long time but could never get past the queue. Actually, I have been unable to post a question for the last 3 weeks from 9.30 AM to 7.30 PM IST which is 4 AM to 2 PM GMT. Do you have any suggestions on what time of day would be best to try?

Since this question has come up now anyway, could you
kindly review the following convention critically?


Object Naming Conventions:
-------------------------
1. ALL objects in an application use a common 2 or 3 letter prefix
2. Trigger names end with _TR, package names with _PKG and proc names
with _PR
3. The fact that an object is a table or view is NOT encoded in the
object's name to avoid application changes if a view changes to
a table later.


Column Naming Conventions:
--------------------------
1. Every table will have a single column numeric primary key called
'ID'. In case the table needs a multi-column primary key, this
will instead be replaced with a multi-column unique constraint in
addition to having the surrogate ID as primary key.
2. Column names will have a short description of the contents. They
do NOT have a table prefix (not normally) and do not encode the
data type. Examples of column names are SHORT_DSC, LONG_DSC,
START_DATE, etc.
3. Foreign key columns will have the table name (or an abbreviation of
that) prefixed to ID. For example, a table called MS_COMPANY that has
a foreign key to MS_COUNTRY will have a column COUNTRY_ID.
4. Denormalized columns from other tables will also have a table name
prefix. For example, in the above table where MS_Company has a foreign
key to MS_Country, if I wanted to store the Country's description also
since it is used frequently, then that will be called COUNTRY_SHORT_DSC


To sum up, all the table's 'own' columns will be un-prefixed while the
foreign key columns will be prefixed with a table name.

Are there some written (or unwritten) naming conventions (especially column
naming) that Oracle follows or recommends throughout its RDBMS, the
applications it develops, etc?

thanks,
Ramakrishna

Tom Kyte
December 04, 2002 - 8:15 am UTC

4 AM to 2 PM GMT is 11pm to 9am EST which is where I am. I generally start attacking these about 7/7:30am mytime. 9-10am (got other things I do) is when the queue opens up again. I do a batch at night as well -- opening it up around 8/9pm typically. you are right on the begin/end of my times (i sleep in between ;)

objects:

1) sounds good -- we do that here lots. All of the asktom tables start with wwc_ for example.

2) I've always used "meaningfulname_BIFER" for triggers where BIFER might be AIFER, AU, etc:

bifer = before insert, for each row
bi = before insert
budfer = before update/delete, for each row
aiudfer = after insert/update/delete, for each row
and so on...

I many times use _PKG for packages to set them apart from tables.

I don't use procedures for real code -- very bad practice. I use procedures for standalong utilities like print_table, show_space and such. It would just be a hassle to have _PR (like SQLServer with "sp_" on the front, yuck). So, since I don't use procedures for "real" code, I don't have any preferences there other then the name should be meaningful -- i don't put _pr (what do you do for functions? _fu?)

3) good idea, a view is a table, a table is a view as far as I'm concerned. There should be no difference between them.

columns:

1) Sure, almost every table I have has one of these

2) perfect

3) that sounds like a good idea, yes.

4) ok -- if you do this denormalization -- which is generally frowned upon...

Within Oracle -- the "older" APPS team (HR, FinApps -- ERP stuff) has a very long and detailed set of coding standards. the "newer" Apps team (crm) has one as well -- they are now merged into one team "APPS" and are consolidating their methods. Yes, they have a very formal one.

The kernel code -- written in C -- has coding standards, naming conventions as well -- they are necessarily different then the ones APPS uses, different languages, different "things"

The dictionary guys -- well, sure but it changes over time and they don't roll the changes back into the established dictionary over time so you see some inconsistencies there....




naming convention

Antoine Bocti, December 04, 2002 - 7:22 am UTC

Thanks Tom,

BTW: Do you think Oracle is going to increase the 30 characters limit in futur version?

Tom Kyte
December 04, 2002 - 8:27 am UTC

I'm not aware of any plans to do so personally.

Roll your own

Sean, December 04, 2002 - 7:24 am UTC

Your best bet would be to look for some standard naming convention practices online, and produce some type of standard that you always use. About three years ago, we came up with a five page naming conventions & coding practices document that I memorized about 2.9 years ago and never looked at again. It closely resembled most of Oracle's supplied packages coding practices and naming conventions, simply because it looked like their code. If you look in $ORACLE_HOME/rdbms/admin/* you'll find LOTS of example code to go by. You can also just do a google search on Oracle Naming Conventions and check out one of the 22,200 hits it finds ;-). Good luck

naming conventions

Ed, December 04, 2002 - 8:27 am UTC

You could click your way to </code> http://examples.oreilly.com/orbestprac/ <code>which points to a zip file containing some suggested naming conventions (related to Steve Feuerstein's book).

Personally, I use Tom's l_, g_ and p_ prefixes and tailor anything else to the demands of pedantic management and DBAs or what is appropriate to the application I am developing.

Naming Conventions - QMS

jan van mourik, December 04, 2002 - 11:45 am UTC

A while ago, around Oracle 6 as I recall correctly, Oracle had something called QMS. A set of standards for design, development. It might have been written by Oracle UK, I kinda forgot. I used to have a copy of it back in the Netherlands but somehow somewhere I lost it.
Just wondering if anybody heard of it?

Oops..

jan, December 04, 2002 - 11:46 am UTC

"as I recall correctly"

IF I recall correctly -- my memory isn't that good...

global pkg variables names...

Robert, December 04, 2002 - 12:00 pm UTC

>> The only naming conventions I have are in PLSQL.
>>local variables must start with L_
>>parameters must start with P_
>>global package variables must start with G_

global package variables --- defined in spec or just body --- does that make any difference to you in terms of naming convention ?

Tom Kyte
December 04, 2002 - 1:27 pm UTC

spec or body -- doesn't matter to me ( although, I try to keep them out of the spec unless I really really have to )

Great description.......'pedantic'!

Robert, December 04, 2002 - 4:15 pm UTC

We should have strong simple skeleton of standards similar to Tom and Ramakrishna (above), etc.
After that it is up to the individual developer...
More than this is oppressive... less than this is chaos.

Thanks,

Robert.

What about cursors

Chris, December 04, 2002 - 4:23 pm UTC

I would add a convention for arguments to cursors so that there can be no confusion in the select statement - which can access global and local variables and procedure arguments. I think accessing global and local variables and procedure arguments within the cursor is a bad thing as it is not obvious at the point the cursor is invoked.

Tom Kyte
December 04, 2002 - 4:41 pm UTC

Being one that never uses EXPLICIT cursors -- i have no comment.

You'll not be seeing code from me that has:

cursor c is .....

in it. It'll all be


select ... into ...from ...

or
for x in (select .....)

or
open <some cursor variable that starts with L_ or P_> for select.....


;)



Keep it simple or no one will use it...

Colin Davies, December 04, 2002 - 7:43 pm UTC

Here is a simple set of conventions I use (and document in each package specification file):

Naming Convention Used (identifier prefixes)

gt_ Global Type
gc_ Global Constant
gv_ Global Variable
gcur_ Global Cursor Definition

t_ Local Type
c_ Local Constant
v_ Local Variable
cur_ Local Cursor Definition


i_ In Parameter
io_ In Out Parameter
o_ Out Parameter

excp_ Exception


Hate Explicit Cursor

Robert, December 05, 2002 - 10:16 am UTC

>> You'll not be seeing code from me that has:
>> cursor c is .....

Hi Tom, been wanting to ask you this...
So we know you hate Exp Cursor, and especially the Fetch Into/Loop part, (but Explicit-Cursor-For-Loops are cool with you), So you have implicit cursor dotted all over your app.
How would you say to a co-worker or boss who believes
strongly in the benefit of Explicit Cursor in "code organization" and that SELECTs should be easily visible and
collected in declaration section as much as possible ?
Thanks

Tom Kyte
December 05, 2002 - 11:30 am UTC

IMPLICIT cursor for loops

for x in ( select ... )



I would say we have a difference of opinion. I find code such as:


open c;
loop
fetch c into record
exit when c%notfound
...
end loop


or
for rec in c
loop
...
end loop;


Lots less readable, understandable (I'm asked to look at lots of code) than

for x in ( select * from all_users )
loop
...
end;


You know -- what the HECK is "C" -- oh wait, I have to page up 5 times to read what "C" was, now page back down -- oh wait, what table did the 4th column come from? Oh yeah -- page up, page down. I find it to flow alot better when the SQL is plainly visible, in line, right there. If the sql gets heinously large, well, that is what a VIEW is all about in my opinion.


Matter of taste I guess, but from a guy who looks at lots of other peoples PLSQL -- I can say that having the SQL inlined is infinitely easier for me.



Cursor Update

Robert, December 05, 2002 - 12:24 pm UTC

Hi Tom,

I agree, your cursor is much more readable. Can you do a "select for update" this way? If so how do you refer to it in your "where current of" clause?

Tom Kyte
December 05, 2002 - 12:40 pm UTC

I generally see select for update on single rows (select into for update) - hardly ever in a loop but in an implicit cursor loop (where you by definition don't have a cursor "named") you DIY:

for x in ( select rowid rid, a.* from t a for update )
loop
...
update t set ... where rowid = rid; /*+ instead of where current of */


if you check out a sql_text of a where current of update -- you'll find (surprisingly) that plsql just turns that into "where rowid = :rid" ;) anyway.

declaring cursors

Ed, December 05, 2002 - 12:45 pm UTC

But Tom, I'm SURE I've read elsewhere on your site that you always try and code to fit each routine on a screen, so paging up and down won't be an issue for you ;-)

An argument I heard for declaring cursors is in case you want to use it more than once in a routine, though I would have thought if this is required, the code should be modularized to another level.

If pedantic management / DBAs insist on having cursors in the DECLARE section, you can at least make them more meaningful e.g. "c_get_outstanding_payments" instead of "c".

Tom Kyte
December 05, 2002 - 1:00 pm UTC

but -- if you hide the cursor at the top of the package, or in the declare section (which I don't count as code! ;)....



implicit or explicit cursors

Bruno, December 06, 2002 - 6:07 am UTC

Hi Tom,

always amazed by your responses. Really great! Please excuse me to ask you the following...
I dislike the
open c;
loop
fetch c into record
exit when c%notfound
...
end loop;
way, but I "always" use
cursor c1 is ...
for r1 in c1
...
I find "your" way 'for r in ( select ... ) ...' less readable because I prefer to separate the distinct parts of the code; I mean that, for ex., instead of writing a long list of instructions (a program), people usually define functions and procedures and ultimately, the main program is made readable because it is a "short" list of tests ans calls to procedures.

(
To give another example: it can be that re-assigning the same value again and agin in a loop is more readable, but this is not a good idea:
loop
x := 'value';
... (x not modified)
end loop;
)

Anyway, you write about the human being point of view (useful when READING the code, not when EXECUTING it), and this is my question:
What does the kernel prefers?
Maybe we spend a lot of time on writing / modifying applications, but I hope that the code will be used many many times, so we have to write in an "optimize execution"-minded way.

Best regards and tanks a lot for the time spent!


Tom Kyte
December 06, 2002 - 7:35 am UTC

I don't see the correlation between your example of assigning a constant in a loop (inefficient code, bad code) and using a cursor for loop:

   for x in ( select ... )


seems to be comparing apples and flying toaster ovens.

Anyway, you seem to consider the SQL statement like a function call.  I simply don't see it that way.  I see it (the sql statment) as being the single most important thing in the procedure itself.  I prefer to see it inline.

We can see which is "better" by using runstats (seems I've put this up so many times that others would start using it!!) 
http://asktom.oracle.com/~tkyte/runstats.html


ops$tkyte@ORA920> create or replace package demo_pkg
  2  as
  3          procedure explicit;
  4          procedure implicit;
  5  end;
  6  /

Package created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package body demo_pkg
  2  as
  3          procedure explicit
  4          is
  5                  cursor c is select * from all_users;
  6          begin
  7                  for x in c
  8                  loop
  9                          null;
 10                  end loop;
 11          end;
 12
 13          procedure implicit
 14          is
 15          begin
 16                  for x in ( select * from all_users )
 17                  loop
 18                          null;
 19                  end loop;
 20          end;
 21  end;
 22  /

Package body created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5  begin
  6      insert into run_stats select 'before', stats.* from stats;
  7
  8      l_start := dbms_utility.get_time;
  9          for i in 1 .. 1000
 10          loop
 11                  demo_pkg.explicit;
 12          end loop;
 13      commit;
 14      l_run1 := (dbms_utility.get_time-l_start);
 15      dbms_output.put_line( l_run1 || ' hsecs' );
 16
 17      insert into run_stats select 'after 1', stats.* from stats;
 18      l_start := dbms_utility.get_time;
 19          for i in 1 .. 1000
 20          loop
 21                  demo_pkg.implicit;
 22          end loop;
 23      commit;
 24      l_run2 := (dbms_utility.get_time-l_start);
 25      dbms_output.put_line( l_run2 || ' hsecs' );
 26      dbms_output.put_line
 27      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 28
 29      insert into run_stats select 'after 2', stats.* from stats;
 30  end;
 31  /
321 hsecs
319 hsecs
run 1 ran in 100.63% of the time

PL/SQL procedure successfully completed.


they are for all intents and purposes the same when used this way.  

 

Thank you

Bruno, December 06, 2002 - 8:51 am UTC

Thank you Tom.
My point about the example of bad code was just to write "more readable" dosn't mean "better".
Thanks for the clear and fast answer; I'll try to change my coding habits.

parameter naming

Will, January 03, 2003 - 5:12 pm UTC

Tom,

I've always used:

i_ In
io_ In/Out
o_ Out

for parameters, but am considering switching to your p_ convention.

Don't you think it's sort of handy to know the parameter's direction at a glance?

If not, why?

Tom Kyte
January 03, 2003 - 6:43 pm UTC

I guess cause I rarely use OUT parameters and when I do -- i just "know" they are out. Besides - sometimes an IN becomes an OUT later -- then you have to change the name everywhere...

Comment on Ramakrishna's Naming Conventions

John Gilmore, January 08, 2003 - 6:08 am UTC

"Every table will have a single column numeric primary key
called 'ID'. In case the table needs a multi-column
primary key, this will instead be replaced with a multi-
column unique constraint in addition to having the
surrogate ID as primary key."

I once worked on a site where *every* table had a numeric surrogate primary key driven by a sequence. This was even the case when a perfectly adequate numeric natural key existed in the table.

The result of this was that the content of every foreign key column was an arbitrary, and meaningless, number. In essence it meant that foreign key column values were no longer informtion-bearing in isolation from the actual FK table.

As an extreme example, the FK column "gender" would have been populated with, say, "11" and "32" instead of "M" and "F".

One of the great advantages of relational systems is the concept of tables being "Content Addressable". Using contrived numeric identifiers for tables seems to me to be an attempt to make relational tables look more like segments in a hierarchical database.

I for one would much prefer to see meaningful values in foreign key columns, even if this implies somewhat less efficiency from a data processing viewpoint.

Thoughts on John Gilmore's Thoughts?

Mark, June 23, 2004 - 4:58 pm UTC

I know this is an old post, but I was considering doing just what John Gilmore is frowing upon.

I was wondering if Tom or anyone might know the pros and cons of having a surrogate primary key driven by a sequence vs. what John is espousing. Here are my feelings on the subject.

In the database I am developing, we're going to have a table where a row can be uniquely identified by more than one column. I feel that foreign keys consisting of a few columns are ugly and clunky. I haven't tested yet, but I'm pretty sure that joining by a single ID # would generally be more efficient (quicker) than joining by more than one column.

I also feel that using the surrogate key allows you to change the associated data more easily. The surrogate key has absolutely no relation to the data on the business level, so you should never need to change it. If my primary key for an employee is their email address, for example, if that address ever has to change I'm going to have a headache.

Ideally the user should never have to look at any of these keys, so it doesn't matter if they are meaningless. The Developers and DBAs can always make join views if they wish to see more information. For example if they don't like that their employee table has a departmentid column consisting of arbitrary numbers, they can always make a view which joins employee with department. The view does not select departmentid but rather the department name.

Any thoughts?

Thanks very much in advance.

Tom Kyte
June 23, 2004 - 9:35 pm UTC

What John said was:

...This was even the case when a perfectly adequate numeric
natural key existed in the table....


can you take things to extreme? sure, say you were building a US only product and had a table:

state_code state_name


any reason for a surrogate key here? No, no no.

how about:

create table gender( sex varchar2(1), description varchar2(20) );

ok, sex (gender) as I understand it has maybe 3 values -- M, F, U (unknown). does it need a surrogate? no no no.




use a surrogate when common sense says "we should", eg:

o clunky keys
o keys that might change (well, they would not be keys)
o objects without easily identified keys




naming conventions for Constraints

Matthias Rogel, June 24, 2004 - 6:58 am UTC

hi Tom,

you wrote:
"I have no naming conventions personally."

I'm quite sure I sometimes read
"always name your constraints"
on this site.

maybe one should add
"and don't name them SYS_C%"
to this convention






Tom Kyte
June 24, 2004 - 10:03 am UTC

I name things, but I don't have a strict formal naming convention.



being consistent and explicit ... (may explain my _annoyingly_ long reviews :-)

Gabe, June 24, 2004 - 2:24 pm UTC

1.
<quote>
for rec in c
loop
...
end loop;

Lots less readable, understandable (I'm asked to look at lots of code) than

for x in ( select * from all_users )
loop
...
end;

You know -- what the HECK is "C" -- oh wait, I have to page up 5 times to read what "C" was, now page back down -- oh wait, what table did the 4th column come from? Oh yeah -- page up, page down. I find it to flow alot better when the SQL is plainly visible, in line, right there. If the sql gets heinously large, well, that is what a VIEW is all about in my opinion.
</quote>

Well, replacing the large sql in the implicit cursor with a view won’t save one from having to go “somewhere else” to find what table did the 4th column come from. Personally I don’t subscribe to the suggestion that views are all (or mainly) about _hiding_ large sql. I see them more as (oops! … almost said “artifacts”) a mechanism to encapsulate (and hence share) database logic. I usually have most of the views defined well before the first line of PL/SQL code gets written.

2.
<quote>a view is a table, a table is a view as far as I'm concerned.</quote>

I prefer to always be obvious when a view rather than a table is being used in a statement Â… in Â…
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9087996409290 <code>
you very nicely explain why, with Â…
create or replace view v as select rownum r, ename from emp;
these Â…
select * from v where ...
and
select * from emp where .....
are semantically different. I find that every time I read a select based on views I invariably need to look up their definitions Â… so all this transparency of what the object actually is doesnÂ’t buy us anything.

<not your quote>avoid application changes if a view changes to a table later</not your qoute>
A view changing to a table is by its very nature data denormalization Â… one has bigger problems now since all that denormalized data has to be kept logically consistent (so, maybe a materialized view is what one wants and not a table) Â… some impact analysis is required and the code referencing the view would have to be inspected anyway. As for, avoiding the recompilation of the code using the view Â… well, that is easy Â… leave the code with the view as is and redefine the view to point to a brand new table created underneath.

I hope I didnÂ’t take you quote out of context Â… as far as IÂ’m concerned naming conventions for views are in (I prefer a _vw suffix Â… I reserve the prefix for an application code applied to all objects) because they are note exactly _like_ a table.


3.
<quote>"hungarian notation" -- variables were named after their datatypes and case was important and all that</quote>
I find having the datatype in the variables _comforting_ Â… forget about the rest of the Hungarian notation Â…

insert into t ( id, name, amount, created_dt)
values (n_id, v_name, n_amount, d_create_dt);

seems a bit more verbose than Â…

insert into t ( id, name, amount, created_dt)
values (l_id, l_name, l_amount, l_create_dt);

It is more of a comfort level that the writer has paid attention to data types Â… I find a lax attitude towards data types a constant source of bugs.


As far as IÂ’m concerned, the following are truly annoying:
1. not using any conventions (personal or otherwise) at all
2. using tabs
3. writing SQL and PL/SQL in uppercase
4. not indenting code
5. long lines over (80-120 chars)

As for the rest, as long as whatever conventions are used are applied consistently (at least within the same package) IÂ’m happy.

Just another _view_ on this perennial subject.


Tom Kyte
June 24, 2004 - 3:27 pm UTC

1) we are allowed to differ in our experiences I hope. I'm telling you how I use them, not how you use/used them. not how others use them. I'm telling you how I use them

2) I prefer differently? to me, they are in fact one in the same. Explain plan (always run all new code through tkprof as a matter of fact) would make it fairly blantantly obvious to me.

how a view changing to a table is by definition denormalization is news to me?

if it wasn't my quote, how could you take it out of context? I don't recall ever discussing a view becoming a table -- the other way around, sure. so a little confused there.

3) I hate(d) that hungarian notation, totally despised it, still do. Matter of personal preference I suppose.

not using any conventions -- we agree.
using tabs -- pretty much agree
write sql and plsql in uppercase -- concur, don't like it.
not indenting code -- more importantly, not indenting it the way i like :)
long lines -- aggree


and i'll add one:

subroutines that do not fit on a screen. If you have 50 pages of code and call it a subroutine (method, whaaattteever) -- I'll send it right back.

being explicit ...

Gabe, June 24, 2004 - 4:31 pm UTC

A.
In response to Â…

<not your quote>3. The fact that an object is a table or view is NOT encoded in the object's name to avoid application changes if a view changes to a table later.</not your quote>

you said Â…

<quote>3) good idea, a view is a table, a table is a view as far as I'm concerned.</quote>

I was hoping I didnÂ’t take your reply out of context Â… sorry about the confusion Â… I didnÂ’t see that as a good justification.

B.
<quote> how a view changing to a table is by definition denormalization is news to me?</quote>

Scott in deptno=20 has a job=ANALYST Â… defined in emp
Create view emp20 as select * from emp where deptno=20;
View emp20 is replaced (materialized) by table emp20
Scott is promoted to job=MANAGER
There are 2 different tables where I have to update the info for Scott in order to be consistent Â… hence data is denormalized.

C.
<quote>subroutines that do not fit on a screen Â… -- I'll send it right back.</quote>

ThatÂ’s a bit harsh Â… some sql is longer than that Â… hope youÂ’re using a 21Â’Â’ monitor and small fonts!

<quote> Â… If you have 50 pages of code and call
it a subroutine (method, whaaattteever) -- I'll send it right back.</quote>

ThatÂ’s being too forgiving :-)


Tom Kyte
June 24, 2004 - 4:37 pm UTC

b) but I can take as many examples where a view into a table is not denormalizing, just removing a view

create view v as select * from emp;

and later

drop view v;
rename emp to v;

so -- many times, I would agree, but it is not "by definition"....


c) i forgot to mention (i did in the book ;) - the only other rules are -- the font must be readable (i have excellent vision, I use really small fonts) and you can use the biggest screen you have.

So, today, for me -- that is about 60 lines (one printed page)


the "generic" trap ?!?!

Gabe, June 24, 2004 - 5:38 pm UTC

<quote>so -- many times, I would agree, but it is not "by definition"</quote>

roger that.

rename emp to v? .... (aren't you changing a table into a view here? ... the egg and chicken conundrum :-)

Hmm ... one would hope nothing except V was relying on EMP ... I thought the whole point of all this view-table switcharoo was to eliminate/minimize code maintenance (and I understand it could ... with discipline).

But sure, your particular case invalidates my generic statement!


Naming standar for tables/view/materialized view /object types/collection types

Ajendra, March 09, 2005 - 6:24 am UTC

Tom
while refering the table/view/materialized view/type objects names (and also the column names) should we use capital letters or small letters. Does oracle gets us better perfiormance if we use capital letters for refering any of the above in the PLSQL code. Which is the best practice ?

Regards
Ajendra

Tom Kyte
March 09, 2005 - 7:52 am UTC

it doesn't care what case you use. use what looks best to you

Unique name of an index

Bhagat Naorem, September 08, 2005 - 4:53 am UTC

Hi Tom
What i would like to know is "is index name on a table in an oracle database is unique in that table only or in that database only".
In another word in the same oracle database, can i create same index name for 2 different tables ?
e.g. can i create indx_cnty for table t1 and indx_cnty for table t2 in the same oracle database?

Tom Kyte
September 08, 2005 - 8:43 am UTC

index names are unique in a schema.


ops$tkyte@ORA10G> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA10G> create index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA10G> create index scott.t_idx on t(y);
 
Index created.


so, I can have an index T_IDX, scott can too -- and they can even be on the same table (wonder how many tools would barf on that trick), but I cannot own two indexes named T_IDX.

I can have a table that has the same name:

ops$tkyte@ORA10G> create table t_idx ( x int );
 
Table created.
 
ops$tkyte@ORA10G> create index t_idx on t_idx(x);
create index t_idx on t_idx(x)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
 
but not an index.  index names are unique in a schema
 

naming convention

A reader, December 14, 2007 - 4:15 pm UTC

Tom:

Do you any specific standard for data models. like do you use "Singular like INVOICE, INVOICE_ITEMS" or "plural like INVOICES, INVOICES_ITEMS" table names?

DO you use "column_id" or "Column_no" for auto generated sequence numbers.

It seems people have different opinions on that.

http://www.oracle-base.com/articles/misc/NamingConventions.php

thanks,
Tom Kyte
December 14, 2007 - 4:23 pm UTC

There are as many naming conventions as there are developers. Probably more.

I prefer singular - EMP, not EMPS
Truth be told, I prefer "ID" for columns I populate via a sequence. EMP.ID...



A reader, December 14, 2007 - 4:48 pm UTC

well usually it is EMPLOYEE or EMPLOYEES unless you abbreviate a lot.

Do you use "ID" in every column or you do use "NO" too.

For example, ORDER_NO, INVOICE_NO seems to be more logical thant ORDER_ID, INVOICE_ID.
Tom Kyte
December 17, 2007 - 10:49 am UTC

You have a table called ORDER or ORDERS.

I would have a column called ID


because you query

select order.id from order

and

select order.order_id from order is just verbose.

and if it is an ID, it is an ID - use of the abbreviation NO (since it seems you abbreviate a lot :) ) for number would indicate it is a number - sequential to some people and they'd be disappointed to find it isn't sequential and that the numeric value has nothing to do with the "order" of creation in truth and that there are gaps.

view naming

A reader, January 24, 2008 - 11:48 am UTC

Tom:

When you create views what naming standard you use

emp_vw
emp_view
vw_emp
view_Emp

??

anything easier than tan the other.

I think if we start with "vw_" it groups everything together or it might not matter.
Tom Kyte
January 24, 2008 - 12:15 pm UTC

A view is a table to me, I do not denote them any differently.


EMP would be the name of a view.


and yes, there will be many chiming in "but I do it like this....".

that is ok, this is what I would do. Views are tables, tables are views. I do not name them special.

Just like I don't put the table name on column names (that *drives me nuts*)

view

A reader, January 24, 2008 - 7:09 pm UTC

Tom:

do you use views in your application code instead of a table?

how would you distinguish if they are both same name.
Tom Kyte
January 24, 2008 - 10:13 pm UTC

I use both

why would you need or want to distinguish them?

they are both things you select from, and in most cases, you can insert/update/delete from them too.

naming

A reader, January 25, 2008 - 11:21 am UTC

Tom:

Let us say you have an EMPLOYEE table with 20 columns.

Now, I create an EMPLOYEE view with 10 columns.

How would the code or sql*plus know whether to use the table or view if they are same name.

select * from employee;

I did not even realize you can have both with same name.
Tom Kyte
January 28, 2008 - 6:36 am UTC

start out by telling me you how you have a table and a view with the same name first (assuming the same schema)

I never said they would have the same name, just that neither needs to scream out "I am a real table!!!!", or "I am a real VIEW!!!"

they just need "names" that mean something.

naming

A reader, January 28, 2008 - 12:03 pm UTC

you can not.

I think what you are saying is that you do not have any naming standard for a view. it is a table to you and you just select any name for it like a table.
Tom Kyte
January 29, 2008 - 2:49 am UTC

correct!


"A view is a table to me, I do not denote them any differently."

Naming conventions

ahborges, February 14, 2008 - 9:48 am UTC

Hi Sir

I'm now triying to explain to my co-programmer that prefix ALL
tables with TBL_ or TAB_ is not useful, but they (well he) doesn't believe me. I try and try but...

Could you help me to convince about this? I think is better prefix with 2 or 3 characters related to the application.

Thanks anticipated


Tom Kyte
February 14, 2008 - 12:57 pm UTC

how can you convince someone that doesn't want to be convinced.

short of saying "hey, this guy tom over here says he laughs at people that do TAB_ or TBL_", not sure what I could do.

naming conventions are very "personal" and habitual. Old habits are hard to break - and in the grand scheme of things, there are much larger fish to fry.

column name

A reader, April 21, 2008 - 5:20 pm UTC

Tom:

Is there a problem calling a column name "Directory".

I did create it and it works fine. However, just wondering if this a reserved oracle word and whether there might be some issues down the road.

thanks
Tom Kyte
April 23, 2008 - 5:24 pm UTC

it would fall into the category of a bad idea, but it "works"

and remember in sql

create table t ( "Directory" number, Directory number );

works as well - so watch the use of air quotes when explaining things :)

name

A reader, April 23, 2008 - 7:47 pm UTC

TOm:

I have not had issues in queries or cusrors using it. since the system is still under dev do you recommend changing it to like "dir" or just leave it.
Tom Kyte
April 28, 2008 - 11:02 am UTC

if you are still under development - now would be the time to say "hey, this sounds like a bad idea, since there is a database object called directory, let us make this change NOW while we can without impacting production"


(that seems sensible doesn't it? It'll work as directory, it'll cause confusion and might have to be quoted as "DIRECTORY" from time to time, place to place depending on the tool - rather than risk an obvious hassle, why not... change it while it is easy?)

Clarification

Robert C, April 25, 2008 - 4:33 am UTC

Hi Tom,

Earlier in this thread you said

"I don't use procedures for real code -- very bad practice. I use procedures for standalong
utilities like print_table, show_space and such. It would just be a hassle to have _PR (like
SQLServer with "sp_" on the front, yuck). So, since I don't use procedures for "real" code, I don't
have any preferences there other then the name should be meaningful -- i don't put _pr (what do
you do for functions? _fu?)"

Coming from an SQL Server development background, I am unsure of the distinction you are referring to between procedures and stored procedures in Oracle. Could you please explain where you store code that represents your transactions - that is the transaction APIs of a schema - if not in a procedure?

Regards,

Robert
Tom Kyte
April 28, 2008 - 12:37 pm UTC

You use packages in Oracle.

Packages that work like packages in Ada, Classes in C++ or Java, Modules, whatever you want to call them.

A package holds related procedures and functions (and maybe even 'state' - data). A package publishes in the specification the code others should have access to and allows you to hide procedures in the body that others cannot 'see'

this leads to a significantly reduced "namespace" (instead of 1,000 procedures - all 'visible', you end up with a dozen well named packages that expose 500 procedures and hide 500 that are private to the package body implementation)

Leads to structured, modular, well designed maintainable code... Not 1,000 standalone little procedures.

Makes it so you don't end up writing 1,500 line procedures - since you are not afraid to break the code up (modularize it) since you don't have a namespace overload problem and you have the ability to have private procedures and functions in your package body.

naming audit schema/tables

sam, February 04, 2011 - 11:44 am UTC

Tom:

I would like to see what naming conventions you use when you design a new schema for database auditing that uses shadow tables and DML triggers.


1. Is it better to create a new tablespace for new AUDIT schema to store audit tables or shall i store the schema in same tablespace where application data is. I thinka new database/instance is not good idea too.

2. Is there a best practice naming convention for audit tables that mirror or shadow production tables. I might be creating 100 audit table. Which option would work best?

a) Keep tables same name as production tables (i.e T1)
b) Use this conventtion T1_AUDIT, T2_AUDIT, etc.
c) Use this convention T1_AUD, T2_AUD, etc
d) Use this convention AUDIT_T1, AUDIT_T2, AUDIT_T3


3. is there standard name for audit schema name for application XYZ stored in XYZADMIN schema.

a) XYZAUDIT
b) AUDADMIN
c) AUDIT
Tom Kyte
February 04, 2011 - 1:52 pm UTC

1) it depends. I like to keep an entire application in a single tablespace if it is "smallish" - that way I can manage it easily.

If it is large - then I would split it out in a way that pleased me, made my life easier, made my day better.

A new database would be out of the question, it would not even be considered

2) it is entirely up to you and your corporate standards. I prefer personally something like aud$table_name.

3) no, there isn't. Many times the audit tables are in the same schema.

naming

sam, February 04, 2011 - 2:40 pm UTC

Tom:

On size, it is hard to predict growth but audit schema can easily grow in size and become x number of time the original schema size (about 20 GB). I am also storing the whole ROW after insert, update, delete (not only changes0 to improve performance and make it simpler.

On location, I can't place it in same application schema since everyone now uses one db account. I also think audit data is logically separate from application. you only check audit data to investigate things. I want to only grant SELECT to other users on that.

On table names, I thought AUD$T1 is more for oracle system tables. I have never seen application using the "$". They mostly use "_" underscore. But it works i just tested it.

thanks for you prompt response.
Tom Kyte
February 06, 2011 - 11:50 am UTC

On size, it is hard to predict growth but audit schema can easily grow in size
and become x number of time the original schema size (about 20 GB). I am also
storing the whole ROW after insert, update, delete (not only changes0 to
improve performance and make it simpler.


Sam, then simply follow my advice from above.


If it is large - then I would split it out in a way that pleased me, made my life easier, made my day better.


Everything was anwered above, just read it and either

a) take it into consideration
b) ignore it.

There is nothing more I can say.


Of course it worked (the $), it is a valid identifier and I would not have suggested it otherwise.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library