That's interesting
Doug, November 17, 2001 - 5:22 pm UTC
Thanks Tom that's interesting, I just got curious. How big is your db?
November 18, 2001 - 3:19 pm UTC
This is just the "asktom" part.
Its about 400meg in data + index.
There are (as of Nov 18th) 12,773 Q&As.
The biggest Q&A is 33k, the average is about 1.5k.
I've gotten 3,657 "reviews/followups" where the biggest was 10k and the avg is 263 bytes.
I've followed up 927 of them (well, thats actually hard to tell cause we implemented the actual "followup" function well after the review function, I used to just followup in the question itself but that got confusing after a bit). My biggest followup was 19k and the avg is 777bytes (guess I'm much longer winded then you guys are ;)
It started on Jan 2000, so this represents almost 2 years of data collection. asktom recieves about 20,000 page views (not hits, pages generated) a day mon-fri (sat and sun drop to 4,000 and 2,000 respectively). I see about 1,300 different IP addresses on any given day (firewalls mask many people as "one" so its somewhat higher)
some question about askTom
Michael, November 19, 2001 - 2:54 am UTC
Tom,
I am always curious about the hosting of "askTom".
Are you paid for that "job"?
How many people working on it?
Is there anyone helping you answering question?
Indeed, I do thank for your kindly help and admire your knowledge on Oracle!
November 19, 2001 - 7:40 am UTC
In as much as Oracle provides me my paycheck, yes, I am compensated.
I used to write the software that runs it, someone else maintains that now on a very part time basis (not much needs to be done to keep it going).
I answer most all of the questions, I send some out, but the vast majority (well over 95%) are me.
Good Job
srimal, November 19, 2001 - 9:48 am UTC
Hello Mr Tom,
This is a wonderful and a very useful site for Oracle questions and the answers are always what I am looking for..
Thank you for all your help and a great support!
A reader, November 19, 2001 - 9:56 am UTC
Thanks Tom ! I guess, everybody might be wondering about the environment behind asktom.oracle.com !
Are you addicted to this, like some folks are to email or discussion boards ? Like, do you keep checking for new questions all the time; say, on weekends & holidays ? Or like, check what are the latest questions whenever & wherever you get an internet access ???
November 19, 2001 - 10:22 am UTC
Addicted -- no, i wouldn't say that (thanksgiving is coming up for me here -- I'm going to shutdown for a couple of days).
I tend to answer in batches -- morning, afternoon, evening. I strictly control the number of questions....
Have a nice break
REader, November 19, 2001 - 11:41 am UTC
Hi, Tom,
I strongly agree with you ---- have a nice break and happy thanksgiving day.
I'll say thanks----Tom for your all supportive and profesional answers.
Reader
virtual teleconf with u.
Ramasubbu Muthuraj, November 20, 2001 - 2:04 am UTC
Hi Tom,
Especially regarding this question and answer,am feeling virtually watching some interview on television giving by u.
great!!!knowledge sharing!!!.
Thanks tom.
reg
muthu
where to start with 9iAS
Igor, November 20, 2001 - 4:15 am UTC
For someone who should start with 9iAS, where is possible to
find simple examples to start ?
November 20, 2001 - 7:51 am UTC
about f?p= ... (in the asktom URL)
Vikas Sharma, August 26, 2002 - 12:56 pm UTC
Hi Tom ,
Can you please tell me in url
ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1995355238285 what is this f. I believe you r calling a procedure f and passing parameters in p= 4950:8:::::. Are you dynamically executing the others packaged procedures or function depending upon the parameter you passed by using execute immediate.
I know you are not in favor of dynamic executes whenever somthing can be done by statically. But if you are doing it that probably for the security reasons. I am very curious to know about f?p=
Thanks
Regards,
Vikas Sharma
August 26, 2002 - 1:47 pm UTC
No, it is not dynamically calling other procedures. To see and understand the software behind asktom read:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3489618933902 <code>
It is an application develop environment we've been working on for a bit and have made "public". There is a link to OTN there that takes you to the software in question.
F is a static function -- it is our "runtime engine" (and that engine just happens to live in the database, written in plsql)
Realsing features Vs Using features
A reader, August 26, 2002 - 7:10 pm UTC
Tom,
Beyond doubt, Oracle is leader in RDBMS technology to release the new features. But these features are of no use, if people are not using it. Most of the new features are better way of doing things; and sometimes it takes time to change the mind set of people, whenever there is new feature usage idea proposed in developing applications. There is always a more confidence in adopting the previous WORKED approach for doing things which has been done in the past, BUT this may not be the best approach today. Atleast for technology lovers people, this site is one of the aspiring site with strong encouragement, and definitely helps a lot in using the new features. The information is trustworthy, it is not only the questions which are of high standard but the approach for answering questions is beyond comparison. I don't think there is even a single *BOOK* can replace this. The response time for getting reply is great. When a site has become so rich in information, and travelled so far, and the information is being applied in the real world, I DONT THINK there is a time for thanksgiving by Tom. More appropriately, it is the time for OTHER SOFTWARE VENDORS to adopt this model to help their customers/users.
Once again thanks Tom!
OTN Link
Vikas Sharma, August 27, 2002 - 12:45 am UTC
Hi tom,
--There is a link to OTN there that takes you to the software in question. --
I shall be grateful if you provide me the OTN link. I searched OTN but could not get.
There was link (interested in software behind asktom) on you site some time back which is not seen now is that is the same link you are talking about.
many thanks in advance.
Vikas
August 27, 2002 - 8:26 am UTC
how to call funtion from mod_plsql
Vikas Sharma, September 07, 2002 - 8:15 am UTC
Hi tom,
This is regarding a review post by you. you said :
"F is a static function -- it is our "runtime engine" (and that engine just appens to live in the database, written in plsql "
If 'F' is a function then can you please tell me how to call functions from mod_pl/sql. for eg </code>
http://host/pls/dad/function_name <code> does not work. AS far as i know we cam only call package.procedure or standalone procedures.
I shall be very thankful if you can elaborate your review ("F is a static function -- it is our "runtime engine" (and that engine just appens to live in the database, written in plsql " ) more.
Thanks,
Regards,
Vikas Sharma
September 07, 2002 - 10:35 am UTC
sorry, F is a static "procedure".
You cannot call a function from mod_plsql (nor would it make sense -- what would happen with the return value?).
how to call funtion from mod_plsql...Thanks
vikas Sharma, September 07, 2002 - 1:22 pm UTC
Thanks..
page layout change
robert, February 20, 2003 - 4:55 pm UTC
hi, tom,
Was it your idea to switch the message/annoucement section to the bottom ? why the change ? because of user-feedback or something ?
February 20, 2003 - 7:16 pm UTC
user feedback -- since I moved the search to the home page, it didn't look right to have it(search) in the middle.
What's that tiny little....
robert, February 21, 2003 - 11:33 am UTC
What's that tiny little number like "1.42" at the far bottom left corner of the (home) page ? it changes with each refresh.
February 21, 2003 - 3:13 pm UTC
it is how long it took to generate the page -- I am working with the marvel team to get that back to to like 0.20 ;)
tiny "feature" in their new reporting engine we are working on...
Feature: Your Questions (a suggestion)
Robert, February 22, 2003 - 1:33 pm UTC
This new feature is great ! Let's one know how much
one's been bugging you :) or time to bug you again....
Now the result list is not sorted in some way at all, is it ?
It'd be nice to have it sorted by the date column.
February 22, 2003 - 2:10 pm UTC
Here is the query we run:
select lnk, how, description, to_char(status_ts,'Dy Mon dd, yyyy' )
from (
select '<a href="f?p=4950:61:'||:FLOW_SESSION||'::::P61_ID:'||a.id||'">' || a.subject ||'</a>' lnk,
'posted by you' how,
description,
status_ts
from wwc_ask_submitted_questions$ a, wwc_ask_posting_status$ b
where a.status = b.id
and a.email = lower(:P60_EMAIL)
union all
select '<a href="f?p=4950:61:'||:FLOW_SESSION||'::::P61_ID:'||a.id||'">' || a.subject ||'</a>' lnk,
'reviewed by you' how,
description,
status_ts
from wwc_ask_submitted_questions$ a, wwc_ask_posting_status$ b
where a.status = b.id
and displayid in ( select displayid from wwc_ask_question_reviews$ where email = lower(:P60_EMAIL) )
)
order by status_ts descso it is sorted by the date associated with it (date of last modification -- not the SUBMITTED date - the date of last mod)
Yes, YOUR QUESTIONS list is indeed sorted
robert, February 22, 2003 - 1:38 pm UTC
Woops, never my my "suggestion", the list is indeed sorted
by date desc.
It's just the text got wrapped...
Mike, February 22, 2003 - 11:09 pm UTC
"it is how long it took to generate the page -- I am working with the marvel team
to get that back to to like 0.20 ;)"
it has been always in 1.2 for me, and accessing ASKTOM.COM
I use the cable modem to access internet.
February 23, 2003 - 8:49 am UTC
we just did an upgrade, it went from 0.2 to 1.2 due to a issue with their reporting engine, instead of just getting the first 10, they are getting the first 500 rows and just displaying the first 10. they are working on it.
Mike, February 23, 2003 - 10:01 am UTC
"..wwc_ask_submitted_questions$ a, wwc_ask_posting_status$.."
are these _$ tables/views your custom stuffers or from some Oracle apss/9ias...?
see we become nosy...
February 23, 2003 - 10:21 am UTC
they are mine, part of "ask tom"
_$ for ?
Mike, February 23, 2003 - 10:32 am UTC
In your convention, _$ is a table or view? or might have some other meaning?
February 23, 2003 - 10:45 am UTC
the guys working on it use $ at the end of every tablename... (or view as a view is really just a table as far as anyone should be concerned)
Search Results of Ask Tom now
Vikas Sharma, February 23, 2003 - 3:23 pm UTC
Hi Tom,
Since when the new look has been introduced. The search on you web site are giving the exact result found. So your team must be doing must be doing select count(*) from... to get the exact no. of rows then dividing it into the paging set of 10 and then select for getting the actual rows using pg_min_row=11, &pg_max_rows=10, pg_rows_fetched=10
I know you strong beliver that one should NOT do select count(*) from ... just for this reason. As earlier you use to do it with the intermedia's approximations. May i have your comment on this? or this is the viewer request to show the excat no. of result.
February 23, 2003 - 3:50 pm UTC
Yes, that is a BUG they put into the software without me knowing about it -- there is an OFFICIAL bug filed against this. It is KILLING my performance. I'm pretty upset about it -- they are fixing it and adding the "tom pagination" feature back in.
the "feature" that is killing my performance, limiting my scalability, slowing everything down -- it is going away....
Search
Vikas Sharma, February 23, 2003 - 4:09 pm UTC
Hi Tom,
Thanks for immediate reply.
I was sure that you would not accept this bug.
I am also the fan of "tom pagination" methodology on which i already had discussion with you in a thread and implemented it in one of my web application.
Pointing at your book GIF...
Robert, February 24, 2003 - 1:44 pm UTC
It says "Arrived in June..."...just splitting hair....
Mike, March 12, 2003 - 7:31 pm UTC
"
it is how long it took to generate the page -- I am working with the marvel team
to get that back to to like 0.20 ;)
tiny "feature" in their new reporting engine we are working on...
"
It has been improved from 1.2 to 0.6
How your team did that? Please just outline the methods/'tricks' used to get what's now
many thanks
March 14, 2003 - 7:16 am UTC
It is between .2 and .3.
I fixed the pagination (i have a message saying ignore that "1-10 of 11" thing, that is going away.
Query from Internet Search
Nurlan, March 14, 2003 - 8:05 am UTC
Hello again Tom,
Just wondered why your answer pages are not available from internet search services? Like yahoo or google? I do not remember that I had a hit to your site from querying these services,
March 14, 2003 - 6:05 pm UTC
look at the URLS -- every page is a new experience. they cannot walk my site. i turn them away.
ORA-0602
Eddie, March 14, 2003 - 10:47 am UTC
report error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Got this error when first logged on to asktom.oracle.com
The search feature did not work either-giving the same
error.
So I passed :7: inplace of :1: in the url and got the
listing of question. There on Search worked as well.
Some maint. work going on?
Did you have to flush your shared pool today?
Tianhua Wu, March 14, 2003 - 3:07 pm UTC
I saw ora-6502 this morning from this site. I have very similar problem in a 8.1.7.3 database. We had to flush the shared pool when this happens. Is this the same problem?
March 15, 2003 - 8:50 am UTC
No, not at all -- it was a self induced "programmer made a change and didn't check his work" error. I was away for the day and he never checked his "fix"
If you are getting a 6502 -- unless you are hitting a bug (which I've not seen), flushing the shared pool would not fix it. Either you are experiencing false causality (the effect of the 6502 going away is unrelated to the flush shared pool -- it is just a coincidence) or you have hit a bug and opening a tar with support would be called for.
Ask Tom vs search engines
A reader, March 14, 2003 - 6:40 pm UTC
>look at the URLS -- every page is a new experience. they
>cannot walk my site.
>i turn them away.
Could you explain that in more detail ? What do you mean by "experience" ? I have always been curious how these search engines work. How do you turn them away ? Thanks !
March 15, 2003 - 9:02 am UTC
Look at my urls -- you have a session in there. even if they walked the site (which I don't permit, I don't allow web wackers either -- I turn them away, it is based on user agents and in extreme cases where people persist, I'll block an ip address) it wouldn't be useful as the page is a one time experience. it is for your session.
most intelligent indexes stop all by themselves right there.
PL/SQL web toolkit
Robert Xuequn Xu, April 18, 2003 - 1:42 am UTC
It's a nice toolkit for developing web enabled database applications, and I can see that this site is a live example. However, there are two questions (or concerns):
1) Would you rather use straight hpt.print() to print out plain html, or use those htp API calls? Which way is more efficient? To me, those htp APIs (htp.title, htp.htmlopen, etc...) are just re-inventing the wheel. If one can just htp.print anything, way bother using those API calls? There must be a reason for their existence. What do you use inside asktom site?
2) Is there an "API" (a really useful one) to generate web graphics (dynamic JPG or PNG diagrams)? I know some Perl modules can do that - generate diagrams on the fly in JPG format, based on data retrieved from the database. I wonder if the PL/SQL web toolkit has the same capability.
Thanks!
April 18, 2003 - 11:53 am UTC
1) why code in C when you can code in assembler? I mean, C is just an abstraction, an inefficient layer on top of assembler. Going further -- isn't assembler just a layer on object code? All we need therefore is a binary editor and a good grasp of octal/hex and binary code and we'd be done :)
We use a mixture of both. we use the htf functions alot in SQL -- select htf.anchor( a, b ) .... for example. Depends on
o which is most convienent
o which makes most sense.
htp.p() sometimes.
htp.bold() others
htp.pre() frequently (i'm a big fan of courier as you can tell!)
2) PLSQL no, its not really good for that. Java -- yes and you can even load them right into the database as a java stored procedure in many cases (not a full AWT implementation in there but generally "full" enough)
A reader, May 10, 2003 - 9:34 am UTC
"
it is how long it took to generate the page -- I am working with the marvel team
to get that back to to like 0.20 ;)
tiny "feature" in their new reporting engine we are working on...
"
You said: it is how long it took to generate the page.
I would know what sorts of work related to generating the page. It is most by iAS or the backend DB, or both?
May 10, 2003 - 8:15 pm UTC
in my case, since the page is generated by plsql, 100% the DB.
Sean, May 11, 2003 - 11:43 am UTC
Thanks for answing.
I have just click asktom.oracle.com home, it was 0.45 shown at left of the bottem. How much of it was database cost and how much was from the mid-tier iAS server?
Thanks.
May 11, 2003 - 12:52 pm UTC
100% from the db -- the "start timing" is in plsql, the code to generate the page is in plsql, the "end timing" is in plsql.
all the app server is doing is translating a URL into a stored procedure call, calling the procedure and returning the page.
Great site but a bug
Gabriel, May 30, 2003 - 9:33 am UTC
Hello Tom,
I used to serach sites in this oreder: tahiti.oracle, metalink, tom. After 1 year is tom, tahiti, metalink. All this to say this is an excellent site, thanks for keeping up.
I think there is a bug on the web site. Often after I do a serach, it says we found 90 results but after the first page of 10 results there is no link to the next pages. The link does not reapear if I reexecute the query.....
Thanks
May 30, 2003 - 9:43 am UTC
it says "approximately"
I use google as the "gold standard" for search engine excellence. Anything they do, I do.
They say approximately, so do I.
They are often way wrong, so am I.
I am all about performance, so are they.
I refuse to count exactly that there are "123 results" when in all probability you'll look at 10 of them and no more. What a waste that would be (i hate that technique. when I get a system, first thing i do is remove those exact counts -- and the links to "every single silly page" in the result set and the link to the last page).
See what google does and follow its lead. They ain't broken over there.
goto google and search for oracle.
note that they DO NOT offer to take you to the last page -- or even page 22. You have to work hard to get to page 20. Now, just try to get to the last page -- what, they actually have the nerve to cut you off at a reasonable limit? (you cannot get past page 70! and as you page through, they take longer for each page)
Now, search for
"tom kyte oracle"
on google, they'll say "showing you 1-10 of about 18" (as of May 30, 2003 anyway). But wait --- where is that NEXT button? Hey, it isn't there. Even if you use the "show redundant ones", there are not 18 for that exact string.
No bug, working as designed ;)
utl_tcp and your page
robert, August 21, 2003 - 2:37 pm UTC
Tom, how can I get YOUR home page with this code below ?
DECLARE
c utl_tcp.connection;
ret_val pls_integer;
BEGIN
p.set_linelen(200);
p.set_prefix(NULL);
c := utl_tcp.open_connection(remote_host => 'asktom.oracle.com',
remote_port => 80,
charset => 'US7ASCII'); -- open connection
ret_val := utl_tcp.write_line(c, 'GET / HTTP/1.0'); -- send HTTP request
ret_val := utl_tcp.write_line(c);
BEGIN
LOOP
dbms_output.put_line(utl_tcp.get_line(c, TRUE)); -- read result
END LOOP;
EXCEPTION
WHEN utl_tcp.end_of_input THEN
NULL; -- end of input
END;
utl_tcp.close_connection(c);
END;
How Asktom content is being output?
Jap Boon Churn, September 30, 2003 - 1:53 am UTC
Hi Tom,
I understand that AskTom is built using PL/SQL.
So, when your engine output the content from your AskTom site, most of the content is dynamic right?
I would like to know (if you don't mind), inside your stored procedures, do you htp each and every line of html codes? or you have some techniques that can segregate between static template html that can be pumped in with dynamic data at run time?
I understand PLSQL Server Pages can do that, but i don't think AskTom is using that.
So, would appreciate your sharing on how can i do that without sacrifying the output time if i don't use htp.p to directly my web application content. Or maybe you have a reusable component that we can use.
Please advise.
Thanks.
September 30, 2003 - 7:07 am UTC
all of the content is dynamic, yes.
we use HTML DB, i don't really "htp.p" much of anything (but it does).
see
</code>
http://otn.oracle.com/ow2003/ow_all.html <code>
there is a paper/presentation on html db there.
link
Mirjana, September 30, 2003 - 7:52 am UTC
Hi Tom,
I followed this OW link to get HTML DB presentation,
but how come there's no "Whitepaper and/or Presentation"
link for "Accelerated Web Development with Oracle HTML DB" session?
September 30, 2003 - 8:31 am UTC
i don't know, I don't manage otn content, you would have to ping them over there.
-------------------------
Mirjana,
Try the OTN forum on HTML DB at:
</code>
http://forums.oracle.com/forums/forum.jsp?forum=137 <code>
If you can't find what you are looking for or need some information on HTML DB, you can get in touch with me at (I know I'm going to regret this on such a broad forum) tyler.muth@oracle.com
Thanks,
Tyler
HTML DB Development Team
Thank You.
Sandeep s Bajwa, September 30, 2003 - 9:13 am UTC
I really love this site. Please do not make it a pay site or part of metalink. Keep up the good work.
You are a big man
Bao Nguyen, May 02, 2004 - 2:46 pm UTC
Tom, I wonder how you can answer all the questions. Look like you know everything ;-). How long have you been with Oracle ? I found a lot useful explanations from your site. Excellent job.
Thanks very much !
about f?p= ... (in the asktom URL)
Jon, June 10, 2004 - 3:57 am UTC
Tom,
You have stated that F is a static procedure (the HTMLDB "runtime engine").
I am trying to understand the concept behind this runtime engine. I assume the F procedure will do the authenication, authorization, etc.
Here are my questions:
1) Does the F runtime engine use "execute immediate" to call the stored procedures? For example, I am thinking it can be something like this:
procedure f as
proc_name varchar2(30);
sess_val1 varchar2(30);
begin
--does runtime code, like authentication and authorization
--parses request parameters
--gets session values
proc_name := 'my_proc';
sess_val1 := 'some session values';
executue immediate 'begin ' || proc_name || '(p_param1=>:p1); end' using sess_val1;
end;
2) If HTMLDB page needs to execute my existing stored procedures, how will it called them?
3) If the F engine uses "execute immediate", do you think it is an OK design for performance if it binds the variables? I have used and seen many Java applications that have this design, the controller code dynamically instantiates the Java class (Model) to execute the page. The "execute immediate" statement seems to me like Java "Object c = Class.forName(); c.doSomething();".
4) It seems to me that F procedure is like a controller in a MVC pattern. Besides HTMLDB, do you know of any other PLSQL web development framework?
5) If I want to create a lite PLSQL web application framework, do you think I can use "execute immediate" like example in question #1?
6) Is there a HTMLDB Developer Guide? I find the existing HTMLDB User Guide pretty good. However, it is not design to cover more detail information, such as the internal design, tables, APIs, etc.
As always, thank you for all the helps. You are doing a great job helping many of us who want to correctly use Oracle products.
Peace,
Jon
June 10, 2004 - 7:57 am UTC
f does not do authentication or stuff like that.
f looks at the inputs which includes the flow (4950 is my flow, my application), the page number (the number right after the flow) and then parses the rest of the inputs and invokes the right page for the right flow.
htmldb is metadata driven, it does not generate a procedure for page 8 of flow 4950 -- it stores data in a table about that page and then reads that information and renders a page.
2) it would use dynamic sql to call things that are not known at compile time. (compile time being the time the htmldb guys themselves build the product)
3) yes.
4) there are others, but htmldb is the one I am familar with. people have written their own, yes. as far as I know there is at least one "for fee" 3rd party product that is similar in some respects to htmldb.
5) yes, but -- it would be a "scientific" endeavor in 2004 -- meaning, the stuff exists and has some 5 years of development into it. I'd just use it.
6) the internal table design and non documented apis are -- well -- INTERNAL TABLES and NON-DOCUMENTED. they change, they are not something you or i would/should/could use!
Nice Site for Oracle DBAs
MEHMOOD, June 10, 2004 - 8:53 am UTC
Tom:
It is a great site and effort. We always learn many things here. And it is such a nice site for Oracle DBAs, that if once we come on the site we have to control on ourselves to read the articles, because every thing is interesting and very nicely explained.
Great work Tom.
MEHMOOD - is that you???
Anthony, June 27, 2004 - 2:47 am UTC
Hey MEHMOOD,
is that you from MCB?
Cheers,
ANTON
Amazing site but how about email notification?
Gabriel, July 08, 2004 - 9:28 am UTC
Hello Tom,
This is an amazing site! I've been using it (abusing it) for 3 years now and it was a real help. I was wondering if you could implement email notification of when our questions are answered. I know that you answer them very quickly, it still amazes me how quickly ( I regularly have arguments with my fellow DBA who are convinced that you have at least 10 people working for you on this site, don't worry, I'm on your side), but it would still be nice to be notified when our answers are ready.
This way, the effort that you make in answering them quickly, would be immediately recognised, by passing on the time savings to the oracle users immediately. This would translate to even happier customers (DBAs) (if such a thing is possible), increasing the popularity of Oracle even further.
July 08, 2004 - 9:43 am UTC
maybe... thinking about some rewrites later this summer, that idea is on the list.
Bookmarks
A reader, August 19, 2004 - 8:10 pm UTC
Would you please tell me how to access the bookmarked
articles? Can't find how to do this on website.
August 19, 2004 - 8:19 pm UTC
you click on the bookmark this page link and the resulting page has a url that is "bookmark safe"
the normal urls contain a sessionid, if you bookmark them -- they work for a day or so but then stop working.
Advanced Search in site
Susan, August 19, 2004 - 9:12 pm UTC
Tom,
In one of your earlier answers to 'pipelined' questions you suggested to search in your site for 'pipelined' . I was trying and do not get any results. What I do is wrong ?
August 19, 2004 - 10:50 pm UTC
Search
{pipelined} Approximately 551 records found.
???
maybe you typed it in wrong?
page contains 'pipelined'
Susan, August 20, 2004 - 8:40 pm UTC
Thanks Tom.
Previously I was searching for subject contains {pipelined} returning no results. Now searching for page contains returns 562 results.
HTML Generation of your site
Bob, August 31, 2004 - 6:54 pm UTC
Where are the queries for generating the html on your page stored?
The reason I ask is that the application I'm working on has an oracle database with xsql/xsl pages to generate the pages. This becomes a real niusance when a table changes and you have to figure out what files might be affected.
If you have the time, an example of how the technology of behind this site might be used with the emp/dept tables would be terrific.
Thanks!
September 01, 2004 - 7:31 am UTC
everything is in the database -- literally.
we use
</code>
http://htmldb.oracle.com/ <code>
to build this. everything about the application is just data in tables itself.
Followup
Bob B, September 01, 2004 - 9:46 am UTC
If everything is literally in the database, how do you scale it once the data exceeds the initial database? Is there a performance hit for not spreading your application's logic along an n-tier architecture?
I know I read somewhere here that asktom is one of 6 applications hosted by the same ip? If asktom were an application all by itself, what kind of generic hardware would it have? (i.e. A web serer and a database box or a web server, an application server, and a database or what?)
Personally, I'd love to have all the database related stuff (i.e. queries) in the database, but everyone here thinks the database should just be a data store and the business logic should be in a separate layer (in this case, java instead of pl/sql, yuck).
September 01, 2004 - 10:16 am UTC
give me a "for example" -- i've yet to hit the wall on a well designed application.
consider the performance hit you get by spreading it out all over the place. If what you have is a data centric, database application -- what point to bringing the data out to a middle tier just to be turned into html?
oh, then we get into "well, the database is obviously slow, so we'll build middle tier caches, we'll dump data out, we'll do transactions for you, yadda yadda yadda".
if it looks like a duck, walks like a duck, quacks like a duck -- it must be a duck. middle tier apps that cache, do transactions, etc -- well, they are just databases all of a sudden -- and if they had taken advantage of the functionality they already bought in the database -- well, maybe they might not have had to write so much code.
This doesn't apply to every application, in every situation (nothing does, never). However, for most applications I see developed -- a large complex, infinitely scalable (in theory -- purely hypothetical) middle tier application with 50 billion moving pieces to install, manage, configure, debug, fix, upgrade, whatever -- is overkill to the nth degree.
Not only that, but it takes N times the hardware to do the same thing you could do "simplier"
On asktom, we have a database and a webserver that simply turns a url into a stored procedure call (mod_plsql). Nothing more. Nothing less. Pure simplicity. Not only that, but the machine is in a dmz, direct access is hard -- but http flows in and out just fine. So, to work on the application, to deploy a new application, nothing more then a web browser, no ear, war, zip, xml, etc etc etc files -- just data in a database, where data belongs.
Further follow up
Bob B, September 01, 2004 - 11:28 am UTC
Tom,
I agree whole-heartedly. The problem is, with only 1 year experience, I don't have enough firepower to convince the experienced "vets" over here that more code often means slower and harder to maintain.
My biggest argument is that two of the slowest operations around are disk io and network traffic. By spreading out the application, the disk io increases and the network traffic certainly increases. As for maintenance, seems like we're spending more time trying to figure out what we need to maintain than actually maintaining it.
Can you give a situation where you might use xsql/xsl or some other html generation method over html db?
September 01, 2004 - 11:53 am UTC
Actually, no I cannot ;)
not "X" technologies in any case. If you exceed what you can do simply, then generic technologies are going to be less than scalable as well. You get into the realm of "proprietary implementation built from the ground up to be super fast and scalable"
How large are your main tables?
Bob B, September 01, 2004 - 12:18 pm UTC
Tom,
What are the (current estimate) sizes of the tables that you grab the question details from (i.e. the page with the question, the answer, the user followups, and your followups)
September 01, 2004 - 2:22 pm UTC
TABLE_NAME LAST_ANAL MBYTES
------------------------------ --------- ----------
...
WWC_ASK_REVIEW_FOLLOWUPS$ 01-SEP-04 17.2
WWC_ASK_QUESTION_REVIEWS$ 01-SEP-04 32.9
WWC_ASK_SUBMITTED_QUESTIONS$ 01-SEP-04 57.1
WWC_ASK_QUESTION_ACCESS_LOG$ 01-SEP-04 264.7
WWC_ASK_RSS_LOG$ 01-SEP-04 645.2
WWC_ASK_QUESTION_SEARCH_LOG$ 01-SEP-04 750.2
WWC_ASK_ACTIVITY_LOG$ 01-SEP-04 1255.3
18 rows selected.
so, review_followups is where I type, query_reviews is where you type and submitted_questions has the original Q&A....
yes, my audit trails are my biggest objects :) 3gig of audit, 100meg of data...
www.asktom.com ?
A reader, December 16, 2004 - 7:34 am UTC
asktom.com was registered by a SnapNames customer using SnapNames' patent-pending acquisition technology.
I wonder???
web connection to db
denni50, January 03, 2005 - 2:31 pm UTC
Tom
I know you've recently implemented some stringent rules on posting questions to existing threads...however the question I am about to ask more or less falls in line with the threads here on how you setup and connect to your website from your machine...so in that context I am posting my question here :-)
Question:
We are in the development stages involving major changes to our web-based operations. I have been directed to research how to connect our internet site to our database.
We recently terminated our contract with a data management company that handled the web-based files that were then sent to us for importing.
This will be a first for the organization in setting up a web-connected application with security, performance and data integrity being critical issues.
Essentially what we're thinking is somekind of link or connectivity to the server that hosts our website where we download the data into a batch file system, preferrably in .csv format that we can then run through the software import module that would perform all the business rules before inserting into base tables.
I sifted through your book EOO and the only reference I could find on web applications was in chapter 22...nothing that related to our interest.
Would we use ODBC to connect to server hosting web site?
This is a new realm for me so any advice/help to set me on the right course is greatly appreciated.
thanks a bunch
ps: what is Direct Path Load API?
January 03, 2005 - 10:28 pm UTC
doubtful you would use ODBC.
but I won't be able to answer this question here - it is a very large question under the covers.
You need to talk about how your firewalls work, where the DMZ's are, what protocols are allowed where, why you even BATCH in the first place (why not just update the source) and so on
In other words - it is much bigger than a bread basket.
Anyone have a pointer to a good book to get the "overall picture"?
direct path loads are the ability to read from a file and write directly to oracle database blocks -- bypassing the sql engine. like a direct path load does. OCI (our C api) supports an interface to this high speed load option.
thanks Tom....
denni50, January 04, 2005 - 8:35 am UTC
I kinda figured there'd be alot more to it.
I would need to get with the System Admin to discuss
firewall and protocols. What are DMZ's?
Batch System:
What is meant is that the files would be downloaded
in .csv format. We have an Import Module that is part
of the packaged software. The module is setup to
apply all business rules i.e. de-duping, address standardization, data validation and filtering(if a pranskter submits inappropriate data it will not get inserted into the base table). The import module requires
a batch posting system...hence the need for batches.
Hopefully someone here can guide me to resources about
how to get this done.
thanks
January 04, 2005 - 8:51 am UTC
when you ask them about firewalls/protcols, ask them to describe the DMZ as well (asktom is in a DMZ, firewall between me inside Oracle and it -- sort of outside Oracle)
DMZ's
denni50, January 04, 2005 - 9:37 am UTC
Tom
did some research on internet connections and firewalls,
my initial interpretation of the acronym was correct(
demilitarized zone from the Vietnam war).
from what I understand should we be looking at getting
a web server to connect to website, that will leave
the production db safeguarded from potential attacks.
this way we can block all tcp connections to ports
except the http traffic, which I am assuming will be
the protocol to connect to the website.
sorry for the plethora of questions, I was just told about
this yesterday and we need to get this up and running by
1/15/2005...geezz talk about long-range planning.
appreciate any guidance if what I am thinking is correct.
January 05, 2005 - 8:02 am UTC
It is normal to have the db behind the firewall (in your space), the app server in the DMZ..
to have someone who doesn't know this stuff (self included, I would be the wrong person to architect all of this as well) do this in two weeks is "not a good idea (tm)". do it wrong and you are toast. Problem is, you don't know you did it wrong until after someone breaks it all apart.
Agree Tom...
denni50, January 05, 2005 - 8:20 am UTC
thanks for your help/input just the same.
Fortunately mgt came up with an interim plan given
the short time span and cost it would take to set
up the web connection(including hiring an expert in the
field to do the job). The web administrators(consultants
and programmers) will manage the data for us and set up
a secure ftp site for us to download the data encrypted
on a daily basis(phew!).
Like you said(and I totally agree)...even though I am
someone who is willing to do and learn whatever it takes
to accommodate the organization's plans and needs at this
point in time I have no knowledge or skills in this area.
It's taken me almost 3 years to get where I am today with db
administration,programming and data management.
as always thanks for your help, advice and honesty
one thinhg which is quite annoying
A reader, March 22, 2005 - 7:21 pm UTC
Hi
When we search the site there are sometimes over 1000 of posts and sometimes as few as 50 or 60. Not sure how you limit the results but for example when I get 50 results I can normally see around 35, I understand that if there are 1000 there is no point to see them all but for small number of results why do you still limit them? I mean if there are only 50 most of times I am ready to see them all....
March 23, 2005 - 1:07 am UTC
it says (just like google) APPROXIMATELY....
it would be such an utter waste of resources to compute "there are 501" when by the time you got there, there would be 503 or 450....
I never LIMIT the results
I only show you the ACTUAL hits..
I modeled this after the way the gold standard of search engines works....
how do you calculate the "approximate"
A reader, March 23, 2005 - 3:11 am UTC
Hi
How do you get an approximate value for the number of hits? Curious
March 23, 2005 - 8:46 am UTC
using text there is a builtin function.
using SQL, there is the plan in v$sql_plan with the estimated cardinalities
David Prabhakar, June 27, 2005 - 10:17 am UTC
Hi Tom,
Ur site is extremely useful resource to all of us. I have one basic question,
How do I post my Database problem in ur site?
need help..pls let me know. I worked around ur site for posting questions..and i was not successful.
Thanks in advance.
June 27, 2005 - 10:27 am UTC
I am not familar with the UR site? You'd have to ask the person running the UR site for information.
Maybe they have a message on their home page, like mine does that says:
...
Sorry I have a large backlog right now, please ask a question later
See the "other resources" tab above and to the left for recommendations of places to go to get answers to questions!
.........
I want to submit my question or ask question to TOM
ashutosh swain, January 19, 2006 - 7:42 am UTC
Sir,
I want to submit my question or ask question to TOM
but i dont no the process of sending or asking question
please wtrite the same to me
January 19, 2006 - 1:21 pm UTC
see home page.
it either has "submit a question here"
or a message explaining why you cannot current submit a question here.
Page Impressions
Phil, February 20, 2006 - 9:15 am UTC
Tom
In Nov 2001 you had 20,000 impressions per day. Out of interest, what is the figure five years on?
Phil
February 20, 2006 - 12:43 pm UTC
</code>
http://asktom.oracle.com/Misc/birth-of-asktom.html <code>
<quote as of april 2005>
In the early days, I used to get 5,000 or so views a week. Today I get about 80,000 to 120,000 views a day from about 15,000 unique IP addresses per day. In the last month, well over 2,000,000 page views from over 100,000 unique IP addresses. It is hard to say how many people that 100,000 represents given that some people use more than one IP address (dhcp at work/home) and firewalls make large communities of users look like the same IP.
</quote>
to date:
Answered, Do not publish 18390
Answered, Publish 9478
(27,868 questions total)
70,662 reviews to date
48,224 followed up by me
here are the hits for the last couple of days:
TS CNT
------------------------ ----------
Monday January 30TH 80235
Tuesday January 31ST 81110
Wednesday February 01ST 76309
Thursday February 02ND 77284
Friday February 03RD 66457
Saturday February 04TH 21114
Sunday February 05TH 18169
Monday February 06TH 74583
Tuesday February 07TH 87211
Wednesday February 08TH 90493
Thursday February 09TH 87068
Friday February 10TH 72152
Saturday February 11TH 20825
Sunday February 12TH 30393
Monday February 13TH 88180
Tuesday February 14TH 88444
Wednesday February 15TH 130667
Thursday February 16TH 86113
Friday February 17TH 71727
Saturday February 18TH 43283
Sunday February 19TH 24869
Monday February 20TH 40543
22 rows selected.
about tracking page views
Ryan, February 20, 2006 - 1:31 pm UTC
1. I believe you said you built askom with htmldb? What are you doing to track page views and IP addresses? Is that through htmldb?
2. how much of the code is in htmldb and how much is in pl/sql?
3. how long did it take to do the initial development of asktom and how much maintenance/new development have you done since?
February 20, 2006 - 2:18 pm UTC
1) yes
2) We wrote a transactional API, htmldb did the rest (I have the API to modify things, to return result sets based on user inputs - htmldb does the rest, state management, pagination, wizards, etc)
3) the transactional API, maybe 2 days total. Maintenance - almost none, 5 minutes here or there every couple of weeks.
I am planning on version "3" of asktom (this being version 2), we have someone coming on board to do it hopefully pretty soon. They have a long list of things to do before this though :)
New features
Gabriel, February 20, 2006 - 3:12 pm UTC
Hello Tom,
Looking forward to version 3 of AskTom. Are you going to have a separate thread for the users to express what they would like to see in the new version or should we put our ideas in here?
One idea would be to use cookies so we don't have to fill in our email address every time we check our questions...
Thank you,
February 21, 2006 - 6:58 am UTC
you built asktom in a week?
Ryan, February 20, 2006 - 9:19 pm UTC
you said it took 2 days for the transactional layer. how long did the web tier take? I didn't realize that htmldb was that productive.
any plans to open source asktom? Your method for getting logging and metrics(when you put YES in the url) is very interesting. I have never seen it in that much detail anywhere else.
February 21, 2006 - 7:37 am UTC
Joel Kallman built it in HTMLDB (he is the lead guy on the development side, wrote much of it).
That took almost no time at all. Maybe an afternoon or two - once we had the transactional API, putting lipstick on it (the user interface) was pretty trivial. A tweak here and there over time, but nothing big.
Yes, htmldb can be that productive.
Next release of asktom (see link right above) I plan on making generally available.
Would you ever make the code behind askTom available?
Peter Tran, March 28, 2006 - 9:06 pm UTC
Hi Tom,
Would you consider making the core code behind askTom available? I know a lot of the technologies is already available, but you did put in a lot of work to make it look like it does today.
I realize I can try to reproduce all the same feature, look and feel using all the same technologies (Oracle, HTMLDB, etc.) but it would be easier to start with a great working example.
Thanks,
-Peter
March 29, 2006 - 7:03 am UTC
The APEX (htmldb) team is giving me a new application (they are building it with all of the new stuff we'd like to have)
That will be free to download and use once completed. It is "under construction" right now
Looking forward to it!
Peter Tran, March 29, 2006 - 12:50 pm UTC
Thanks for the response.
no-wrap feature
Alberto Dell'Era, March 29, 2006 - 1:47 pm UTC
> The APEX (htmldb) team is giving me a new application
> (they are building it with all of the new stuff we'd like
> to have)
I hope that "page width wider than the *max* output of dbms_xplan/autotrace" is on the feature list :)
March 29, 2006 - 2:22 pm UTC
yes, no more word wrap at 72 characters for new stuff.
and support for a proportional font for the non code stuff :)
Alberto Dell'Era, March 29, 2006 - 2:40 pm UTC
> and support for a proportional font for the non code stuff :)
Be careful with that - we have it on a forum I frequently contribute to - people *never* tag the code to switch to fixed-width ...
March 29, 2006 - 2:44 pm UTC
I'm the one that posts the most code :)
and I hope
&#931;&#969;&#954;&#961;&#940;&#964;&#951;&#962;, March 31, 2006 - 2:47 am UTC
you will be able to post in the letters
which first formed
Μαντείο
(Oracle)
32K limit in PL/SQL
VA, July 25, 2006 - 2:40 pm UTC
Given that most pages on Asktom are more than 32K in size, I was wondering how HTML DB (Apex) handles this?
Everything in Apex is template driven, content is replaced by substitution strings like #FOO# so I assume that some piece of code somewhere goes something like
l_html_sofar := replace(l_html_sofar,'#FOO#',l_this_piece);
But if the final result (the HTML page) is more than 32K in size, a single PL/SQL variable cannot hold it.
Thoughts? Thanks
July 25, 2006 - 2:45 pm UTC
I use clobs.
this page comes from three tables - I have a plsql block that pumps out each region.
I don't have the need for any "#var" stuff in the document body at all.
32K limit in PL/SQL
A reader, July 25, 2006 - 3:17 pm UTC
I have a plsql block that pumps out each region
Right, what if a single region (most likely the "Region 2:Reviews") is more than 32K in size? Storing them as CLOBs in the database tables is fine, but how are you outputting the entire >32K region content? Are you chopping the CLOB in 32K-size chunks and htp.p'ing the chunks?
Thanks
July 25, 2006 - 3:40 pm UTC
correct. htp.prn'ing them actually. Don't want to add spurious linefeeds.
32K limit in PL/SQL
A reader, July 25, 2006 - 4:27 pm UTC
Can you please show some sample code showing how you break up the CLOB into 32K chunks and output them?
Conversely, how can I build up a CLOB and an email containing more than 32K worth of data using the HTMLDB_MAIL.SEND API (using the p_body IN CLOB parameter)
Thanks
July 25, 2006 - 4:48 pm UTC
dbms_lob.substr it, psuedo'ish code
x := dbms_lob.getlength(lob);
for i in 1 .. trunc(x/32000)
loop
htp.prn( dbms_lob.substr( lob, 32000, 32000*(i-1)+1 );
end loop;
dbms_lob.writeAppend to "grow" a lob and append to it.
Hardware behind Asktom
Reader, August 17, 2006 - 11:39 pm UTC
I am unable to find the Hardware specs of your machine.
How many unique IP address do you have now (per day)?
If we wish to host an application with 400 users what kind of Hardware should we use?
Can we use a 4 CPU box ?
August 18, 2006 - 8:00 am UTC
my hardware won't be your hardware. I'm using a 2 or 3 year old dell box with 2 cpus.
You'd have to define what your concept of a "user" is. 400 users coming through a connection pool might mean 1 active session per second or 40, or 100, or whatever. That is what counts.
Hardware ?
Reader, August 18, 2006 - 11:27 am UTC
We do not plan to use Application Server .
So,how can we do connection pools --correct me if I am wrong or point me to a documentation ?
It is still possible to have 300 users (peak load) on one box ?
THANKS !
August 18, 2006 - 12:36 pm UTC
you can have way more than 300 users on a single box.
400 concurrent users may or may not need shared server (search the documentation for shared server, that is database connection pooling).
Probably not, not in 2006, 400 is a pretty small number given a conventional 4-8 cpu machine.
What you'll be most interested in determining (to figure out how many cpu's you need) is your true degree of concurrency - what would your average and peak values of:
select count(*) from v$session where status = 'ACTIVE';
be.
RSS down!
A reader, October 06, 2006 - 5:14 am UTC
errm:ORA-01653: unable to extend table ASK_TOM.WWC_ASK_RSS_LOG$ by 64 in tablespace ASK_TOM, sqlcode:-1653
Are your offer to host free htmldb proyect online is on?
A reader, October 10, 2006 - 8:24 am UTC
Are your offer to host free htmldb proyect online is on?
Hi Tom Sorry, I'm not sure if I'm right, but I remember once there was the possibility to hold on your server htmldb projects, but I think that was only for the test time.
Any way I ask it because I will like to know if this offer is on.
Thanks :)
October 10, 2006 - 12:01 pm UTC
you can host your application on htmldb.oracle.com (apex.oracle.com).
It is "for free" and "pretty darn available", but that is about it. It is for prototyping, you wouldn't host a production system there or anything like that.
A reader, October 10, 2006 - 6:50 pm UTC
Thanks Tom,
This was only one idea I am thinking to create an answer and question on spanish, something like asktom, but I really I don't have the answer I only translate them ;). I'm thinking to get an ace in the future :).
You answer means I can't do it, but there is no problem I can use mysql in my site, so there is no problem. I was only asking.
October 10, 2006 - 8:26 pm UTC
you can use XE as well, it costs less than mysql.
A reader, October 11, 2006 - 3:33 pm UTC
Thanks Tom, but you should say this to yahoo geocities, where I have the hosting .)
materialized views
Susan, March 03, 2007 - 1:20 pm UTC
Hi Tom
At the beginning you said you are using Materialized Views. I was wondering for what purpose?
I am working in a e-commerce website. We have some performance problems which I would like to solve.
The problem we have is that we have a main product table which is around 500000 rows. One of the most used query in our site access this table and usually needs to group 40000 rows to return 200 rows. It is similar as yours to inform users the quantity of products he is interested. The query is very simple but since it has to scan 40000 rows (and groupping) it becomes slow, takes around 2 seconds. I was told my our functional analysts that the website does not need to return real time information for this sort of information, like how many books of this publisher we have in stock and how many toys of that brand and so on. Only the detailed information of each product needs to be real time.
I am thinking about using MVs for my purpose, I wouyld refresh every 10 minutes so instead of running queries against 500000 tables I will probably be running against 10000.
My doubt is, what happens when I am refreshing the MVs, will there be any chance website users see zero counts? (when the MV data is being refreshed and deleted and inserted). I am planning using complete refreshes, I might be considering fast as well but at this moment only complete is considered.
Is thi a good idea?
Thanks
Susan
March 04, 2007 - 6:56 pm UTC
the purpose of materialized views for me is - for example - to show on the home page text such as:
...
In the last 4 weeks, I've taken 136 new questions, read 1,062 followups, and responded to 678 of the followups
....
given the home page is hit A LOT - I don't want to count to 1,062 each time you hit it, enter the materialized view.
in 10g - complete refreshes are done via "delete + insert" - so the data is always there.
In 9i, we can make the complete refresh be done via "delete + insert" (not truncate + insert /*+ append */) by using a refresh group.
if it dramatically reduces the work you need to perform and increases performance - it is probably a good idea :)
Source Code for this Site
Ashutosh, March 05, 2007 - 10:47 am UTC
------------------
Followup:
The APEX (htmldb) team is giving me a new application (they are building it with all of the new
stuff we'd like to have)
That will be free to download and use once completed. It is "under construction" right now
------------------
Hi Tom,
Now that your new AskTom website is up and running, have you posted its source code somewhere for a free download?
Thanks for your time.
March 05, 2007 - 2:15 pm UTC
How your paging works
A reader, March 09, 2007 - 1:40 pm UTC
Hi Tom
I am wondering when we first access asktom.oracle.com we see 15 questions, if we hit next then there is another 15 and so on.
I wonder, whenever we hit on next you rerun the query such as follows?
select
from (
select rownum rn, *
from questions
order by answer_date )
where rn >= 1
and rn <= 15
select
from (
select rownum rn, *
from questions
order by answer_date )
where rn >= 16
and rn <= 30
Thanks
March 12, 2007 - 4:02 pm UTC
Materialized View curiosity
Mark, March 12, 2007 - 6:52 pm UTC
I am curious about the AskTom materialized views you mentioned in reply to the March 3 followup question. I've always found the topic fascinating... These things are very powerful, but can be difficult to get just right.
1) Are your Mvs are ever stale?
2) How do they refresh? (i.e. fast, full every night...)
3) If you do a full refresh periodically, how long does it take? I would also like to see the underlying query, but if that's not something you want to release then I understand. :)
4) Do you query the MV directly or use query rewrite? Also, more generally speaking, when should you aim for query rewrite? (If that is answerable.) It can be difficult to achieve in my experience, but very neat once you do...
Thanks!
Mark
March 12, 2007 - 7:46 pm UTC
1) almost always
2) complete every 15 minutes
3) just a simple select status, count(*) from t group by status - type of query.
4) directly
but - that is based on my needs, someone else would answer:
1) never
2) on commit, incremental
3) don't know, never did it
4) query rewrite
and many other combinations. they are all valid - DEPENDING ON WHAT YOU NEED
I want to tell you about how many questions I've taken - simple count(*), don't really care if it is EXACT as of that instant, just "about"
A reader, May 14, 2007 - 2:05 pm UTC
I need to change the userid on the 'Your Questions' page as it is my alias. Is there a way to do this?
May 14, 2007 - 5:03 pm UTC
no, you can just plug in various names as needed, as you change your email over time
A reader, May 15, 2007 - 8:34 am UTC
On the Your Questions page, how does it remember the Email on the Submitted questions item? Does it store as a cookie or does it remember the IP address?
Its basically this page:
http://asktom.oracle.com/pls/asktom/f?p=100:60:0: Everytime I open IE, it has my old email id, I do change it during a session, close IE and then come back again, but it appears to retain the old email id?
May 16, 2007 - 10:10 am UTC
The cookie is only updated when you submit a question/review, not when you search on email id. So if you clear your cookies or submit one with your new email, it will stop defaulting to the old one.
Thanks,
Marco from the APEX team
email address caching!
Jay, May 15, 2007 - 9:26 am UTC
IE browser -
Tools --> options --> delete everything (cookies, files, history)
When you go back now, it probably won't be there anymore.
Firefox browser -
Tools --> options --> privacy --> uncheck 'remember what I enter in forms and the search bar' should do it.
wow! Uncheck is not a word?? ****confused.. confused...****
Thanks
MV
Reader, May 15, 2007 - 5:32 pm UTC
Can you clarify what you mean
<in 10g - complete refreshes are done via "delete + insert" - so the data is always there. >
Thanks.
May 16, 2007 - 10:05 am UTC
by default, a complete refresh is done via:
delete from materialized_view;
insert into materialized_view select ....;
commit;
the data is always apparently there to anyone that queries the materialized view
if you use atomic_refresh=>false, the refresh will be:
truncate materialized_view;
insert /*+ append */ into materialized_view select ....;
commit;
right after the truncate, and up till the commit - the materialized view will appear empty to everyone.
A reader, May 17, 2007 - 3:48 am UTC
Thanks for the help on the cookies stuff Marco and as you would you have guessed am submitting this to store my new Email.
Ravi
APEX Workspaces in AskTom
dieter oberkofler, May 30, 2007 - 1:52 am UTC
Hi Tom,
I hope not to be off topic but I was curious how AskTom uses the workspaces in APEX.
1) Is there one workspace for AskTom?
2) Is all in one schema or do you have the database stored away in one schema and the actual apex code in another schema?
Since I've started with APEX I was puzzled with the concept of workspaces and somehow "feel" that I should not create my APEX workspace in the actual schema where the database holding the "data" s. What would the best practice when adding several APEX applications to an existing production database?
May 30, 2007 - 12:05 pm UTC
Dieter,
Marco from the APEX team here, we helped develop the new AskTom application.
Yes, there is one workspace for asktom. Then there is one schema which contains the APIs and data tables.
The basic architecture for APEX in terms of schemas:
- APEX itself is always installed in its own schema (e.g. FLOWS_030000 for APEX 3.0). This is where your applications are stored as metadata in Oracle tables.
- Each APEX workspace is associated with one or more schemas. You can use the SQL Workshop to browse database objects or run ad hoc SQL in these schemas.
- Each APEX application resides in a workspace and has a "parsing schema", chosen from the list of schemas associated with the workspace. All SQL in the application is parsed as this user.
Associating a workspace or application with a schema doesn't create anything in that schema, and many APEX applications are associated with schemas holding production data. Unless you want fine-grained control over exactly what your application and developers can access, that is the way to go.
Try installing this:
http://www.oracle.com/technology/products/database/application_express/packaged_apps/packaged_apps.html#ASK on apex.oracle.com to see AskTom in action.
Thanks,
Marco
Always online complete-refresh-mview in Oracle 9?
Marco Coletti, June 01, 2007 - 11:06 am UTC
Tom:
In 9i, we can make the complete refresh be done via "delete + insert" (not truncate + insert /*+ append */) by using a refresh group.This sounds interesting to me, as I would like to make some mviews "always online".
Unfortunately I am observing the opposite, i.e. even when using a refresh group seems that the mview becomes empty at some point in time.
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit
I provide my test case here:
set serveroutput on size 9999
create table MVT1 as select 1 N from dual;
create materialized view MV1
build immediate
refresh complete on demand with rowid
as select N from MVT1
;
-- add mview MV1 to refresh group MV
-- DBMS_REFRESH.REFRESH executes in another session spawned by the job scheduler
begin
DBMS_REFRESH.MAKE(name => 'MV', list => 'MV1', next_date => sysdate, interval => NULL);
commit;
end;
/
declare
l_enne number;
l_countdown pls_integer := 999999;
begin
-- loops as far as MV1 is not empty or l_countdown > 0
-- if MV1 becomes empty then ORA-01403: no data found
loop
select N into l_enne from MV1 where rownum = 1;
l_countdown := l_countdown - 1;
exit when l_countdown <= 0;
end loop;
DBMS_OUTPUT.PUT_LINE('finish');
end;
/
begin
DBMS_REFRESH.DESTROY(name => 'MV');
commit;
end;
/
drop materialized view MV1;
drop table MVT1;
June 01, 2007 - 1:17 pm UTC
i don't see a refresh happening?
Always online complete-refresh-mview in Oracle 9?
Marco Coletti, June 01, 2007 - 11:25 am UTC
Well, It stumbled my head that perhaps if DBMS_REFRESH.REFRESH notices that there is more than one mview in the refresh group then it needs to use delete instead of truncate in order to maintain read consistency in between.
And the modified test case confirms it:
one-mview refresh group => truncate
two-mview refresh group => delete
set serveroutput on size 9999
create table MVT1 as select 1 N from dual;
create materialized view MV1
build immediate
refresh complete on demand with rowid
as select N from MVT1
;
create materialized view MV2
build immediate
refresh complete on demand with rowid
as select N from MVT1
;
-- add mview MV1 to refresh group MV
-- DBMS_REFRESH.REFRESH executes in another session spawned by the job scheduler
begin
DBMS_REFRESH.MAKE(name => 'MV', list => 'MV1,MV2', next_date => sysdate, interval => NULL);
commit;
end;
/
declare
l_enne number;
l_countdown pls_integer := 999999;
begin
-- loops as far as MV1 is not empty or l_countdown > 0
-- if MV1 becomes empty then ORA-01403: no data found
loop
select N into l_enne from MV1 where rownum = 1;
l_countdown := l_countdown - 1;
exit when l_countdown <= 0;
end loop;
DBMS_OUTPUT.PUT_LINE('finish');
end;
/
begin
DBMS_REFRESH.DESTROY(name => 'MV');
commit;
end;
/
drop materialized view MV2;
drop materialized view MV1;
drop table MVT1;
June 01, 2007 - 1:19 pm UTC
ahh, sorry - yes, I've demonstrated this elsewhere on this site using "dual" as another MV base table.
search text question
James Su, June 04, 2007 - 5:24 pm UTC
hi Tom, when user inputs search text at your home page, how do you convert it into a parameter that can be used in function CONTAINS?
For example,
cat dog ---> cat and dog
can you post your source code here? Thanks a lot.
June 07, 2007 - 10:59 am UTC
James,
Marco from the APEX team here. You can download a packaged version of the AskTom application here:
http://www.oracle.com/technology/products/database/application_express/packaged_apps/packaged_apps.html#ASK Here is the current standalone function:
function parse_search_string( p_search_str in varchar2 ) return varchar2
is
l_temp_value varchar2(32767);
l_temp_value2 varchar2(32767);
l_return_value varchar2(32767) := NULL;
l_start_token number := 1;
l_in_token boolean := FALSE;
l_num_tokens number := 0;
l_quotes number;
l_phrases dbms_sql.varchar2s;
n number;
begin
if nvl(length(p_search_str),0) = 0 then
return NULL;
end if;
--
l_quotes := length(p_search_str) - length(replace(p_search_str,'"', ''));
if ( l_quotes > 0 and mod(l_quotes,2) = 0 )
then
l_temp_value2 := lower(p_search_str);
for i in 1 .. l_quotes/2
loop
n := instr( l_temp_value2, '"' );
l_temp_value := l_temp_value || substr( l_temp_value2, 1, n-1 );
l_temp_value2 := substr( l_temp_value2, n+1 );
n := instr( l_temp_value2, '"' );
l_phrases(i) := substr( l_temp_value2, 1, n-1 );
l_temp_value2 := substr( l_temp_value2, n+1 );
end loop;
l_temp_value := l_temp_value || l_temp_value2;
else
l_temp_value := lower(p_search_str);
end if;
--
l_temp_value := trim(replace( l_temp_value, '{', ' '));
l_temp_value := trim(replace( l_temp_value, '}', ' '));
l_temp_value := trim(replace( l_temp_value, ':', ' '));
l_temp_value := trim(replace( l_temp_value, ';', ' '));
l_temp_value := trim(replace( l_temp_value, '"', ' '));
l_temp_value := trim(replace( l_temp_value, ':'',', ' '));
l_temp_value := trim(replace( l_temp_value, '(', ' '));
l_temp_value := trim(replace( l_temp_value, ')', ' '));
l_temp_value := trim(replace( l_temp_value, '!', ' '));
l_temp_value := trim(replace( l_temp_value, '&', ' '));
l_temp_value := trim(replace( l_temp_value, '+', ' '));
l_temp_value := trim(replace( l_temp_value, '\', ' '));
l_temp_value := trim(replace( l_temp_value, '-', ' '));
l_temp_value := trim(replace( l_temp_value, ',', ' '));
l_temp_value := trim(replace( l_temp_value, ' and ', ' ' ));
l_temp_value := trim(replace( l_temp_value, ' or ', ' ' ));
--
if length(l_temp_value) > 0 then
l_in_token := TRUE;
end if;
--
for i in 1..nvl(length(l_temp_value),0) loop
if substr(l_temp_value,i,1) = ' ' then
if l_in_token = TRUE then
l_return_value := l_return_value || '{' ||
substr(l_temp_value,l_start_token, i-l_start_token) || '}' || ' and ';
l_in_token := FALSE;
l_num_tokens := l_num_tokens + 1;
end if;
elsif l_in_token = FALSE then
l_in_token := TRUE;
l_start_token := i;
end if;
exit when l_num_tokens > 30;
end loop;
--
if l_in_token = TRUE then
l_return_value := l_return_value || '{' || substr(l_temp_value,l_start_token) || '} and ';
end if;
--
for i in 1 .. nvl(l_phrases.count,0)
loop
if ( l_phrases(i) is not null )
then
l_return_value := l_return_value || ' {' || l_phrases(i) || '} and ';
end if;
end loop;
return trim( substr( l_return_value, 1, length(l_return_value)-4 ) );
end parse_search_string;
Do it Yourself AskTOm
MK, May 07, 2008 - 10:51 am UTC
Hi Tom,
Was wondering about the AskTom s/w that you allow for free download. Does it require an Oracle license if you use it as you do... which is basically like a forum which is free of cost? How do you moderate the influx of messages/questions that come in?
As an ex-employee of Oracle UK I would love to start something similar along the lines of AskTom to support the Oracle Community better. Would you like to come along and visit the forum and post on it from time to time when you are free (not that you are ever free)? Or is that too much to ask now? :)
Cheers,
MK
May 08, 2008 - 3:08 am UTC
you can use APEX with Oracle XE and the "askus" software. Oracle XE is "free" (read the license for documented limitations), the askus application is downloadable as a sample application and may be freely reused.
Do it Yourself AskTOm
MK, May 10, 2008 - 2:13 pm UTC
Hi Tom,
I do use Oracle XE locally on my desktop to test SQL queries, play with schemas etc. I think you directed me to XE as I think you have in mind the fact that it is really EASY to install and maintain plus I don't have to be a DBA? (would make my life a lot easier)...though I am planning on hosting the application on a quad-core CPU on a 64-bit LINUX so if I remember correctly XE works on a single CPU only and 32-bit only. Was wondering how I would be able to scale the solution if there are more concurrent sessions that take place and how XE would handle it. But I also know that if I went with XE you would actively support XE on your forum... which would be awesome and can only help!!
I was actually thinking of porting my existing 10g DB (on a VM) to Oracle VM and give that a go. Unless I can get Oracle 10g "pre-installed" on Oracle VM that would be even better. Oracle VM sounds amazing so I want to learn more about the VM too in the process. Plus I think if I wanted to have multiple versions of Oracle I could install them on a VM each. When someone asks you Oracle Version specific questions (ex for Version 8) ... do you have an Oracle 8 installation that you test and give examples against?
What do you suggest?
Or do you think its a better path to go down the APEX/XE combination and then if need be port to an oracle std edition? Still not sure if using it for dev and testing and supporting a forum would be considered commercial use and hence require a license?
Finally when you mentioned the "AskUs" s/w ... did you mean "Ask the Expert"? Or is there another one out there that I don't know about?
http://www.oracle.com/technology/products/database/application_express/packaged_apps/packaged_apps.html#ASK Cheers,
MK
ps: No response on the invitation? :)
May 12, 2008 - 1:15 pm UTC
AskUs, Ask the expert - what ever they decided to call it - it was way back when "askus".
You'll have to figure out what configuration you want to go with - XE would be able to do 4 or 5 hits a second constantly on a single cpu (you can install on a multi-cpu machine) I hardly use my two cpu's.
pagination
Vikas sharma, July 22, 2009 - 3:17 pm UTC
Hi tom,
In my review posted long back "Search Results of Ask Tom now February 23, 2003". We talked about the count(*) from pagination. YOu said a bug is logged and count(*) will be removed.
It is removed from the normal search but not from "Your questions" page. I looked from my question and it show me a exact number of questions.
Thanks
Vikas sharma