ad hoc queries in oracle 11i

avoorheis
avoorheis used Ask the Experts™
on
I'm in a new group, so, instead of running queries in my old familiar, small, sql server database, I'll be using Oracle 11i. I can get help from co-workers, but, I'd like to be able to start studying and learn some on my own. I've found some ERDs online, some on the TRD and other sites, so, that will help.

So, I guess what I'm looking for are tips for learning, specifically, ad hoc queries, on the main tables like install base, customers, contacts, service contracts. Maybe books, online sites or what ever might help a noob.

thanks
alan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
if you're looking to learn oracle query syntax, much of what you know for sql server will still apply.

The Oracle SQL Reference is probably the best place to start

http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm

and for 11i apps the documentation for it can be found here...
http://docs.oracle.com/cd/A97501_01/html/erpset.html
Most Valuable Expert 2011
Top Expert 2012

Commented:
the question is pretty wide open, if you can narrow down the criteria I might be able to give more specific advice.
David VanZandtOracle Database Administrator III

Commented:
Remember that apps 11i is built upon rdbms 9i, so don't get confused with the newer versions of SQL syntax.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
I guess basically in what tables will I find what I'm looking for. An example might be to find order created yesterday and include company address, order number, install base items on the order and things like that. My problem is that there are so many tables, I could spend hours trying to figure out which tables contain the fields I need.
David VanZandtOracle Database Administrator III

Commented:
Take a look at diagram 3-1 in the documentation at http://docs.oracle.com/cd/E18727_01/doc.121/e12841/T120505T120510.htm.  You'll see that the APPS schema is probably the one you should use if you can.  After all, the eBS suite installs en masse, so that there are any number of schemas you may ignore.  From memory, you'll find orders under OE.

Author

Commented:
thanks dvz.
So, you said find orders under OE, but, what if I need some other field. Do I just have to search for it either in the hundreds of tables or is there an easier way?
Oracle Database Administrator III
Commented:
To search the data dictionary in general, query the sys.all_tab_cols view -- for example, if I want to check for a consistent datatype and length for all occurrences of CUSTOMER#.  I recall there is some view in APPS that provides which schemas are active (turned on), but I can't check that tonight.  If I didn't answer your question, please clarify.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial