Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, CJ.

Asked: February 19, 2003 - 8:01 am UTC

Last updated: May 04, 2020 - 3:44 am UTC


Viewed 100K+ times! This question is

You Asked

Why do parts of the new Ask Tom appear in another language? e.g. on the list of questions asked previously:

Kedd Február 18, 2003 (H. Feb 17, 2003, 0 days old)


and we said...

We pick up the language from the browser now and set the NLS_LANG appropriately -- dates, numbers, etc should appear in your locale now.


  (607 ratings)

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


Looks good, but...

Eugen, February 19, 2003 - 9:50 am UTC

Hi Tom,

Good to know, that you are using the NLS_LANG.

I found an other minor "problem".
The email-search in the "Your Questions" Tab is case sensitive :-(


Tom Kyte
February 19, 2003 - 1:47 pm UTC

not anymore.

Foreign languages

Ed, February 19, 2003 - 9:57 am UTC

I like the new features, especially the ability to page forward from the front page now.

However, the NLS stuff doesn't seem to be working consistently with me. I've had at least 4 different languages today. Still, at least I'm learning a lot - today is Onsdag 19 Februari in Swedish, you know! :-)

Tom Kyte
February 19, 2003 - 1:48 pm UTC

yeah yeah, shows what happens with connection caching - we've rolled that back until we figure out what exactly to do permanently ;)

Nice new features....however, small bug?

Martin, February 19, 2003 - 10:02 am UTC

Hi Tom,

I 'm not sure if this is a bug or intended behavior, but if you have perfomed a search earlier and then click the asktom logo to go back to asktom home, the search results are shown again, instead of a 'clean page' with recent questions.

Tom Kyte
February 19, 2003 - 2:05 pm UTC


Foreign Languages

CJ, February 19, 2003 - 10:12 am UTC

I've been through several languages today, although I'm from the good 'ol USA and only speak English. :-) I've seen French, Spanish, and a couple I don't recognize at all.

Today is Çar¿Amba ¿Ubat 19, 2003

A reader, February 19, 2003 - 11:11 am UTC

I liked the other one better...

Al, February 19, 2003 - 11:14 am UTC

Before, when I searched and paged forward (or back), the majority of the page contained search results. Now, the results are at the bottom of the page and cannot be seen without additional key strokes or mouse action. That won't stop me from using Ask Tom but it's just not as convenient.

Thanks so much for being available with consistently good answers and advice. I believe that all of us who come here benefit greatly from it, I know I do.

Tom Kyte
February 19, 2003 - 2:21 pm UTC

well, we moved the search to the top, pushed the messages to the bottom -- how does that look now?

Put the search on the bottom

Jeremy Smith, February 19, 2003 - 12:12 pm UTC

I assume you put the search on the main page to encourage its use. Usability wise I think it would be better to move the search to the bottom of the page or to the side.
1. It gets 'lost' visually in the announcements.
2. It allows all the changes from the 'old' version to be at the edges (title bar and bottom) for those who are used to the old look.
3. dead center of the page is probably the rarest placement for a search capability (unless you're a search engine exclusively)

Also, a checkbox on the "Your Questions" that also searched for questions on which we have commented would be cool.

Tom Kyte
February 19, 2003 - 2:22 pm UTC

how about the top?

I agree, dead center was probably "inappropriate" ;)

as for the your questions to find reviews -- sorry, but I don't keep an email there. To find your questions you've reviewed -- search for your name "Jeremy Smith"

Like the new features.......

A Reader, February 19, 2003 - 12:58 pm UTC

....looks like the Your Questions will be useful - would it be possible to update that in future to include reviews of others questions ? Sometimes (although we shouldn't) we ask a question via the reviews (it might be 100% related to the question already asked) and this wouldn't allow us to track that currently.

Damn users...always want more !!!

Tom Kyte
February 19, 2003 - 2:23 pm UTC

See above -- if you use your NAME (or something unique in the name field), you can find them...

I don't get an email address in the followups...

A reader, February 19, 2003 - 3:11 pm UTC

Congratulations :) nice

You ask for an email in the form for the follow-ups.

Jeremy Smith, February 19, 2003 - 3:15 pm UTC

Do you just "black-hole" it?

Tom Kyte
February 19, 2003 - 4:23 pm UTC

ok, if you want to point out the blindingly obvious to me :)

Yes, it is there after all (just forgot about it, i don't use the review screen too often myself you see)....

Added -- you now get your posted questions and your reviewed questions IF You put an email in there...

several languages

Paul Druker, February 25, 2003 - 8:18 am UTC

I clicked several times on the askTom logo, and saw several languages today as well. Bit it's not consistent - most of the times I see English version. I'm using IE 5.50, and I have only one language [en-us] defined.

Tom Kyte
February 25, 2003 - 9:54 am UTC

Hey, its a feature -- I thought I would expose everone to different languages ;)

seriously -- they are working on it, the marvel team introduced a "bug" we didn't find since we all do english ourselves. they are missing an alter session -- leaving the language set by the last person.

Re several languages

Helen, February 25, 2003 - 11:47 am UTC


Ta muchly for a response on this at last.

Not improved my knowledge of foreign languages much, no idea what Pzt ¿Ub 24, 2003 is in!!

Tom Kyte
February 25, 2003 - 7:56 pm UTC

ok, ok -- it should be fixed now!

Language lesson

Ed, February 25, 2003 - 12:52 pm UTC


That'll be Turkish. Try running this for a language lesson:

FOR r IN (SELECT value
FROM v$nls_valid_values
WHERE parameter = 'LANGUAGE'
AND value NOT LIKE '% %'
DBMS_OUTPUT.PUT_LINE (initcap(r.value)||' - '||
TO_CHAR(SYSDATE,'fmDay DD Monthfm','NLS_DATE_LANGUAGE='||r.value));

Yo, Helen, what is this ???

robert, February 25, 2003 - 3:21 pm UTC

>> Ta muchly for a response on this at last.

And I thought you still speak English there in the UK.
Boy, things change so fast....

Yo ???

Adrian, February 26, 2003 - 4:07 am UTC


You can't take the moral highground over "ta muchly" when you start your campaign with "Yo"... ;)


Number of records found in search

Pratap, February 26, 2003 - 10:49 pm UTC

Hi Tom

I searched for dbms_trace. There are total 11 records. The page displays -

{dbms_trace} Approximately 17 records found. Because you are asking intermedia for the approximate count.

But look at the combo below the search results - It shows -

1-10 of 11. There are exactly 11 records. Now how is it able to do that? Are you again asking for the exact count?



Tom Kyte
February 27, 2003 - 7:34 am UTC

it is a bug that it is temporarily working that way. it won't be soon i hope.

search box on every page

A reader, February 27, 2003 - 4:40 pm UTC

would like search box on every page

Thanks for adding new features on Ask Tom Site

Pauline, February 27, 2003 - 10:52 pm UTC

I like one of the new feature which is I can retrieve my own questions by click 'Your Question'. Thanks for you putting the efforts on it.

please send me A book of oracle

shaukat, February 28, 2003 - 4:14 am UTC

this is not enough to understand so please send me A n oracle boook thank you
my postal address
shaukat ali otho
A/59 Abdullah town qasimabad
hyderabad sindh

Tom Kyte
February 28, 2003 - 9:55 am UTC

sure -- i'll get right on that -- not.


</code> <code>

click on documentation. read away.

I could use a library myself

Simon Holt, February 28, 2003 - 5:06 am UTC

Tom, a lot of my books on Oracle are a little out of date now, and I can't be bothered to use the net or do any research, or send myself on training courses.

Whilst you are at it for the gentleman above, can you send me a load of 9i books and anything on 10i that you might have kicking around?

Thanks - it's really appreciated ;-)

Can you do that?

A Reader, February 28, 2003 - 9:34 am UTC

If you can move the "Was this response helpful to you? Let us know!" to the end of the last post, not the end of your first answer, it would save readers some time to go up and find the "Let us know", especially when there are a lot of discussions. Most "let you know" are related to the latest post.
Thank you.

Tom Kyte
February 28, 2003 - 10:15 am UTC

already done

Please include me as well

Joel, February 28, 2003 - 10:09 am UTC

Tom, since it appears you're going to have the TKyte shipping department busy, could you also include me on your list? I would appreciate a full 9i documentation set, a copy of your recent book, and any additional copies of TV Guide or ESPN magazine sitting around your house.

Thanks in advance!

Me tooooooooooooo

Alex, February 28, 2003 - 10:36 am UTC


Can you send to me the same things that you will send to Joel( ESPN magazine is a nice touch ) but it will be really nice of you if you can ask Larry E. to send me couple of Oracle shares.
Thanks in advance ! :-)

Return button

Holger Baer, February 28, 2003 - 10:43 am UTC


I definitly like the new askTom. Especially the little return button that appears when reviewing my questions is a nifty feature! (not that there are that many so far) ;-)

It would be nice to have it on the other pages, too. Of course, there's always the 'Back' button from the browser, but then, someone put that button in for a purpose...


Its cool ,but One Request

Ashiq Shamsudeen, February 28, 2003 - 10:48 am UTC

New Ask Tom is damn cool .Keep it up.
One request , U should provide us to save the questions in differenct formats like .txt,rft or doc.Because i used to save the many important queries from ur site to a file .For this i've select it and after pasting it in notepad , theni've to do some alingmetns to make it proper.
Instead of hitting your site i can get in my file itself.
It'll be very useful for others also( i mean for enitre oracle world)if you provide this feature .And it'll be damn good to store the archives ,like day wise ,week wise ,and month wise .
I sincerely believe you will consider my request .

Tom Kyte
February 28, 2003 - 11:34 am UTC

well, I considered it and ruled it out.

You can cut and paste HTML directly into a word processor -- they mostly accept it -- then the formatting is "preserved".

file save as TXT in the browser does the txt already.

So, to get to TXT --> file save
to get DOC/RFT --> cut and paste right into word

Cleaning old questions

I'm just curious, March 02, 2003 - 1:20 pm UTC

Tom, I observed that some old questions are not in your site database any more. It is understandable, for example, that you may want to get rid of old Orcacle7 questions. It's my belief there are no more questions from 1999 backwards.

What criteria do you use to purge old questions ? Do you maintain a "window" of current questions (say, from 2000 up till now) ?


Tom Kyte
March 02, 2003 - 1:38 pm UTC

It is not your belief that questions from 1999 backwards are not to be found.

asktom started in january 2000 ;)

I haven't purged a thing. As of March 2, 2003, I have:

New, Never Read 1
Read, Not Answered 0
Read, Awaiting More Info 26
Read, More Info Has Been Supplied 0
Answered, Do not publish 13456
Answered, Publish 7178
Deferred, answer later 0

questions -- so there are 7,178 you can see. 13,456 I answered but didn't publish. 26 waiting for more information from the askee. and 1 in the queue I haven't gotten to yet....


mo, March 02, 2003 - 2:54 pm UTC


are you still working in the problems for search on the site.

i have a problem finding old questions/followups i posted. as search for "mo reports server" and I can not find an article you answered for me.

Tom Kyte
March 02, 2003 - 3:34 pm UTC


mo reports server

returns 10 hits for me and

"mo reports server"

returns a hit -- so, unless you can be a little more specific (eg: show me a document that should be returned by providing the URL and the search that doesn't find it) I don't know what to say....

Also -- you do know you can plug in your email and see all questions you asked or "reviewed" right.

New TOM Architecture

Harry, March 03, 2003 - 5:58 am UTC

Hi Tom!
Are you really shipping books? I will provide you the details if you are starting one! Can you add me? Are you using Intermedia/Oracle Text for doing the search?
Can you explain the search facility briefly?

Hey - Mr "I'm just curious"

Steve, March 03, 2003 - 6:19 am UTC

Some of us are still having to work with 7.3.4 databases!!

Could "article only" be placed between question title and date?

Jukka, March 03, 2003 - 7:01 am UTC

Thank you very much for this superb site!

Usually I read AskTom with "by the week" -view. I open interesting questions with "Article only" links(middle click: tabbed browsing with Mozilla rocks!). However, it's quite difficult to click on right line, because there's a lot of space between question title and "Article only" link(at least for my eyes). I think it would be easier, if "Article only" link was located between question title and date field.

Tom Kyte
March 03, 2003 - 7:43 am UTC

check it out -- rearranged and color coded...

Could "article only" be placed between question title and date?

Jukka, March 03, 2003 - 8:25 am UTC

Wow! I think it's about 1 hour since I proposed this, and now it's done! Looks and works just great.

Thank You very much!


mo, March 04, 2003 - 10:14 pm UTC


if you search for 'Database Free Space' which is the title for an article you have it lists the found artivel as #24 on hte third page. Would not you list the exact match as #1 instead.

Tom Kyte
March 05, 2003 - 7:24 am UTC

we've had this discussion before. read

</code> <code>

it describes the scoring algorithm. Now, if you search for

"database free space"

see what happens. The text, just because it is in the "subject" is no more (or less even) relevant then if it is in the body, a review, a followup by me -- anywhere. I index a document, the "subject" is just text in a document.

"n days old" calculation

Tony Andrews, March 05, 2003 - 5:53 am UTC

When searching old postings I noticed that the listing diplays something like this:

October 24, 2001 (October 21, 2001, 3 days old)

i.e. the "days old" refers to how old the question was when it was last updated. Should this not be showing how long ago (from today) it was created?

Tom Kyte
March 05, 2003 - 8:04 am UTC

yeah, its the days between updates, i'll change that a bit soon

Nice ! Excellent ease of navigation

J, March 05, 2003 - 12:56 pm UTC

But, I think it may be time to get rid of the 'LET US KNOW'!
Top 30 questions today and only 7 are in 2003. It's already March!!

I can understand the need (i get most of the questions that I have answered by the time I page through) but I think you should limit it to the original poster and question. Got a sub question, post a new one. Some of these have started as one question/portion and by the last letusknow post, it has zip to do with the original.

Then there is Mo and Makito(one wants you to do his/her work and the other questions everything with no stats)

Dude, I can see your answering questions 365 days of the year (a geek like me) minimum 30 (3 lots of ten like you originally set about to do) but you gotta be up to 50 or more.

Take a break, we need you out here.

Tom Kyte
March 05, 2003 - 2:36 pm UTC

I don't understand this part:

But, I think it may be time to get rid of the 'LET US KNOW'!
Top 30 questions today and only 7 are in 2003. It's already March!!


Dates on search result pages

tom, March 05, 2003 - 2:02 pm UTC

What's with the 'days old' dates on search result pages? What date is the base date for these? Is this a bug or is there some sort of date calc that I am not aware of?

(Actual screenprint of search results screen converted to text)

{global} and {temporary} Approximately 179 records found.

1. insert into Temporary tables Score(99) Fri Feb 28, 2003 (Tue May 08, 2001, 661 days old)
2. Temporary tables (comparision with SQLServer) Score(59) Thu Sep 05, 2002 (Tue May 02, 2000, 855 days old)
3. Creating Temporary Table Score(47) Thu Jan 16, 2003 (Mon Nov 27, 2000, 779 days old)
4. Temporary tables and indexes Score(46) Mon Nov 12, 2001 (Thu Oct 19, 2000, 388 days old)
5. Best way to copy From Global TEMP Table to an empty Standard Table Score(40) Tue Oct 22, 2002 (Fri Oct 18, 2002, 3 days old)
6. create global temporary table foo as subquery produces no rows ! Score(40) Mon Jan 14, 2002 (Mon Jan 14, 2002, 0 days old)
7. Global Temporary Table Score(33) Fri Jun 28, 2002 (Fri Jun 28, 2002, 0 days old)
8. How do I pass a CLOB value to a stored procedure? Score(33) Thu Jun 13, 2002 (Tue May 02, 2000, 771 days old)
9. Connection pools and global temporary tables Score(26) Mon Jul 08, 2002 (Sun Jul 07, 2002, 0 days old)
10. pl/sql Tables Score(26) Sat Dec 08, 2001 (Sat Dec 08, 2001, 0 days old)


(Fri Oct 18, 2002, 3 days old)
(Mon Jan 14, 2002, 0 days old)
(Fri Jun 28, 2002, 0 days old)
(Sun Jul 07, 2002, 0 days old)
(Sat Dec 08, 2001, 0 days old)

(Tue May 02, 2000, 771 days old)
select sysdate - 771 from dual = 1/23/2001

Tom Kyte
March 05, 2003 - 6:45 pm UTC

it was (past tense now) the difference between the time the article was first submitted and the day of the last update -- confusing at best.

It is now the period of time from today to when the article was first submitted.

Sorry to keep using the LET US KNOW but,

j, March 05, 2003 - 5:18 pm UTC

what I meant was
(date in paran is original date)
1. New ask tom 05 Mar 2003 (19 Feb 2003) 2 weeks old

and at the time I checked in today
posted questions
1. to 30. had only 7 new ones
(the rest were older questions with reviews that were new questions)

Joel on Building Communities with Software

A reader, March 05, 2003 - 5:27 pm UTC

A bit off topic but interesting reading.

Joel Spolsky writes how forum software encourages or discourages certain kind of behaviour with design.

</code> <code>

book mark

A reader, March 06, 2003 - 1:03 pm UTC

when I try to book mark the page, It marks with the question id instead of subject ??

Tom Kyte
March 06, 2003 - 4:01 pm UTC

try it now ;)

it'll be the first 40 characters of the subject now. good idea.


Spawn, March 06, 2003 - 11:04 pm UTC

Congratulations Tom on your very successful site. I think everythings ironed up. A little more time and everything will be perfect. I congratulate you and your team for this.

And by the way, since your shipping books, why dont you have your own shipping company and post orders here in your site. (just kidding). I just cant believe that people are really thinking that you'd send them books.

Congrats once more. More power.

Very Cool ......

VL, March 06, 2003 - 11:15 pm UTC

Hi Tom,

I'm interested at the part "Your Questions", how can u implement it? track the same IP and show question histories?


Tom Kyte
March 07, 2003 - 7:51 am UTC

it is done by email address.

When you ask a question, I get your email (if I don't, you lose, no answer for you).

When you add a review, you have the opportunity to put in your email address.

If you do, I can find "your questions".

If you do not, I cannot.

the ip address doesn't work -- firewalls make many people look "the same"

book mark

v, March 07, 2003 - 4:05 pm UTC

Thanks for the book mark Tom :)

Dates again

A Reader, March 10, 2003 - 12:48 pm UTC

The last update date (the last date *you* responded, not a reader) is also quite useful, sometimes. You seem to have put both this date and the date created in columns - at least that's what I think the first date is.

Can you please label the columns? And, if you wish, put a delimiter between the two dates? Thanks

it was (past tense now) the difference between the time the article was first submitted and the day of the last update -- confusing at best.

It is now the period of time from today to when the article was first submitted.

Subject width

Scott, March 10, 2003 - 9:38 pm UTC


For the review regarding subject column width on the "Recent" page (re: recent q with looong subject)...
ditto on the truncation for the "Your Questions"... long subjects rather skew the page to be not as readable.


Tom Kyte
March 11, 2003 - 7:14 am UTC


Ability to search just the subject or body or both

Logan Palanisamy, March 11, 2003 - 1:42 pm UTC


It will be nice if there is way to restrict the scope of the search like
- The subject headings only
- The body of the message only
- or both

The default could be both.

IE "Refresh" button

Tony Andrews, March 12, 2003 - 7:20 am UTC

I'm not sure if this is an issue with AskTom or with IE, but I find that this happens:
1) I go to AskTom using Favorites
2) AskTom is displayed from cache, i.e. may be stale
3) I press Refresh
4) AskTom is refreshed, but now applying the search criteria from last time I performed a search. I then have to clear the seach box and re-query.

Tom Kyte
March 12, 2003 - 8:04 am UTC

I guess -- don't use refresh. Just click on my icon on the top of the page.

Alt text on recent article links

Howard Hammermann, March 12, 2003 - 11:16 am UTC

I like the alt text that pops up for the links to recent articles. It's nice to get a quick look a the question/response without having to follow the link. You might want to try it with a longer substring.


A reader, March 12, 2003 - 11:25 am UTC


Hi Tom, I was writing a question, it took some time, while I'm working now, but when I tried to send I got

Cannot accept new questions at this time. Please try again later.

I think there should be a timeout, this is once you are writing you question, and don't sumbmit the review in 20 minutes, then if there are not space for asking question then it is rejected.
But otherwise no, or I'm wrong?


I think it could be a good idea to put a cookie that save the name, email, and where in the world you are, because, I had seen that many people including me, fill it the first times, but after sometimes no.

Thanks :)

An error?

Joe, March 14, 2003 - 10:41 am UTC

I received following error on the home page :)
report error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

A reader, March 18, 2003 - 6:49 pm UTC

Congratulations for you new advanced search.

if you need some ideas, a good idea can be, to login, this gives more seriousness.

and record of the questions you read, to avoid reading again.

A qualification ( 1-5 ) to the questions, but from you.

( like metalink )

the posibility to add a category, because some topics are really dificult to find, because you search a word that is used in almost every article. ( import, export, materialized views, etc. )

Thanks :)

Tom Kyte
March 19, 2003 - 6:08 am UTC

to login implies "hey tom, i forgot my password, what is it", more email, more complexity.

I rather like the anonymous nature of it all actually.

"Advanced Search" Really rocks. Great!!!!

Logan Palanisamy, March 19, 2003 - 9:07 pm UTC

include in review database version

A reader, March 20, 2003 - 9:54 am UTC

Hi Tom, I think additionaly to the name, email and where are you, user should add the database version, because now people to a question about database 8.1.7 add question about database 9.x or 7.x.

Maybe you should not accept a review without all that data entered.

Search rows - results mismatch

Sameer, March 20, 2003 - 11:32 am UTC

Tom: I searched for the word "stragg" and the result said "{stragg} Approximately 11 records found." -- but only 1-6 rows are displayed.

Why is this mismatch ?

Tom Kyte
March 20, 2003 - 12:09 pm UTC

Main Entry: 1ap·prox·i·mate
Pronunciation: &-'präk-s&-m&t
Function: adjective
Etymology: Late Latin approximatus, past participle of approximare to come near, from Latin ad- + proximare to come near -- more at PROXIMATE
Date: 15th century
1 : located close together <approximate leaves>
2 : nearly correct or exact <an approximate solution>

- ap·prox·i·mate·ly adverb

I look at as the worldwide and shining example of the correct way to do these things. I mean -- they got it right. They do not count the exact hits, they approximate.

goto and enter:

polly wolly doodle all day oracle

At the top, on March 20, 2003 I saw:

Results 1 - 9 of about 11. Search took 0.12 seconds.

1-9 of about 11, hmmm -- they guesstimate too ;)

new ask tom

A reader, March 20, 2003 - 4:09 pm UTC

plze the link for new asktom

Tom Kyte
March 20, 2003 - 4:34 pm UTC

hmm, your already here -- it was just an upgrade.

the link is, was, has been, will be


A reader, March 21, 2003 - 3:10 am UTC

LOL, thanks

A reader, April 01, 2003 - 3:32 pm UTC

whay are we not using the horizontal place to the
asktom logo ?

Problem with Search?

Sri, April 03, 2003 - 1:33 pm UTC

Dear Tom,

Something strange happening with search. I had reviewed and followed up a topic "Error in creating .dat with SQL", I infact placed a question for you there. Later, When I wanted to see whether you have responded or not I put the string "Error in creating .dat with SQL" (with that double quotes) in that search text box and hit 'Go'. It responded with "{error in creating .dat with sql} Approximately 1 records found.", but I am not able to see the record that it found. I even tried Advanced Search, but no luck. How can I see your response now Tom?

Tom Kyte
April 03, 2003 - 2:28 pm UTC

got it - but in your case, you could have gone to the "your questions tab" as well...

Adding the release to the Review

A reader, April 03, 2003 - 3:35 pm UTC

Several times the original post is for example for Oracle 8.1.7 and there are several reviews, but not all uses 8.1.7,
it could be useful to ask this information in the review, when the reviewer is not who post the original post.

A reader, April 07, 2003 - 3:21 pm UTC

Only a idea, sometimes could be interesting to set underline, italic or bold some words.

How about emoticons. you type :( and a sad icon appears. It helps to express.

adding bookmarks

A reader, April 25, 2003 - 11:34 am UTC

Hi Tom,
when bookmarking a page, by default it could be a good idea to put the name of the question

Tom Kyte
April 25, 2003 - 3:35 pm UTC

it does doesn't it? not sure what you mean?

enlarging the text box horizontally ?

Alberto Dell'Era, April 25, 2003 - 3:07 pm UTC

Hi Tom,

what about enlarging horizontally the box that we can use to enter the text of the question, to match the actual width of the screen (perhaps moving away the "Enter you review" text that is currently sitting on the left) ?

This may help in trying to obtain a Good Look for the question, without jumping forward and backward by the "Preview Review" and "Edit Review" button ... especially for sqlplus output.

Today I missed a Question Round because, after jumping around, when i hit the "Submit review" the site kicked me away (I was the 11th, probably).

Besides, you know how much we Italians love to have a Good Look ;-)


What is Bookmark supposed to do?

A reader, April 25, 2003 - 3:49 pm UTC

This probably seems like a dumb question, but what is the effect of hitting the Bookmark this page with the link HERE, supposed to do? When I click it nothing seems to happen, it just redraws the current page. Is there a my Bookmarks somewhere that I am not finding? I use MS IE 6.0.


