Forum Stats

  • 3,838,987 Users
  • 2,262,432 Discussions
  • 7,900,829 Comments

Discussions

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

New2SQLWoah
New2SQLWoah Member Posts: 7 Green Ribbon
edited Jan 17, 2020 9:06AM in SQL & PL/SQL

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

Tagged:
jaramillChristian Berg-0racleJohn Thorton

Best Answer

  • HolgerH
    HolgerH Member Posts: 125 Bronze Badge
    edited Jan 17, 2020 5:29AM Answer ✓

    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

    New2SQLWoah
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 15, 2020 4:26PM
    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
  • New2SQLWoah
    New2SQLWoah Member Posts: 7 Green Ribbon
    edited Jan 15, 2020 4:41PM

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

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 15, 2020 4:54PM

    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.

    jaramillChristian Berg-0racle
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond
    edited Jan 15, 2020 7:37PM

    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
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,640 Blue Diamond
    edited Jan 16, 2020 5:01AM
    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 ....

    Christian Berg-0racle
  • New2SQLWoah
    New2SQLWoah Member Posts: 7 Green Ribbon
    edited Jan 16, 2020 9:17AM

    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
    New2SQLWoah Member Posts: 7 Green Ribbon
    edited Jan 16, 2020 3:22PM

    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
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 16, 2020 9:24AM
    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
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,640 Blue Diamond
    edited Jan 16, 2020 10:57AM

    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.

    John ThortonNew2SQLWoah
  • New2SQLWoah
    New2SQLWoah Member Posts: 7 Green Ribbon
    edited Jan 16, 2020 10:57AM