Where do I begin when it comes to SQL and Oracle?

New2SQLWoah

Forgive me for having the most basic question, however, I am brand new to using SQL.

I am curious how to find out which tables are available to query in my system for the HCM product. Is there a query I can enter in BI that will give me a list of all the tables that we are using currently?

From there, I would also be interested in knowing the names of all the columns within these tables so that I have that information readily availably for querying.

Lastly, when I am transacting within 'Person Management', is there a way to determine what the name of a field would be if I wanted to report on it? For instance, I enter gender on a new employee and know that in BI that field would be called 'Gender_Employee' (making that up).

HolgerH

Hello,

from your question I assume, you are working with Fusion Oracle HCM Cloud. So you may not have a direct SQL database access (clouds let you stand in the rain). However, you can execute SQL select statements in OTBI tool or OBIEE - depending on what your company has subscribed to.Frank Kulash gave you a link to a specific BI forum handlign the operations of this tool.

Rather than asking the Oracle database for all its tables/views (quite a lot), you better refer to the product specific documentation. You may want to start with the place Assignments are stored in  https://docs.oracle.com/en/cloud/saas/global-human-resources/19a/oedmh/PER_ASSIGNMENTS_F-view.html . If you don't know what assignments are, you may need to check the HCM documentation. But if you do, try to join it upon PERSON_ID with  https://docs.oracle.com/en/cloud/saas/global-human-resources/19a/oedmh/PER_ALL_PEOPLE_F_V-view.html  you will get the PersonNumber - (the User Key for a Person) which you can find in the GUI. I am deliberately referring to the views as their documentation show you anyway the underlying tables and give aou an idea of useful joins.

If you have further questions, please use consider the forum space

Have fun.

Regards

Holger

John Thorton

4163174 wrote:Forgive me for having the most basic question, however, I am brand new to using SQL.I am curious how to find out which tables are available to query in my system for the HCM product. Is there a query I can enter in BI that will give me a list of all the tables that we are using currently? From there, I would also be interested in knowing the names of all the columns within these tables so that I have that information readily availably for querying.Lastly, when I am transacting within 'Person Management', is there a way to determine what the name of a field would be if I wanted to report on it? For instance, I enter gender on a new employee and know that in BI that field would be called 'Gender_Employee' (making that up).

Please click on the URL below to see where you can find answers to your questions

https://lmgtfy.com/?q=oracle+data+dictionary+tables

If using sqlplus or sqldeveloper you can do as below to see the columns & datatypes within a table

DESC <table_name>

New2SQLWoah

Thank you - next silly question. Can I perform this query in BI? Or do I have to have sqldeveloper to do this?

John Thorton

I don't do BI.

What stops you from trying & see what results instead of waiting for response from here?

Either it will work or it will throw error; which leaves you no worse than before asking.

Frank Kulash

Hi,

For BI questions, you'll get better replies faster if you post them in the

Forum.  This is the SQL and PL/SQL Forum, for question specifically about those languages.

Gianni Ceresa

New2SQLWoah wrote:Thank you - next silly question. Can I perform this query in BI? 

There isn't a thing called "BI".
You will need to be more precise about the products you talk about as "BI" is Excel for a large majority of people ....

New2SQLWoah

Well my original question was about SQL – I just happened to have an offshoot question about where I could perform the SQL query he was suggesting.

Thanks!

Elyse

New2SQLWoah

Oracle Business Intelligence – again, this was an offshoot question I was asking to the original person who answered me and in the context of my question, no one would think I was talking about Excel.

EdStevens

New2SQLWoah wrote:Oracle Business Intelligence – again, this was an offshoot question I was asking to the original person who answered me and in the context of my question, no one would think I was talking about Excel.Thanks!Elyse

Wouldn't they?

Many people use excel as their "BI" tool and so would think of Excel when BI is mentioned.  And since you had NOT specified your BI tool, it would be perfectly natural for some to assume it was Excel. The whole point of the 'excel' remark was to get you to understand why you need to name the tools you are using.

Gianni Ceresa

Don't get me wrong, not saying you were asking for Excel, but you would be surprised by own many actually do!

If you have an Oracle database just go with SQL Developer: powerful and free, not much else to ask for.

For OBIEE it's more challenging as the tool works based on models, not on SQL queries you would write. OBIEE expects you to model things and the tool generate SQL dynamically based on what analysis you perform. In 12c or OAC/OAS you could enter a query in the Data Visualization interface, create a dataset and use it to build a visualization project, or also use a direct database request. But all these ways would require you to perform many more steps to just see the results of your queries (as the tools aren't written with that as main target).

SQL Developer will be a lot better: you will not lose time and get lost into learning the tool, you will be able to fully focus on the SQL.

New2SQLWoah

Thank you!