Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Jen.

Asked: December 09, 2002 - 8:34 pm UTC

Last updated: September 28, 2012 - 7:13 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm a beginner in learning PL/SQL. Instead of reading Oracle documentation on PL/SQL, can you please recommend a good book for a beginner to learn PL/SQL from and would possible lead to intermediate level of PL/SQL programming?

Thank you!

and Tom said...

but -- have you READ the oracle documentation:

is an absolute MUST READ

is another you MUST READ

and you will find <code>

to be pretty well written.

I understand many people like feuresteins plsql books.

Oracle PL/SQL Programming, 3rd Edition
by Steven Feuerstein, Bill Pribyl


  (71 ratings)

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



AK, December 10, 2002 - 8:01 am UTC

Tom, are there hard copies (in book-form, I mean) of these 3 manuals available?

Tom Kyte
December 10, 2002 - 9:35 am UTC

You can order any of the books -- yes. Just like ordering the software.

goto -- there is a blurb there about it.

Sound advice

Ed, December 10, 2002 - 9:13 am UTC

Strange though that Jen requested "Instead of reading Oracle documentation on PL/SQL ..." when it is so easily available and of high quality.

I have pointed people towards "Beginning Oracle Programming" by Dillon, Beck and Kyte.

Luda., December 10, 2002 - 9:20 am UTC

I'm Oracle developer. I have to learn C programming.
What documentation or books could you recommend in this case?


Tom Kyte
December 10, 2002 - 9:51 am UTC

</code> <code>

that is the only C book I ever used. Make sure to sort the reviews from worst to best -- might not be the best beginning C book, but IMO it's the only one.

PL/SQL beginner book

Den Raphaely, December 10, 2002 - 11:41 am UTC

*I see that Jen's question referred to "version 8.1.7" but she might find the latest documentation (9.2) to be more useful even if it refers to some features her version of the database doesn't have (see the "What's New" chapter in every manual).
* Users can download Oracle documentation from OTN in pdf form and print it themselves at no extra cost to the management ;)

Tom Kyte
December 10, 2002 - 12:45 pm UTC

I always try to point the people to whatever version of docs they have in the "version" section.

But yes, ALL documentation is available on OTN

</code> <code>

Feuerstein's main book not the best to learn from

Jeremy Smith, December 10, 2002 - 12:14 pm UTC

Bill Pribyl has a "Learning PL/SQL" which is a more basic introduction. It walks you through simpler examples and assumes a bit less knowledge. Also, PL/SQL Developer's workbook, by the same pair, has a lot of good exercises to work through.

(If you do buy Feuerstein, get the most recent version...he has corrected some mistakes/myths in his previous two)

Thank you all for your advise!

Jen, December 10, 2002 - 12:23 pm UTC

I will check out everything..

A reader, December 10, 2002 - 1:50 pm UTC

You can sort reviews on!?

</code> <code>

that is the only C book I ever used. Make sure to sort the reviews from worst
to best -- might not be the best beginning C book, but IMO it's the only one.

Tom Kyte
December 10, 2002 - 2:07 pm UTC

click on the "see all customer reviews" and you can sort a variety of ways. (i do it on my book to see who "dissed me" :)

that is the only C book I ever used....

Robert, December 10, 2002 - 4:45 pm UTC

that C book by Ritchie and company...isn't that the very FIRST C book ever ? wow, going way back....

ias book

Nikunj, December 10, 2002 - 11:23 pm UTC

is there any book available which can teach us to build site like ?

Tom Kyte
December 11, 2002 - 6:29 am UTC

</code> <code>

look for marvel -- it is all online

Oracle's documentation is well written

Dwayne Croteau, December 11, 2002 - 1:52 am UTC


I use Oracle's documentation almost every day and I find it comprehendable and well written. I can't say that for other vendor (i.e. Microsoft MSDN)documentation I have read. In fact, I haven't purchased any Oracle books - except Expert One on One and Beginning Oracle Programming of course :^)

oracle 9i/9.2

hannibal, December 11, 2002 - 3:57 am UTC


seeing you talking about oracle books i would like to ask if
you could recomend a book/source about oracle9i. i am looking
for something focusing on oracle 9i for programmers ( PL/SQL ,
SQL , etc. ) who are already experienced with oracle 8.1.7
features but want to learn about the new features, their
merits and uses.



Tom Kyte
December 11, 2002 - 6:52 am UTC

</code> <code>

there is

o a plsql manual, with a new features chapter
o a sql ref, with a new features chapter
o a new features guide
o that pesky concepts guide you need to re-read since it is a new release....

and it's all free, you can get it today!


mo, December 11, 2002 - 5:42 pm UTC


are you sure that oracle has this documentation in book form like "Oracle 8i Concepts Release 2". I could not find this on amazon or is it only sold through oracle.

Tom Kyte
December 11, 2002 - 10:50 pm UTC

only oracle

one question

Abdul Wahab, June 29, 2003 - 7:18 am UTC

hi TOM,
I have some questions regarding pl/sql programming.Need your help i am trying to write one script. what i want is after i start the should execute few statements and then stop ..asking the user whether to continue or not (y/n)..and then proceed. How to do this? i mean if i am putting the &var in the program it is asking for it's value at the start of the program itself!. how to make the program interactive?
Another thing which i want to ask is ..can i use the SET operator in the pl/sql program to set the 'set numformat 99999.99 ' if yes then how? thnx

