Skip to Main Content



Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Jen.

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

Answered by: Tom Kyte - Last updated: September 28, 2012 - 7:13 am UTC

Category: Developer - Version: 8.1.7

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: The real question is why are you NOT blogging

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 we 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

and you rated our response

  (71 ratings)

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



December 10, 2002 - 8:01 am UTC

Reviewer: AK from Germany

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

December 10, 2002 - 9:13 am UTC

Reviewer: Ed from UK

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.

December 10, 2002 - 9:20 am UTC

Reviewer: Luda. from USA

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

December 10, 2002 - 11:41 am UTC

Reviewer: Den Raphaely from Redwood Shores, CA USA

*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

December 10, 2002 - 12:14 pm UTC

Reviewer: Jeremy Smith from Salt Lake City

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!

December 10, 2002 - 12:23 pm UTC

Reviewer: Jen

I will check out everything..

December 10, 2002 - 1:50 pm UTC

Reviewer: A reader from Ca, USA

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....

December 10, 2002 - 4:45 pm UTC

Reviewer: Robert from PA

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

ias book

December 10, 2002 - 11:23 pm UTC

Reviewer: Nikunj from Ahmedabad,India

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

December 11, 2002 - 1:52 am UTC

Reviewer: Dwayne Croteau from Calgary, Alberta


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

December 11, 2002 - 3:57 am UTC

Reviewer: hannibal from autris/europe


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!


December 11, 2002 - 5:42 pm UTC

Reviewer: mo


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

June 29, 2003 - 7:18 am UTC

Reviewer: Abdul Wahab from Jeddah,K.S.A

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.


June 30, 2003 - 1:14 am UTC

Reviewer: Abdul Wahab from Jeddah,K.S.A

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?

June 30, 2003 - 10:14 am UTC

Reviewer: A reader

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 ...

June 30, 2003 - 11:20 am UTC

Reviewer: Alex from UK

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?)

June 30, 2003 - 5:19 pm UTC

Reviewer: Dan from Yukon, Canada

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!

July 01, 2003 - 8:50 am UTC

Reviewer: A reader


July 10, 2003 - 8:38 pm UTC

Reviewer: Marian from Iasi, Romania

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.

July 22, 2003 - 8:56 am UTC

Reviewer: Harish from London, England


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

July 22, 2003 - 3:58 pm UTC

Reviewer: Brian from Phoenix

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

July 22, 2003 - 4:10 pm UTC

Reviewer: Tarry from Netherlands

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 :-)

July 23, 2003 - 5:55 am UTC

Reviewer: Harish from London, England

Thanks for the information peeps :-)

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


PL/SQL Practices

July 23, 2003 - 7:53 am UTC

Reviewer: Muneer Oubaid

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

July 24, 2003 - 2:45 am UTC

Reviewer: H.S.Anand from New Delhi, India

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

August 05, 2003 - 9:23 pm UTC

Reviewer: Espie from NY, NY

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

January 29, 2004 - 9:40 am UTC

Reviewer: Denise from Virginia,USA

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!!

January 29, 2004 - 11:13 am UTC

Reviewer: Denise from Virginia,USA

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....

January 29, 2004 - 1:41 pm UTC

Reviewer: Denise from Virginia, USA

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?

January 29, 2004 - 5:33 pm UTC

Reviewer: Dan Kefford from New Haven, CT

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

January 30, 2004 - 12:49 am UTC

Reviewer: A reader

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

To the previous poster...

January 30, 2004 - 8:54 am UTC

Reviewer: Dan Kefford from New Haven, CT

There is no spoon.

elusive Concepts Guide

February 24, 2004 - 12:06 pm UTC

Reviewer: Lynn from NJ

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

February 24, 2004 - 8:52 pm UTC

Reviewer: Mark from USA

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

February 25, 2004 - 9:19 am UTC

Reviewer: Lynn from NJ

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

SQL error

March 23, 2004 - 3:47 pm UTC

Reviewer: alex from uk


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..

January 12, 2005 - 9:17 am UTC

Reviewer: Alex


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

January 12, 2005 - 11:54 am UTC

Reviewer: Alex

Thanks for saving me 75 bucks.


December 02, 2005 - 1:43 pm UTC

Reviewer: A reader

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

</code> <code>

December 04, 2005 - 7:25 am UTC

Reviewer: A reader


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


December 12, 2005 - 12:12 pm UTC

Reviewer: A reader

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.


December 13, 2005 - 1:07 pm UTC

Reviewer: Vithal from Singapore


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

December 13, 2005 - 1:08 pm UTC

Reviewer: vithal from sinagpore

Sorry TOM... name


December 14, 2005 - 1:38 am UTC

Reviewer: Vithal from Singapore


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

January 10, 2006 - 2:34 pm UTC

Reviewer: V from NY


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.


January 10, 2006 - 3:42 pm UTC

Reviewer: V from ny


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

January 10, 2006 - 10:35 pm UTC

Reviewer: Jonathan Gennick from Munising, Michigan

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

February 23, 2006 - 3:42 pm UTC

Reviewer: Maverick

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

February 23, 2006 - 11:07 pm UTC

Reviewer: A reader

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.

February 24, 2006 - 5:06 pm UTC

Reviewer: A reader

Continuing from previous question...

April 26, 2006 - 4:19 pm UTC

Reviewer: A reader

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..

April 28, 2006 - 9:37 am UTC

Reviewer: A reader

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..!!

April 28, 2006 - 1:37 pm UTC

Reviewer: A reader

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".


April 28, 2006 - 3:01 pm UTC

Reviewer: Michel Cadot from France

"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

April 29, 2006 - 11:04 am UTC

Reviewer: Michel Cadot from France

"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


May 01, 2006 - 12:06 pm UTC

Reviewer: A reader

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.


May 02, 2006 - 10:17 am UTC

Reviewer: A reader

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.


May 02, 2006 - 4:45 pm UTC

Reviewer: AD from UK


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)


May 02, 2006 - 5:28 pm UTC

Reviewer: A reader

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

May 03, 2006 - 1:50 am UTC

Reviewer: Supriya from INDIA

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

May 04, 2006 - 5:24 am UTC

Reviewer: Supriya from INDIA

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.


August 16, 2006 - 11:04 am UTC

Reviewer: V from NY


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

May 25, 2007 - 3:02 am UTC

Reviewer: mrora

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.
Tom Kyte


May 26, 2007 - 11:55 am UTC

May 19, 2010 - 11:39 am UTC

Reviewer: A reader

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.

May 27, 2010 - 11:57 pm UTC

Reviewer: A reader

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.


January 18, 2011 - 9:06 am UTC

Reviewer: sam


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'


January 24, 2011 - 8:08 am UTC

Reviewer: sam


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.


September 24, 2012 - 12:29 am UTC

Reviewer: doug

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

September 27, 2012 - 6:23 pm UTC

Reviewer: A reader

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? ;-)

September 28, 2012 - 1:42 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

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

XAPI - yes.

October 03, 2012 - 12:15 am UTC

Reviewer: Doug

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