Tom Kyte
April 25, 2003 - 3:54 pm UTC

Look at the URL.

If you bookmark the "big" url - it has a sessionid in it. Eventually, that link will cease to function and will just take you to the home page. The "bookmark this page" link gives you a link that is stable enough to bookmark.

back ground color for search word

A reader, April 25, 2003 - 7:04 pm UTC

hi tom

before when i submit a search for word it appeare in defrent background color in the article search result but now the search word is not high lited with background color

Tom Kyte
April 25, 2003 - 7:55 pm UTC

that was a lllooonggg time ago -- the hilight function was not "url" friendly -- it broke more pages then it worked on so I took it out.

not approximate records found in advanced search

A reader, May 07, 2003 - 12:17 pm UTC

Hi Tom,
there is no no approximate records found in advanced search
I don't know if this is wrong or if this had been always in this way.

Tom Kyte
May 07, 2003 - 1:46 pm UTC

it was never there.

:) Thanks, May 07, 2003 - 1:52 pm UTC

hope you " DO NOT REMOVE ANYTHING " from your database

UMESH, May 07, 2003 - 11:43 pm UTC


Minnie, May 08, 2003 - 12:03 pm UTC

I have difficult to bookmark. After bookmark, where could I find all I've bookmarked?

Tom Kyte
May 09, 2003 - 11:51 am UTC

i don't know do you use the "bookmark" link I have?

what that does it takes you to a URL you can then bookmark with your browser. If you try to bookmark a normal page (which includes a session), when the session expires -- poof, there goes that page.

so you click on my link and then you can safely bookmark that link.

"hidden" folders

A reader, May 12, 2003 - 10:38 am UTC

Hi Tom
Where you can see a list of the "hidden" folder you have like
</code> <code>


Tom Kyte
May 12, 2003 - 1:07 pm UTC

there are no "hidden" folders.

That is Sean Dillons home directory. There is a ~tkyte where I keep some stuff as well.

A reader, May 12, 2003 - 3:47 pm UTC


export of all the articals

A reader, May 13, 2003 - 1:39 pm UTC

Hi tom,

1.) I think you store all the article in a table
if this is true can you give us an export
of this table so that I can import it in my local laptop
oracle db and I can read it/browse/search it off line ?

Tom Kyte
May 13, 2003 - 4:57 pm UTC

nope, but you can goto the archives -- download an entire week and read that anytime you want.

Every week is there.

Downloading the Archive works !!!!

A reader, May 13, 2003 - 7:41 pm UTC

Yup. The download works. Each week's archive comes to about 1 to 2MB.

I am planning to download all the weekly archives and burn it into a CD and take "asktom" wherever I go. I need not have an internet connection to look for a tip or something.

With this asktom CD and Oracle documentation CD, you are kind of covered.

"Asktom to go". That is the way to go.

do I have to down load everything one by one

A reader, May 14, 2003 - 10:12 am UTC

Tom Kyte
May 14, 2003 - 3:19 pm UTC

every week, yes.

there are over 7,500 articles right now and growing. I publish by the week in the archives. I am not going to put it out in any other sort of format. by week is pretty big already.

How to change the URL for index to local path if save the weekly archive to a HTM file

Minnie, May 14, 2003 - 6:18 pm UTC

Hi Tom,
I just want to say "Thank you for your wonderful work here'.
I don't want you to answer my following question because I know you are too busy for other important questions and it's not worth to waste your time.

Hi, a Reader upstair or anybody else,
I like your idea about burning CD. But, if the weekly archive is save to a html file, it's hard to locate one article because the link to it still points to Does anybody have any idea?

a book on ask tom

Reader, May 14, 2003 - 6:19 pm UTC

Have you thought about publishing a book on tips to resolve issues based on asktom questions and answers? I know your book addresses some of them especially "why my indexes are not used?" however, you handle so many issues day by day and it would be helpful to publish a paper back with the best tips for oracle users on most common issues. I will be the first one to buy!

open links in new window

vijay, May 14, 2003 - 7:43 pm UTC

hi: tom,

how about having the feature of opening the search result links in new window. would ease viewing.

showing all results on a page.



Tom Kyte
May 14, 2003 - 11:48 pm UTC


I hate it when web sites decide for me what I should be doing as far as windows go. Me, i use mozilla, it is all about tabbed browsing. I use a single window - no more, no less. I have 15-20 tabs open maybe not 15-20 windows.

I would HATE it if I opened a window/link.

You can shift or ctl-click as you see fit. It is what I do.

How to search on the downloaded weekly archive

A reader, May 14, 2003 - 7:51 pm UTC

Hi "Minnie from MA, USA"

If you click on the html link in the downloaded weekly archive file, it will come up with 'the page can not be displayed' error. But if you search on the Subject using the browser's "Edit -> Find", it will take you to the text below in the same html file.

Also, I copied all the weekly archives to a folder and searched using Windows File Explorer's "Search" (Ctrl + F) to locate all the files containing a particular text.

Of course, it is not as robust as the "Advanced Search" on Tom's web-site. But it does work.

Tool bar at the bottom

Logan Palanisamy, May 14, 2003 - 8:02 pm UTC


It will be good to have the tool bar "Recent | Archives | Advanced Serach | .. | .. | Question Review" at the bottom of some of the lengthy threads, right below where it says

"Was this response helpful to you? Let us know!"
"Bookmark this page with the link HERE"

That way we don't have to scroll up to the tool bar at the top to navigate.

No doubt, your site is a gold-mine.

Tom Kyte
May 14, 2003 - 11:51 pm UTC

Just hit "home"?

This is helpful

Minnie, May 15, 2003 - 8:43 am UTC

"A reader",
Thank you very much

How it archived?

A reader, May 15, 2003 - 10:51 am UTC

Hi Tom,
1. How did you put the article to weekly archives? According to Original Date or latest updated date?
I could not find this article that is original created in Feb 19,2003 in the weekly archive of Feb 19 2003. I tried to find some other articles in archives according to their original date but failed.
2. the suggestion of Logan Palanisamy is very good, I think.
If hint the "Home", one still needs to scroll up to the top of the page because HOME is also on the top.

Thanks in advance,

Tom Kyte
May 15, 2003 - 5:48 pm UTC

1) last update

2) Hint "home" meant hit the "home" key on your keyboard.

end -> end of page
home -> top of page

so, do the little "top" links on the page do it for you?

Security check!

Mark J. Bobak, May 15, 2003 - 10:51 am UTC

Hey Tom,

Looks like you made some changes so that folks who try
to "save" the "Submit a Question" button can no longer do so?

Some new security check in place? I suppose it's more fair
that way.


I did not understand

A reader, May 16, 2003 - 2:52 pm UTC

I did not understand the purpose of you "top" link. I click on it but nothing seems to happen. Could you explain ? I'm using IE 6.

Tom Kyte
May 16, 2003 - 5:32 pm UTC

it should take you to the top of the page. the link has #TOP in it (if you hover over it, you should see it).

At the top of my page, I have a TOP anchor.

If you are already at the top -- it will appear to do nothing (other then add #TOP to the URL)

Followup Part 2
I fixed the issue with the top link not working in IE6. For some reason using an anchor named "TOP" did not work, whereas renaming it to PAGETOP worked fine... (insert Microsoft dig here)

Thanks for the feedback,
A "Friend of Tom"


Same as above, May 16, 2003 - 5:37 pm UTC

I see "top" works in this IE5 browser I'm using right now, but didn't work with my IE6 at work...

Tom Kyte
May 16, 2003 - 6:26 pm UTC

sorry, I only test with open systems browsers, not ones limited to a single operating system ;)

Seriously though -- it is just standard HTML, if it doesn't work in IE6, that would be a definite "bug" -- but I don't have access to IE6 on any computer I own to test with.

Followup Part 2 (repost)
I fixed the issue with the top link not working in IE6. For some reason using an anchor named "TOP" did not work, whereas renaming it to PAGETOP worked fine... (insert Microsoft dig here)

Thanks for the feedback,
A "Friend of Tom"

I like about a 'latest' link?

Paul, May 17, 2003 - 7:58 pm UTC

I like the top idea, I find I scroll down far more than up, would it be difficult to a a latest link to jump to your most recent comments on a thread?

Change Request

Sikandar Hayat, May 18, 2003 - 9:25 am UTC

Dear TOM I use my own machine to browse ASKTOM so in case of any review or new post I have to enter Name, Email and Where in the world are you?: again and again. Is there any way that if once I entered should be stored in the cookies so next time the default values will be picked. This request is as you are improving it these days by adding new features. I hope you will not mind for this request.

A suggestion

David, May 19, 2003 - 4:30 pm UTC

I like very much the idea of going to the latest review. However, I think this should be located together with the "top" and "bookmark" links with every review. You can call it "bottom" -- making it intuitive and concise and contrasting with "top".

It would be better then, since it would be accessible everywhere on the page, not only on the top, where you are trying to put it.

Also, I would suggest you put the "bookmark this page" and "let us know" with them too, making it easier to access from anywhere. After all, a review can relate to the original question or to other reviews and followups. One can get lost when trying to find them on the bottom.


Tom Kyte
May 19, 2003 - 5:17 pm UTC

Ask and you shall receive (kind of). I added the bottom link because I think it makes sense. We are up to 3 links on that bar now, anymore would "muddy the waters" too much, so I am going to leave it at that for now and wait for more feedback from other users.

Thanks for your feedback,
A "Friend of Tom"

One More Link on the bar

Ganesh Raja, May 19, 2003 - 11:05 pm UTC


Can your also Put a Back to Questions Link there so that when we hit the back button on the browser we dont need to go thru all the links that we clicked on that page. ;)



Ganesh R

Why 'Do Not Publish' ?

Ashiq Shamsudeen, May 20, 2003 - 9:02 am UTC

Hi Tom,

just for a curious , from your followup
Answered, Do not publish 13456

Why you didn't published (though you've answered) these questions.Is any reason for this .Or you believe these questions doesn't help others??

Tom Kyte
May 20, 2003 - 1:03 pm UTC

and as of today, the numbers are:

Status Count
New, Never Read 15
Read, Not Answered 0
Read, Awaiting More Info 19
Read, More Info Has Been Supplied 4
Answered, Do not publish 14209
Answered, Publish 7455

basically, for every one you see, there are two more you don't. Many are of the form "hey, my database is slow, why?" -- my answer is along the lines of "hey, my car won't start -- why?"

or they ask the same question that has been asked -- and I just ask them "well, when I searched for <their subject> I found N hits -- what happened when you did that search?"

or they ask an in depth question about some tool I don't use -- and I send them to technet

why ?

robert, May 20, 2003 - 3:22 pm UTC

why is it that asktom never has a link to ?

Tom Kyte
May 20, 2003 - 8:50 pm UTC

because i don't know what is...

but if you mean -- then a simple search for on returns lots of hits. I reference it all of the time.

small feature request

Alberto Dell'Era, August 02, 2003 - 9:46 am UTC

Why not sending an automatic e-mail confirmation when a new question is submitted (or, better, making it appear under the "Your Questions" tab having a "new" status) ?

I've just submitted a new question and i'm not sure whether it has been accepted by the server; say the Internet Sea Monster has eaten it ...

Tom Kyte
August 02, 2003 - 10:05 am UTC

I've asked the guys who work on it to make it more obvious that "yes, we got your answer", thanks.

addition to the addition:

and they very quickly did. when you submit a question now, you are directed to a page saying "yup, question is in there and tom will be looking at it"

My Questions

Sikandar Hayat, August 02, 2003 - 12:15 pm UTC

The tab labeled "Your Questions" should be "My Questions". As when I click on this tab so I am going to view my questions.

Tom Kyte
August 02, 2003 - 12:23 pm UTC

think of it from MY perspective.

They are YOUR questions ;)

They are not 'my' questions. It is me speaking to you.

Default values

Sikandar Hayat, August 10, 2003 - 3:49 am UTC

On the new thread and on reply screens we have to enter name, email and location everytime. Is there any way to set my default values as I am only using my machine. So I should enter this info again and again.

One suggestion

A reader, August 22, 2003 - 1:52 pm UTC

For a new question I get an opportunity to be sent an email
every time you follow up. Can the same be done for
any page. Say if I review a thread and ask a question
and want to be notified as soon as you follow up.

WIll this be too much overhead on the system?



Tom Kyte
August 22, 2003 - 8:17 pm UTC

we'll keep it in mind. not the first request for it.

but, it bumps into the other request for "i want to know about updates of this question for the next N weeks" insead of "forever"

maybe we can do both sometime soon...

Re: One suggestion

A reader, August 22, 2003 - 1:53 pm UTC

oops - i guess I forgot about the fact that you don't
know about our emails. You could get it registered
through another link on all pages though?
Too much load though methinks!


Tom Kyte
August 22, 2003 - 8:18 pm UTC

when you followup, you can supply that.

yet another small feature request

Alberto Dell'Era, September 07, 2003 - 4:03 pm UTC

I'm not sure if i'm asking the same as Sikandar Hayat above, but when I submit a "review" by clicking on the link "Was this response helpful to you ? Let us know!" at the bottom of every page, I have to reenter name, email, and location each time, while when I ask a new question, the same infos are pre-loaded automatically in the form. What about extending this feature to the review form?

PS My thanks to the developers for having implemented my previous "small feature request" as per August 02, 2003.

fyi - caption of your new book on home page ...

Alberto Dell'Era, September 08, 2003 - 1:22 pm UTC

... still reads "new book coming soon".

That may distract junior asktommers ;-)

Tom Kyte
September 08, 2003 - 2:07 pm UTC

thanks, got it.

Something for the future perhaps?

A reader, September 12, 2003 - 10:49 am UTC

Love your site! However, I was thinking what would be nice is "next" and "previous" buttons as you're reading messages, instead of having to go back to the list of messages to select one. Hope I'm being clear.

Thanks for this great site.

Max Length of Message

Robert, September 18, 2003 - 2:33 pm UTC

what is the max on messages to/from us ?
Is each single message stored in a single row in a single column (cell) typed LONG ?


Tom Kyte
September 18, 2003 - 2:46 pm UTC

the original question is upto a 32k LOB...
the answer is a 32k lob as well...

each followup is a 32k lob
each of my followups to a followup is a 32k lob

the structure is a little like this:

create table questions ( id primary key, ...., question lob, answer lob );
create table followups ( id primary key, question_id references questions, text lob );
create table toms_followup( id primary key, followup_id references followups, text lob );

32K limit

robert, September 18, 2003 - 2:55 pm UTC

>> upto a 32k LOB...

That's because of PLSQL's limitation, right ?
Hmm..I wonder what happens if one enters more than that...
you got a "Hey! you entered too much data !" error page ?


Tom Kyte
September 18, 2003 - 3:28 pm UTC

no, you get a "request failed" sort of page :)

if someone puts in more then 32k -- they are probably posting an entire statspack report or trace file -- i don't want that!


Gary, September 18, 2003 - 10:51 pm UTC

Hey, we've got a view that's more than 32K.

[20 queries UNIONed together, 'cos we don't have CREATE TYPE privilege to turn
into multiple rows of
col1a, col1b,
col2a, col2b

Tom Kyte
September 20, 2003 - 4:50 pm UTC

can you explain further -- why do you need a create type to avoid a union?

you just need a JOIN????

take the query:

select col1a, col1b, col2a, col2b, ....., colNa, colNb
from ......

Call that "Q".. then:

select decode( r, 1, col1a, 2, col2a, 3, col3a, ..., N, colNa ) colA,
decode( r, 1, col1b, 2, col2b, 3, col3b, ..., N, colNb ) colB
from ( Q ),
( select rownum R from all_objects where rownum <= N );


Large view

Gary, September 22, 2003 - 12:28 am UTC

Doing the object route (as below) runs significantly faster than the decode(rownm...).
If we are rewriting the code, we want to switch to the faster solution, or get the DBAs to come up with a reason why we shouldn't.

create or replace type fred is object
(col_1 char(1) ,col_2 varchar2(26))

create type fred_tab as table of fred

SELECT tab_1.colx, tab_2.coly, tab_3.colz
tab_1, tab_2, tab_3,


Nihad Saleh, September 24, 2003 - 7:47 am UTC



Tom Kyte
September 24, 2003 - 9:48 am UTC

host computer? is that the server? you can get the hostname from v$instance and then use utl_inaddr to convert that.

is that the client? then sys_context( 'userenv', 'ip_address' ) gets that.

is there any way else

Nihad Saleh, September 25, 2003 - 7:38 am UTC


Tom Kyte
September 25, 2003 - 8:42 am UTC

sys_context( 'userenv', 'ip_address' )

gives you the ip address of the client -- with webforms that is the app server.

if you want the terminal ip address of the terminal where the gui is -- you'll have to pop on over to and get into the discussion forums there. I haven't used forms since about 1995 myself (way before webforms) and don't know if you can get it or not (or in the days of dhcp, nat, etc etc of what real use it would be)

how i use again document file stored in database ...?!!

Nihad Saleh, October 03, 2003 - 4:51 pm UTC

hi Tom
thank you for tell me how to save document file in database and it's work , but i don't know how i can restore it from database agin ..
let me explain the problem for you , i have a document file had stored on c:\nihad.doc and i save it in database. now the file had deleted from c:\ "partion", now i want to restore it from database to use it again , how i can restore it to c:\ partion again, please explain your answer for me step by step if you can , and thank you again

Tom Kyte
October 04, 2003 - 9:43 am UTC

you have to write a client application. There is no "unload to filesystem" option for blobs.

If you have my book "Expert One On One Oracle" -- i have a pro*c external procedure example in there that will unload to a directory on the DATABASE server (never the client)

You could do the same with a java stored procedure on the DATABASE server.

if you want it on the client -- whatever client you are using will have to retrieve it.

If you are using mod_plsql, you can use filedownload to do it easily, you would just return a mime type and lob locator to the mod_plsql module and it'll stream it to the client.

where are you ?

Nihad Saleh, October 04, 2003 - 8:09 am UTC

hi Tom
where are ?!!!! why you don't answer me , but i have to know how i restore document file from database to my hard disk again, please if you can ... answer me ok.

Tom Kyte
October 04, 2003 - 9:58 am UTC

geez, give me a break.

you do know that there really is "me", one person?

I've got a life sometimes too.

Yo, chill out...Mr Nihad Saleh

King of Yemen, October 06, 2003 - 3:30 pm UTC

Dude, what are you ? Prince Nihad of the kingdom of Yemen ? (giggle..) in case you forgot, but this is a FREE forum.
That means NOONE is obligated to ask or answer a question.
But maybe you're just used to have your own army of advisors & consultants....

We may be living in a savage & crazy world but let's keep this little corner of the web civilized & professional, OK ?

who are you Mr ... Savage ?

Nihad Saleh, October 17, 2003 - 5:06 pm UTC

hi man..
who are you Mr Savage >? but i want to tell you i'm not a Prince of Republic of Yemen .. but thank you man ? but i want ask you why you say that for me ..? can you answer me .
any way .. thank you again Man >>>

how i can find solution in minimume statment?

Esam Ebrahim, October 23, 2003 - 1:58 pm UTC

hi tom

i have two tables , the relation ship between them as the following
tableone A ( tabletwo B(
no number not null, no number not null,
dep number not null, name varchar2(60),
kind number, dep number not null)
age number)
assume we have these tables ok.
table b is the master table and assume we have in b this records
1 tom 1
1 esam 1
and assume table include this records
1 1 1 30
1 1 2 25
now , i want to select from B the name and from A all data but the records have kind =1 and note that relation ship between two tables

Tom Kyte
October 23, 2003 - 7:09 pm UTC

umm, simple join... very straightforward.

How I can find relation ship ?

Nihad Saleh, October 25, 2003 - 6:42 am UTC

hi Tom
i want to write function takes two parameters and return varchar2 , this function must find relation ship between two tables(it is the parameters ) and the result must be like that :
table1.column_name = table2.column_name.
now , how i can find this relation ship ?

Tom Kyte
October 25, 2003 - 8:17 am UTC

query the data dictionary -- user_constraints, user_cons_columns.

I hope this isn't for some generic routine to construct queries on the fly is it?

It is for report builder?

Nihad Saleh, October 25, 2003 - 4:27 pm UTC

Hi Tom
thank you , but i ask if you can help me to write the function return relation ship between two tables , i know about dba_constrints, or user_constraints but i want if you can help me to write this function in simple way .. i need this function for building dynamic report using the forms builder , user can choose the tables and columns which want to display in his report , i will make the sql and relation ship and where clause for his choice and then execute the sql and return the result to the txt file or html file or pass this sql to the report builder ,
this form is ready now, but it is still this function ,
can you help me .. ?

Tom Kyte
October 26, 2003 - 9:08 am UTC

You can start with this query and modify it as you see fit.  Bear in mind is makes the assumption:

a child is joined to a parent via the PRIMARY KEY and never just a unique constraint -- although that is not always the case.  Up to you to take this further if you see fit

ops$tkyte@ORA920> create table p ( x int, y int, primary key(x,y) );
Table created.
ops$tkyte@ORA920> create table c1 ( a int, b int, foreign key (a,b) references p );
Table created.
ops$tkyte@ORA920> create table c2 ( m int, n int, foreign key (m,n) references p );
Table created.
ops$tkyte@ORA920> variable child varchar2(30)
ops$tkyte@ORA920> variable parent varchar2(30)
ops$tkyte@ORA920> exec :child := 'C2'; :parent := 'P';
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> column fkey format a80 word_wrapped
ops$tkyte@ORA920> select decode( parent.position, 1, 'where ', ' and ' ) || parent.cname || ' = ' || child.cname
  2  from
  3  ( select '"' || a.table_name || '"."' || column_name || '"' cname,
  4           position
  5      from user_cons_columns a, user_constraints b
  6     where a.constraint_name = b.constraint_name
  7       and b.constraint_type = 'R'
  8           and a.table_name = :child ) child,
  9  ( select '"' || a.table_name || '"."' || column_name || '"' cname,
 10           position
 11      from user_cons_columns a, user_constraints b
 12     where a.constraint_name = b.constraint_name
 13       and b.constraint_type = 'P'
 14           and a.table_name = :parent ) parent
 15     where child.position = parent.position
 16     order by parent.position
 17  /
where "P"."X" = "C2"."M"
 and "P"."Y" = "C2"."N"

links problem

A reader, October 26, 2003 - 11:09 am UTC

the links in oracle documentation have moved considerably
disabling the links in many pages. Would it be a good idea
to use another level of indirection for the links that
get generated (they get generated dynamically.) That
way you have the info at one place and can automatically
update them when somethings like this happens.
Not sure how complicated your system gets if you do this.

It is work

Nihad Saleh, October 29, 2003 - 5:52 am UTC

Hi Tom
Thank you for your solution and it is work , but if the table ( master table ) has more than relation ship with many tables it does not work,and it's return all relation ship with master table but I added sqlstatment to your answer like this (
select DISTINCT decode( parent.position, 1, ' ', ' and ' ) ||
parent.cname || ' = ' || child.cname RELATION

( select a.table_name || '.' || column_name cname,position
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and a.table_name = child AND

(select a.table_name || '.' || column_name cname,position
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P'
and b.table_name = parent ) parent
where child.position = parent.position ;
) and it is working now , Thank you again and it is mazing idea from you ...

Tom Kyte
October 29, 2003 - 6:58 am UTC


my example explicity had 2 child tables????

Thank You again Tom

Nihad Saleh, October 30, 2003 - 8:49 pm UTC

Hi Tom
Thank you Tom, I had add that's statment to tell you what i added to your answer, and i want to tell you that your answer was very simple and very good idea , and without your answer may be i have a complex solution ,but with your answer i have small code and easy solution .
thank you again , and i hope if i can be in touch with you .


A reader, December 10, 2003 - 1:27 pm UTC

Hi tom, can you explain the search page on your site ?

I have similler search and java coders are coding the query !! and I want to explain them in their manner ( front-end point of view) how to query when they are using like operator.

right now they have only one query(huge and complex) that query's all the data no matter what fields are blank and it takes long time.

do you think there should be seperate query.
do you think after the serarch is performat all the
related (child data) should be pulled to gether
or wait for user selection and then query the related
data ?
e.g. let's say query returned 20 result do you think
they shold query all related data for all 20
records or wait for user to select one of 20
and then pull the related data ?

please guide



Tom Kyte
December 10, 2003 - 4:15 pm UTC

i don't use like

i use Oracle text to index the page and then use the sql contains operator to efficiently search.

using like isn't going to get them very far.

How does the bookmark work?

A reader, December 19, 2003 - 5:43 pm UTC

Tom, Sorry to bug you on this, but I am unable to use the bookmark feture of your site. Please explain what happens when I bookmark a page, where to go and see that?

Tom Kyte
December 20, 2003 - 9:40 am UTC

you hit bookmark to get a URL that will exist forever (he says confidently)

If you bookmark a page with a sessionid in the URL, it'll stop working in a while.

So, the "bookmark this page" link gives you a page that you can then really bookmark and get to next year.

need your help again

Nihad Saleh, January 05, 2004 - 1:58 pm UTC

1 1 110 TOM 1000
1 2 110 TOM 1100
1 3 110 TOM 1500
2 1 110 TOM 700
2 2 110 TOM 500
2 3 110 TOM 9000
2 4 110 TOM 450
1 1 111 NIHAD 900
TOM 1000 1100 1500 ...
TOM 700 500 9000 ...



Tom Kyte
January 05, 2004 - 2:26 pm UTC

(your shift lock key seems to be stuck)

But anyway, is there a resonable upper bound to the number of records for an empno? (you sort of need to know the number of columns).

that is, what is the:

select max(cnt) from ( select count(*) cnt from t group by empno );

and is that max(cnt) that is returned the true "max".

If not, if you do not know the maximum max, we cannot do this as a static query -- we would have to write a procedure that

a) runs that select max(cnt) query
b) dynamically builds a query with the proper number of columns
c) returns a result set (ref cursor)