Abdul Wahab

Tom Kyte
June 29, 2003 - 9:28 am UTC

you cannot. PLSQL runs on the server -- over the network -- and is not an interactive programming language. It would be like wanting a query to stop on row 5 and ask the user "do you like what you see".

The CLIENT code must do this interaction with the user.

SET is another CLIENT thing, it is a formatting command in SQLPlus. You use TO_CHAR in PLSQL with a format.


Abdul Wahab, June 30, 2003 - 1:14 am UTC

Thanx TOM
Thanks for giving me this information otherwise i was wasting my time trying to get its solution.
But it was sad to know that PL/SQL is not an interactive language like C.Yeah it would have been great if i could stop it at row 5 and see what it is!;)
take care

Tom Kyte
June 30, 2003 - 7:05 am UTC

C is not "interactive" when run on a server either.

PLSQL can be run on a client (in forms for example). there is can be interactive.

C can be run on a client, there is can be interactive (well, yes then there is X windows where C is running remotely but the UI is local).

When you run C on a server over a network, or as a background process on your local machine (mimicking PLSQL stored procedures), it is only interactive if you build some other client that talks to the "C server" code and interacts for you

Why so expensive?

A reader, June 30, 2003 - 10:14 am UTC

The entire Oracle 9iR2 application development documentation set is around $540!!! Even with a 20% discount for OTN members this is outrageous! Is the Oracle Documentation printed in special paper? Why so expensive? Is this being done to save some trees (I doubt it)?

Tom Kyte
June 30, 2003 - 10:26 am UTC

umm, why you would ask me is beyond me? but you can download cd's (actually you should already have that as part of your install) of the docs for free.

Given that individual technology books retail for $40-60, seems priced comparably.

And yes, trees/manufacturing costs, etc have something to do with it. I myself haven't had a printed set of Oracle doc since version 7.2. I have every set of Oracle doc since on my machines.

A good book on ...

Alex, June 30, 2003 - 11:20 am UTC

When I learned PL/SQL programming I found this book -

Building Intelligent Databases With Oracle Pl/Sql, Triggers, and Stored Procedures
</code> <code>

to be quite good on conceptual side.

PS. Dear Tom, Your recommendations on PLSQL and C books are very useful, thanks. Could you also recommend a good Java book please?

Tom Kyte
June 30, 2003 - 1:18 pm UTC

I don't really have a recommendation on a java book -- except everyone that reads them should also read the CONCEPTS GUIDE and APPLICATION DEVELOPERS GUIDE and use the database...

Comment to "A Reader" (Why so expensive?)

Dan, June 30, 2003 - 5:19 pm UTC

ummmm.... $540 for the complete Oracle Documentation set on paper sounds pretty cheap. I've never seen to whole shebang on paper (haven't seen anything on paper since 7.3) but I understand it's over 15,000 pages. So at $540 the cost is under 4 cents a page. I really don't now what volume printing costs are but that sounds not bad. In fact if it was much cheaper you might be able to sell it to a recycler and make some money!

Expert One-on-One Oracle is far more expensive at almost a whopping 4.8 cents per page! Extortion! (That's amazon's price, by the way).

... actually maybe I should be including the cost of the shelf I'd have to buy if I did get the whole doc set on paper...

Tom Kyte
June 30, 2003 - 7:30 pm UTC

well, the appl developers stuff is about 8/10 books as I recall -- it is not the entire set (thats 1,000$ plus for hardcopy)

you can check it out there. Not that you want to buy them, I find the pdf to be infinitely more useful personally.

Read the docs online. It's free!

A reader, July 01, 2003 - 8:50 am UTC


Marian, July 10, 2003 - 8:38 pm UTC

Hi...I was just passing through...I stopped for a little bit and I started reading the questions and answers. I finished by bookmarking Tom's page and started reading the Oracle docs on PL/SQL :) I should kill you, because I lost my sleep! Anyway..I'll follow some of the paths I found here..Thanks.

Harish, July 22, 2003 - 8:56 am UTC


Can you recommend any books which teach Oracle SQL for a relative beginner to SQL?


Tom Kyte
July 22, 2003 - 2:50 pm UTC

sorry, it has been a long time since I would have read one of those -- Not sure of the quality of specific books since I haven't read them.

Maybe someone reading this will have a good idea for you...

Quick Start Book

Brian, July 22, 2003 - 3:58 pm UTC

Don't laugh, but I read SAMS Teach Yourself SQL, and that got me going for the first week.

I, then, read Beginning Oracle Programming and some of the major (Concepts, SQL Reference, PL/SQL etc.) Oracle Docs that Tom mentioned above.

Once you get through those, Tom's book is a definite read.

On the contrary....Brian

Tarry, July 22, 2003 - 4:10 pm UTC

Couple of years I got started on the same path as the one who started this thread.

I found this an excellent source to start with and indeed it was Sams.

This one will teach you SQL pretty good and it's all free. Depending on your perpicacity, you might want to jump over to more advanced book but trust me you'll find your way out once you get started with this one.

