What's new
  • Do not use Chrome Incognito when registering as it freezes the registration page.
  • Guest, Join Papa Zoom today for some uplifting biblical encouragement! --> Daily Verses

Free Oracle training

Knotical

Shepherd of the Knotical kid-farm
Member
Joined
Sep 11, 2012
Messages
5,889
Gender
Male
Christian
Yes
#2
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.
 

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#3
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
 

Knotical

Shepherd of the Knotical kid-farm
Member
Joined
Sep 11, 2012
Messages
5,889
Gender
Male
Christian
Yes
#4
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?
 

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#5
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.
 

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#7
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.
 
Joined
Jan 30, 2016
Messages
432
Gender
Male
#8
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:

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#9
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:
Joined
Jan 30, 2016
Messages
432
Gender
Male
#10
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

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#11
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.
 

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#12
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.
 

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#13
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!
 
Joined
Jan 30, 2016
Messages
432
Gender
Male
#14
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

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#15
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?
How are you doing? Did you finish the course or did you just go straight for sqlzoo?
 

Knotical

Shepherd of the Knotical kid-farm
Member
Joined
Sep 11, 2012
Messages
5,889
Gender
Male
Christian
Yes
#16
How are you doing? Did you finish the course or did you just go straight for sqlzoo?
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

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#17
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.
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:

Knotical

Shepherd of the Knotical kid-farm
Member
Joined
Sep 11, 2012
Messages
5,889
Gender
Male
Christian
Yes
#18
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.
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.
 

StoveBolts

Staff member
Administrator
Joined
Nov 4, 2004
Messages
13,909
Gender
Male
#19
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.
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.
 

Site Goal

Total amount
$295.00
Goal
$5,020.00