do you want Max to sort it as one row

Nihad Saleh, January 05, 2004 - 4:27 pm UTC




Tom Kyte
January 06, 2004 - 7:56 am UTC

shift key -- hit it. yours is stuck in the caps lock position.

you need to now the max number of columns -- that is what the max is for.

assume it 100

Nihad Saleh, January 09, 2004 - 2:03 pm UTC

hi tom
assume that is the max of culomns in 100 ok , how i can write the select statment.

Tom Kyte
January 09, 2004 - 2:35 pm UTC

use this technique.

ops$tkyte@ORA920PC> select deptno,
  2         max( decode(rn,1,ename) ),
  3         max( decode(rn,2,ename) ),
  4         max( decode(rn,3,ename) ),
  5         max( decode(rn,4,ename) ),
  6         max( decode(rn,5,ename) ),
  7         max( decode(rn,6,ename) ),
  8         max( decode(rn,7,ename) ),
  9         max( decode(rn,8,ename) )
 10    from (select deptno, ename, row_number() over (partition by deptno order by ename) rn
 11            from emp )
 12   group by deptno
 13  /
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD


Sikandar Hayat, January 10, 2004 - 9:43 am UTC

I think the discussion is going away from the subject of this topic.

how i can use oracle tables in c++ program

Nihad Saleh, January 28, 2004 - 4:24 pm UTC

Hi Tom
how i can use oracle tables from C++ program to insert or delete and others operation

Tom Kyte
January 29, 2004 - 7:39 am UTC

read about OCI or Pro*C -- all documentation is on

How to bookmark

Gabriel, February 26, 2004 - 11:45 am UTC

Hello Tom,

Can you please tell me how can we bookmark pages on your website using the bookmark links that you have on the page.
Bookmark this page with the link HERE
Bookmark Review
Bookmark Page

Tom Kyte
February 26, 2004 - 2:20 pm UTC

You click on those links to get a "bookmark friendly" (static, unchanging) URL.

That url you can in fact "bookmark"

if you bookmark the nasty big urls with session ids in them, they "disappear" tomorrow. so, you click on the link to bookmark and then you can safely bookmark

Advance search by email not returning hits on reviews

Mark, February 26, 2004 - 3:01 pm UTC

I have always used the same email address when submitting Reviews. Search on this yields no hits.

Tom Kyte
February 26, 2004 - 4:21 pm UTC

yes, it is not part of this page, the emails are not exposed to the public.

if you could search them, you'd actually be able to see them here and that would be "bad"


linchao, February 27, 2004 - 2:18 am UTC

Hi Tom ,I cann't install Oracle9iClient on Win2000FamilySever. Could you tell me why?
Everytime I have installed it, it always tell
me "you install failed".
Thank you for answer my question!

Tom Kyte
February 27, 2004 - 7:30 am UTC

what is a "family server"?

I would guess you are using some "for home use without all of the features of a real server" edition of windows and that is not a supported, supportable configuration.

A reader, February 27, 2004 - 4:26 pm UTC

Tom, after a long time of using asktom I understand the problem behind several fool questions, the reason is the following:
Sometimes you need urgently something, if yuo would have time, you could read all the documentation, test and do it, even more there are times this is not urgent, but you really really don't have time, and is better to search in the internet.

So there is the need for how tos, ordered and clasified, ask tom is full of them, the point is, Oracle takes a lot of time and ivnestment (including asktom) to help customer to use better ( not to say only use correctly oracle), why not to start to do a
howto's by Oracle release, but seriously and planned as asktom is. Maybe as an additoinal metalink service.

The source can be ASK TOM, user input , who periodically be
inserting new how tos.

This is only another of my %)ideas.

Tom Kyte
February 27, 2004 - 4:38 pm UTC

you mean obe's

A reader, February 27, 2004 - 5:59 pm UTC

Exactly, but there you can't find for example
Java, run os comand in windows, linux, etc., etc., etc., etc., etc., etc., etc., etc., etc., etc., etc., etc., etc., etc., etc., etc.

And now there are too few topics and examples.

A reader, March 01, 2004 - 10:43 am UTC

Sorry Tom, but I think in this way, I'm right.?
what do you think, honestly about this idea?
I'm nut or this is not in what Oracle is t hinking to do now.?
Last time I ask it :)

Why there is an asktom site?

The reason I think is somewhere here, the problems are:
People works without read the documents
People don't have time to read the docuents
There is the need of an Oracle official works
where several dbas says the opossite.
One is not sure if "there is something more",
because when one have to apply something in
the production database wants to be sure
about what is happening.
Release and platform specific questions
There is the need of how to's, even when you know you
want to know if what you do is right.
Information and tested techniques in new
technologies Java is the best example, most don't know
java but wants to use it.
The information about one topic is spreaded in too
oracle documents.

I think asktom comes to solve some of this problems, but after one see the same question asked frequently 5 times, It took to me 4 hours to search in several questions if in one question was not the answer I was looking for.
And you are losing your precious times answered questions.

I think asktom is the first step, but only the first,
The question is

If Oracle wants to give the help to customer to use technologies without having a deep knowledge on them.
Doing more detailed how-tos

I'm not telling, Oracle MUST do this, I'm telling, the problem Oracle has is the misuse of his technologies, because Oracle database covers too much technologies, and dbas even when they want don't have time, or don't have money to learn, or are not sure about them or don't have enough knowledge about them, don't know they exists.

Most people can help, if there would be a place where I could paste my how-tos takeme sometimes days to find, I will do. And this will be helpful to other people.
Forum don't covers this kind of needs, there is a big difference between a place where you can paste your how-tos and share with other people, and a place where you ask anything.
Ordered by topic and goal, example Database:create database
And categorized by database release, type (standar, lite, enterprise) Database:odbc, etc.

And this can be really a very interesting support for new customer, say them "here we have a database where you can find the most asked how-tos"

about NOT IN

adnan Taha, March 02, 2004 - 8:00 am UTC

dear sir
i have question for u
i have this case
i have table A and Table B
i wanna write select statment to select data from table A , where the row not in table B
like this select c1 ,c2 from a where c1 not in
(select c1 from b)

but i dont want to use Not IN cause in because it make retieve time is slow , isnt it right

so can u help me to do it

Tom Kyte
March 02, 2004 - 8:55 am UTC

on the contrary, not in makes things blindingly fast

if you use the CBO
if the c1 values in B are "not null"


</code> <code>

methods and object types

Adnan taha, March 28, 2004 - 8:31 am UTC

dear sir 

i am started to work in OODB with oracle 

and i built oject type obj_info

  1  create type obj_info as object (
  2  obj_name varchar2(100),
  3  Obj_creation_date   DATE,
  4  Obj_Realease_no     NUMBER(3),
  5  Object_ojective        varchar2(150)
  6* );
SQL> /

and its done

also and i want to creat method on the data base
  1  create type func_type as object (
  2  wrd varchar2(200),
  3  member function IsSpace(word varchar2) RETURN boolean
  4* );
SQL> /

Type created.

SQL> create or replace type body func_type  as
  2  member function IsSpace(word varchar2) RETURN boolean is
  3  BEGIN
  4    if word = ' ' then
  5     return true;
  6    else
  7     return false;
  8    end if;
  9  END;
 10  end;
 11  /

now i have type called func_type
contains one method 

then i created table 
1  create table program
  2  (
  3   prg_id varchar2(100),
  4    prg_info obj_info   ,
  5    analyze_prog   func_type
  6* )
using the tow object types 


Tom Kyte
March 28, 2004 - 9:18 am UTC

we have OR, no OO. But anyway.....

have you read the Application Developers Guide for Object Relational Features?
</code> <code>

But in answer to the question, you cannot invoke that method from SQL since it returns BOOLEAN and BOOLEAN is not a sql type at all. You can invoke it in plsql:

l_data func_type := func_type('hello');
if ( l_data.isSpace() ) then....

wrt:I've updated the documentation links (finally) in the Q&A's

A reader, March 28, 2004 - 10:13 am UTC

Thanx a million.
I think Oracle is so popular because of TOM.

about Method and object Type

Adnan Taha, March 29, 2004 - 5:16 am UTC

okey sir about ur reply and thank u
and i have the book u told me about
i have also tow comment
1- if its return numer or date how i can call the method in sql
2- u declared varaible using this objtype but without
refering to the table ,, I mean now the method is part of the table , how i can revoke it using the table ,
i want to use the concept of encapsulation in OO .. the method and attribute r part of table , so how can i accses the method throgh the table

Tom Kyte
March 29, 2004 - 10:19 am UTC

1) lots of examples (hundreds) in that book!

select o.obj.method() from table o;

2) read that book!

Using Object types In form 6

Adnan Taha, April 03, 2004 - 8:14 am UTC

dear Sir
thank u very much for ur help

now i have my OODB , i designed all enttities and functions ,
BUT when i tried to use one of the functions which its a part of Object type IN THE FORM BUILDER

RPCs cannot use variable with schema level user-defined types in this release

Notes that i am using Form6
Forms [32 Bit] Version (Production)

so please tell me how to call and use functions and object types from inside the
form builder


Tom Kyte
April 03, 2004 - 8:52 am UTC

i haven't touched forms since march 1995, sorry -- i don't work with that tool

suggest you try -> discussion forums

A reader, April 12, 2004 - 6:47 am UTC


Can you provide us some link to read questions that could'nt make it to the archive ( questions that were not relevant )


Tom Kyte
April 12, 2004 - 7:16 am UTC

no. I publish what I want seen, I hide that which I do not.

The changing number

Arun Gupta, April 14, 2004 - 12:32 pm UTC

On the homepage of, in the bottom left corner, there is a number which keeps changing like .23, .24, .56 etc. every time I refresh the page. What is this number? I have been seeing and ignoring this for quite some time, but cannot contain my curiosity now.

Apologize in advance since this question does not pertain to any underlying technology.


Tom Kyte
April 14, 2004 - 2:08 pm UTC

the amount of time in hsecs spent in the database to build that page.

I use it as a "is everything ok at a glance" check.

number on bottom left of home page

Jim, April 14, 2004 - 4:01 pm UTC

Oh, I thought it was my personal random number generator for the lottery. No wonder I haven't won yet! <grin>


Natarajan, April 14, 2004 - 8:57 pm UTC

Hi Tom
Good day to you.
Your 'Asktom by week' is very useful. But things get
repeated very frequently. Could you also give 'Asktom by year'. Would you consider my request.
Thanks in advance.

Tom Kyte
April 15, 2004 - 8:19 am UTC

that would be a huge huge huge page -- I'd rather not serve that up. (many megabytes in size)

Just curious and being nosy...

robert, May 04, 2004 - 11:23 am UTC

Tom, just curious why is it you never ever required
"registeration" of any kind. This site is so interesting in the sense that it is so interactive but totally no-question-asked.

What's the spec of your new Linux server ?


Tom Kyte
May 04, 2004 - 1:49 pm UTC

people get uptight about registering. I know of people that refuse to access the free documentation on -- simply because of that. Like this guy:

</code> <code>

afraid to make up data to sign up for otn to get access to docs and forums....

I just want people to be able to use the stuff, to get what they need.

it's going to be some sort of 2 CPU xeon 2.8GHz, 7GB RAM Dell box.

Arun Gupta, May 10, 2004 - 3:11 pm UTC

Today I find that the time taken to build a page has gone down to 0.10 or less. Is this because of a faster server or Linux upgrade?

I am also facing a problem. I tried to search for this thread by entering The New Ask Tom in the search field, but this thread doesn't show up in the first 30. I had to look it up by going to My Questions. Then I copy/pasted from the original thread itself, but still no show.

Tom Kyte
May 10, 2004 - 8:33 pm UTC

new hardware probably accounts for most all of the speed.

I was running on 7 year old hardware, a 4 cpu sun e450.

We are now running on a 2 cpu Dell box (circa 2004 instead of 1997).

fixed the search.... thanks/

Package wwctx_api

Ahmad Darwish, May 12, 2004 - 5:48 am UTC

Hi tome
can you help me about how can download
wwctx_api Package from oracle.


recent/ new questions..

A reader, June 10, 2004 - 10:34 am UTC

Hi tom,

can we have one more tab in your menu for the NEW questions. I want to in general what are the new questions comming in. I mean you already have a pritty large knoledge base already.

Tom Kyte
June 10, 2004 - 5:05 pm UTC

under consideration, thanks

Feature request

A reader, June 20, 2004 - 3:21 pm UTC

Currently, if I post a question, I get email whenever you answer it and whenever you post a followup to someone else's review.

Can you please make it such that if I provide my email address even on any review I make, your system will send me email notification when you make a followup on that review? This way, I dont have to keep "polling" to see if you have responded.

Thanks for all the wonderful work you are doing.

Tom Kyte
June 20, 2004 - 4:27 pm UTC

i'll add it to the list

Thread-like Navigation

Jon, June 22, 2004 - 1:21 am UTC


Your site is getting popular everyday. Some of the questions are getting more and more responses. This will only continue to grow, which will create the bigger and bigger HTML page.

As a regular reader of your site, I notice that I have to hit the "Page Down" key and scroll up/down to find the right responses that I have not read.

Have you thought about creating a thread-like navigation (i.e. similar to tree or drill-down) to read specific response?



Tom Kyte
June 22, 2004 - 7:54 am UTC

do you mean like a table of contents at the top of the page?

Link tow Database

Ahmad Darwish, June 22, 2004 - 7:10 am UTC

Hi Tom.
I have tow database in one server like apdbs and fzdbs
whith Oracle 9i.2
in fzdbs i create databas link (aplink) as succesfuly;

now i have connect from client PC's
-- -----------------------------------------------------
SQL*Plus: Release - Production on Tue Jun 22 13:40:15 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production

when i execute statment 
SQL> select * from dual@aplink;
then the result is 
but when execute another statment like
SQL> select * from table_name@apling;

then you can show error
ORA-03108: oranet: ORACLE does not support this interface version

no rows selected

please can you hep me; 

Tom Kyte
June 22, 2004 - 8:56 am UTC

please cut and paste examples -- apling? or aplink? exact error including call stack if any.

also, can you try with a 9ir2 client to see if that is the root cause?

Ahmad Darwish

Dave, June 22, 2004 - 8:02 am UTC

Whats that got to do with the technology behind asktom?

Tom Kyte
June 22, 2004 - 9:18 am UTC

good point, I forgot to even look up to see if it was relevant.

strange site behavior

bob, June 22, 2004 - 8:17 am UTC


I noticed this behavior from my improper usage of your site navigation.

1. Enter search "generate dummy data"
2. Look at first ten and decide to go back to asktom home page by truncating url back to </code> <code>
3. This shows ten most recent posts, click next to get the next 10 most recent posts
4. Site resurrects search term from original query and gives me 11-20 for "generate dummy data"

I realize now that the proper way to get back to the most recent is through the icon in the top left, but thought this behavior was probably unintentional.

Tom Kyte
June 22, 2004 - 9:26 am UTC

I could not reproduce with firefox, but I see that the browser "internet explorer" seems to do that.

It's an IE Cache problem, not HTML DB problem. Set your cache settings in IE to "Every visit to the page" and *not* "Automatically", and you will not reproduce this problem.


A reader, June 22, 2004 - 9:51 am UTC

As another reviewer pointed out, your site is getting to be almost like Usenet and it desperately needs some of the same features. One super-duper "flat" page with dozens of conversations is just getting too confusing! It needs threading, tree-view, attribution, etc.

[PS: Are all these "forum" type features available out of the box in HTMDB?]

Database Link

Ahmad Darwish, June 22, 2004 - 12:13 pm UTC

Thank Tom about this answer.
but if you think u connoisseur dont burlesque from another
Every think if u whant answer me tell mu about
how can upgrade Sql*Net Version8 to Ver9.

thank connoisseur tom.

Tom Kyte
June 22, 2004 - 1:57 pm UTC

? huh?

Thread-like Navigation

Jon, June 22, 2004 - 1:24 pm UTC

do you mean like a table of contents at the top of the page?

Yes, it can be like a table of contents. For example, it can be like this site: </code>

Google has used similar navigation to display the Usenet messages.  This is one example with 30 posts, <code>

Google's UI of the Usenet uses frame. As a developer, you may not like frame. You can avoid it by using JS/DHTML.



P.S. The "Submit Review" button disappears after click on the "Preview Review" button. I remember all the buttons were available before.

Tom Kyte
June 22, 2004 - 9:03 pm UTC

well, I've always thought this a bit different then google groups (very familar with that, I've been known to lurk there as well).

this is more like a directed discussion, at least that was my intention.

I sort of agree/disagree, i'll have to stew on it a while.

Wide page

A reader, June 22, 2004 - 8:52 pm UTC

Sometimes when I click on a question, the resulting page has the 'Bookmark Page','Bottom', links to the faaar right of the page, so much that I have to use the horizontal scroll bar to get to it! Can this be fixed? Thanks

Tom Kyte
June 22, 2004 - 10:36 pm UTC

only if people stop putting in really really really really really really long links...

(yes, so noted -- I'm thinking sometime this summer to shutdown for a bit and just add this stuff)


sharique ejaz, June 23, 2004 - 12:37 am UTC

hello sir
plz give me idea to increase the db_block_buffers & buffer_pool_keep parameter.i am disabled to increase the size of both of them.i am getting this error...
>Alter system set buffer_pool_keep=8m;
>Alter system set db_block_buffers=5m;
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Tom Kyte
June 23, 2004 - 8:46 am UTC

insufficient data.

don't know if you have pfile or spfile.

don't know what version you have.

don't know if db_block_buffers is what you should even be setting, given that it is specified in numbers of BLOCKS and you are seeming to be trying to give it a "size"? perhaps you really meant db_cache_size.

do you have a DBA around to help you out?

Back button

dxl, June 23, 2004 - 4:47 am UTC

A lot of the time when i visit a question on this site i then press the back button to return to the previous page i get the Page expired message asking me to click on refresh button, when you do this it then asks you if you want to reload the page. This makes it 3 mouse clicks just to go back to the previous page -- very annoying!

It only seems to happen on this site and it doesn't happen all the time, only sporadically.

Why would this occur? If this is a setting in my browser, how come i have only had it on this site?

(I am using IE6)

Tom Kyte
June 23, 2004 - 9:07 am UTC

some of the pages are "large" with lots of text.

Perhaps you are having the same issue as:

</code> <code>

was. I use firefox on linux and have never hit this.

I just fired up vmware and windows/xp with ie6 -- i could not reproduce. so i would have to guess your cache settings might be a bit small.

I can say that tabbed browsing could be life changing for you. What I do is lock tabs so that links open in their own tab -- very nifty. if a given tab isn't locked, I can still ctl-click on a link and have it come up in its own tab. A single window with 10's of open pages..... IE is starting to look a bit anemic compared to other browsers out there again. The lack of tabbed browsing support makes it wholly unusable to me.

Sql*plus Proplem

Ahmad Abd Al Majeed, June 23, 2004 - 6:33 am UTC

dear sir Tom
i have question for u 
i have this case 
1- On My Server I have Oracle DataBase Release
2- On Clinet Pc I have Sqlplus Release
When I connect to database from the clinet It is Ok when I
Use Sql*Net Version 8

-- --------------------------------------------------------SQL*Plus: Release - Production on Wed Jun 23 10:32:19 2004
(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production

-- -------------------------------------------------------- But My problematic Is ,when execute statmen (Merge ) i se this Message 
(unknown command "merge" - rest of line ignored.)
if i Excute this message from the server then it is do success.
Can You Help Me Why apparition error from Clinet Pc.


Tom Kyte
June 23, 2004 - 9:32 am UTC

sqlplus is looking at what you type in.

if it sees something like:

SQL> begin ..... 

it knows "ah hah, a plsql block is coming, we will not look for a ';' to terminate this command -- we will wait for '/' instead"

when it sees:

SQL> select ....

it knows "ah hah, they are going to run a query, we'll wait for ';' at the end or '/' and then run it and print it"

and so on.  When it (in 8.0.6, many many many releases before MERGE) sees:

SQL> merge ....

it equates this to:


that is, both of those as just as meaningful to sqlplus version 806.  the application that is "sqlplus" plainly and simply does not understand what to do with "merge".

sqlplus is an environment -- it has many commands -- set, column, show, etc -- it looks at what you type in to try and figure out "what do I do with this, submit it to the server, process it as a directive locally, and so on".  with merge in 806, it has "not a clue what to do"

so, it is assuming you made a mistake and is telling you that. 

Sql*plus Proplem

Ahmad Abd Al Majeed, June 23, 2004 - 6:38 am UTC

dear sir Tom
Can You Help Me how about Install sql*plus Release On My Clinet
Or How Can Be Upgrade My Sql*Net 4 tune whith
Oracle Release On Server.


Tom Kyte
June 23, 2004 - 9:32 am UTC

insert cd
click on what you want
wait patiently
remove cd

? it is just a software install.

Ahmad Abd Al Majeed

Dave, June 23, 2004 - 9:08 am UTC

Stop posting new question in threads wwith which it has got nothing to do.

Wait for a time to ask a new question and ask it there.

Better yet, read the documentation - all installation guides are there

</code> <code>

relink old questions to new email address

Sean, June 26, 2004 - 11:33 am UTC

It is a nice feature to use email address to search “Your Questions”. I used my company’s email address, but now I left the company. Are there any ways to relink my old questions to my new email address (of course, this time I will use commercial email address so it will not expire)?

The reason that I want to change my email address is that I remembered that sometimes you send answer to me directly through email. Of course, I can still keep my old question in old email address and new questions in new email address, but this is not very convenient. Thanks so much.

Tom Kyte
June 26, 2004 - 7:01 pm UTC

Not unless I start making people log in, which I am not wanting to do.

URI for search

A reader, June 27, 2004 - 9:59 am UTC

Can you please expose a URI for searching your website?

For example, to search Google for a string called foobar, the URI is

</code> <code>


Tom Kyte
June 27, 2004 - 11:35 am UTC

I don't have one.

URI for search

A reader, June 27, 2004 - 6:43 pm UTC

Why isnt there a URI for search? Is it because of the way you have implemented HTMLDB or is this a inherent limitation in HTMLDB itself? Thanks

Tom Kyte
June 28, 2004 - 7:58 am UTC

our implementation that screen. Obviously not a limitation of htmldb, you can bookmark pages and such (i give you a nice link to do that).

the search screen as implemented wants a session id. no session id, you get redirected to the home page.

I'll see if we cannot set one up.



there you go..... a simple:

<form method=get action= > <code>
<input type=text name=p_string>
<input type=submit>

will let you add a search to your page.

Problem Related To Oracle.

Ravi Shankar Singh, June 28, 2004 - 8:18 am UTC

Sir, I have exported access table to Oracle . there are 18000 records. Record has been exported in oracle. But there is proble that when I write select command then Problem Occur. How It can be Remove . Give me soon Answer.

ERROR at line 1:
ORA-00904: "APPL_NO": invalid identifier
But when we create table in oracle from create command then no error is ocuring.


Tom Kyte
June 28, 2004 - 9:06 am UTC

perhaps the column is in lower case from access.

SQL> desc ravi

and see. 

A reader, June 28, 2004 - 10:35 am UTC

To Ravi Shankar Singh,

How is your question related with this thread?

web link

Sean, June 29, 2004 - 11:38 am UTC

Hi Tom,

Whenever I click the web link on your page, I lose your page. It will be nice if web link will pop up another browser.


Tom Kyte
June 29, 2004 - 4:14 pm UTC

ctl-click opens in a new window in most browsers.

or use firefox, I use it with tabbed browsing extensions, lock the tab and any link I click on opens in a new tab (single window, lots of tabs).

I despise popups and applications that open new windows. I want to control that behaviour (and so I use firefox.....)

Opening links in another window

Tak Tang, June 29, 2004 - 1:31 pm UTC

Hello Sean,

In Netscape Navigtor 7.1, you can open a link in a new tab by holding down CTRL while clicking on the link.

In MSIE, you can right-click, and select 'Open in a New Window' from the popup menu.

Hope this helps.


Opening links in another window

Sean, June 29, 2004 - 3:17 pm UTC

Thanks, It works.

Using tabbed browsing in Firefox

Sean, June 29, 2004 - 9:09 pm UTC

Hi Tom,

I just downloaded firefox. It is very cool.

But I have to right click to choose "open link in new tab". How do you lock the tab? Thanks


Tom Kyte
June 29, 2004 - 9:28 pm UTC

get extensions.....

We dont need threaded discussions.......please

A reader, June 30, 2004 - 6:27 pm UTC

Read about the suggestions to make asktom webpages threaded. I don't think it will be a good thing to do. This is not a discussion forum per se, but a qa forum where people followup with additional questions and Tom answer that. If you show each followup as threads, it will lose all the readability and appeal.

There is one thing Tom can/should do though. Just delete the totally out of place followups and remarks. Tom has been very liberal with people who ask totally unrelated questions. If you just delete any unrealted posts the moment you see them, that will not only improve the readability, but also prompt people to wait for your time to ask their questions.

BTW, I too downloaded firefox. Great stuff. Thanks.


A reader, July 06, 2004 - 11:46 am UTC

Not really related to anything about Oracle, but just came across </code>

Thought it was a neat idea and wanted to share.

For example, the URL for this page is <code>


A reader, July 08, 2004 - 10:00 am UTC

Just saw the link promoting Firefox at the bottom of your homepage. Great, FireFox is a great browser and needs all the publicity it can get as it nears its 1.0 release!

Mozilla Firefox

Pratap, July 09, 2004 - 2:18 am UTC

Thanks for the Firefox link on the home page. Took me just 1 min to download, 1 min to install and all personalized info from IE was imported!!! Also no popoups and inbuilt google toolbar. Firefox really rocks!!!


A reader, July 12, 2004 - 8:35 pm UTC

Assuming Google's spiders do a good job of crawling your site, the following keyword search in FireFox does a good job of searching from the location bar

</code> <code>

Basically, it is followed by your search string

a few suggestions

Sean, July 24, 2004 - 10:52 pm UTC

Hi Tom,

As an avid reader of your site, I have a few suggestions:

(1) I always waned to know when you released a new batch of answers, so I can check the answer. But there is only date, but no time for each question, so I lose the track whether I have checked the answer or not. It will be much nicer if you can put time somewhere on the home page, so we can know last 'White House News Release'. There are two places I can think of. One is add time in Last update columns:
Subject Last Update
Ask New Tom 17:00:00 24 JUL 2004.

If there is not enough space there, you can put it near Go button:
Search__________________ Go Last_update: 17:00:00 24 JUL 2004.

(2) Very often I have to go to 'Your Question' menu to check the old answer. But when I click the subject, it goes to the top of the thread, not the place I submit the comment. If the thread is very long, it is not easy to find the answer I wanted. It will be nicer to see the answer related to my question or comment right away if I click the Subject in 'Your Question' page.

(3) The date I am interested on 'Your Question' page is not the last updated date of the thread, but the date I submitted my last comment or question. Well at least you can add one extra column on that page (But I am not sure whether you have catch that info in your db).

Again thanks so much for your help

Tom Kyte
July 25, 2004 - 11:38 am UTC

1) ok -- added fmhh24 to the date format... down to the hour should be sufficient I think.

2) if you posted the question, it'll take you to the question (at the top).