</code>{1BFB99FF-FC1E-453C-82F5-E88E7875A9FF}&element_id={B1F8D6EF-42F8-4F6B-9488-8A5AC81F8CDC} <code>
and this stuff is all free.

I liked the illustrations then and looking back I still like it now. I always try to picturize in thin air what I'm doing and that helps me get closer to the answer.

Later along the path I found Tom's book(expert one-on-one) an excellent stuff to get a deeper insight. There are lots of people who know the stuff real good but aren't able to translate it to others. Tom can do it very well.

All the best and get started with the beautiful language we call SQL,


Thanks :-)

Harish, July 23, 2003 - 5:55 am UTC

Thanks for the information peeps :-)

I will give the SAMS SQL book a try and also read the documentation on-line.


PL/SQL Practices

Muneer Oubaid, July 23, 2003 - 7:53 am UTC

Hello Tom.
I am Muneer Oubaid,
I am tring to learn more about the PL/SQL practices , Iam now in the last step of the PL/SQL course 'the teacher book of this course is 'Introduction to oracle: SQL and PL/SQL '

My question is if its posible ,if you could make me a favour to download immediately aditional practices on the PL/SQL , more difficlut kind of these practices .

many thanks
eng.Muneer Oubaid

Oracle Guides

H.S.Anand, July 24, 2003 - 2:45 am UTC

Hi tom,
are the oracle guides, available some where in PDF format?

Tom Kyte
July 24, 2003 - 8:43 am UTC -> documentation

all of them are there.

A Excellent PL/SQL Book

Espie, August 05, 2003 - 9:23 pm UTC

Try the Oracle PL/SQL Interactive Workbook by Ben Rosenzweig & Elena Silvestrova published by Prentice Hall. It's a great beginners/intermediate book that's full of many useful examples and exercises.

Matrix Programming

Denise, January 29, 2004 - 9:40 am UTC

Hi Tom

Is PL/SQL designed and capable of producing data matrix

We are currently outsourcing specially designed Matrix
Step Reports(based on Largest Gift vs Last Gift Date)along
with numerous other matrix reports
that we are considering doing in-house to scale back
on costs.

I have Scott Urman's book Advanced PL/SQL Programming..
however I don't see any mention or reference to matrix

I've been looking into the GAUSS Programming Language..
(do you know anything about it?).

Could it be written in 'C' 'C' is very limited..
however I did write a tic-tac-toe game in 'C'(which is
kinda matrix in concept) for a school project some years

Anyhow..if you have any suggestions or recommendations
that would be great.

btw...I ordered the Beginning Oracle Programming book..
I like the style and hands-on exercises..even though
the early chapters are very elementary and rudimentary
some of the later chapters will definitely help me with
debugging PL/SQL, SQL Function Reference, Indexing...etc.

tata..and take care

Tom Kyte
January 29, 2004 - 9:45 am UTC

You'd have to describe what you mean by "matrix programming"

"largest gift" vs "last gift" sounds alot like "analytics"
</code> <code>

Thanks Tom!!

Denise, January 29, 2004 - 11:13 am UTC

I forgot about the enhanced SQL Analytic Functions.
The link you provided appears to be what will work in
designing data matrices.

Are these functions part of the OLAP Windows Functions?
I have 9i on my test server with OLAP=True.

I have a .pdf file showing the report design...I can't get it to copy and paste's one page.

With your permission I can send it to your Oracle E-Mail
address for you to assess whether the type of data we
are looking to analyze can be generated through SQL
Analytic Functions.

That would be fantastic!!
what else is there to are simply the BEST!

Tom Kyte
January 29, 2004 - 1:27 pm UTC

You need Enterprise Edition until 9iR2 when they became a feature of Standard and above.

I'd rather not get an email -- I do not think I can tell just by looking at a report if you can or cannot do it with analytics

Thanks anyway....

Denise, January 29, 2004 - 1:41 pm UTC

I do have 9i EE on my testserver.
I'll do some research on using OLAP
and start playing around with the functions.

The link you sent had some good examples so that
should get me going.

I'll let you know when I figure out how to design
a data matrix report.


What is matrix programming?

Dan Kefford, January 29, 2004 - 5:33 pm UTC

Could the poster tell us what she meant by "matrix programming"?

A reader, January 30, 2004 - 12:49 am UTC

Dan Kefford have you watch the triology movie Matrix, if yes, then yeah!! Denise is the one who programmed it :-)

To the previous poster...

Dan Kefford, January 30, 2004 - 8:54 am UTC

There is no spoon.

elusive Concepts Guide

Lynn, February 24, 2004 - 12:06 pm UTC

I keep reading references to the Concepts Guide but I'm having a devil of a time finding a pdf with the words "Concepts Guide". Can someone point me in the right direction? I've gone to the OTN page and do not see anything with the title "Concepts Guide". Thanks.

Tom Kyte
February 24, 2004 - 1:00 pm UTC

</code> <code>

(it is called "concepts" in the list of books....)

Cheap tech books

Mark, February 24, 2004 - 8:52 pm UTC

I PROMISE I don't work for these folks, but their books are a bargain, arrive quickly, etc.

</code> <code>


Effective Oracle by Design Thomas Kyte
Oracle Press, Paperback, Published August 2003, 848 pages, ISBN 0072230657
List Price: $59.99
Our Price: $36.50
Amazon: $41.99

