Free Oracle training

Discussion in 'Tech Talk' started by StoveBolts, Aug 8, 2017.

  1. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
  2. Knotical

    Knotical Staff Member Moderator

    Joined:
    Sep 11, 2012
    Messages:
    5,292
    Location:
    Armpit of California
    Christian:
    Yes
    This is awesome. I will definitely be checking this out. Thanks, StoveBolts !

    I have actually thought about getting some additional training on this. I have a pretty good base of knowledge with Microsoft Access, but I wanted to get more into SQL databases.
     
  3. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    You can download a free copy of Oracle with their test schema Scott that you can use to hone your skills.

    I would recommend downloading Oracle Developer and then connect to the Oracle database.

    Let me know if you need help
     
  4. Knotical

    Knotical Staff Member Moderator

    Joined:
    Sep 11, 2012
    Messages:
    5,292
    Location:
    Armpit of California
    Christian:
    Yes
    I have already gone through the first two lessons, and have only found them to be a video followed by a few quiz questions. Does this actually get into some hands-on development?
     
  5. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    There isn't any hands on, but it will give you a foundational understanding that you won't get from an all hands on site.

    Here is an example of what I was able to apply after the course. I just wrote this in about 15 minutes.

    -- Each level is seperated by a pipe. Comma seperates sublevels.
    -- Parse the record into it's base level, and sublevels showing values.
    with STR as (
    select '1A,222B,3 33C|44A,5B|66A,77777 B,888C,9999D,10E' as STRING from dual)
    ,
    -- parse String from STR into rows using pipe as delimeter (|)
    SubStr1 as (
    Select LEVEL as BASE_LVL, trim(regexp_substr(STRING, '[^|]+', 1, LEVEL)) LVL1
    from STR
    Connect by instr(STRING, '|', 1, LEVEL - 1) > 0
    )
    ,
    -- parse LVL1 from SubStr1 into rows using comma as delimeter (,)
    SubStr2 as (
    Select distinct BASE_LVL, LEVEL as SUB_LEVEL, trim(regexp_substr(LVL1, '[^,]+', 1, LEVEL)) VALUE -- Must use distinct
    from SubStr1
    Connect by instr(LVL1, ',', 1, LEVEL - 1) > 0
    ) -- End WITH

    select BASE_LVL,SUB_LEVEL, VALUE from SubStr2
    order by BASE_LVL, SUB_LEVEL;


    Once you complete the course, you can play in Developer with the populated database Oracles gives you for training.

    Sqlzoo.net is probably the best hands on course out there for beginners.
     
  6. markathome

    markathome Member

    Joined:
    Jan 30, 2016
    Messages:
    395
    Location:
    Alaska
    I really need to get the Brenton Septuagint Translation into a mysql database for this project..
    The eng-Brenton_osis is in XML format.
    The software to convert XML to Mysql cost $699.
    Any other ideals as to how to get it converted?

    Thanks
     
    Last edited: Aug 9, 2017
  7. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    Do you know Java? There is a class that will parse xml with a few lines of code.

    If not, xml is structured, so you could parse using Regex in Notepad++ and using the find replace option, you could generate your insert statements. It could get a little tricky keeping chapters and verses etc depending on how they structured the xml.

    Do you know the table structure of your database?
    Assuming your storing each book in the same table, I'm thinking your attributes would be:
    Book
    Chapter
    Verse
    Passage.

    Insert into book (book, chapter, verse, passage) values ('Obadiah', '1', '1', 'The vision of........'

    I'll PM you with my email. Send me Obadiah and well see how doable this is.
     
  8. markathome

    markathome Member

    Joined:
    Jan 30, 2016
    Messages:
    395
    Location:
    Alaska
    I know php & used regex to manually import the byzantinnt text & got it to a mysql db but made a boo boo with one chapter/book in the OT so I will have to redo it.

    The software created 2 tables but without purchasing the stuff it won't do all the inserts, only 50% of the umport.
    CREATE TABLE Ndiv (
    N_ID char(3),
    N_IDosisText char(1),
    N_IDdiv char(3),
    Ncanonical char(5),
    Nchapter char(1),
    Ndiv text,
    Nlb char(1),
    Nlg char(1),
    NosisID char(6),
    Np text,
    Ntitle char(140),
    Ntype char(12)
    );

    INSERT INTO Ndiv VALUES
    ('2',NULL,'1','true',NULL,' ',NULL,NULL,'Gen',NULL,'GENESIS','book');

    CREATE TABLE Ntitle (
    N_ID char(3),
    N_IDwork char(1),
    N_IDdiv char(3),
    Ncanonical char(4),
    Nnote char(66),
    Nshort char(18),
    Ntitle char(185),
    NtransChange char(20),
    Ntype char(12)
    );

    INSERT INTO Ntitle VALUES
    ('2',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL);
     
    Last edited: Aug 9, 2017
    StoveBolts likes this.
  9. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    After looking at the xml, I may be able to patch this.
    Can you run this and give me the results.

    Select *
    From Ndiv
    Where rownum <= 2;

    Same with Ntitle.

    Thanks
     
    Last edited: Aug 9, 2017
  10. markathome

    markathome Member

    Joined:
    Jan 30, 2016
    Messages:
    395
    Location:
    Alaska
    INSERT INTO Ndiv VALUES
    ('4',NULL,'1','true',NULL,' ',NULL,NULL,'Exod',NULL,'EXODUS','book');

    it didn't produce any more records for the INSERT INTO Ntitle
     
    StoveBolts likes this.
  11. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    Thanks.
    I'm not having much luck parsing the xml with the standard Java xml document parser. But this is my first time using that library.

    I really don't want to try and Regex the document lol.
     
  12. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    Figured out how to get the context ( biblical text) but not book, chapter, verse and notes.

    I'll pick at it again tomorrow. Once I can get all the pieces, it will be super easy to push out to an sql script that you can use to insert.
     
  13. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    markathome
    Good news Mark. I'm using a Java xml SAX parser and I'm having great luck getting the elements, attributes and context of the xml!
    I figure I'm 3/4 the way through the learning curve, 2/3 of the way to extracting all the data and of course I haven't begun creating the objects to store everything in memory so I can format and write it out to a script for you.

    In all I'm happy with the progress I'm making. I should have some time next week. I can post my code if you want to play with it, or if another Java developer wants to jump in and spare this amature lol!
     
  14. markathome

    markathome Member

    Joined:
    Jan 30, 2016
    Messages:
    395
    Location:
    Alaska
    The text of The New Testament in the Original Greek: Byzantine Textform 2005,
    edited by Maurice A. Robinson and William G. Pierpont
    Pre-formatted with Strong's numbers and parsing data
    BP05FNL.ZIP 17 July 07
    http://www.byztxt.com/download/BP05FNL.ZIP
    which like everything on internet disappears after a while
    the BP05FNL.ZIP file looks like this
    1:1 paulov 3972 {N-NSM} klhtov 2822 {A-NSM} apostolov 652
    {N-NSM} ihsou 2424 {N-GSM} cristou 5547 {N-GSM} dia 1223 {PREP}
    yelhmatov 2307 {N-GSN} yeou 2316 {N-GSM} kai 2532 {CONJ} swsyenhv
    4988 {N-NSM} o 3588 {T-NSM} adelfov 80 {N-NSM}
    which is easy to parse, I put the file link here BP05FNL.ZIP
    since it has disappeared off the net except for Github.
    The text and its analysis are in the Public Domain.
    I love the Public Domain!
     
    StoveBolts likes this.
  15. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    How are you doing? Did you finish the course or did you just go straight for sqlzoo?
     
  16. Knotical

    Knotical Staff Member Moderator

    Joined:
    Sep 11, 2012
    Messages:
    5,292
    Location:
    Armpit of California
    Christian:
    Yes
    Haven't really taken the time to get back to it. Though, I will need to get some more study in due to an Access database I am wanting to optimize at work. Currently, I am running an update every day that pulls from multiple external tables and dumps data into temporary local tables that I could possibly get around by either building some queries in studio management or some pass-through queries in Access.
     
    StoveBolts likes this.
  17. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    I'm going to assume your running reports out of Access?

    Have you considered Oracle Developer? You can connect directly to an Oracle Database on your network by creating a local tnsnames.ora file with the proper tns entry and pointing Developer to it, or you can enter the connect information that your using for your odbc connection directly into Developer.

    With a quick Google search, you can learn how to use Developer to connect to other types of databases including mysql.

    Once your connected, you can write your own custom queries and the results are displayed in a window below.

    If you want everything from a table, it's as easy as:
    Select * from schema.table_name;
    Want to filter on the color red where color is a column? Simply add:
    Where color = 'red';

    One of the cool things about this is that you can right click on the results and extract to Excell and I'm sure you see the potential value of that.

    I work in Developer daily and I'm happy to share.
     
    Last edited: Aug 15, 2017 at 11:13 AM
  18. Knotical

    Knotical Staff Member Moderator

    Joined:
    Sep 11, 2012
    Messages:
    5,292
    Location:
    Armpit of California
    Christian:
    Yes
    Would I really need the developer tool if I already have access to the Management Studio? I am not really looking to build new tables outside of what I have in the existing Access database. Which is also used as a front end by a number of users.
     
  19. StoveBolts

    StoveBolts Member Member

    Joined:
    Nov 4, 2004
    Messages:
    11,307
    Location:
    Michigan
    Christian:
    Yes
    Ahhh, so I take it your not using Oracle to load your Access temp tables. I assumed your source was Oracle.

    I don't work in sql server via Management Studio very often, so I don't know it as well as I do Oracle Developer, so I'm partial to Developer.

    Anyway, I don't know enough about what your doing etc to give any direction. But I'm open to share what I know if you need anything I can help with.
     

Share This Page