if you reviewed, it'll take you to the "last" review you made on that page now by an anchor.

3) made that change too.... htmldb rocks, point click and ship as they say.

Just testing a review link

Tom, July 25, 2004 - 11:05 am UTC

So as I add the feature above -- of linking right to your followup in a question, I would actually have a followup to test with :)

Tom Kyte
July 25, 2004 - 11:11 am UTC

Test the link

Sean, July 25, 2004 - 11:29 am UTC

This is just to test the new link feature

The error message after the testing.

Sean, July 25, 2004 - 11:32 am UTC

Hi Tom,

Here is the error message I got when I try to use 'Your question' menu.

failed to parse SQL query:
ORA-00907: missing right parenthesis

Different error message after the testing

Sean, July 25, 2004 - 11:36 am UTC

Hi Tom,

Here is error message:

report error:
ORA-01722: invalid number

All three items work.

Sean, July 25, 2004 - 11:42 am UTC

Hi Tom,

Thanks so much.

Tom Kyte
July 25, 2004 - 11:52 am UTC

no fair testing whilst I'm typing in the future! :)

The size of "Last Update" column

Sean, July 25, 2004 - 11:59 am UTC

Hi Tom,

I am sorry. I thought that you wanted me to test it.

By the way, if you can increase the size of "Last Update" column one more character, the date and time will be in one line like:

25 Jul 2004 11am

Thanks so much for your help.

Tom Kyte
July 25, 2004 - 2:48 pm UTC

I was joking -- you were testing it as I was building it is all :)

the date and time are in one column on my screen? how wide is your window -- how big your font? it is just an html table that tries to fill the screen...

Time zone?

A reader, July 25, 2004 - 1:05 pm UTC

Just a thought -
While you at it, it might be worth letting the user select his/her timezone, and display results accordingly - rather than default to an EST.

Tom Kyte
July 25, 2004 - 2:51 pm UTC

sorry -- that would mean cookies, logging in -- something like that. Too much extra stuff -- stuff that many times just seems to make people go off "how dare you cookie me!"...

East Coast time is my time :)

"Tom's picks" (enhancement request)

Alberto Dell'Era, August 03, 2004 - 8:41 am UTC

Hi Tom,

today i've discovered this gem (your followup on "plsql commit optimization"):

</code> <code>

This is definitly a feature worth knowing, that i miss in June for whatever reason. I guess that you will consider it for inclusion in your next book, also.

Enhancement request: what about marking relevant answer like this as "Tom's picks" (or "important new stuff","soon in my book", ...), and grouping them under a tab (next to "FAQ","most popular"), so that we can get to important new/old stuff easily ?


Tom Kyte
August 03, 2004 - 9:04 am UTC

</code> <code>

is sort of that -- take the most popular/coolest every other month, make them "pretty" and print them.

Alberto Dell'Era, August 03, 2004 - 9:40 am UTC

Sure .. but imagine that when you fill your form to answer/followup, you can check a checkbox to make it appear in the "Tom's picks" tab, sorted by date. Not only we get a great real-time gem-miner, but you could make your bimonthly selection for oramag faster also. Excellent revenue/cost ratio, IMHO.


Adnan Taha, August 08, 2004 - 10:11 am UTC

Dear Tom ...

can u tell me about the requirment and configuration that i need to start working with SMS message , OR to send sms message to specific mobile number from oracle database..
what is the requirment and tool that i need ?

Adnan Taha

Tom Kyte
August 08, 2004 - 2:04 pm UTC

typically the "easiest, do it this afternoon" setup would be to use utl_smtp to send an email to your PhoneNumber@provider -- most providers have this facility, they receive email, parse it and SMS it to you.

Otherwise, you'd be looking at the Mobile offering that is part of the application server. It takes a bit of infrastructure to be an SMS sender.

If I wanted to be able to simply sms someone because an event happened in my database, my first response would be "what's your email"

mohamed, August 08, 2004 - 2:15 pm UTC

I want to make data base about students

Tom Kyte
August 08, 2004 - 3:28 pm UTC

uh huh, well go for it.

You would be the only one on this planet that knows what needs to go into your model now.

Don't really know what else to say -- other than most people buy a student system these days (we make one, many other companies do too).

mohamed, August 08, 2004 - 2:22 pm UTC

thank you - I want to show me some tables or some columns about database for student

Ahamed, August 08, 2004 - 2:30 pm UTC

how insert table about database student afires?

mohamed elw, August 08, 2004 - 2:49 pm UTC

I want some table about students database

using COM and PORTS

Nihad Saleh, August 16, 2004 - 8:37 am UTC

Hi Tom
i come back again, i want to know if there's packeges to use COM and PORTS in SQL . which use Fax device and Telephone and how to control of this device .

if you remmber me i was asking you too much but i had stoped for three months or more .

thank you Tom befor and thank you after .

Tom Kyte
August 16, 2004 - 6:57 pm UTC

you can load java into the database, and i'm sure there are more than one java package out there to do whatever.

you can invoke C as a stored procedure -- likewise as for java.

Question regarding Feedback from AskTom

Bill, September 03, 2004 - 9:15 am UTC

Once a question is submitted and the requester asks for notifications when the thread is updated, how does one turn that off? I thought it was for insuring I was notified when you replied, but I get a message every time someone adds to the thread and you reply to it. I did not anticipate that it could go so far (thought my initial question was fairly simple, but apparently many folks related to it and had more to add).

Since I have a browser window open to your page all day (and refer to it frequently to see what's new) I realize I didn't need the notification after all.

Thanks much!

Tom Kyte
September 03, 2004 - 12:56 pm UTC

there is no API to turn it off, i'll see if we can change the behaviour (to email only when I update the question)

Number of questions, answered followups reducing?

Arun Gupta, September 07, 2004 - 1:29 pm UTC

Just now I saw:

"In the last 4 weeks, I've taken 201 new questions, read 1,806 followups, and responded to 1,337 of the followups"

I am pretty sure that the number of new questions in the morning was 212, which dropped to 202 couple of hours later and now it is 201. Similarly, the 1806 number was 1811 in the morning. Since morning, I am watching that more follow up questions are being answered. So why these numbers are dropping?


Tom Kyte
September 07, 2004 - 2:13 pm UTC

because time is marching onwards?

the MV refreshes every so often -- as time goes on, the 28 day sliding window slides along with it........

why marvel

A reader, September 08, 2004 - 2:00 am UTC

you are using marvel (htlmdb) just because it is a product of oracle or is it really a good web design tool than the other that exist in the market.

Tom Kyte
September 08, 2004 - 9:27 am UTC

asktom and htmldb are virtually chicken and eggs -- which came first, did one help cause the other....

I use htmldb (i'm the first production htmldb application...) because quite simply it is the easiest, most productive, least maintanence way to do it.

virtually no moving parts (mod_plsql and apache and database -- turn url into stored procedure call)....

when I want to tweak something, all i need is a web browser and anyones computer (no development environment to be installed)

I already knew sql, so that part is a no brainer....

I hate writing GUI code -- so it does it for me.

Bookmark error

A reader, September 10, 2004 - 1:30 pm UTC

I bookmared one question 2 days ago. and today I wanted to see it. I got this error...


A reader, September 10, 2004 - 1:31 pm UTC

the url for that error looked like

</code> <code>

Tom Kyte
September 10, 2004 - 1:34 pm UTC

you did something wrong. A url for bookmarking on asktom would resemble:

</code> <code>

you lost part of it.

In order to bookmark (safely) a page on this site, you should click the bookmark link -- to get a short URL that has no sessionid -- and bookmark that.

Why you have $ sign in the table name ? why that convension ?

A reader, September 10, 2004 - 2:12 pm UTC

Why do you have $ sign in the table name ? why that convension ?

Tom Kyte
September 10, 2004 - 2:34 pm UTC

cause the guys who wrote that part of it liked to end tables in $'s (like the data dictionary -- file$,obj$, col$ and so on


Bob B, September 10, 2004 - 6:13 pm UTC


I think it'd be interesting to have a "Tom's favorites" reading area on your site. Basically, you mark whatever you want as a favorite and then there's a link on your site listing your favorite questions, answers, followups or whatever.

weak and useless suggestion: permalink

Alberto Dell'Era, September 11, 2004 - 2:11 pm UTC

Just noticed that Mark Rittman calls "permalink" what you call "Bookmark review/page" - what about borrowing it [if not already copyrighted :) ] ... less confusion with the feature that puts a page into the browser's bookmark list (at least I was confused as a novice asktommer).

Tom Kyte
September 15, 2004 - 3:35 pm UTC

Well, I changed the wording on the page to make it clear that the bookmark link is to goto a page that is "bookmark-able"

little bug on "new question" tab ?

Alberto Dell'Era, September 11, 2004 - 2:19 pm UTC

Clicking on the tab "New Question" fires:

"Sorry, I am not accepting new questions right now."

even if the home page, right now, sports the "Submit a new question" grey button.

Tom Kyte
September 11, 2004 - 3:00 pm UTC

not a bug, just means between the time you got the home page and clicked on the tab, the queue shut itself down, someone else got in there...

Alberto Dell'Era, September 11, 2004 - 3:26 pm UTC

>not a bug, just means between the time you got the home page and clicked on
> the tab, the queue shut itself down, someone else got in there...

Not quite, my "bug test case" was

- click on home page: "hey, the Grey Button!"
- click on tab - "I'm not accepting question..."
- click on home page: "Grey button still here!"

Redone it right now - reproduced.

Tom Kyte
September 11, 2004 - 3:37 pm UTC

ahh, i see, i'll have someone look at that later...thanks

Disappearing hyperlinks

Arun Gupta, September 15, 2004 - 4:06 pm UTC

There is a problem I am seeing with this page only (The New Ask Tom). When I load this page in browser, while the page is loading, I see the hyperlinks "Tom's latest followup | GOTO a Bookmarkable Page | Bottom". As soon as the page finishes loading, the hyperlinks "Tom's latest followup | GOTO a Bookmarkable Page | Bottom" disappear. The Information box also does not show.

Does not happen with IE. Happens with Mozilla FireFox 0.9.1. I have tried clearing the cache but still the same behaviour.


Tom Kyte
September 15, 2004 - 4:12 pm UTC

scroll right --->>>>>

Scroll right

A reader, September 15, 2004 - 4:22 pm UTC

Why do I need to scroll right in FireFox but not in IE?

Is this due to your use of the <PRE> tag which FF honors but IE does some wrapping of the text anyway?

Tom Kyte
September 15, 2004 - 8:29 pm UTC

don't know, i don't run IE

Disappearing hyperlinks

Arun Gupta, September 15, 2004 - 4:58 pm UTC

Thanks. So used to seeing everything on one page that didn't look at the scroll bar...!!

Tom Kyte
September 15, 2004 - 8:32 pm UTC

i'll probably have someone look at that, it is annoying.

Gold arrow..

Reader.., September 15, 2004 - 5:07 pm UTC

Tom, I wonder why the Gold arrow is only avaliable once you click on one of the links under the 'Most popular' category and not always ?

Tom Kyte
September 15, 2004 - 8:34 pm UTC

hey, that's neat -- didn't even realize that was there :)


A reader, September 15, 2004 - 10:31 pm UTC

Hi Tom I sent a letter to santa, asking to receive in chrismas a cookie saving my name and email in your site and copy it in every post I do, and the posibility to subscribe to receive emails from a thread every time there is some post.

I hope santa this year don't failme ;)

Error message

Lev Erusalimskiy, September 16, 2004 - 12:13 pm UTC

I think I found a small bug:
If I search for something like
{table} I receive an error message.

I don’t think that this bug is really important.

What is more interesting is how do you make this
nice error message? Usually HTML DB can't produce
a clear message.


Tom Kyte
September 16, 2004 - 1:09 pm UTC

fixed it -- that was just the default htmldb error message on that region as far as I know.

A reader, September 24, 2004 - 10:31 am UTC


What is this at the botttom of the page.

Width: 993


Tom Kyte
September 24, 2004 - 11:41 am UTC

it is a number

mine says 1260 (my screen is wider). we are looking at a fix for really long urls in a page pushing the buttons and links waaaaayyyyy over like this page has. temporary debug until we get back to it.

welcome back ...

Alberto Dell'Era, October 09, 2004 - 9:00 am UTC

... and I would be curious to know whether people used the search engine less or more while you were away - to see if asktom is used more as a knowledge base or as a "forum".
I bet on the knowledge base side :)

Tom Kyte
October 09, 2004 - 5:17 pm UTC

--------- ----------
21-AUG-04 67914
28-AUG-04 70442
04-SEP-04 68019
11-SEP-04 64674
18-SEP-04 74281
25-SEP-04 73071
02-OCT-04 70603
09-OCT-04 71099

not really -- about the same it looks (week by week) - these are the "week ending on that day" numbers....

Alberto Dell'Era, October 09, 2004 - 6:34 pm UTC

Imho, 10,000 searches per day means that asktom is used for "knowledge mining" - probably around 100 searches for each question or followup if memory serves.

(Unless people search for "Britney Spears", that is :)


Re: "we are looking at a fix for really long urls"

Lauritz Jensen, October 10, 2004 - 8:52 am UTC

I suggest, that you have a look at the <wbr>-tag, that can be used to mark points in words where the browser is allowed to insert a line-break. It is non-standard, but still works in at least IE and Mozilla. Just put a <wbr> in front of any "&" and after any "/" and "?" in the text (but ofcause not in the copy of the url inside the href-attribute). You could also put a wbr after each 50th letter in each word.

(The spelling feature seem not to recognize any words)

Tom Kyte
October 10, 2004 - 9:28 am UTC

problem is -- I'd have to start parsing the entire document text again.....

<a href=really_long_url>really_long_url</a>

it is not as simple as a "replace()" unfortunately.....

relatively low priority, just thinking about it in the back of the head for now.

Alberto Dell'Era, October 10, 2004 - 10:05 am UTC

why not simply

<a ref=really_long_url>really_long_url_truncated_to_100_chars</a>

Paranoid users can always look at the real url by moving the mouse over the link and looking at the bottom edge of the browser (at least in Netscape). And copy the url by right-clicking and selecting "Copy Link Location" (again in Netscape).

Tom Kyte
October 10, 2004 - 10:35 am UTC

i have to write the code to do that -- back to parsing again......

how i can hide the source code of my procedure

new programmer, October 11, 2004 - 8:20 am UTC

Hi Tom
some time i opened some procedure from oracle packages i noted that all the procedures body is gone and i found numbers , so how i can write like this procedure to hide my code if any one wants to open my procedure.

i hope replay me soon ...

Tom Kyte
October 11, 2004 - 8:46 am UTC

search for

wrap plsql

Logic behind Asktom

Riyaz, October 11, 2004 - 10:00 am UTC

I guessed like this :), Am I partially right

1. Recent
You are taking the latest FAQ's based on the posted date. Posted date is available in the table,

2. Archive --> Again based on the posted date, you are taking month-wise.

3. Most popular --> Based on the hits (mostly seen through by viewers)

4.Hot articles --> Whenever you review, you are putting under HOT category based on your assessment.

5. Category --> When reviewing, selecting the categoy under which it has to come.

Tom Kyte
October 11, 2004 - 10:13 am UTC

1) recent is sorted by "last updated", the time of last modification by me.

2) the archives are by "last updated" as well.

3) by hits, yes.

4) well, technically YOU are putting things into the 'hot' category, I don't review them, you guys do....

what wrap and where

new programmer, October 11, 2004 - 5:09 pm UTC

i don't understand wrap and how i can search ,and do you mean to search in developer or not ?
i need know how i can hide source code of my functions and procedures if any one try to see my code .

Tom Kyte
October 11, 2004 - 7:45 pm UTC


wrap plsql

into the search box on my home page.

Dave, October 11, 2004 - 6:38 pm UTC

go to </code> <code>

search for wrap, its not hard

Dave, October 11, 2004 - 6:40 pm UTC

go to </code> <code>

search for wrap, its not hard

Thank you Tom but...

new reader, October 12, 2004 - 7:25 am UTC

Hi Tom
sorry because i'm asking too much , but i want if you can to give me an example to wrap this procedure :
procedure Tom
for i in 1..10
dbms_output.put_line('your the best' );
end loop;
can you tom please .........

Tom Kyte
October 12, 2004 - 8:30 am UTC

wow, did you search?

believe it or not, I've been there, done that.

please -- search:

wrap plsql

thats all -- just do that. really, you'll find it! there is actually an article titled "Is it possible to encrypt PL/SQL procedure?" check it out.

thank you tom but it doesn't work

new programmer, October 12, 2004 - 9:47 am UTC

Hi Tom
i forget to tell you that i had search befor and i found an example to wrap a procedure , i did it step by step but when i do the command (@test_wrap.sql) it show an error said can't open file test_wrap.sql so i stoped their and asking you how i wrap it . i enter sql with scott/tiger user , now what i can do else asking your help ...

be patient man please .....

Tom Kyte
October 12, 2004 - 9:59 am UTC

ops$tkyte@ORA817DEV.US.ORACLE.COM><b>host wrap iname=test.sql oname=test_wrap.sql</b>

test_wrap.sql was the output of the command line utility wrap, which is what the links on that page point you to documentation wise. (did you see the first link, to the docs?)

The example went step by step to convert "test.sql" -- a file you create with your code into "test_wrap.sql" -- a file produced by wrap, the command line program, used to obscure plsql code.


Broken image

A reader, October 12, 2004 - 10:23 am UTC

Not a big deal, but FYI, there is a broken image right above the 'Rating' dropdown on this page (that I am typing the review in)

see what i did

new programmer, October 12, 2004 - 9:58 pm UTC

Hi Tom
Patient man , i want to see what i did and you can complete it ok please ... ,
sql>create procedure Hello is
procedure created
sql>host iname=tom.sql oname=tom_w.sql
SP2-0310:can't open file "tom.sql"

i stoped here what i can do to complete my work if you please Tom ....

smile please ....

Tom Kyte
October 13, 2004 - 7:53 am UTC

please -- go read it carefully.

I even cut and pasted it in bold above.

the name of the program is WRAP.
the documentation calls it WRAP.
I asked you to search for WRAP.
I told you test_wrap.sql was the output of a host program named WRAP
I said "a file produced by WRAP, the command line program"

what is missing from your "host" command is the command WRAP

did you not notice that your "host" command utterly failed?

tkyte@ORA9IR2W> host iname=x oname=y
'iname' is not recognized as an internal or external command,
operable program or batch file.


So, I'll just ask you -- please, read it slowly, read it carefully and double check next time. It seems sort of obvious that you missed the program name itself doesn't it?

AskTom on 10g ?

robert, October 13, 2004 - 6:45 pm UTC

Been away for a while....

hi Tom,
Is this site running on 10g now ?
Is it running on iAS or still just mpd_plsql/Apache ?


Tom Kyte
October 13, 2004 - 6:54 pm UTC

today, Oct 13, 2004 we are:

[tkyte@govt tkyte]$ uname -a
Linux 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux

[tkyte@govt tkyte]$ sqlplus /

SQL*Plus: Release - Production on Wed Oct 13 18:52:26 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

using html/db and the apache/mod_plsql that comes with it.

Thank you

new programmer, October 14, 2004 - 3:01 am UTC

Thank you tom but please don't be ungry next time..
any way i trying the same command and does not work thank you

Metalink like functionality when submitting questions

A reader, October 14, 2004 - 4:32 am UTC

In Metalink when we submit a TAR, it searches the database for related topics and shows the results. The user may find an answer to his/her query there, and not submit the TAR. Do you intend to put this kind functionality in this site? May reduce your work!

Tom Kyte
October 14, 2004 - 10:07 am UTC

I'm waiting for human being version 2.0 to come out. They'll have the ability to actually read the page that you submit stuff from and see the "hey, search" and use it.


Web Site Bug

Giuseppe, October 19, 2004 - 12:48 am UTC

This is not a review but a bug submission

If you go to this URL

</code> <code>

You will get the following error



Tom Kyte
October 19, 2004 - 8:54 am UTC

so, where did that URL come from?

Why we have to rad all the follow ups every time

A reader, October 19, 2004 - 8:33 pm UTC

Hi Tom for those frequent readers from asktom, and for your bandwidth, could be possible only to see the followups starting from one date, for eaxmple,
every time I see a question I'll only the follow ups done starting on 10ene2004.


Tom Kyte
October 19, 2004 - 9:02 pm UTC

how would that help my bandwidth?

I will not remove material, no -- it would be totally confusing

"hey tom, in the above you say...."

but there would be no "above"

Firefox/Archives Question

Sue, October 28, 2004 - 12:18 pm UTC

When I use the Archives link on this site and open up any week, I get very teeny tiny characters when using Firefox, but IE displays everything fine. (I used to think it was just the PC that I was using [I have a different PC which had Netscape & IE on it -- Netscape has the teeny characters but IE is fine], but I notice the same thing on another PC that has Firefox & IE.)

But, Firefox is working OK for any individual questions that I select -- it just seems to be the weekly sets on the Archives page. Am I the only one with this issue?

Thanks for the great site!

Just curious, Tom...

Kim Berg Hansen, November 09, 2004 - 7:37 am UTC

At the front page you say :

"In the last 4 weeks, I've taken 226 new questions, read 1,741 followups, and responded to 1,321 of the followups"

You've said in a followup response that you use a MV with a moving 28-day window for this.

I used your advanced search and search for timestamp between 12-OCT-2004 and 09-NOV-2004 - which should give me a window of 29 days midnight to midnight completely containing the 28-day window from 12-OCT-2004 <some hour/minute> to 09-NOV-2004 <some hour/minute>.

I'd expect about 226 hits, perhaps slightly more as my search time-window is slightly larger than the MV's time-window. I got 68 hits.

Don't misunderstand me, I'm not saying you don't do enough (on the contrary I'm amazed at how much you manage to help all the Oracle community, thanks) - I just got curious and wondered if the difference was due to errors either in the advanced search or the MV, or if the difference means that you get more than 3 unpublished questions for each one you publish?

(I suspect the last explanation is true and as I understand that most unpublished questions are of the form "see this question: <link>", then I'm amazed at your un-ending patience with people not knowing how to search for an answer before posting questions... And yes, I tried to search for an answer to this question but didn't find it - probably because I didn't search for right words :-)

Anyway, I apologize for taking your time with this non-essential follow-up - please ignore if you are busy :-)


Kim Berg Hansen

("Curiosity killed the cat"...)

Tom Kyte
November 09, 2004 - 9:13 am UTC

More and more and more the questions I recieve are not "post worthy".

the tally thus far since Jan 2000 is:

Answered, Do not publish 16,781
Answered, Publish 8,578

so, for everyone I've published historically -- there are 2 more that never see the light of day.

1 select status, count(*)
2 from (
3 select decode( status, 0, 'Not Read',
4 1, 'Not Answered',
5 2, 'Waiting for more info',
6 3, 'More info supplied',
7 4, 'Answered but NOT published',
8 5, 'Answered and published' ) status
10 where timestamp >= sysdate-28
11 )
12* group by status
ask_tom@ASKUS> /