Expert One-on-One Oracle Thomas Kyte
Apress, Paperback, Published August 2003, 1297 pages, ISBN 1590592433
List Price: $59.99
Our Price: $38.50
Amazon: $41.99

Oracle PL/SQL Programming, 3rd Edition Steven Feuerstein, Bill Pribyl
O'Reilly & Associates, Paperback, 3rd edition, Published September 2002, 989 pages, ISBN 0596003811
List Price: $54.95
Our Price: $33.95
Amazon: $38.47

Orders of $40 or more ship free within the 50 US States

I REALLY don't work for them.

Found it

Lynn, February 25, 2004 - 9:19 am UTC

Thanks so much. I now have and am reading the Concepts manual.

SQL error

alex, March 23, 2004 - 3:47 pm UTC


I have following statement in a.sql script file

spool c:\a.txt

select empno || dname from emp where eno = 10;

spool off;

if the above select failes because of any reason can it be traced without using pl/sql block, i mean only using SQL to extarct the data in some file. right now it writes the error on file bt script say success.

any idea please.

Tom Kyte
March 24, 2004 - 8:13 am UTC

um, well, what would you like to have happen?

you can use the sqlplus "whenever" setting (see the sqlplus guide, all docs freely available on otn) to have sqlplus exit upon error with a return code.

You could use plsql like this:

variable x refcursor

open :x for 'select empno || dname from emp where eno = 10';
exception when others.....
blah blah blah
print x

as well, it'll use sqlplus to format the results, plsql to give you control

Wrong person to ask maybe..

Alex, January 12, 2005 - 9:17 am UTC


I'm also interested in getting a hardcopy of the concepts guide. Unfortunately the site has zero information about the book. Do you have, or have you seen these hardcopy books? I'd just like to know a little something about the product I'm paying $75 for (Is it paperback? hardcover? pages?) stuff like that.

Tom Kyte
January 12, 2005 - 10:02 am UTC

if you mean the copies from, last time I saw them -- they were paper with a black plastic binding strip (eg: not paper back, not hard back, the paper with lots of rectangular holes down the side and that black plastic "roll" holding them together -- like you might get at a kinko's).

No thanks

Alex, January 12, 2005 - 11:54 am UTC

Thanks for saving me 75 bucks.


A reader, December 02, 2005 - 1:43 pm UTC

Would it be possible for you to give detialed table of contents for your book "beginning oracle programming"..i would be thankful to you.



Tom Kyte
December 03, 2005 - 10:07 am UTC

A reader, December 04, 2005 - 7:25 am UTC


i know of these contents..i wnated the detailed contents like what is in the chap 1 and you have for your book effective oracle by design available in pdf format


Tom Kyte
December 04, 2005 - 11:24 am UTC

I don't have that with me and as I did not write the entire book I cannot comment. I'm on the road for a while and don't travel with my "library"

however, I can say, as it is a "beginning book", the table of contents is pretty much "dead accurate" - it is introductory material


A reader, December 12, 2005 - 12:12 pm UTC

Hi Tom,

Could you please tell me what kind of linux or unix skill are required for pl/sql either he shoul know the basic commands for linux os ..or he should know installing linux..or something else..PLEASE GUIDE .


Tom Kyte
December 12, 2005 - 12:37 pm UTC

if all you are doing is coding plsql and probably doing it from windows to a linux server, the amount of linux/unix you need to know could be quite close to zero.


Vithal, December 13, 2005 - 1:07 pm UTC


I like to what is the diffrence betweek
for i in (select * from emp where empid = '7788')
end loop;


cursor emp_cur is select * from emp where empid = '7788';
for i in emp_cur
end loop

please guide me on this

Tom Kyte
December 13, 2005 - 1:29 pm UTC

not much...

I like the first, unless the query gets obnoxiously large then the second.

FW :query

vithal, December 13, 2005 - 1:08 pm UTC

Sorry TOM... name


Vithal, December 14, 2005 - 1:38 am UTC


Thanks for this.

Then whey we need the cursor consept.As far I know cursor will allocate the memory for the query. Is the for condition dose the same thing?

then why we are not always using the for condition.

Tom Kyte
December 14, 2005 - 8:11 am UTC

select * from ten_billion_row_table;

that will not "copy" anything anywhere, it will not allocate the "memory" for a query. If you have access to Effective Oracle By Design - I go through this in great detail in how a statement is processed.

cursors do not "run the query and copy the data somewhere". An analogy could be is that they are a program that is executed. Think of them like a subroutine.

EXIT From sqlplus

V, January 10, 2006 - 2:34 pm UTC


I have the following I read in this months issue of ORACLE MAG.

sqlplus -s / <<EOF
COLUMN user_cnt NEW_VALUE user_count
TTITLE 'user_count' SKIP 2 'JOB: ' user_count
select count(*) user_cnt
from user_objects;
EXIT user_count

echo the user owns $OBJCNT objects.

WHen I run it I get:

JOB: 257

the user owns 1 objects.
Why am I not seeing 257 in the above line?

Tom Kyte
January 10, 2006 - 2:58 pm UTC

that won't work - exit codes are either 0..127 or 0..255 depending on platform.

exit codes are one byte.

Here is one approach using back-ticks:

$ cat

OBJCNT=`sqlplus -s / <<EOF
set feedback off
set serveroutput on
l_cnt number;
select count(*) into l_cnt from user_objects;
dbms_output.put_line( l_cnt );

echo the user owns $OBJCNT objects.

$ ./
the user owns 294 objects.


V, January 10, 2006 - 3:42 pm UTC


Did you see the article on page 59 & 60 of the Jan/Feb issue of Oracle Magazine detailing how to run the script?

Is the article wrong?

Tom Kyte
January 10, 2006 - 4:31 pm UTC

I don't have the jan/feb issue with me, that script will not work correctly when there are more than 127 or 255 (depending on OS) objects in a schema, your OS appears to support a return code of 0..255 - given that 257 returns "1" (255 would return 255, 256 would return 0, 257 would return 1 and so on)

Yes, my article was wrong

Jonathan Gennick, January 10, 2006 - 10:35 pm UTC

Tom pointed me to this thread, and in particular the notes about returning values to a shell script through the status value. As several people have pointed out, return values are limited to 8 bits, so you can only return values from 0 through 255 using the method I gave in my article.

I am most annoyed at myself for overlooking that detail. I even mention the limitation in my book on SQL*Plus. It's right there smack in the middle of page 356 of the second edition. Even so, it obviously slipped my mind while writing the article. I should have choosen an example gauranteed to return a result within the 0-255 range.

I apologize if my oversight has caused anyone consternation or extra work. I am going to go soundly kick myself now...

Don't know where to post to this

Maverick, February 23, 2006 - 3:42 pm UTC

From your experience, can you tell me how can I approach this one.

I have a table with some column names. let's say some of those columns are
[14 id's]

and I need to check each id and depending on some conditions
i have to set some variables

if v1_id=1
end if;
if v14_id=1
end if;

there is a lot of logic written between "If-EndIf".

Since logic between if-end if is same for all id columns,
can i do that in some kind of loop, instead of repeating same for each column id?

Hope I am making sense.

Let's say that I cannot put these columns in a sperate table as different rows .

Sorry, to post this here. I could not find a relevant question on this site[May be my search was not correct]

Tom Kyte
February 23, 2006 - 7:33 pm UTC

I have a table with some column names. let's say some of those columns are
[14 id's]....

chills already... a bunch of columns that really want to be rows...

seems you want to write a procedure in PLSQL?

for x in ( select * from this_bad_table )
proc( v1_id, other parameters );
proc( v2_id, other parameters );

don't you?

Yes , I want to use PL/SQL

A reader, February 23, 2006 - 11:07 pm UTC

Yes, Tom I want to use pl/sql and in that code I do not want to give 14 If-EndIf Statements. I think your solution you mentioned was to put that logic in a procedure and call it 14 times.
Is there any other way to do this? Like call same procedure 14 times in a loop?

Thanks a bunch for your help.

Tom Kyte
February 24, 2006 - 8:05 am UTC

ops$tkyte@ORA9IR2> create table t
  2  ( v1_id number,
  3    v1_nm varchar2(5),
  4    v2_id number,
  5    v2_nm varchar2(5)
  6    /* .... */
  7  )
  8  /
Table created.
ops$tkyte@ORA9IR2> insert into t
  2  select rownum, 'a'||rownum, rownum+100, 'b'||(rownum+100)
  3    from all_users where rownum <= 5;
5 rows created.
ops$tkyte@ORA9IR2> declare
  2      type numArray is table of number index by binary_integer;
  3      type vcArray is table of varchar2(4000) index by binary_integer;
  5      l_ids numArray;
  6      l_nms vcArray;
  8      cursor c is select * from t;
  9  begin
 10      open c;
 11      loop
 12          fetch c into l_ids(1), l_nms(1), l_ids(2), l_nms(2) /* ..... */;
 13          exit when c%notfound;
 15          for i in 1 .. l_ids.count
 16          loop
 17              dbms_output.put_line
                 ( 'processing ' || l_ids(i) || ', ' || l_nms(i) );
 18          end loop;
 19      end loop;
 20      close c;
 21  end;
 22  /
processing 1, a1
processing 101, b101
processing 2, a2
processing 102, b102
processing 3, a3
processing 103, b103
processing 4, a4
processing 104, b104
processing 5, a5
processing 105, b105
PL/SQL procedure successfully completed.

Thanks Tom, I think this would work.

A reader, February 24, 2006 - 5:06 pm UTC

Continuing from previous question...

A reader, April 26, 2006 - 4:19 pm UTC

Any other easy way to implement this?

I have a data looks like this. "col_val" is the column i have to insert into table "my_table".


1 Y Y Y Y Y Y Y
2 Y Y Y Y Y Y N
3 Y Y Y Y Y N Y
4 Y Y Y Y Y N N

PL/SQL Code:
If a
If b
If c
If d
If e
If F
if G
end if;
if G
end if;
end if;

end if;

insert into my_table(col) values (col_val);

It's tedious and very error prone by typing all these "if" statements. is there any better way to handle this?

Thanks for any help you can provide.

Tom Kyte
April 27, 2006 - 2:46 pm UTC

well, how about you explain in TEXT what you are trying to do - I don't know why you are assigning 1, 2, 3, 4 when you are. I know it is clear to you (it is your logic after all) but all we have is incomplete logic with .... and no explaination of what you are doing is.

Here are the details..

A reader, April 28, 2006 - 9:37 am UTC

I am trying to generate a report for our Agency that has the following information

Code Dept total
f.1 HR 145000 [D]
f.2 Finance 250000 [D]


When a user clicks on D, i show details of all the people in that department with their salaries.

Now, I am writing a procedure to generate codes[f.1, f.2] like this for each department depending on some criteria.

if dept is finance
if no. of emp are more than 2000
end if;
end if;

end if;


So, i have already written this code , but problem is if i need to add another criteria, i have to change everything. Its like rewriting all those conditions again..

That's my question. Hope I am clear in explaining and thanks for your time.

Tom Kyte
April 28, 2006 - 12:39 pm UTC

that is as clear as ...


sorry to say, I have no idea what the algorithm or logic here is at all. Pretend you are describing this to someone that has never seen your data, that hasn't been looking at the problem for a long time - because you are.

if .. <<<=== not very useful in understanding what ".." is, does, or means.

I thought I was clear enough..!!

A reader, April 28, 2006 - 1:37 pm UTC

Tom, I thought I gave the explanation very clearly what exactly I need on the report etc.

But anyways, forget what i said before. Just let me know if there is another way of replacing an If-elseif conditions to set values. If yes, except for CASE Statement, is there any other way to do this.

Sorry about the way i explained.I am going to try this on my other colleague [who has no idea what i am doing ] and see if he can understand what my need is. Meanwhile i 'll appreciate if you can provide me any answer on this one.

Thanks for your help [again]

Tom Kyte
April 28, 2006 - 5:33 pm UTC

you have got to be kidding me - right? this is like "a joke". You thought

if ..... then .... else .... maybe.... if raining ... elsif .... <something...>

was clear? ouch.

this hurts.

maybe ... I ... every ... word ... replace ... with ..., I'll ... more ....

maybe if I erase every other word and replace them with dots, I'll be more efficient

I worry for the future sometimes. The ability to phrase "a requirement" seems to be going "away".


Michel Cadot, April 28, 2006 - 3:01 pm UTC

"A Reader",

I think if you explain the relation, the logic behind the matrix of N/Y and the final numbers maybe we can find a compressed statement.
If there is no logic or pattern between source and target (except the full matrix) then there is no way to get another answer than the IF THEN ELSIF ELSE block.


My crystal ball is repaired

Michel Cadot, April 29, 2006 - 11:04 am UTC

"A Reader",

Now I can see that the values in first column of your matrix (April 26) are not row numbers but the values of your final col_val column.

And, yes, I can see... sorry it's fuzzy... yes there is a pattern... maybe...

SQL> select t.*, 
  2           sign(ascii('Y')-ascii(g)) * power(2,0)
  3         + sign(ascii('Y')-ascii(f)) * power(2,1)
  4         + sign(ascii('Y')-ascii(e)) * power(2,2)
  5         + sign(ascii('Y')-ascii(d)) * power(2,3)
  6         + sign(ascii('Y')-ascii(c)) * power(2,4)
  7         + sign(ascii('Y')-ascii(b)) * power(2,5)
  8         + sign(ascii('Y')-ascii(a)) * power(2,6)
  9         + 1 col_val
 10  from t
 11  order by col_val
 12  /
- - - - - - - ----------
Y Y Y Y Y Y Y          1
Y Y Y Y Y Y N          2
Y Y Y Y Y N Y          3
Y Y Y Y Y N N          4

Clairvoyant regards


A reader, May 01, 2006 - 12:06 pm UTC

i was reading a pl/sql book and got to know recently that you can make fully functional web sites with pl/sql...i wanted to ask is it worth learning to make web sites with pl/sql..or it is not as good as or php , what are the benifits of making web site with pl/sql in comparison to or php etc.


Tom Kyte
May 02, 2006 - 2:57 am UTC

</code> <code>

the web site you are using right now - built with that tool. Which comes with the database "for free". Which requires no software installed on your desktop. Which works quite well.


A reader, May 02, 2006 - 10:17 am UTC

thanks for replying..I also wanted to ask one thing like if somebody asks me why i am making web site with htmldb and not using or dreamweaver or etc that what should i say..


Tom Kyte
May 02, 2006 - 3:50 pm UTC

I usually reply

"Because I am super smart"

does that work for you?

I am a person that has NEVER

a) used
b) dreamweaver

ever. Not even once. I'm not sure I can spell correctly in fact.

I use systems other than windows frequently, that sort of factors into it as well.

and - it is just so "easy". I'll take on the people any day, give me a new hire right out of college - no training, I'll give you one too. You get to pick even from the pair.

And we'll see which one might "get it" and be productive faster using each.


AD, May 02, 2006 - 4:45 pm UTC


Continuing from the above post ...

Does HTML DB require any installation if not on the desk top?


Tom Kyte
May 03, 2006 - 1:35 am UTC

nothing on desktop beyond browser.

packages installed on server and an apache instance with mod_plsql or just use the dbms_epg package (no webserver outside of the database necessary)


A reader, May 02, 2006 - 5:28 pm UTC

AND One more question Pleaseeee...I have oracle installed on my win xp..where is htmdb in it.. is it some thing i need to install as a separate software or it will be there in oracle sql*plus ,how do we launch htmldb


Tom Kyte
May 03, 2006 - 1:37 am UTC

goto, you may download it from there - it shipped first with 10g on the installation media.

Delay in job + how to give select statement in interval calculation

Supriya, May 03, 2006 - 1:50 am UTC

Hi Tom,

I have created a job like................

jobno NUMBER;
'begin aaa; end;',

This job is scheduled to run daily at 11.30pm.
But, because of other jobs (which are running previously to this), it got delay and execting after 12'clk (so, the time is 00:30:10 etc..)

Here i have 2 questions
1) 'trunc(sysdate+1)+(23.5/24)' is the statement to calculate, when to run net job.Here i am taking sysdate.
suppose my job should run on 01/may/2006 at 11.30pm, but running at 02/may/2006 at 12'clk(00:30:10)am.
my next job should run at 02/may/2006 at 11.30 again..
but it skipping one day & executing on 03/may/2006 at 11.30 again (next date for this job--'trunc(sysdate+1)+(23.5/24)')

Here instead of this statement:'trunc(sysdate+1)+(23.5/24)'
is it possible to use any sql statement?

2)Could you please provide reasons for delaying in jobs...

no.of job queues = 5

I am testing these scenarioes on windows 2000 server
and production server is unix.

Thanks in advance,

Tom Kyte
May 03, 2006 - 1:55 am UTC

answered in reverse order

#2 - you ANSWERED this for us!

This job is scheduled to run daily at 11.30pm.
But, because of other jobs (which are running previously to this), it got delay
and execting after 12'clk (so, the time is 00:30:10 etc..)

that is the reason. so job queue processes was 5 - you had 5 other jobs running and they did not finish until after midnight so the 6th job (your job) had to wait.

#1 - basically, you are saying "if the current time is before, say 12:00, then just trunc(sysdate) and add 23.5/24; else use trunc(sysdate+1)+23.5"


case when to_number(to_char(sysdate,'hh24')) < 12
then trunc(sysdate)+23.5/24
else trunc(sysdate+1)+23.5/24

could be your interval - if before noon, let's run this job at 11:30pm today, else run it at 11:30pm tomorrow.

If you don't like noon - pick your own cutoff time.

Delay in job + how to give select statement (or calculated value) in interval calculation

Supriya, May 04, 2006 - 5:24 am UTC

Hi Tom,

I tried the following code for interval...

jobno NUMBER;
'begin aaa; end;',
'case when to_number(to_char(sysdate,''hh24'')) = 00 then trunc(sysdate)+(23.5/24) when to_number(to_char(sysdate,''hh24'')) = 01 then trunc(sysdate)+(23.5/24) else trunc(sysdate+1)+(23.5/24) end;'

But its giving following error

ORA-23319: parameter value "case when to_number(to_char(sysdate,'hh24')) = 00 then trunc(sysdate)+(23.5/24) when to_number(to_char(sysdate,'hh24')) = 01 then trunc(sysdate)+(23.5/24) else trunc(sysdate+1)+(23.5/24) end;" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 132
ORA-06512: at line 4

any thibng wrong in syntax?

I tried by using function also

create or replace function xyz return varchar2 is

b date;
a varchar2(30);
b := case when to_number(to_char(sysdate,'hh24')) = 00
then trunc(sysdate)+(23.5/24)
when to_number(to_char(sysdate,'hh24')) = 01
then trunc(sysdate)+(23.5/24)
else trunc(sysdate+1)+(23.5/24) end;

a := to_char(b, 'dd/mon/yyyy hh24:mi:ss');

return a;
----------- end of function

jobno NUMBER;
'begin aaa; end;',

giving following error

ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 132
ORA-06512: at line 4


Tom Kyte
May 04, 2006 - 7:13 am UTC

why did you put a semi-colon there?

ops$tkyte@ORA9IR2> DECLARE
  2        jobno   NUMBER;
  3  BEGIN
  4         DBMS_JOB.SUBMIT
  5           (jobno,
  6           'null;',
  7            trunc(sysdate),
  8            'case when to_number(to_char(sysdate,''hh24'')) = 0 ' ||
  9                 'then trunc(sysdate)+(23.5/24) ' ||
 10                 'when to_number(to_char(sysdate,''hh24'')) = 1 ' ||
 11                 'then trunc(sysdate)+(23.5/24) ' ||
 12                 'else trunc(sysdate+1)+(23.5/24) end'
 13            );
 14        dbms_output.put_line(jobno);
 15        rollback;
 16  END;
 17  /

PL/SQL procedure successfully completed.

semi-colon doesn't belong in the interval - not valid.


V, August 16, 2006 - 11:04 am UTC


I have the following to check if the instance has connectivity and it MUST be run as root.

export ORACLE_HOME=/apps/oracle/product/10.2.0
OBJCNT=`su - oracle -c $ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<END
set feedback off
set termout off
set serveroutput on
l_cnt number;
select 1 into l_cnt from dual;
dbms_output.put_line( l_cnt );

if [ "$OBJCNT" != "1" ] ;then
echo "Instance is down">/tmp/test
exit 201
echo "Instance is up">/tmp/test
exit 0

ORACLE is a OS authenticated user in the db. But when I run this my environment is not set. I do have multiple ORACLE_HOMES and instacnes on the server. Any thoughts?

Tom Kyte
August 16, 2006 - 11:25 am UTC

on what?

the environment for the "su" command is set, it is set to whatever the default environment for 'oracle' is

Begiiner book for Oracle DB

mrora, May 25, 2007 - 3:02 am UTC

Dear Tom.
Could you possibly advice book for Oracle begiiner? I mean book for university students. Really it will be very hard to them to start from Concepts. Instead it will be good if they started from install Oracle, create sample schema, play with it, and so on. After it they can start to think how Oracle works? why you need to understand it's concepts and so on.
Waiting for your anwser...

Thanks in advance.

A reader, May 19, 2010 - 11:39 am UTC

what is your opinion in that book for a database fundamentals and RDBMS ... what do you think?

Tom Kyte
May 24, 2010 - 10:43 am UTC

I have never read it.

A reader, May 27, 2010 - 11:57 pm UTC

then,look at the book index..
Are that a good start book for fundamentals??
Tom Kyte
May 28, 2010 - 7:59 am UTC

I don't know since a table of contents tells you nothing at all about the quality of the contents.

I would not be a good reference for a book at this level since I have not read one myself for about 20 or so years.


sam, January 18, 2011 - 9:06 am UTC


A quick question on hardcoding using PL/sql.

Few users say not to hardcode as it makes system locked up and not flexible and I sometimes find business logic dicates that.

If you have code like this that supports real-life business logic

If (media='CD' and production_stage='XX') then
do this...
caluclate costs using this formula
elsif (media='PAPER' and production_stage='YY') then
do this....
calculate costs using this formula.
end if;

DO you see this as hardcoding and bad practice or not?
Tom Kyte
January 23, 2011 - 3:32 pm UTC

it depends

the only answer to a question like that is - IT DEPENDS.

If there were these two conditions - I would say "go for it" - you might put 'CD' and 'XX' and 'PAPER' and 'YY' into globals in case they change - but otherwise - go for it.

Having if statements is not 'hard coding' in my terminology.

Remember that 'infinite flexibility comes at a HIGH - excessively high - performance cost'


sam, January 24, 2011 - 8:08 am UTC


This is a small example of what the system has. Some claim it is hardcoding and it make the system hard to change when they change the business rules. They think systems should adapt automatically with changes!

I am curious on what other alternatives there are. There is no way to avoid this kind of coding when you want to support business logic like this. Is there?

Tom Kyte
February 01, 2011 - 10:22 am UTC

every WHERE clause you code would be "hard coding" by definition.

Every line of CODE you CODE would be hard coding.

I differ with their analysis. This is not hard coding to me, this is programming your system.

If you want to make the end all, be all, data driven - rules engine processing - meta data megastar application - go for it. It'll end up with pretty much the rest of them. Unfinished or so complex to manage (you have to program it ultimately - there will be LOGIC somewhere - in your data that you compile and interpret or in code that we've already compiled...) or so slow as to be useless.