------------------------------ ----------
Answered and published 67
Answered but NOT published 144
Not Read 6
Waiting for more info 9

So, in the last month..... 67 asked and published. 144 asked but not deemed by me to be "meaningful", 6 in the queue waiting for me to look at them.... 9 that I need more info on before I can comment....

the reasons for "not published" generally are:

o i answered with "so, when I searched for <their subject here>, I saw NNN articles, what did you see when you did that?"

o i answered with "so, when you read the form that I made you click a checkbox on that said 'i haven't touched forms for years and if you ask me a forms question I'm going to say goto -> discussion forums....."

o i answered with "well, I take questions about Oracle here...."

o i answered with "I've never seen a VB program :)"

o the question wasn't "interesting"

Curiosity satisfied... :-)

Kim Berg Hansen, November 09, 2004 - 7:48 am UTC

I think I found my answer in your response to a followup in this thread:

</code> <code>

Apologies for taking your time unnecessarily (should have spent more time searching before posting) - thankfully you read fast so I haven't wasted too much time :-)

New things happening?

Flado, November 30, 2004 - 9:26 am UTC

Recently my Opera started showing a toolbar with a "Newsfeed" button on it which opens something like a newsreader with the latest messages from the site; however, they contain only a few lines of the original post and a link to the whole thread, i.e., not very useful, at least not more than the home page supplies.
Question1: is that Sean Dillon extending his RSS Feed thingy?
Question2: am I just trying to use the RSS Feed in a way it was never meant to be used, and if so, what is the intended usage of this feed?

Tom Kyte
November 30, 2004 - 9:41 am UTC

No, I just added:

<link rel="alternate" type="application/rss+xml" title="Just Updated"
href="http: //" />

<link rel="alternate" type="application/rss+xml" title="Hot Articles"
href="http: //" />

<link rel="alternate" type="application/rss+xml" title="Most Popular"
href="http: //" />

it is in support of the "live bookmark" feature in firefox. You can bookmark that and use that instead of an RSS newsreader if you like.

it is the same exact RSS feed, unchanged, just accessible in a different fashion.


Magesh, November 30, 2004 - 12:05 pm UTC

It is really amazing how you find time/knowledge to answer so many queries so fast. I have noticed even if we post queries in Metalink, it takes time for them to reply despite the fact that there are obviously more than one person in customer support. You being just one person(I hope you are not cloned :>!!!), and also that you travel/address conference/meetings, I wonder how you manage this. Simply Great!!

(minor problem) new popup

Preview Review Addicted, December 06, 2004 - 8:52 am UTC

The new "q review" popup pops up anytime i press the "Preview Review" button ... intended to burn it into my mind, or mistake ?

Tom Kyte
December 06, 2004 - 12:09 pm UTC

absolutely intended to burn it into your mind.

totally 100% on purpose.

Alberto Dell'Era, December 07, 2004 - 7:03 am UTC

>The new "q review" popup pops up anytime i press the "Preview
>Review" button ...
>absolutely intended to burn it into your mind.
>totally 100% on purpose.

Please note that i said PREview and not REview button ;) - i press PREview *dozens* of times before hitting "submit" (i bet you like a well-formatted q or r, don't you ? eg without wrapped sqlplus output), and so i get the popup dozens of times for a single review - i can live with it, just wanted to let you know.

Tom Kyte
December 07, 2004 - 10:23 am UTC

ahh, i see -- i'll look at that when I get a chance, thanks

Pl comment on it...

Sikandar Hayat Awan, December 07, 2004 - 10:38 pm UTC

Sorry for posting here but was unable to get any other suitable place and wanted to inform you ASAP.

</code> <code>

This page, from the SQL Server Worldwide Users Group ( suggested that Oracle expert Tom Kyte was now working for SSWUG! (emphasis added)

On Fetching, Storing, and Indexing
Available for Members. See Related Articles

Article Abstract:

(Tom Kyte) Our technologist fetches sequentially, stores inline, and indexes globally.

We've got a problem concerning an ORA-01002 error in a PL/SQL block. We learned that this error sometimes happens under special circumstances when updating selected data, such as when using a SELECT for an UPDATE. But this is not the case here. The strange thing is that we're getting this error only when reading data from a cursor, without any updates on the cursor data.

Read this article...

Key (Please note):
(R) - registration may be required for access at the target site
($) - target site may require paid membership for access to this or other content

Tom Kyte
December 08, 2004 - 10:18 am UTC

I contacted them when I found out about it, asked them to pull my content (because I felt it was presented in a misleading fashion) and they did.

it was easy, didn't need to write a flaming 'article' or anything to have it happen.


A reader, December 08, 2004 - 11:03 am UTC

I agree that the the contenct was misleadng,
and the site should have presanted the matiral in proper way

but I don't think that it is an issue if somebody sell the links after searching,organizing and summrizing it because there are many-many links which can mis-guide the new commer over the internet,and may not know the "Right way"
to perform the search.

clearly </code> <code>guyes tried to make money and fame (?) out of it.

In anycase, If you are oracle user, you should be smart enough to search it your self on the internet. and if you are not you sould pay for it.

Tom Kyte
December 09, 2004 - 12:34 pm UTC

I agree -- it was just that in this case

a) it looked like i was working for them
b) it was very unclear that the content wasn't theirs
c) they added absolutely NOTHING -- there was no summary stuff happening, they just used the first two paragraphs of my content -- leading to the problem in a)

how i can write the same as following

NIHAD SALEH, December 12, 2004 - 4:46 am UTC

Hi Tom
please note this statment :
select 1,level,ename,null,to_char(empno)from emp
connect by prior empno=mgr
start with job='PRESIDENT';


Tom Kyte
December 12, 2004 - 10:00 am UTC

I'm left wondering what this might have to do with the original subject?

but I couldn't really understand the goal either. "i want the level one be ther col_name and the value col_id"????

it is a tree

Nihad Saleh, December 13, 2004 - 2:01 pm UTC

Hi Tom 
  we use this statment to build tree in oracle developer ok,note the folloing statment and the output :
SQL> select 1,level,ename,null,to_char(empno) from emp
  2  connect by prior empno=mgr
  3  start with job='PRESIDENT';

         1      LEVEL ENAME      N TO_CHAR(EMPNO)
---------- ---------- ---------- - ----------------------
         1          1 KING         7839
         1          2 JONES        7566
         1          3 SCOTT        7788
         1          4 ADAMS        7876
         1          3 FORD         7902
         1          4 SMITH        7369
         1          2 BLAKE        7698
         1          3 ALLEN        7499
         1          3 WARD         7521
         1          3 MARTIN       7654
         1          3 TURNER       7844

         1      LEVEL ENAME      N TO_CHAR(EMPNO)
---------- ---------- ---------- - ----------------------
         1          3 JAMES        7900
         1          2 CLARK        7782
         1          3 MILLER       7934

14 rows selected.
- nowi have this table and it's data:
col_id col_na doc_id  doc_na  s_id   s_na     st_id st_na
   1   scince    1     Tom     1   oracle        1   nihad 
   1   scince    1     Tom     1   oracle        2   robeen
   1   scince    2     weliam  1   internet      1   nihad
  now the out put must be as the above values can you tell me how i can do it , the table structure is 
create table subject (
col_id        number,
col_name      varchar2(40),
doc_id        number,
doc_name      varchar2(40),
sub_id        number,
sub_name      varchar2(40),
stud_id       number,
stud_name     varchar2(40),
primary key (col_id,doc_id,sub_id,stud_id));
now i hope the main idea was understod now i'm waiting for your replay ...

thank you for your fast replay ..    

Tom Kyte
December 13, 2004 - 2:25 pm UTC

not only do I not understand the question.....

I don't see any sort of relationship between it and the original question on this page!

(did you read the big message box that popped up?)

check out the fault!!

Akhilesh, December 18, 2004 - 1:37 am UTC



create or replace procedure drop_object_proc
(type_in IN varchar2,name_in IN varchar2) IS

cursor_name integer;
row integer;

dbms_sql.parse(cursor_name,'drop :object_type :object_name',dbms_sql.v7);




execute immediate 'drop :object_type :object_name';

when others then

ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -903 is out of
ORA-06512: at "TEST.DROP_OBJECT_PROC", line 40
ORA-00903: invalid table name
ORA-06512: at line 2

Tom Kyte
December 18, 2004 - 8:57 am UTC

what does this have to do with the this thread?

but the answer is simply "you cannot bind an identifier". just parse 'drop ' || object_type || ' ' || object_name;

client privileges check

Akhilesh, December 28, 2004 - 5:17 am UTC


I want to know is there any SQL command by which a client can check the privileges given to him by his DBA. This would help client in saving his time, because some time without knowing about the given privileges client gets irritated (some queries which he wants don't run) also there is time loss.


Tom Kyte
December 28, 2004 - 10:36 am UTC

well, seems the client would know immediately by running a query that "i don't have the priv to do so".  Seems faster then having them look at a list and seeing if they belive they might have access by role "X" or priv "Y".

But anyway:

select * from session_roles;
select * from session_privs;
select * from user_tab_privs;

sys@ORA9IR2> create user a identified by a;
User created.
sys@ORA9IR2> grant create session to a;
Grant succeeded.
sys@ORA9IR2> @connect a/a
a@ORA9IR2> select * from session_roles;
<b>I've granted this to public...</b>

a@ORA9IR2> select * from session_privs;

<b>only other thing we got going for us...</b>

a@ORA9IR2> select * from user_tab_privs;
no rows selected
a@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> grant select on t to a;
Grant succeeded.
ops$tkyte@ORA9IR2> @connect a/a
a@ORA9IR2> select * from user_tab_privs;
GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
A                              OPS$TKYTE
T                              OPS$TKYTE
SELECT                                   NO  NO

Correction required...

Sikandar Hayat Awan, December 29, 2004 - 10:36 pm UTC

I was visiting "Files" tab and found wrong date 16th 2005 as I think it should be 2004?

"Top 5 things done wrong and 10g bits/bytes presented at the Ann Arbor OUG meeting November 16th 2005."

Tom Kyte
December 30, 2004 - 10:39 am UTC

you would be safe in that assumption :) fixed it, thanks

Errors can give wealth of information :)

David, January 14, 2005 - 12:38 pm UTC

when bookmark the question and got back to it later
(using mozilla, I guess my cookie is gone or never existed)
get interesting error:

Looks very interesting in the context of your "SQL Injection" article :))



Have a good week!

Tom Kyte
January 14, 2005 - 8:11 pm UTC

why does that look "interesting"?

To bookmark a question on this site, use the links that provide "safe" bookmarkable links that won't change.

i want to know where is the error

Nihad Saleh, January 14, 2005 - 1:47 pm UTC

Hi Tom
note this please :
sql>conn system/manager
sql>create table my_bfile(nam varchar2(255),
the_bfile bfile);
sql>create or replace directory nih as 'c:\nihad';
directory created.
now realy i have a doc file in this path c:\nihad\nihad.doc ok .
sql> insert into my_bfile values('doc 1',bfilename('nih','nihad.doc'));
1 row created.
commit complete.
until now every thing is ok .

sql> declare
l_bfile bfile;
select the_file
into l_bfile
from my_bfile
where nam='doc 1';
if dbms_lob.fileexists(l_bfile) = 1 then
dbms_output.put_line('valid file');
dbms_output.put_line('not a valid file');
end if;
if dbms_lob.fileisopen(l_bfile) = 1 then
end if;
error at line 1 :
ora-22285: non-existent directory or file for fileopen operation
ora-06512: at "sys.dbms_lob", line 475

my question is where is the error , but i sure i have afile in this path c:\nihad\nihad.doc
i saw your all example on your web but i don't know where is the error , it is in my path or my directory .

i hope to replay me soon .
thank you tom .
nihad saleh

Tom Kyte
January 14, 2005 - 8:38 pm UTC


yes, it is

Nihad Saleh, January 15, 2005 - 5:50 am UTC

Hi Tom
thank you for your fast replay , but i want to tell you that i'm working on my personal computer .
and the folder c:\nihad exists in this partion

thank you
nihad saleh

Tom Kyte
January 15, 2005 - 9:48 am UTC

insert into my_bfile values('doc 1',bfilename('NIH','nihad.doc'));

or, you have to create or replace directory "nih" ...

thank you

Nihad Saleh, January 15, 2005 - 11:26 am UTC

Hi Tom
thank you for replay , and it is work now , that is mean the problem was a capital latter it is all "nih" must be "NIH" thank you tom , do you know i have been never take any course in oracle just i'm reading a books tell me what the best way to be very good in oracle, which books must be read or something like this,just know i'm programmer .

nihad saleh

Tom Kyte
January 15, 2005 - 4:07 pm UTC

I'm fond of the books I wrote.

Others that I've read and appreciated are on the "links I like" tab above.

RSS validation error

Franco, January 17, 2005 - 3:15 am UTC

Hi Tom,
I would like just report this error (Feed validator):

This feed does not validate.

line 107, column 18: 'utf8' codec can't decode byte 0xb4 in position 4805: unexpected code byte (maybe a high-bit character?) [help]

<title>What?s your oppinion about the DBA job in the future</title> ^
Source: </code> <code>

(the '^' is actually under 'What?s' question mark, but i'm not able to post this with the right format...).
So my RSS reader ()is not able to update the latest headlines since 6th january.

Tom Kyte
January 17, 2005 - 10:29 am UTC

Hi Franco, Sean here. What RSS reader are you using, so I can try to reproduce the problem. The feeds work fine for me in Sage/Firefox (even the entry you're talking about). Thanks.


Feature request

Bob B, January 19, 2005 - 12:12 pm UTC

Can you please show the minutes component of time for the "Last Updated" field on the home page?

where is the error

Nihad Saleh, January 20, 2005 - 2:05 am UTC

Hi Tom 
  plz tel me where is the error, i want to tell you befor i had created a table as 
create table bfile_t(name varchar2(30),
                     the_file bfile);
ok , now i had been inserted into this table a row with doc file , the file exist in c:\nihad\nihad.doc,
see this code :
SQL> declare
  2    l_bfile bfile;
  3   l_dir varchar2(2000);
  4   l_filename varchar2(2000);
  5  begin
  6    select the_file into l_bfile from bfile_t
  7    where name='doc1';
  8    dbms_lob.fileopen(l_bfile,dbms_lob.file_readonly);
  9    if dbms_lob.fileexists(l_bfile)=1 then
 10       dbms_lob.filegetname(l_bfile,l_dir,l_filename);
 11       dbms_output.put_line('Directory Alis :'||l_dir);
 12       dbms_output.put_line('file name :'|| l_filename);
 13    else
 14       dbms_output.put_line('not a vaild file');
 15    end if;
 16    if dbms_lob.fileisopen(l_bfile)=1 then
 17      dbms_lob.fileclose(l_bfile);
 18    end if;
 19  end;
 20  /
Directory alis :NIH                                                             
file name :NIHAD.DOC                                                            

PL/SQL procedure successfully completed.
now , i want to retrive it from db so see this code :

SQL>create or replace procedure file_n is
  4      l_lob    bfile;
  5      l_amt    number default 30;
  6      l_off   number default 1;
  7      l_raw   raw(4096);
  8  begin
  9      select the_file into l_lob
 10          from bfile_t
 11           where name = 'doc1';
 13      owa_util.mime_header( 'WORD/doc' );
 14          begin
 15             loop
 16       l_lob, l_amt, l_off, l_raw );
 17                -- it is vital to use htp.PRN to avoid
 18                -- spurious line feeds getting added to your
 19                -- document
 20                htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
 21                l_off := l_off+l_amt;
 22                l_amt := 4096;
 23             end loop;
 24          exception
 25             when no_data_found then
 26                NULL;
 27          end;
 28  end;
procedure created
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error 
ORA-06512: at "SYS.OWA_UTIL", line 323 
ORA-06512: at "SYS.HTP", line 860 
ORA-06512: at "SYS.OWA_UTIL", line 373 
ORA-06512: at "SCOTT.FILE_GET", line 13 
ORA-06512: at line 2 

ERROR at line 1:
ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB
ORA-06512: at "SYS.DBMS_LOB", line 656
ORA-06512: at "SCOTT.FILE_GET", line 16
ORA-06512: at line 2


Tom Kyte
January 20, 2005 - 10:27 am UTC


the problem is the OWA_UTIL package. It has not been initialized.

nm owa.vc_arr;
vl owa.vc_arr;
vl(1) := 'WEB$';
owa.init_cgi_env( nm.count, nm, vl );

will initialize it, use that for your testing session.

but please, search this site for wpg_docload and use that approach -- infinitely SUPERIOR for returning a blob.

Dilip Patel, February 08, 2005 - 12:59 pm UTC

You may want to update "Do you use Linux? then you need rlwrap" link, now since that website has changed quite a bit.


Tom Kyte
February 09, 2005 - 1:34 am UTC

thanks, removed it.

a Thanks Button

A reader, February 18, 2005 - 9:37 am UTC

Hi Tom, why don't you ask a button to send thanks, for example I want to say you thanks I press that button rate the response and that's all.

Because now you have to add a post-reply.

hope this helps.

Automatically changing of IP

Amit Gupta, February 22, 2005 - 6:12 am UTC


In my (R51 IBM Lap Top) there is problem of IP automatically getting changed everyday after I shout down. Before I start work , everyday, I have to assign (Static) IP. I have tried with antivirus like Norton, Trend Micro Office client but no respective virus found.

I am having XP as OS and 2003 for Office with Intel (R)Pro/100 Ve Network

Tom Kyte
February 22, 2005 - 8:40 am UTC


don't know what else to say - welcome to windows?

Dave, February 22, 2005 - 9:06 am UTC

well perhaps your machine is set to DHCP and your sys admins want to keep it that way

The New Ask Tom

Ashutosh Prasad, February 22, 2005 - 10:23 am UTC


Your site is quite a popular reference in our dba-friends group. Can you give PostId/ThreadID for the titles you post, please? That will make the reference to your postings MUCH DETERMINISTIC.

An extension to this request would be to give Numbers to each of your follow up (e.g. if the postId is 2345, and it is your 7th follow up response, the question/response part will have # 2345.7 . But this is a minor request.

Giving your postings a numeric ID will be a big help.

Keep up the good work. It is wonderful.



Tom Kyte
February 22, 2005 - 11:34 am UTC

this already totally exists.

see the text "goto bookmarkable page"?

or "GOTO a page to Bookmark Review"

in firefox I just right click on those links and "copy link location"

A reader, February 22, 2005 - 11:08 am UTC

</code> <code>

Tom would now find it difficult to ask people not to use 'u' 'ur' etc :-) B4N ;-)

Tom Kyte
February 22, 2005 - 11:37 am UTC

No I don't, trust me on that point.

I'll just get to the point where I outright delete them as garbage. Poof, away they go.

SMS stuff

A reader, February 22, 2005 - 12:29 pm UTC

Perhaps you might also want to get rid of your emoticons. They are not standard English and can cause syntax readability issues when included in parenthetical notes (like this one :)). One good replacement is to surround actions in an uncommon character combination (like this *smiles*).

On a side note, I thought I had met the elusive 'u'. Someone was talking about a lecture by Prof. 'u'; it turned out his name was 'Yu' though. Very disappointing.


Mr. Stanley Thomas, February 22, 2005 - 2:13 pm UTC

I want to know what is oracle design. Is there a course in school that you take or major in and what is the major?

Tom Kyte
February 22, 2005 - 5:15 pm UTC

Oracle's design is "relational"

there are tons of books on it, there are dozens of courses on it.

Seaching 'AskTom' site

Praveen, February 23, 2005 - 2:24 am UTC


I have seen something very disappointing the way people can use your site to find information. There are a lot of redundant questions (same questions asked several times) and same redundant answers (instead of a link to the answer you have already given) -(this is something in complete contrast to our database principles, right? ;). This, I believe, is due to a poor search engine used by your site.

For example, a search string like 'IN' returns no results! I am sure nobody looks for a dictionary meaning of 'exist' in this site. Unfortunately, the search engine is merely doing that. Returns all pages that contains the word
'exist'! How about, then, a search like 'IN vs Join'?

This is something too disappointing considering the fact that the site one of the best in the world in terms of sophistication and quality of information contained-among all computer related sites. As with my experience using your site, most of the information (about 90%) are given as follow-ups rather than as an answer to a particular question that starts the thread. Just feeling bad about my inability in makeing use of your site to the fullest advantage.

My suggestion is to add a littel more intelligence to the search engine (like keeping a keyword database and links to them from the page), provide facilities for compelx strings like 'IN vs Join' or 'IN and Join' (instead of searching as separate strings). Its not too difficult (for your team) since our assumption is we are using this site for database related queries only.

I am not sure if somebody had already raised a similar complaint -- you know why ;)

And, ofcourse, please remember to keep a pointer in the homepage itself about how to use your site.

Thankyou very much for giving due importance to this mail.


Tom Kyte
February 23, 2005 - 3:43 am UTC

in is sort of a popular word don't you think? virtually EVERY SINGLE DOCUMENT has the word "in"! goto google, search for in

Results 1 - 10 of about 2,900,000,000 for in

2.9 BILLION -- oh boy, that's useful eh?

think about it (and try the advanced search perhaps????)

Searching 'Ask Tom'

Praveen, February 24, 2005 - 1:42 am UTC

That was the same thing I was trying to say above, Tom. 'IN' is a very common word in English language. But if I give a search string like "IN" in this site, obviously I am not looking for a meaning of the word 'IN', but problems and issues related to oracle key word 'IN' - that make more sense in a site specifically discusses database related issues, Oracle in particular. I will be expecting the search engine returns pages that contains the oracle key word 'IN - not the English word 'IN'! The search engine needs a little more intelligence to distinguish between the two, apart from maintaining something like a 'metadata' about the information content in the site. This will help to search more complex search-strings like "IN vs Join" or 'IN and Join'.

Google is a very generic search engine. 'Ask Tom' site deals with only Oracle database related issues which ofcourse has a very finite set of 'keywords', right?

Tom Kyte
February 24, 2005 - 5:39 am UTC

but every single page has the word IN in it.

Unless I sit here and categorize every page, this would be a non-workable solution.

there are 26,000 pages, and some 60,000 followups and 40,000 responses to followups.

No way I'm even going to consider categorizing each and every page.

I find that if you type in the subject of your question, what you would have named your subject had you posted a question, you generally find what you are looking for.

More sugestions....

J. laurindo Chiappa, February 24, 2005 - 8:10 am UTC

yes, is out of question a solution requiring any type of page handling. An idea : to search for pages with "IN" usage in Oracle, surely WILL exist in the pages a small SQL code with the "IN" keyword. The "IN" can be written i many ways, BUT in Oracle syntax always must exist a "(" after that. So, if could be possible search for "IN nnnn (" - ie, "IN"+"whatever charactersincluding none or ENTER+"(" , we will find pages with texts like :

IN (nnnn


IN (nnnnnnn



etc. Surely this pages WILL contain the desired articles about the "IN" keyword using in Oracle.



Tom Kyte
February 24, 2005 - 8:39 am UTC

well, that fixes "in", but basically -- I'll stick with what I said -- search for your subject and you'll be surprised what you find.

search for

in versus exists

for example -- see what you see.

Spelling ?

KP, March 04, 2005 - 9:03 am UTC

The home page has this in bold:

If you want to peek at raw material for the 2cd edition of Expert One on One

Is the #B"2cd"#B a mistake or is it #B second #B ?

Tom Kyte
June 18, 2012 - 11:03 am UTC

2cd is actually an accepted term as far as I understand it, it is commonly used in writing and written text.

It is the 2cd edition, no mistake there.   

Google 2cd and you don't find much other than references to 2 CDs

A reader, March 04, 2005 - 9:49 am UTC

Google 2nd and it's a totally different story.

Tom Kyte
March 04, 2005 - 10:15 am UTC

I don't know, way up on the hitlist "Stop Smoking Hypnosis 2cd Set And Book"

or one of my favorite albums:

30th Anniversary 2CD Edition of The Rise and Fall of Ziggy Stardust

It does seem to me that 2cd 3rd 4th 5th and so on....

oh well, back to the database stuff

No, seriously... is it 2cd or 2nd?

Vladimir Andreev, March 04, 2005 - 12:00 pm UTC

Not that I am a native speaker of English, and living in Germany certainly does little to improve my English skills, but I do a lot of reading in that language, and I have never seen before the word "second" abbreviated as "2cd". Nor does "define: 2cd" find anything, in contrast to "define: 2nd".

And the things you quote are nowhere near the top of the list when googling "2nd". Only when googling "2cd".

Not that it matters much, either. Just nitpicking.


Tom Kyte
March 04, 2005 - 1:51 pm UTC

I've fixed it.

Bolding works funny

Vladimir Andreev, March 04, 2005 - 12:05 pm UTC

Your pound-B tags for bold do seem to cause problems. I bolded just two letters in my previous post above but it is all in bold now :-(
The preview showed it correctly.
Sorry, I didn't mean to shout.


Flado, 2cd <> second but 2nd does.

A reader, March 04, 2005 - 1:26 pm UTC

Tom Kyte
March 04, 2005 - 1:59 pm UTC

i've corrected, thanks

where do I post my question?

ap, March 07, 2005 - 6:47 pm UTC

could you please tell me where I can post my questions?

Tom Kyte
March 08, 2005 - 7:45 am UTC

when I am taking them, there is a link on the home page.

remote database

Nihad Saleh, March 10, 2005 - 3:50 pm UTC

Hi Tom
i Have a database on server1 and anther database on server2 how i can access data on server2 if i work in server1 .
Thank you

Nihad Saleh

Tom Kyte
March 10, 2005 - 7:36 pm UTC

database links, not that this has a THING to do with the thread.

4950 and 4951 (No, not those ORA numbers!)

Rajesh, March 11, 2005 - 3:42 am UTC


When I get into your home-page, the url says " </code> <code>". However, if I change it a bit (from 4950 to 4951), it gives a similar page, but with little backdated messages. (The question details are upto date, but the messages are very old!)

Is this 4951, a kind of testing/staging instance ?! Just curious to know!

Rajesh S

Hey, it wasn't me who started all the shouting

Vladimir Andreev, March 11, 2005 - 4:28 am UTC

Gosh... I had to look at the page source to find this in a followup above:

Is the &quot;2cd&quot; a mistake or is it second ?

So, let's build a hypothesis: The poster(KP from Singapore) "closed" their bold-tags with the opening tag ('#'B) instead of '#'b. So we now have four open tags. We can stop the shouting in this thread by putting four "bold-close" tags.

Now, you'll have to take my word for this, but I tested this hypothetical solution in TEST (a local copy of the page and by using the "Preview Review") and it seems to be correct (after I fixed a minor bug :-).

Let's now deploy the solution in PROD. There are four '#'bs you don't see on the next line:

There. Am I still shouting?
Hope not. I have no way to be 100% sure until I see it posted (after all, the preview didn't reveal the problem the last time), and it'll be too late for fixing it then.

Tom, you might want to do something about these unbalanced pound-Bs, e.g., check that all of them are closed within a single followup.

Flado :-)

hai tom

mahesh, March 11, 2005 - 5:08 am UTC

Tom we have visited u r website and its very nice.

i am seraching for an answer to myproblem, but i do't know
how to post a problem to u r website. Can i know u r mail-id
for post my problems.

please give me the reply

Tom Kyte
March 11, 2005 - 6:20 am UTC

who is "u"
what is "r"

and see the homepage, it explains whether

a) i'm taking new questions
b) i have too many questions in the queue already and am not taking new questions.

For new comers on this site - Tom hates abrreviations especially 'u'

A reader, March 11, 2005 - 2:38 pm UTC

And ofcourse arguing without sufficient proof ;-)

Tom Kyte
March 12, 2005 - 8:20 am UTC

funny thing is, in order to put the post up, the poster had to have read the disclaimer.

I am not alone in this "SMS" speak issue.

how get data by data link in cursor

Nihad Saleh, March 14, 2005 - 2:47 pm UTC

Hi Tom
thank you about data link , now if i want to get data from server2 from emp table through data link if i work in server1 i want the cursor like this :
cursor nih is select empno from emp;
for i in nih loop
end loop;
what i can modify on this cursor(declare
cursor nih is select empno from emp) to get data from server 2 assume that i have data link name is 'nihad'
can you help me plaese ..

Thank you again ..
Nihad Saleh

Tom Kyte
March 14, 2005 - 2:49 pm UTC

from emp@dblink_name;

if you know about dblinks, if you read about dblinks, it would have been somewhat "obvious" as all of the examples are right there.

and hey, what does this have to do with the original thread again?

sory tom

Nihad Saleh, March 14, 2005 - 3:56 pm UTC

Hi Tom
sory may be i don't explain my problem will so you write
from emp@data_name right i want the data_name is a variable ,that i can change it any when i need because i have many data link to many servers can i

Tom Kyte
March 14, 2005 - 7:48 pm UTC

that would be called "dynamic sql"

plsql guide shows how to do that -- but don't do that unless you absolutely (and still wondering what this has to do with this thread, but anyway...)

"Look inside" Expert one on one

Menon, March 16, 2005 - 3:19 pm UTC

on asktom home page - takes me to amazon.
When I click on look inside it shows the whole
edition (the staring eyes one!;)) Just wanted you
to know in case it needs be corrected...


Tom Kyte
March 17, 2005 - 7:51 am UTC

it doesn't show the book -- just the table of contents and index?

not really but..

Menon, March 17, 2005 - 10:53 am UTC

I got thrown off by the "Look inside" on the image itself
(on amazon).
Since when you click on it, it shows the old edition
(both TOC and cover page) Might want to replace it
with the apress reprint TOC and image...
It does say below the image "Look inside another edition of the book".

I am sorry!!

Menon, March 17, 2005 - 10:55 am UTC

I just realized there was a typo in my first comment (a major
one!!) I meant "old edition" - not "whole edition"! I can
see why you might have been concerned! :)

how i can stroe image

Nihad Saleh, March 27, 2005 - 12:11 pm UTC

Hi Tom
how i can store image in database , but i want take image from scanner to the database ,that is mean i want write function to make sacn to my photo by scanner and at same time store it in database ...

Tom Kyte
March 27, 2005 - 12:18 pm UTC

well, I can tell you how to get the image from your APPLICATION to the database, but you'll have to ask your scanner vendor how to get the image from the scanner into your application.

</code> <code>

Searching on asktom

IK, April 01, 2005 - 7:51 am UTC


Always wanted to post this because i do a lot of searching on your site. I always felt it would be great if you could highlight the searched keywords on the pages displayed. Not sure if it is possible but..say i searched for "dbms_output.get_lines"...i can see many questions as search results but..opening each page i have to browse through line by line to see where get_lines is there.

Please give it a thought the next time you make enhancements.

Thanks a zillion for the wonderful service you are doing to the oracle community.


Invisible, April 01, 2005 - 10:09 am UTC

Tiny little question...

Why do the links across the top ([Recent], [Archives], et al) use JavaScript? Why not just a regular link? (Sorry - it's just a tad annoying when I try to open them in a new tab and get a blank page with some JavaScript URL...)

Tom Kyte
April 01, 2005 - 11:17 am UTC

htmldb is "that way"

Personally, I concurr ;)

question : how can I get the information ?

van aken marleen, April 05, 2005 - 5:12 am UTC

In the past I was using this website very often.
But now when searching something; the hits are given.
But they aren't shown somewhere.
Where can I see the hits of my search-command ?

Tom Kyte
April 05, 2005 - 7:32 am UTC


not sure what you mean. when you search, you get a hit list, the articles are all there.


Invisible, April 06, 2005 - 9:07 am UTC

Hi Tom.

Sometimes the initial page of AskTom takes a little while to come up. (Also if I do a search from there...)

I thought I saw it somewhere, but I can't find it now... Is there something I can do to the URL to get your server to tell me how long IT thinks the request took? (I don't really think it's your server that's slow - probably either my proxy being lazy, or just the fact that you're on the opposite side of the planet...)

As always, thanks for being such an Oracle GOD! :-)

Tom Kyte
April 06, 2005 - 1:16 pm UTC

look in the lower left corner, time in seconds is there, like 0.15, right above red bar.

Oh yes :-D

Invisible, April 07, 2005 - 5:45 am UTC

You're quite right of course... ;-)

0.1 seconds... gee... that doesn't sound particularly slow to me. Yup, it's my equiptment that's slow! :-S

OOC... what's the really tiny faint grey number?

Tom Kyte
April 07, 2005 - 10:42 am UTC

That really faint grey number (thanks to Tyler Muth) is the time it took for your client to create the page once it recieved the first byte.

So, the number right above the red bar -- time in database.
the number right below, the time in the web browser (view page source and ctl-f for starttime to see the javascript that does this)

So, if both numbers are *small* then the time is spent not in database, not in client. Time spent navigating web (could be slow DNS for example) and contacting my webserver.

If database number is small and time spent from first byte to last byte is big then one of three things could be true:

a) slow network, took long time to get from first byte to last byte.
b) slow client, data got there but the web browser rendered it really slow
c) a and b combined.

A reader, April 07, 2005 - 9:01 am UTC

God!! Can you believe this?

Tom Kyte
April 07, 2005 - 5:29 pm UTC

Well, I guess if you cannot find anything technically wrong -- and you know what, they should be able to -- I make mistakes all of the time and I like being corrected.

Question Authority -- the title of a slide I use in pretty much every session I do. I say "I have 17 years of Oracle experience, don't trust a thing I say until I convince you though because I still remember how it worked in version 5 sometimes...

They have to find some other way...

And here I thought
</code> <code>
his companies motto prohibited that sort of direct, not thru anonymous third parties that may or may not actually exist, bashing as not professional. Guess they had a change in policy.

I don't know about you, but it strikes me as a bit "daft" to start an article with:

It's can be very difficult to get credible information from some of the Oracle-owned forums such as

and then not actually point out anything wrong with the technical content?

Strikes me as very daft...

A reader, April 07, 2005 - 9:05 am UTC

And you can no longer post a link to this site!!!


Invisible, April 07, 2005 - 11:01 am UTC

You've thought of everything, haven't you? ;-)

This page rates 5.147 seconds. (Well... it is rather long!) Anyway, next time a page seems to take too long, I will know what to look at. :-)

What's the "Width: 1216" all about?

(Oh God - this guy is going to get so bored of me! :-/)

Tom Kyte
April 07, 2005 - 11:54 am UTC

My page timings are about 0.062 for that second number (always very small)

and it isn't because I'm in the Oracle network....

5.147 seconds would be painfully long.

How about others -- what two numbers do you see at the bottom of the home page? And where in the world are you?

A reader, April 07, 2005 - 12:19 pm UTC

.09 and .07(grey one)

I am getting 0.844 for this page

Menon, April 07, 2005 - 12:32 pm UTC

Tom Kyte
April 07, 2005 - 12:45 pm UTC

there are two numbers :)

I see only these numbers.

Menon, April 07, 2005 - 1:47 pm UTC

Width: 974
and bottom the number 0.938 (this time.)
I looked and stared quite carefully this time.
And I am using mozilla :)

Tom Kyte
April 07, 2005 - 4:17 pm UTC

there is one number above the bottom red bar (time in database)
there in another number below it (time to receive and paint the page)

on the left hand side.

RE: I see only these numbers.

Mark A. Williams, April 07, 2005 - 4:34 pm UTC

You need to be viewing the *home* page, not a question page to see both of the numbers. The question page does only have a single number.

- Mark

Tom Kyte
April 07, 2005 - 4:44 pm UTC

ahh -- thanks!

Oh, and my numbers are

Mark A. Williams, April 07, 2005 - 4:36 pm UTC

Oh, and my numbers at the moment are:


They vary but that is fairly accurate of the norm.

Thanx Mark!

Menon, April 07, 2005 - 4:37 pm UTC

That is it! I was looking at this question page.
At home page I see 0.07 (above bar) and 0.031.
Wonder why the questions have only one number though...

Tom Kyte
April 07, 2005 - 4:45 pm UTC

cause only the java script is on this page.

it was from testing the gzip module to see how it affected page transfer/rendering times.

got it! thanx!

Menon, April 07, 2005 - 11:46 pm UTC


Invisible, April 08, 2005 - 5:43 am UTC

Today, your home page took 0.09 seconds for the database, and 0.19 for the browser to render it.

The 5 seconds or so was for the *question* page. Given how long it is, 5 seconds to do layout is probably acceptable. (Besides... isn't this number more to do with how fast my workstation is than anything at your end?)

I have a slow 800MHz thing here with minimal RAM. Running Windoze, of all things. Still, at least it has Mozilla! :-)

(That's "the Mozilla suit", not Mozilla Firefox.)

(OOC, does your website track what browser your visitors use?)

Tom Kyte
April 08, 2005 - 7:31 am UTC

the javascript timing times the

a) amount of time to get the last byte from the time the first chunk of the page came
b) plus the amount of time your browser spent rendering the page.

Yes, I track the browser. Now, if someone could point me to a page that shows how to map the thousands of unique browser names I get into a small set of classes of browsers, I'd be glad to share the browser trends :)

Long way

Invisible, April 08, 2005 - 7:43 am UTC

Good luck!

Lots of browsers pretend to be other browsers. (In particular, some websites actually refuse to server content unless it looks like the browser is IE, so many browsers pretend to be IE!)

[Irate anti-M$ comments deleted.]

I don't think there's even any startard format for the browser string - it's just completely free form. Pretty easy for a *human* to interpret... but you'd need a lotta lotta humans to get the same speed as Oracle! ;-)

Tom Kyte
April 08, 2005 - 8:33 am UTC

(I'm just hoping someone pipes up with "well, so and so on the net with way too much time developed this mapping table at this url:....."

then I would load that map into my database and join :)


Invisible, April 08, 2005 - 8:28 am UTC

Although... take a look at this:
</code> <code>

Google is you Friend[tm] ;-)

Interestingly, quite the opposite of my original post; it appears that IE pretends to be Netscape! (You'll recall that "mozilla" was the Netscape mascot - before it became an open source browser in it's own right... oh MAN! Is this complex?)

Anyway, HTH. ;-)

Looks like it's not even worth TRYING to understand the whys - and use the strings supplied. Heh.

how i can know clients IP addresses on local network

Saleh beck, April 10, 2005 - 4:06 pm UTC

i want to know how i can get ip addresses and computers name for clients on my network .

thank you tom

Tom Kyte
April 10, 2005 - 5:17 pm UTC

please see my response in the other posting you asked this...

I'll recap "ask your network guys"?

i mean

saleh beck, April 11, 2005 - 6:40 am UTC

Hi Tom
i mean how i can write a function in sql to do that , to get ip addresses and computers name
Thank You .. and please don't ungry of me.. because i forget where i ask my question therefor i ask you again in deffrient link .
Thank you again

Tom Kyte
April 11, 2005 - 9:04 am UTC

Like I said, you would have to ask your network guys. I don't know what OS, what commands, anything you have at your fingertips.

It could be "use a bfile to read /etc/hosts"
It could be "use dbms_ldap to query a ldap repository"
It could be "use a java stored procedure to run a command line windows program and read the text back"

It could be tons of stuff, but you need first and foremost to know how and where to get the information from.

How can I get a copy from tom kyte's site

A reader, April 11, 2005 - 12:56 pm UTC

Hi, I would like to have a copy from your site please,
the reason is because
1) I'll be reading and dropping files meanwhile I read them.
2) I don't want to miss some question.

I don't know what can I do to get it?
I don't see why could be too difficult to get it, I could pay up to 100U$, for the complete up to date copy?


Tom Kyte
April 11, 2005 - 12:58 pm UTC

sorry, I have no mechanism to support that.

There are the archives by week. that is about it.

thank you

A reader, April 11, 2005 - 1:42 pm UTC


Invisible, April 12, 2005 - 8:05 am UTC

Hi Tom.

I just looked at the source for your website... Hmm, so I'm guessing this is machine generated then. ;-)

[Just kidding - of COURSE it's machine generated!]

Just went to
</code> <code>
The machine nearly fainted!

You have a big button that says "Get Firefox now" - a nice standards-compliant browser. How about a standards-complient website? ;-)

(Being serious now... I imagine that would probably take more than a little while to achive! How many seperate "pages" does AskTom actually have in total? And do you even have low level control over the HTML that whatever engine you use generates?)

Was just a humble suggestion... the pages seem to render OK in either IE or [various] Mozilla browsers. Would just be nice to see them standards compliant is all.

Tom Kyte
April 12, 2005 - 8:49 pm UTC

there are 41 pages

yes, I know they are non-compliant
yes, it would take a ton of work...


A reader, April 13, 2005 - 9:58 am UTC

Congratulations tom you are going to be online five years this month :).

zip per year

A reader, April 13, 2005 - 5:00 pm UTC

Hi tom if you could please zip in one archives your archives per year, this will be helpfull for every one, i think so.

insert image

saleh beck, April 16, 2005 - 7:03 am UTC

Hi Tom
i have table A as
create table A ( id number,img long raw);
and by developer i insert to it some records and img field i insert an image ok ,
now i create another table name is B as
create table B(num number,img long raw);
ok now i want to write sql statment to select from a and store the records in table b.
how i can write it ... note that the fields is long raw data type not bfile or blob ,..ect
thank you tom ..

Tom Kyte
April 16, 2005 - 9:00 am UTC

you cannot. long raws are very inflexible that way.

it takes a program. if all of the long raws are 32k or less, plsql can do it, else you need to write a program in a 3gl.

Copying table with a LONG

DaPi, April 16, 2005 - 1:07 pm UTC

Tom Kyte
April 16, 2005 - 2:11 pm UTC

sure, that works. You could even do this across schema's from X.A into Y.B by doing a fromuser/touser operation as well -- and using a view in Y (create view A as select * from b) and importing into the view.

But within a single schema, you would have to rename the tables at least temporarily.

what is 3gl

saleh beck, April 16, 2005 - 3:11 pm UTC

Hi Tom
i don't know what is a 3gl can you tell me what is this and where i can read about it

Tom Kyte
April 16, 2005 - 3:22 pm UTC

sorry, 3gl is "third generation language".

Most all programming languages like C, Java, Visual Basic would fall into the 3gl category.

You might find google useful for things like this in a pinch, enter

define: whatever

in google and you can many times get a nice defintion:

</code> <code>

the top links in asktom

Menon, April 16, 2005 - 5:59 pm UTC

For a few days, the links "Tom's latest followup | GOTO a Bookmarkable Page | Bottom"
are missing from some of your question pages. For example,
I did not see them in this thread (and I think some other
threads earlier.) Just wanted to let you know in case
it is a bug.

Tom Kyte
April 16, 2005 - 6:05 pm UTC

scroll over.

they are there, just a really long URL has gotten in there making the page really wide.

Yup - that was it!:)

Menon, April 17, 2005 - 5:57 am UTC

Tom Blog webpage

denni50, April 18, 2005 - 11:21 am UTC

Tom...for some reason I can't access any of the links
you have on the blog webpage...keep getting an 'internal
server error'...don't know if it's at my end or something
at your end. I wanted to post a comment about timing
of your books vs marketing strategies but couldn't get
it posted there. I'll post it here and you can move it
to the blog webpage:

"Tom...the success of your books,website, and more important, the success of every reader who has learned from you speaks for itself.

I would never consider a technical book, whether it's Oracle, SQL Server or any other RDBMS where the author provides no proof, testing, research, evidence to support assertions and claims with clear cut examples,that would be tantamount to technical suicide.

Timing of a book is irrelevant to those of us who understand the importance of reading, learning and applying information that is tested, proven and factual.

There is nothing more successful than knowing your knowledge and expertise is 'trusted' by your peers,readers and followers."

Tom Kyte
April 18, 2005 - 11:37 am UTC

I'm using a "free service", the

thanks for the nice feedback though!

i just tried a test comment and it worked ok

thanks Tom...

denni50, April 18, 2005 - 11:55 am UTC

I'll try and test again.

I like the idea of a blog's like having a journal or diary of comments and discussions outside the Oracle realm...or indirectly related to Oracle.

Blogs are replacing personal websites...easier to administer and maintain.

Looking forward to seeing and reading about other topics,

good luck!

Blog links

DaPi, April 18, 2005 - 12:25 pm UTC

"...for some reason I can't access any of the links
you have on the blog webpage...keep getting an 'internal
server error'..."

Not just Tom's blog. For me all have this problem:

Doesn't work from the office: behind firewalls with proxy server etc etc etc

Works fine from home: only has NAT between me and the big bad world.

Tom Kyte
April 18, 2005 - 12:35 pm UTC

Must be just certain firewalls -- I'm at home, behind NAT and it works. When I vpn into work and do it through the Oracle firewall, it worked too.

Blog follow up...

denni50, April 18, 2005 - 1:26 pm UTC

was able to finally access some of the links.
Perhaps the host limits the amount of traffic at peak times
to prevent overloading the servers.

Best place to tell you?

Arian, April 18, 2005 - 4:11 pm UTC

Hi Tom,

Since I couldn't find a better place to comment on Asktom. I'll abuse this question.

I found that if I search for some topic, and choose a subject, I can't use the 'Back' function of my browser (IE 6.0). When I refresh, I get the message: Error ERR-7621 Could not determine workspace for application () on application accept.

Kindest regards,


Tom Kyte
April 18, 2005 - 6:38 pm UTC

IE is reposting the data, not just going back.

firefox seems to get this back concept ok....

IE is rerunning the page. Sorry, I cannot change IE.

Your blog (

Menon, April 22, 2005 - 10:05 am UTC

Seems to be more down than up for the last two days:)
Perhaps you want to move to a different blogging
software? (Howard's seems to be better maintained.)

Tom Kyte
April 22, 2005 - 10:57 am UTC

he and i are both using blogspot -- same place.


Menon, April 22, 2005 - 12:04 pm UTC

I thought his blog was always there and yours
was down (most of the evening yesterday
as well as today morning - now it seems to be up)
so thought he is using a different one.
Or may be he has a special deal with them - given
the kind of sarcastic and funny blogs he can write, I
would not want to offend him if I was hosting him!;)

Tom Kyte
April 22, 2005 - 12:41 pm UTC

if you click on his comments links, you'll see we are the same place.

he might be copying the page to his own site though, I'll ask him (eg: the comments for his site would be broken when mine where but his home page blog might not have been affected since he just copied it -- if that is what he does)

Where to add my question? or How can I create a topic?

B.Prasad, April 22, 2005 - 12:47 pm UTC

I don't find any links to add my question.
Can u please send me the link?

Thanks & Reagrds,

Tom Kyte
April 22, 2005 - 12:53 pm UTC

"u" isn't here.

When taking questions, there is a link and button on the home page.

When having too many questions in the queue, there is a mesasge to that effect in place of the button/link.

The Secret of my Blog's Uppiness

Howard J. Rogers, April 22, 2005 - 3:30 pm UTC

Just saw that's reliability is in question, so how come mine (which is indeed a Blogger/ as Tom's) still works when Tom's doesn't? And the reason is, I think, that when you set up a Blog with Blogger, you are asked whether you want them to do the hosting, or whether you want to host it yourself. I chose the latter option, so in fact all the articles and so on are stored on my own website. Blogger therefore simply provide the front-end and maintenance tools. When you submit a comment to one of my articles, for example, I think Blogger just re-directs that via FTP to my web server. I end up with a new document on *my* server, anyway.

So if Tom's is down because of traffic limits, etc., mine shouldn't be so affected, at least for reading. A Blogger outage would still clobber my ability to create new posts, and your ability to submit comments on them.

Judging from the comments I see here, it sounds like it was a wise move on my part to go for "repatriation" of the blog pages. (But then I didn't and don't expect the sort of traffic Tom's is likely to be getting! Though if I mention it's at </code> <code>here, perhaps that will change?!)


Tom Kyte
April 22, 2005 - 3:47 pm UTC

Hey, it is linked to from my blog page.

But I'm going to keep the blog over there for now -- Just to keep it "outside Oracle" as it is.

Bug in "Your Questions"

Pratap, April 27, 2005 - 5:22 am UTC

For some questions it shows sysdate(first asked), which is not correct. Others that are updated recently show correct dates.

Tom Kyte
April 27, 2005 - 8:06 am UTC

is it fixed, I had a "whoops" moment yesterday.

David Aldridge, April 27, 2005 - 9:09 am UTC

>> Though if I mention it's at </code>
here, perhaps that will change?!) <<

It did when I mentioned <code>!

I was trying to get Tom to wear an Oracle Sponge t-shirt for his address to IOUG, but I don't think he's going for it.

Tom Kyte
April 27, 2005 - 9:21 am UTC

I liked the t-shirt idea you had last night, but it would be out of character for me, yes.

David Aldridge, April 27, 2005 - 10:15 am UTC

Polo shirt with discrete logo? Baseball cap with illuminated revolving sign?

OK, what about just an ordinary bath sponge worn on the lapel, like a carnation but ... more absorbent and yellow and porous.

OTN and asktom

AD, May 03, 2005 - 1:49 am UTC


What would be the tangible/intangible benefits Oracle gets from investing in a forum like OTN?

Thanks for your time,

Tom Kyte
May 03, 2005 - 8:50 am UTC

I don't understand the question -- otn is very different from this place?

OTN and asktom

AD, May 03, 2005 - 12:43 pm UTC

Hi Tom,

I am doing some research work on how a forum like yours or for that matter OTN make a difference to the Company's overall strategy. That is why I am referring to what tangible/intangible benefits Oracle gets from investing in these in my previous post.


Tom Kyte
May 03, 2005 - 2:39 pm UTC

without it, you would not be in the 21st century for starters.

It is a community, a place to go, a place to get answers, a place to congregate. A place to get software to play with (and get hooked on), a place to find the documentation to be more succesful. A place to get ideas about what you can do.

It is part charity and part marketing and part publicity.

but without it, you would be sort of unique in the world anymore. it is a service most all large software companies provide.

and this is all my opinion :)

Alberto Dell'Era, May 03, 2005 - 6:12 pm UTC

> tangible/intangible benefits Oracle gets

developers know Oracle => successfull projects => licensing revenues and increase in market share

developers don't know Oracle => failed projects => Oracle gets blamed => evils abound

"developers" == "most critical and scarce resource"

The "charity" part applies mostly or only to asktom I believe ;)

Tom Kyte
May 03, 2005 - 7:18 pm UTC

well, there are the otn forums, the documentation download, lots of "non-necessary" things that add up to real money (and hence the belief that the intangibles are worth the effort)

asking about v$session table

Nihad Saleh, May 12, 2005 - 6:15 pm UTC

Hi Tom
I want to ask about how the v$session gets data ? when any one on the LAN login to database , oracle insert his computer name and the date also anthoer information about this user , just i want to know how the oracle know this information and how oracle get this information? is there any function or procedure to