doug, September 24, 2012 - 12:29 am UTC

Hi Tom,

I have read that whenever SQL is read in PL/SQL code the PL/SQL passes the SQL to an SQL engine to process.

Would you have any examples on minimising the switching to the SQL engine.

I read a whitepaper that suggested no PL/SQL object should access tables directly and all table access should be encapsulated in procedures, would be please share your thoughts on this?

Tom Kyte
September 27, 2012 - 7:50 am UTC

Would you have any examples on minimising the switching to the SQL engine.

sort of, what you need to do is pack as much into a single sql statement as you can, avoid writing code when possible. for example, instead of:

for x in (select * from t1) 
   insert into t2 (...) values (....);
end loop;

you should just

insert into t2 (....) select ... from t1;

I read a whitepaper that suggested no PL/SQL object should access tables
directly and all table access should be encapsulated in procedures, would be
please share your thoughts on this?

I love that, I've written that myself many times

A reader, September 27, 2012 - 6:23 pm UTC

You Said
>I love that, I've written that myself many times

Where can I read your advice please Tom, in one of your books or elsewhere?
Tom Kyte
September 28, 2012 - 6:38 am UTC

all over this blog, in many of my articles in oracle magazine, in my books - yes.

You do not mean TAPI? ;-)

Kim Berg Hansen, September 28, 2012 - 1:42 am UTC

Doug wrote:

I read a whitepaper that suggested no PL/SQL object should access tables directly and all table access should be encapsulated in procedures, would be please share your thoughts on this?

And you, Tom, answered:

I love that, I've written that myself many times

Just to be certain, I presume that you mean the XAPI way rather than the TAPI way?

The way Doug wrote that question (no PL/SQL object should access tables directly) I have a hunch he might have read about TAPI :-)
Tom Kyte
September 28, 2012 - 7:13 am UTC

Doug, October 03, 2012 - 12:15 am UTC

I have neither heard of TAPIs or XAPIs before.

I was very curious about encapsulating table access in APIs for the very reasons you mention.

I like the XAPI concept