question 2 : if i have an image inserted into nih table as long raw can i read this image char by char or i want to make this image as secrete information that can not any one saw it else me , can i do it?

please focus on the first qestion .. i need this information

Tom Kyte
May 13, 2005 - 9:02 am UTC

(a tad out of context no?)

there are operating system calls that provide this information to programs given that they have connected over a network. also, the client provides some of this information as part of the connect protocol.

you don't want to use long raw, but rather blob. blobs have much better API's.

You can encrypt them, sure.
You can use fine grained access control to prevent access to them.
You can piecewise read them -- from a byte to megabytes at a time.

asktom - tomorrow??

AR, May 19, 2005 - 11:53 pm UTC

I hate to ask you this. But in the highly unlikely event that you leave Oracle corp in the future, what happens to and the copious information along with it? Will it continue to be provided (in the existing form) by Oracle? Or will you be nice enough to take it along and host it someplace?

I depend on asktom a whole lot to do a decent job. I'd be seriously handicapped without it.

Best regards.

Tom Kyte
May 20, 2005 - 7:49 am UTC

It belongs to Oracle, unlikely I would be able to "take it with me".

I'm sure it would run for a while, till it became not-relevant. They are toying with the concept of an "offline version", having someone go through, categorize, clean up and "publish" the content. We'll see where that goes..

Cannot post a question for last 3 months!

Arindam Mukherjee, May 27, 2005 - 4:21 am UTC

I wanted to post a question and have been waiting since February. Unfortunately, everyday, I see the Status as

"Sorry I have a large backlog right now, please ask a question later".

But, also I could notice some questions as 14 hours old, that is, those are just asked. How do I post my question?

Tom Kyte
May 27, 2005 - 7:47 am UTC

I am an individual. On the home page I list statistics as to the number of questions taken, the number of reviews (like this) read and how many I've made a comment on.

When I'm taking questions (about 10 at a time), there is a link on the home page.

abarna, May 30, 2005 - 6:11 am UTC





Tom Kyte
May 30, 2005 - 9:21 am UTC



abarna, May 30, 2005 - 10:15 am UTC




VEH_001 MAK_001
VEH_002 MAK_002




Tom Kyte
May 30, 2005 - 10:26 am UTC

WHY UPPERCASE - HARD TO READ - and it conveys a sense of "shouting" :)

You would have to alias the columns -- that is write sql to write sql. You would have to query the field_defn table to figure out "oh, I meant to call field 1 Manufacturer and field 2 Make

select prai_code_01 manufacturer, prai_code_02 make
from information;

of course, a VIEW would solve this nicely. That would be my approach if at all possible.

A reader, May 30, 2005 - 10:50 am UTC

Aparna from India what does your question have to do this thread?

A reader, May 31, 2005 - 6:38 am UTC

The original question on this site was "The New Ask Tom" - this is over 2 years old - how can anything asked now possibly be relevant to the original question! Surely the New Ask Tom in this question is no longer New?

No wonder I can never submit a new question if all of Tom's time is spent culling new questions that sneak in as reviews of an existing question! I have tried consistently for several weeks to find a time when new questions are permitted, but without success. Must be down to the English politeness of queuing and waiting for my turn - unlike those of you that sneaking questions as reveiws!

Sitting at my piano, the other day

Chris, June 01, 2005 - 8:06 am UTC


Who is the guy sitting at an organ(?) on your home tab? Or am i working too hard and seeing things?


Tom Kyte
June 01, 2005 - 10:26 am UTC

sorry, don't understand?

How can I send my question

Mohammad, June 01, 2005 - 10:35 am UTC


I would like to send my question to you. Please advice.


Tom Kyte
June 01, 2005 - 11:27 am UTC

wait till i'm taking them (on home page) or see the list of resources (from tab bar on home page) that are good places to go.

Piano Man

Chris, June 01, 2005 - 10:58 am UTC

Sorry for the earlier post: The local cache at this site overode your tab_close3.gif (the end curve next to the home tab) to that of a man playing a church organ. Thought i was going mad ;-)

Time spent in the database

A reader, June 12, 2005 - 4:49 pm UTC

Can you share how you get the time spent in the database number on the Asktom homepage (above the bottom red bar)?


Tom Kyte
June 12, 2005 - 11:09 pm UTC

dbms_utility.get_time at start of page
dbms_utility.get_time at bottom of page

subtract :)

Time spent in the database

A reader, June 13, 2005 - 7:02 am UTC

I kind of got that. But can you please elaborate in terms of HTML DB constructs? Where do you put these 2 calls to dbms_utility.get_time()? In hidden page items? In what regions do you put these items? How do you populate them? Using Computations? At what firing point? How do you display the final answer, using what style attributes?


Time spent in the database

A reader, June 13, 2005 - 7:21 am UTC

Also, I would like to add this to my page template so all the pages automatically show it. Any idea how to do that?

dbms_utility.get_time returns " number of 100th's of a second from some arbitrary epoch." So, the difference between the 2 calls would give me...what? The number of seconds elapsed? Wouldnt I have to multiply it by 100?


Tom Kyte
June 13, 2005 - 11:34 am UTC

the number of hsecs. see link above.

blogging problem

A reader, June 14, 2005 - 12:41 pm UTC

In the blogging you have, is difficult to know who you are answering some times.
If you oculd add something like

> (one line from the writer)
And then your answer

Will help to understand the sequence, for example some time ago I did a question in your blogging, and other people do somthing similar, and I was not sure if you answered me or not. So I didn't knew if answer or not to your answer.

Simply to add in your answers, what you are answering wil help to make it more understandable :)

Tom Kyte
June 14, 2005 - 4:16 pm UTC

I start with some of their text as a lead in, that doesn't do it?

(and a comment like this, probably belongs.... on the blog :)

but I'll try to add their name in the future.

erase "garbage" in asktom, June 15, 2005 - 11:48 am UTC

Hi Tom, something interesting will be the fact some one drop the garbage questions, and comment there is in your site.
And the not important or not answered questions.

Some times you read a loooong questions, you didn't answer. To read 2 questions, and understand what is asked and what is answered, can take me at least 30mn.

The other options will be you give a qualification to questions, so we could filter only relevant and important questions, when investigating.

I don't know about others, but for me to get time to investigate, is sometimes hard.

And don't forget the cookie to save name,email, and where your are (optional)

Tom Kyte
June 15, 2005 - 3:43 pm UTC

i don't have the time to edit everything

and no, I'm not looking for any editors either ;)

and besides, I don't like editing, it looks like a coverup or something.

And people just put it back again anyway.

Alex, June 17, 2005 - 4:43 pm UTC


Just curious, did you recently fix the site so that it will refresh and check if you have a backlog or not when leaving your browser open to post new questions?

Say I go to your page, and you are taking questions, and I think to myself, "Hmmm, I don't have a question now, but I know I'll think of one later then it will be too late. I'll just leave the page open with the submit button enabled......"

Sound familar?

Tom Kyte
June 17, 2005 - 5:05 pm UTC

You should not be able to do that, if you are, let me know :)

:) Cleaver Tom...

Santhossh, June 18, 2005 - 8:39 am UTC

You know, it happened recently for me too. I saw "Im taking questions" and was happy that got a chance to ask. Had lots of things to ask but had to format with insert script and all that. So by the time I got this up and paste there, it said, it cannot take questions :)

Anwyays, now I have stored that question in a file... Next time, when I get a chance, just paste and submit.

Tom, thank you for honest help.


KP, June 22, 2005 - 2:14 am UTC

Is the "?" at the top right corner for future use ? From whichever page I click on ? it gives me a standard message. Are you planning to put in some help messages in the future, or is it only applicable for some very specific page

Tom Kyte
June 22, 2005 - 6:41 am UTC

it would be "help" but this application is so easy to use you don't need any :)

A minor suggestion

A reader, July 12, 2005 - 8:35 am UTC

There should be a place where we can leave a message that is not a specific question. For example right now the front page has a note about downtime that was scheduled for last week. It would be nice to be able to point this out to you simply through the app.

join one field to tow tables

Adnan, July 13, 2005 - 3:45 am UTC

Dear Tom
plz let me know if i can join the same filed in one table to tow tables which means same field will be foregin to one of the tow tables at same time

plz give me example about this case


Tom Kyte
July 13, 2005 - 11:07 am UTC

sure, just "join"?

from t1, t2, t3
where t1.c = t2.x and t1.c = t3.y

About refering one table to tow table

adnan, July 16, 2005 - 8:08 am UTC

Dear Tom...
may be i didnt explained good what i want !

i have t1 (c1 pk,c2)
t2 (a1 pk ,a2)
i wanna creat detail table like
det(d1 pk, d2 refernce to t1(c1) or refernce to t2(a1)).

which means can i refer forgin key to tow table in same time (of of them in each insert) some thing called execlusive or arc relation ship


Tom Kyte
July 16, 2005 - 9:35 am UTC

you cannot, that would firstly not be a very good model and secondly, you just plain and simply cannot.

detail (det) would have TWO foreign keys, one pointing to t1, one pointing to t2 and if need be a table check constraint:

check one_must_be_null (fk1 is null or fk2 is null)

Alberto Dell'Era, July 16, 2005 - 9:57 am UTC

Got this error this morning and yesterday when submitting a followup:

"Error ERR-7621 Could not determine workspace for application () on application accept.

Expecting p_company or wwv_flow_company cookie to contain security group id of application owner"

The only difference from usual is that i'm on very slow connection through my mobile and I've disabled images loading.

(not a big issue in Firefox, i pressed the "back" button and resubmitted - just FIY).

Tom Kyte
July 16, 2005 - 10:01 am UTC

I would think that perhaps the entire page did not get loaded possibly? and when you hit back, it finished the loading of it?

it was missing some bits.

Alberto Dell'Era, July 16, 2005 - 10:24 am UTC

>I would think that perhaps the entire page did not get loaded
> possibly? and when you hit back, it finished the loading
> of it?

No, I
a) composed the followup (took half an hour)
b) hit "submit" or perhaps "preview" (i always preview before submitting, don't remember whether I got the error on the N-th "preview" or the last "submit")
c) the error above was printed on a blank page
d) waited about ten seconds
e) when I hit Firefox's "back" button, Firefox brough me to the original "Question review" page, with the text of my followup still intact
f) i hit "submit" or "preview" again - everything was fine this time.

Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.8) Gecko/20050511 Firefox/1.0.4

Breaking news: got exactly the same error now, when hitting "preview"! Same steps as before - same result - still on a very slow mobile connection (home page takes 10 seconds to load), still with images disabled.

Codes table data type

Adnan, July 16, 2005 - 10:59 am UTC

Dear Sir.

i wanna ask about best data type to be used in codes or lock up tables like (country tables or colors code tables or car types table)
let us say the length is two
which better to use number(2) or varchar(2) .. which better in performance , indexing, speed and any other factor


Tom Kyte
July 16, 2005 - 1:06 pm UTC


a) number type when you store numbers
b) date/timestamp/interval type when you store date related information
c) strings when you have something that is not a number or date

nothing else needs be considered -- you use the right type for the data you have to store. period.

just curious

A reader, July 16, 2005 - 1:23 pm UTC

is it possible to connect to asktom through our sql*plus?

Tom Kyte
July 16, 2005 - 1:52 pm UTC

for me, yes.

for you, no.

again Codes table data type

adnan, July 17, 2005 - 2:45 am UTC

regarding the data type in the code table like country,currency or unersitites or any kind of code tables ..

the case that the codes like
2 Canada
3 france
is it better to user vunber data type or varchar2 like

01 USA
02 Canada
03 france

storage wise and indexing and speed which better to use

Tom Kyte
July 17, 2005 - 9:21 am UTC

is the first column a number?
then use a number

is the first column a string?
then use a varchar2

is the first column a date?
then use a date

it looks like a number to me right now, unless your design must allow for sticking the letter 'A' in column one, it looks like a number, walks like a number, talks like a number -- it must be a number.

nothing else counts.

again Codes table data type

adnan, July 17, 2005 - 2:48 am UTC

regarding the data type in the code table like country,currency or unersitites
or any kind of code tables ..

the case that the codes like
2 Canada
3 france

is it better to use number data type or varchar2 like

01 USA
02 Canada
03 france

storage wise and indexing and speed which better to use

import dmp file from oracle9i to 8i

Nihad Saleh, August 21, 2005 - 9:18 am UTC

Hi Tom
i want to ask you if there is any way to install dmp file (x.dmp) into oracle 8i but the export file (x.dmp) was taken from oracle 9i database .

can i do the import as
x.dmp ( export from 9i database ) into ==> oracle 8i database .
thank you ...

Tom Kyte
August 21, 2005 - 1:28 pm UTC

you need to use the 8i EXP tool against the 9i database in order to create a dmp file that 8i IMP can read.

know the time

Nihad Saleh, August 21, 2005 - 9:26 am UTC

Hi Tom
can i know what is the time spend during execute this statment on oracle database
select * from tab;
i mean how many second oracle database needs to execute this select statment.. (befor i execute it )

Thank you

Tom Kyte
August 21, 2005 - 1:35 pm UTC

in 10g, it'll "guess"

ops$tkyte@ORA10G> select * from table(dbms_xplan.display) where rownum <= 25;
Plan hash value: 2873990501
| Id  | Operation                                |...Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                         |...  191   (9)| 00:00:03 |
|*  1 |  TABLE ACCESS BY INDEX ROWID             |...    1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                      |...    0   (0)| 00:00:01 |
|*  3 |   FILTER                                 |...           |          |
|*  4 |    HASH JOIN                             |...  191   (9)| 00:00:03 |
|   5 |     TABLE ACCESS FULL                    |...    3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL                    |...  187   (9)| 00:00:03 |
|*  7 |    TABLE ACCESS BY INDEX ROWID           |...    2   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN                    |...    1   (0)| 00:00:01 |


but it is totally a "guesstimate"  

your blog link at asktom

Menon, August 28, 2005 - 10:45 am UTC

is pointing to a bad link </code> <code>

for some reason.
By the way very nice photos in your latest blog

Tom Kyte
August 28, 2005 - 11:57 am UTC

Thanks! that was the second correction to the updates on the home page. That is what happens when you dump your next three months of stuff onto it.

In Prague, I believe it would be hard to take a bad photo. I ate outside again today for lunch, just spent an hour on the old town square having some good Bohemian food and brew watching the scenery. Very nice.

But not I'm in the hotel editing a book :( back to the real world.

you are welcome.

Menon, August 28, 2005 - 1:01 pm UTC

"In Prague, I believe it would be hard to take a bad photo. I ate outside again
today for lunch, just spent an hour on the old town square having some good
Bohemian food and brew watching the scenery. Very nice."

That sure seems to be the case - I especially loved the photo of stalagmites- Only alberto's question and your answer clarified what it really was - amazing! Had to
go back and look at it again! :-)

"But not I'm in the hotel editing a book :( back to the real world. "

So you are reviewing another book?! Which one, if I may ask?

Tom Kyte
August 28, 2005 - 1:35 pm UTC

A book called Everyday DBA coming out from Oracle Press.

Alex, September 19, 2005 - 3:10 pm UTC


I think it's neat that you keep track of various interesting statics about your site (how many questions you get etc.). Do you know how many people are using your site at any given time? I'm curious how popular it is. What would you say the average is for the busiest part of each day?

Tom Kyte
September 19, 2005 - 4:54 pm UTC

</code> <code>

has some of the stats...

Alex, September 20, 2005 - 9:45 am UTC

That's very impressive. Something you may or may not find interesting; You may recall some of your critics bashing you for not removing all the posts they interpreted as "harmful", and allowing anonymous posts. I went to one of their blogs:

</code> <code>

2,569 visitors at the time of this post. I think you made the right choice. AskTom can rack that up in 10 minutes.

Tom Kyte
September 21, 2005 - 1:20 pm UTC

Robert is a very good guy - there isn't anything wrong about him.

Alex, September 21, 2005 - 3:27 pm UTC

If you say so. I guess I incorrectly assumed he may have shared the opinions of his comrades because I know of at least three different names taking shots at you. The problem with spitting venom from anonymous posts I suppose...not sure where to direct your rebuttal.

Tom Kyte
September 21, 2005 - 8:08 pm UTC

exactly (your last point).

But have it on good faith, Robert is a good guy.

Robert sure is a good guy.... but Tom is the best :-))

Sandeep, September 22, 2005 - 6:53 am UTC

Thanks for all your help and time spent for everyone.

It really is much much appreciated!

Keep it going!!


Alex, September 22, 2005 - 4:33 pm UTC

Ok wow, is that "anonymous" poster for real? The same that wrote senseless insults on just about ALL of your blog topics? Is that really DK or Jack from the Shinning? That can't be an adult. You handle childish behavior very well, you must be an excellent father. He must be up to something though. He's trying to enrage you to the point where you block his IP so he can turn around and say "look! look! Tom can't take criticism, he just removes people who don't agree with him.." or something like that.

Need information about Archives

Giridhar, October 16, 2005 - 1:44 pm UTC

I saved all the archive files and read questions at office. My friends took the questions in CDs and they study at their home.

My doubt is if there is any follow-up for a question, will it appear again in the latest week's archive file? Why i want to know is if i continue to take a copy of the archive files often, will i be able to keep my CD in sync with the data in your site?

Thanks again.


Tom Kyte
October 16, 2005 - 4:24 pm UTC

as it works now - when I update the question, it "moves" in the archive - this question for example will be in this week now.

Site changes

Reader, December 07, 2005 - 7:33 am UTC

Hey, Tom! As i can see - you're changing your site right now! :-)))

Good thing for a new look!

Firefox, askTom and font sizes on some pages

Scott Mattes, December 08, 2005 - 10:32 am UTC

The main page, and the page where I entered this, uses a very small font to display (forcing the use of ctl-+ to enlarge it). IE doesn't.

I notice that at least one other person mentioned this, back in Oct '04, but nothing was said in reply.

Tom Kyte
December 08, 2005 - 11:57 am UTC

I use firefox exclusively.

the home page looks like every other page to me. I cannot see what you are seeing.

Fonts and Firefox

Mark A. Williams, December 08, 2005 - 1:05 pm UTC

For what it is worth... the fonts in Firefox are definitely about half the size of the same pages viewed through IE on my system as well.

- Mark

Extra Small font size

Gary, December 08, 2005 - 11:12 pm UTC

I used to have that problem. A larger monitor helps :)
Seriously, Tools -> Options -> General
Fonts and Colours button.
On the right hand size, there's a Minimum Font Size box.

Set it to 9 or 10 and everything should be readable.
Even the bits in "font-size:x-small;"

report builder

sage, December 12, 2005 - 4:20 am UTC

Dear All,
how can i make summation of 2 columns from 2 different groups in Data Model.

Site throws error if use bad URL

Martin Herbener, December 14, 2005 - 4:27 pm UTC

Not a big deal, but I noticed (by making a mistake copying a link) that if I put a non-numeric character at the end of a link to a posting, like this:

</code> <code>

I get an error

ORA-01722: invalid number

It would be cool if the site gave a more polite/meaningful error when that happens.



Nihad Saleh, December 22, 2005 - 11:14 am UTC


thank you

Tom Kyte
December 22, 2005 - 11:34 am UTC

YOU WOULD HAVE TO ask someone that is familar with your system if the network shares/mounts are setup to permit this.

not really a database question at all.

Alexander the ok, December 22, 2005 - 11:59 am UTC


I'm having some trouble getting my snowblower running I'm wondering if you can help me out. I live in New England so it's vital that......

Seriously though, since this is the new asktom thread, I wonder if during the rewrite you are considering handling questions like that differently. If it were me i'd probably just delete it.

Tom Kyte
December 22, 2005 - 12:23 pm UTC

something has got to give, yes.

I've even thought about *not* migrating the old data over and just starting "fresh"

ask tom slow?

Menon, January 13, 2006 - 5:56 pm UTC

Hi Tom
I notice that asktom is suddenly noticiably slow - is it just me? Even the searches are taking longer..

Tom Kyte
January 15, 2006 - 3:20 pm UTC

no? I have not noticed anything out of the ordinary.

"last updated" date/time

Michel Cadot, January 15, 2006 - 3:50 pm UTC

Hi Tom,

I notice that the "last updated" field is updated only when you update a thread.
Would it be possible that it is updated when anyone updated the thread?


Tom Kyte
January 15, 2006 - 3:56 pm UTC

I didn't like that way of it happening. Otherwise lots of "junk" pops to the top (about 1/3 of the follows are not replied to).

If I deem a followup as being something to be visible to all at the top - I do a followup myself that says "I don't know, but maybe someone else will" and it floats up to the top again.

But alot of them (the followups) are "good", "thanks", "whatever"....

"last updated" date/time

Michel Cadot, January 15, 2006 - 4:18 pm UTC

Well, i didn't think about that.
I was thinking about answering some questions when you're sleeping (we have a difference of 5 hours, if you are still on east coast).

But i wonder if you sleep any time. :)



Menon, January 15, 2006 - 9:47 pm UTC

"no? I have not noticed anything out of the ordinary. "
Now things are back to normal though at one point on friday, it was noticiably slow (at least to me.) If I find it happens again, I will post it here...

perhaps it was me again..

Menon, February 03, 2006 - 4:45 pm UTC

but I could swear that asktom was inaccessible for some time just now (right now it is CA pacific 1:30pm). The browser kept spinning). Other sites were accessible...

Now it seeems to be up again

I am able to see others EMail Id

Rajeswari, February 14, 2006 - 5:55 am UTC


I am using IE. I am reading through the link specified below
From above link I got reference to other link and using "open link in new window" I opened the link below <code>

I noticed the "return" button at the top of the question (which I missed to notice before). I clicked the button to know to which page it is re-directing. It re-directs to "Your Questions" tab and mail_id of the person who posted the question and listed other questions of that person.

So is it normal displaying others Email ID?

Tom Kyte
February 14, 2006 - 8:12 am UTC

so, where did you get that link from, it has a sessionid in it - as long as the session id is "valid", things are remembered.

Alexander the ok, February 16, 2006 - 9:18 am UTC

So Tom do you sometimes not answer new questions? I had one and we were talking about it and then I never heard back. It stills says it's in your queue though. I wasn't sure what's going on. Thanks.

Tom Kyte
February 16, 2006 - 12:09 pm UTC

It is there, haven't had sufficient time or access to big enough screen to look at it - I'm back in town tomorrow.

It isn't a new question, it was one we were in back and forth and back and forth mode in.

to do with tuning queries during an upgrade.

unbold by default?

Duke Ganote, February 16, 2006 - 1:38 pm UTC

Could you default each outside review to start with a hidden poundsign-b so these
</code> <code>
don't occur? My weary eyes thank you.

Tom Kyte
February 17, 2006 - 1:18 pm UTC

nope, then I would have lots of 's all over the place - making the next "not do anything"

Alexander, February 16, 2006 - 1:46 pm UTC

Ah ok my apologies I didn't know you were traveling. I often forget to check your schedule at the bottom of the page.

You not blogging??

CJ, February 24, 2006 - 12:06 am UTC


Just out of curiosity - I haven't seen your blog updated since January the 19th. I did a quick search for a reason here on AskTom but couldn't find one. So some questions:

-- Have you moved your blog site?
-- Not getting enough time to update?
-- Lost the desire to keep on talking?

Genuinely hope it's the first one.


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

hit reload maybe?

there have been 22 posts since that date.

Hasn't moved.
Time is always a problem :)
Not yet.

</code> <code>


CJ, February 24, 2006 - 8:45 am UTC


(Too ashamed to say anything coherent. Consolationis I get to read your blog.)

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

RSS newsfeeds are good too. I subscribe to a couple dozen now, I get alerted when new posts appear.

Great way for appease my short attention span ;) New feeds come in all day long.

just out of curiousity

A reader, February 28, 2006 - 3:06 pm UTC

Hi Tom

I wanted to ask just out of curiousity that is it always you who answer all the questions or you delegate work also.


Tom Kyte
March 01, 2006 - 8:02 am UTC

Unless the answer starts with

"I asked so-and-so and they said:"

It is me. Anything I did not personally do is 100% attributed to the person that did.

What's up with Asktom overhaul?

joe, March 01, 2006 - 8:08 am UTC

Any updates on that?

Tom Kyte
March 01, 2006 - 8:27 am UTC

I just had a meeting last night about that - two developers from the htmldb (apex) team just signed up to do it :)

they want to make it a downloadable utility as part of apex.

Thanks a lot!!

CJ, March 06, 2006 - 11:08 pm UTC


Following your hint about RSS feeds (entry on 24th Feb in this thread) I too have started using the Hotsheet feed aggregator for tracking blogs. It's excellent! Saves lot of wasteful browsing time.

Once again thanks.


Changing E-Mail

P.Karthick, April 10, 2006 - 10:27 am UTC

Hai tom,

in the begining i was posting in your site with my first office mail id and now i have swithced to another company.

so i think using office id is not proper as company's may change but asktom will be the same. so if i want to change all my posting that i have done till now to my personal mail id is it possible.

this will help me in the future. when i post a question i will use my personal id and if my past posting can also be in that same id then it will help when i view my question in YOUR QUESTION.

Thank you


Tom Kyte
April 11, 2006 - 10:17 am UTC

sorry - you'll have to plug in your old email id to pull up your old questions.

suggestion to use auto managed undo tablespace or manual rollback segments

Sandeep Patra, April 25, 2006 - 11:40 am UTC

Hi Tom ,

I have a concern. We have a database whose size is 23.2125053 TB.

In this database we are using manual rollback segments whose sizes are as follows.

------------------------------ ----------
RBS_ROLL_A 40960
RBS_ROLL_B 77482.75
RBS_ROLL_C 90649.375
RBS_ROLL_D 49152
RBS_ROLL_E 57638.0313
SYSTEM 121192

Will it be better if we go for AUTO managed UNDO tablespace . Please give your views.


Tom Kyte
April 25, 2006 - 2:25 pm UTC

unfortunately - nothing supplied here would help answer that question :)

size - doesn't really count.
existing sizes - so what.

if you just have 6 rollback segments, you must have a pretty low update volumne sort of system (not very many concurrent transactions). I'd consider using automatic undo management - sure.

Upgrade Oracle Application Release 11.5.9 to 11.5.10

Raktim, May 10, 2006 - 9:34 am UTC

Hi Tom,
I have cloned Oracle Application Release 11.5.9. Now I want to upgrade this to 11.5.10. Can you please tell me the steps for this upgradation.


Tom Kyte
May 10, 2006 - 10:15 am UTC

not really - I don't do windows, I don't do apps :)

Support would really be the optimum place to ask this particular type of question.

E-Business Suite Upgrade online doc

Catalin CIONTU, May 10, 2006 - 12:51 pm UTC

In order to find out how to upgrade Oracle E-Business Suite, please check online doc at </code> <code>


Upgradation of APPS

Raktim, May 11, 2006 - 12:35 am UTC

Hi Tom,
I have cloned Oracle Application(Apps) Release 11.5.9. Now I want to upgrade this to 11.5.10. I am using Solaris 5.8. Can you please tell me the steps for this upgradation.


Tom Kyte
May 11, 2006 - 9:03 am UTC

see the answer I gave you to this exactly the same question yesterday.

oh wait, it is right above this!

A reader, May 11, 2006 - 11:45 am UTC

To "Raktim from INDIA" what the heck does your question/followup have to do with the original answer?


Michel Cadot, June 10, 2006 - 10:54 am UTC

Hi Tom,

I'm interested in knowing what is the repartition of your posters across countries. Can you give us that?


Tom Kyte
June 11, 2006 - 11:45 am UTC

I'd have to convert IP addresses into probable countries.

Provide me a link to a subnet to country conversion table and I'd consider it ;)


Michel Cadot, June 11, 2006 - 12:51 pm UTC

I take you at your word.
You can find a CSV file for free (limited to countries) at:
</code> <code>

It's not the more accurate one but I think it is enough for a statistics purpose.


Tom Kyte
June 11, 2006 - 1:37 pm UTC

I will take a peek at it when I can, I've bookmarked it. thanks

Dynamic search in SQL

Moorthy GS, June 12, 2006 - 5:08 am UTC


I am Moorthy from India. I have one question about the Dynamic search in SQL.

My Question is how can search in all columns of one table using one keyword.

Example for my question is

I have one table it has more than 20 columns and its some of columns datatypes are object and inside object like(In my table Staff_name is declared as 'XPN' object. XPN object is consist of 'FN' object and normal datatypes.

Now i want to search in whole table columns using one keyword '%ETE%'. If that keyword is matched with any of my table columns value then it retrieve that particular record.

I tried one query which i mentioned below...


this query is not run. Why it is run i can't get it....?

Pls suggest me....



Tom Kyte
June 12, 2006 - 9:51 am UTC

it won't happpen - you have a varray, an ARRAY of data, you would have to either convert it into a table (join it, called UNNESTING) or fetch it out and procedurally process it

Checking firewall

Lise, June 12, 2006 - 10:30 am UTC

I am sorry, but I need to post something to ensure that I have access. I requested for your website to be accessible you see, and is hopefully now opened for the whole company I work for.

Display 20 last questions in stead of 10?

A Reader, June 13, 2006 - 8:17 am UTC


Is it possible to post 20 questions instead of 10 displayed on the homepage.

It would need one click to the homepage and you are covering on an average a day's questions?


functions in sql

subhasish patra, July 12, 2006 - 3:37 am UTC

if we define function and it has one out parameter than
how can we use it in the sql*plus queries.

Tom Kyte
July 12, 2006 - 4:27 pm UTC

trick question

you cannot. You need functions that take only IN parameters and return a value.

you would have to write a funtion that works like that, and calls the function with an out parameter (and you decide then what the heck to do with it !)

A reader, August 07, 2006 - 10:09 am UTC


When i access your website using my pda i am not able to see "Tom's latest followup" button at the beginning of the thread wherelse "GOTO a Bookmarkable Page" & "Bottom" are visible
does this have to do with my pda or is it reproducible at your end as well?


Tom Kyte
August 07, 2006 - 9:39 pm UTC

hmm, never noticed that on my Treo before - but you are correct.

Wednesday this week - I get to see a demo of the first cut of the "new asktom" application.... I'll see what I see :)

RSS feed support?

A reader, August 07, 2006 - 10:17 pm UTC

Maybe it's just a problem with my configuration, but if you update more than 10 articles between Thunderbird refreshes of my News and Blogs feeds, I only see 10 most recent updates at most. Perhaps this is something you can test on the new version of AskTom?

I can sort of workaround this by configuring Thunderbird to check for new articles more frequently, but that doesn't help when I'm offline :-).

ORA-00001: unique constraint (FLOWS_010403.WWV_FLOW_DATA_IDX1) violated

Peter Yates, August 16, 2006 - 1:40 am UTC

Hi Tom

Just noticed when I entered index only (no quotes) in the search box I get the following error :

Error ERR-1008 Error performing branch to page 1. >> p_request=, p_instance=12209133632846299284, p_flow_step_id=1

ORA-00001: unique constraint (FLOWS_010403.WWV_FLOW_DATA_IDX1) violated

Cheers, Peter

Tom Kyte
August 16, 2006 - 8:26 am UTC

</code> <code>

does not reproduce for me.

new site

Dave, August 16, 2006 - 11:28 am UTC

how is it looking?

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

good, was just looking at it in fact :)

Database Migration

Mukesh Singh, August 18, 2006 - 12:47 am UTC

Dear Tom,
Greetings from me,
Question 1: I am planning to migrate my organization datbase from version to 9i. Will you pls brief me the steps which I should follow, and the preventive measures which I should take care. Pls provide me the complete details such as patches needed,hardware configuration etc.
My current database is on sun 5.6 SPARC machine(running Solaris 8) and the target machine is also having the same configuration.

Question 2: What is the best way to cross platform migration?
1. Current database(8.1.7) is on sun solaris and I want to migrate it on Windows XP (9i).

Tom Kyte
August 18, 2006 - 8:10 am UTC

laughing out loud.

... Pls provide me the complete
details such as patches needed,hardware configuration etc. ...


I suggest you read this:
</code> <code>
(the upgrade/migration guide) from cover to cover and practice the upgrade multiple times - and during your test to scale (load test), you'll discover if 9i needs more or less hardware given your application.

Patches and other such related information (as of this writing is the current release on most platforms) are found on metalink.

A reader, September 12, 2006 - 7:14 pm UTC


While going through your advanced search section my filter criteria was

Timestamp Between 26-AUG-2006 and 28-AUG-2006

as i would like to see the questions for that period but nothing shows up. I understand you have archives but can we also have a option similar to this one.

Always thankful for your support and time.

Tom Kyte
September 13, 2006 - 7:14 am UTC

that just means that during that period of time, I took no new questions.

timestamp is the date of "when the question appeared"

it is working as designed.


Sandeep Patra, September 19, 2006 - 8:33 am UTC

When does a session go for longops..

Tom Kyte
September 19, 2006 - 2:37 pm UTC

when it is doing something long?

a link

a reader, September 21, 2006 - 4:05 pm UTC

came across this site which has funny interface with site.

</code> <code>

a reader

Tom Kyte
September 22, 2006 - 2:48 am UTC

thanks for that, I'll see what I can do about it...

new asktom

A reader, October 27, 2006 - 10:17 am UTC

Hi Tom I hear in your podcast about a new asktom2, and I think I understand you coud add, is this going to be for everyone, or only for Oracle personnel?
Can you give more info, thanks :)

Tom Kyte
October 27, 2006 - 6:18 pm UTC

what I said was, the code will be available for anyone to download - so you can make your own:

site if you were so motivated.

A reader, October 30, 2006 - 8:20 am UTC

Thanks Tom, where could I find hosting (not expensive) to put that code. Do you know about some one.
:) Thanks

Tom Kyte
October 30, 2006 - 9:27 am UTC

A reader, October 30, 2006 - 11:16 am UTC

I think this is an excellent idea I always had thought would be a good idea from oracle to provide an internet community (like geocities for example) so all oracle peole could create his own sites, blogs, Q&A (with the conditions they are always related to Oracle).

So do you say I could create a q&a free, there? :)

Tom Kyte
October 30, 2006 - 12:47 pm UTC

go there, check it out, that is what it is there for. It is "pretty darn available", not "production data center available"

A reader, October 30, 2006 - 2:18 pm UTC

Thanks Tom

Your new beta site

juancarlosreyesp, November 20, 2006 - 12:12 pm UTC

Hi Tom
Congratulaionts I like your new site in your previous site, there was some "disorganization" in the areas.
This seems better by I miss the color, previous site was red and white, this is gray, red and white.

I think this is a "bug" even when you are not accepting new questions the submit button is enabled and accept the question, but when you want to submit you receive the "I'm not receiving new questions.


Tom Kyte
November 20, 2006 - 1:49 pm UTC

did you post this on the sticky thread at the top? else the guys working on it will never know.


A reader, November 21, 2006 - 5:37 am UTC

On old asktom searching for kiteley (yes I'm vain) got approx 48 hits. New asktom only gets one.


Tom Kyte
November 22, 2006 - 3:24 pm UTC

did you post on the sticky thread on the "new" site?

Time is wrong

A reader, November 28, 2006 - 10:31 am UTC

The times shown for Last Updated have been an hour fast for the past few weeks, perhaps since daylight savings ended. Currently it is 9:22 am eastern time, but the most recently updated question is shown as 10 am.

Tom Kyte
November 28, 2006 - 11:58 am UTC

thanks, i'll have them look at that.

old asktom

Rudi, November 29, 2006 - 12:35 am UTC

Is any chance the code behind currently running AskTom will available to public when Beta AskTom turn to official AskTom site ...

Tom Kyte
November 30, 2006 - 8:48 am UTC

no, the new code will be but not the old stuff - that wouldn't make sense.


A reader, December 26, 2006 - 8:40 am UTC

Hi Tom, merry chrimas and happy new year.

I remember you told there will be a way you could create your on askX, but I don't find where is that feature.

Tom Kyte
December 26, 2006 - 8:55 pm UTC

Once the code is fully tested (we are in the final phases right here, right now - I'm having it tweaked bit by bit as I really start to use it), it'll be on as a download.


juancarlosreyesp, December 26, 2006 - 8:41 am UTC

You included a cookie for the reviews.
Thank you Tom, you took the time, but you did it :)

Maybe an issue

Michel Cadot, December 27, 2006 - 3:06 am UTC


I maybe encountered a bug in New AskTom.
I opened two threads at the same time in new windows (I use IE6).
While the second one loaded I read the first one and, in this case, I wrote a review.
When I clicked on "Submit Review", I had the surprise to see the page refreshed with the second thread instead of the first one.
Fortunatly, the review was really added to the correct thread, just the refresh process was wrong.
If your team want/can analyze the logs, it was few before this review and the two threads were "How to Rollup Hierarchy from CONNECT BY into Singe Row?" and "CONVERSION DATATYPE" (P11_QUESTION_ID:11452690446345 and 1037232794454).

Tom Kyte
December 28, 2006 - 8:38 am UTC

thanks, I've alerted the developer about this...

I confirm

Michel Cadot, December 27, 2006 - 3:09 am UTC

I had the same thing when posting the previous review.
The refresh after "Submit Review" displays the last loaded thread instead of the current one.


The next button

Jmv, December 27, 2006 - 9:18 am UTC

Happy Holidays one and all!

When I click "next" to view the next set of 5, 10, 15 or 25 questions, I get an "Error on Page" message with IE 6.0.28 and cannot see any of the other messages.

Any thoughts on why?


Tom Kyte
December 28, 2006 - 10:32 am UTC


This is Marco from the APEX team (helping Tom with the new site). I get no error paginating in IE 6. Can you enable script error notifications and tell us what the detailed error is?


The New Ask Tom Fonts

Alex, December 27, 2006 - 12:10 pm UTC

Hy Tom,

Very nice the new Ask Tom

Just one opinion about the fonts. I think that the courier new font is better for the site because it mantains the indentation, the arial didnt.

--courier new

Tom Kyte
December 28, 2006 - 9:30 am UTC

that is why there is a code button for you guys to use

To: Alex

Michel Cadot, December 27, 2006 - 12:22 pm UTC


You're right but as you did it you can post your code with a fixed font and even other formats using the buttons on the review page just below the "Title of Review" field.
They allow you to post in bold, italic, underline and...


AskTom Feature Requests & a minor problem

Stephan, December 27, 2006 - 1:48 pm UTC

Hey Tom -

Have you ever given any thought to having your responses generate an email to the poster? I know you already do it on the original question being asked, but, as an example, I will not know that you have responded to this post unless I keep checking back in this thread. Dunno if it's feasible, but it sure would be neat.

Similarly, how about allowing readers to respond to an individual posting, rather than to the thread - it wouldn't necessarily need to change the order of the thread, still show newest responses at the bottom, but maybe just trigger an email to the person whose post got an answer....

Last - I cannot install the asktom search plugin - I get Firefox could not download the search plugin from: - maybe the path is wrong?

error in "Ask Tom MOST POPULAR" feed

Franco, December 28, 2006 - 4:13 am UTC

Looks like some ID is lost:
Unexpected error, unable to find item name at application or page level.
Error ERR-1002 Unable to find item ID for item "P11_DISPLAYID" in application "100".

Ask Tom MOST POPULAR feed error, more info

Franco, December 28, 2006 - 4:44 am UTC

if I take this broken link:

and change P11_DISPLAYID to P11_QUESTION_ID
it works.
Tom Kyte
December 28, 2006 - 10:10 am UTC

thanks, I've let them know about this and they will look into it.



Thanks for reporting this. I just fixed the broken link.

- Marco from the APEX team


Carsten, December 28, 2006 - 4:49 am UTC


I really like all the new Javascript and Ajax Features...on intranet sites.

However on the internet I prefer to surf without Javascript enabled. I don't have a problem to add AskTom to my trusted sites, now since I know it for years, but I just wanted to make you alert that it might keep new users from learning to know how valuable this archive is.


No Javascript!

Carl Backstrom, December 28, 2006 - 5:29 pm UTC

No Javascript!

Is there any particular reason for that decision?

Your missing quite a bit of very useful functionality if you stick with the no javascript on the internet rule you have. AJAX based pagination (here on asktom), TypeAhead completion, TypeAhead suggest. Not to mention some whole application, google maps, gmail etc.

A good popup blocker and adblocking plugin is much more pinpoint for most Internet annoyances while not blocking a huge amount of very helpful functionality?

I used to use lynx as my main browser but sometimes you just have to move forward with the rest of the crowd.



a reader, December 28, 2006 - 9:26 pm UTC

Do you have links for new rss feeds?

How "Popular" is Ranked, Is it "Recursive"?

K Balfe, December 29, 2006 - 11:08 am UTC

The most popular items on the New Ask Tom sees to be a different list than on the old Ask Tom. Was wondering why.

Also, if someone clicks on a question under the "Most Popular" tab, does that add to the count of "accesses" for that question. If so, might the list might become overly static? i.e. questions remain popular simply because they are on the "Most Popular" list. Sort of a recursive quality. I wondered if that was the case on the old Ask Tom because the list did seem quite static.
Tom Kyte
December 30, 2006 - 8:48 am UTC

the "popular" counters were all reset. These are the "newest most popular".

And yes, it becomes a self fulfilling prophecy over time.

Fonts...once again

A reader, December 30, 2006 - 2:23 am UTC


I totally agree with the earlier poster's comments regarding fonts...courier new is definitely a lot easier to read as compared to Arial.

Also, if people forget to enclose the code bits in the "Code tags", it becomes very difficult to follow the specific example.

And I remember having read somewhere (your blog perhaps) that Courier New is your favourite font :)...any specific reason to move to Arial.


Tom Kyte
December 30, 2006 - 9:46 am UTC

people asked for it and it does actually make it more readable if you mix proportional fonts with non-proportional (for code).

RSS feeds

Rahul, December 30, 2006 - 10:35 am UTC


I couldn't see a link/button for RSS feeds from this site. It might be easier to find for people using IE 6. Also the article explaining how the RSS feeds were built seem to be gone too?

However, the old feeds work. Also in Firefox I see the orange RSS icon in the address bar to subscribe to it.


RSS Feeds

Marco, January 02, 2007 - 12:02 am UTC


The RSS feeds should be auto-discovered by any version of FF and IE 7 (the orange icon that appears in the status bar or toolbar). And for the IE 6 users, I just put up links from the homepage.

The original RSS info page is still accessible here:
- Marco

Thread disappeared?

Frank, January 03, 2007 - 8:02 am UTC

Did this thread disappear?
Its title supposedly was "does refrential integrity make the database slow"
There are some links to this in several forums

Why does this happen

Priya, January 03, 2007 - 2:59 pm UTC

When I click on one of the question say on page displaying questions 76-90 and when I click back, it takes me to the first page of questions. Is that an intended behaviour or is it some kind of bug. Is it not supposed to take me to the same page of questions(76-90)?


Firefox plugin not working

Bob R, January 04, 2007 - 10:31 am UTC

Anyone else having issues with the firefox/mozilla AskTom search plugin. I'm getting the following error when I try to use it:

Not Found
The requested URL /pls/ask/search was not found on this server.

Sorry if this has already been brought up.
Tom Kyte
January 05, 2007 - 9:05 am UTC


Thanks Marco

Rahul, January 05, 2007 - 5:00 pm UTC

'back' takes me too far back

Alan, January 05, 2007 - 5:29 pm UTC

There is one aspect of the new format that I find unhelpful. here's the sequence:

* go to
* use the 'next' link below the list of articles to get to (say) the third page.
* click on one of the article links
* click the browser 'back' button.
* This takes me to page one of the list of articles. I think it should take me to page three (like the old version did).

I'm using IE6
Tom Kyte
January 06, 2007 - 12:26 pm UTC


This is a side-effect of our AJAX pagination (only the report refreshes and the browser doesn't realize you left the first page). We'll work on improving this.

- Marco from the APEX team


JL, January 05, 2007 - 11:08 pm UTC

please put a pretty icon to your website (link). i want to include in my bookmarks mozilla toolbar. thanks in advance. JL
Tom Kyte
January 06, 2007 - 4:32 pm UTC

tell you what....

you make me one, email it to me and we'll use it.

Session 0?

A reader, January 06, 2007 - 4:44 pm UTC

To "Marco from the APEX team":

I noticed that on the main question/answer page the APEX session is 0 i.e. the URL is f?p=100:1:0

From what I know of APEX, a session id of 0 is not normal. Can you elaborate on what this does?

Tom Kyte
January 08, 2007 - 1:57 pm UTC


You're right, this is not your actual session ID. The Zero Session ID is a new friendly URL syntax designed to facilitate bookmarking on public APEX pages (the real Session ID is saved in a cookie). Currently, it's scheduled to be included in APEX 3.0 as a supported feature.

- Marco


JL, January 06, 2007 - 7:10 pm UTC

ok, i made some icons and i send them, i don't know your email, so i just try it.
Tom Kyte
January 07, 2007 - 8:09 pm UTC

and it worked :)

I sent them along to the developers - we'll see what happens, appreciate it.

Request: turn off special fonts at end of review

Stew Ashton, January 07, 2007 - 3:47 am UTC

Happy New Year to Tom and readers.

Occasionally, reviews may end without turning off special fonts. This keeps them on for the rest of the thread, making it harder to read. Could you consider turning these off automatically?

At the top of this review, I put in < / b > < / i > < / u >. Despite this, I can turn the fonts back on with no problems:

Might be a simple solution?
Tom Kyte
January 07, 2007 - 10:42 pm UTC


That works well when a reviewer leaves single b, i, or u tags open, but doesn't handle the case of a review like "<b ><b >This is bold and the next review will be too" because we would need to close both tags.

Instead, we now validate that a reviewer closes all tags when entering the review to avoid this. Let us know if you see new reviews which don't close all tags.

Marco from the APEX team

Error on clicking the next button

Sandy, January 08, 2007 - 5:37 am UTC


every time I click the next button I recive an error and nothing happen. So actually I can not use the Ask Tom Page!

The error I recive is (try to translate the error description form German ...) "automation server could not create the object" in line 241 character 7.

I use an IE 6.0.2800

Tom Kyte
January 08, 2007 - 1:28 pm UTC


Do you have javascript enabled? Do other sites with AJAX (e.g. google maps) work for you?

- Marco

The next button followup...

Jmv, January 08, 2007 - 1:51 pm UTC

In a prior message Marco responded as follows:

This is Marco from the APEX team (helping Tom with the new site). I get no error paginating in IE 6. Can you enable script error notifications and tell us what the detailed error is?


Sorry but I cannot enable script error notification on my work pc, as it is locked down via corp policies.

Yes, the error still occurs.

Hopefully, somebody else out there can help troubleshoot further.


Time Zone

J.R., January 09, 2007 - 3:51 pm UTC

The home page shows: Times are East coast times (GMT-04:00)

I believe the current time is: GMT - 05:00 (Eastern Standard Time)

I want my money back!!!

steve, January 09, 2007 - 8:06 pm UTC

Here I am training a new Oracle DBA and preaching the virtues of instrumentation from lessons learned at HOTSOS 06. Fliping through the symposium notes and ah-ha - see how Toms show how his ASKTOM site is instrumented. Simply click on a question, change the NO to YES, and ahh, hey where did the NO go? Can we no longer get a peek under the covers of the new ASKTOM?


Ask the experts

juancarlosreyesp, January 10, 2007 - 8:04 am UTC

Hi Tom I found the package to create an askXX

The question is where can we upload free, and get it work :)
I remember there is a site for free for tests from Oracle, but I'm not sure if that site can be use for this.

The other problems is geocities and others don't give support for Oracle XE databases, only mysql.

Thank you :)

Thread disappears for sometime after review

A reader, January 10, 2007 - 4:56 pm UTC

Whenever I write a review and submit it, the thread disappears for sometime. Even if I search by exact thread heading, it does not show up in the search results. Is this by design?

I also noticed that the spell check feature is gone.

site not working!?

A reader, January 10, 2007 - 7:31 pm UTC

pls restore the old site. I hope you have a backup!?

Trace function in the new asktom

Doug from Dallas, January 11, 2007 - 1:14 pm UTC

I don't see the trace/timing function like the "old" asktom had. Is it available now?

FYI, some links don't work in Firefox

Khalid, January 24, 2007 - 12:10 pm UTC

1) If I use Firefiox (, The Feeds links (New Questions, Popular, Hot) gives me some XML when I click on them

"This XML file does not appear to have any style information associated with it. The document tree is shown below."

... but they work fine for IE

2)The "Select a Rating" also have a drop down should have a N/A rating, which is what I would have selected for an FYI, or a suggestion such as this, as I am not responding to your reply to CJ

AskTom site not updated

Emad Kehail, January 26, 2007 - 2:43 am UTC

Hello Tom,

From about two weeks and is not updated. I do not know how and why this happen but the main page always displays the following threads

Subject Last Updated First Asked
Stripping special characters 11 Jan 2007 09:20PM 6.4 years ago
CLOB 11 Jan 2007 09:15PM 2.7 years ago
Limit Depth of Hierarchical Query 11 Jan 2007 09:13PM 4.1 years ago
Analytics question 11 Jan 2007 08:59PM 3.3 years ago
How to Update millions or records in a table 11 Jan 2007 09:38AM 4.2 years ago
Storing Chinese Characters 11 Jan 2007 09:37AM 3.6 years ago
Null 11 Jan 2007 09:33AM 2.8 years ago
Triggers vs Packages ! 11 Jan 2007 09:32AM 4.4 years ago
How can I track the execution of PL/SQL and SQL? 11 Jan 2007 09:31AM 6.4 years ago
getting rows N through M of a result set 11 Jan 2007 09:30AM 6.7 years ago
Archive Log file 11 Jan 2007 09:28AM 4.8 years ago
TNS listener, ORA-12500, ORA-00955 11 Jan 2007 09:27AM 4.9 years ago
criteria for selecting a partition key 11 Jan 2007 09:25AM 5.5 years ago
Sequences 11 Jan 2007 09:23AM 4.6 years ago
Intelligent Partitioning Table 11 Jan 2007 09:22AM 5.3 years ago

Moreover, I have tried to search your site for "mutli_column_datastore" but the search ends with "No questions found matching search criteria"

Even in my google main page, I have a customized google page which reads your RSS, it is always never updated!!


Re:AskTom site not updated

Mital, January 26, 2007 - 1:52 pm UTC

Even I found that since past couple of days, when I visit,same threads as mentioned by Emad Kehail.

I was just wondering as why there are no updates or questions & answers follow-up on the site.Even tom's blog does not have any new topic to be discussed.

Hope that T