02 of 13 - Oracle APEX 5 - Run SQL scripts to create tables, sequences, triggers and insert data

This video in the APEX 5 tutorial series show how to use SQL Scripts (part of SQL Workshop) to run scripts that create tables, sequences (to generate unique values for primary keys), triggers and insert data (INSERT INTO statements).

SQL scripts for this series are available to download at:

http://web.nmsu.edu/~jkreie/oracle/Da... or by request at

http://db.kreie.net

---

Closed Caption:

hi this is one video in a series of
videos that will help show you many of
the features of oracle apex application
express version 5 and we will use apex
to build a prototype application based
on a database in oracle x e if you want
to work along with the videos you can
get the scripts that will enable you to
do that from the URLs shown here the
first URL is case sensitive so make sure
you use a capital letter when it shows a
capital letter from there you can
download the script directly if you'd
rather have the scripts emailed you can
go to the second link and use the
contact page to request that the scripts
be emailed to you i'm getting ready to
create some objects and table sequences
and other things in the database right
now i'm logged in is ash weenie i know
that because i can come up here and look
at the account logged in as Sweeney one
of the designers of this application
let's take a quick look under SQL
workshop object browser and see what
exists right now before i run any
scripts what we have are some tables
that were created when apex created the
workspace and we're going to ignore
those I could look at other categories
of objects such as sequences and it was
one created by the administrative
function when it created the the
workspace and I have things like
triggers when we come back we'll see
things that have been created when we
run the scripts so i'm going to go back
to SQL workshop that pic the SQL scripts
feature and
this i will upload one of the scripts so
click the upload and do choose file and
go into a folder with scripts now the
names will probably be saying that if
they're different look for the one that
says create so i'll select that and
upload and the script has been uploaded
over here to the right I have a run
option and i want to click that and I
now click run now not the running
background so i wanted to run
immediately and it's very important that
you click to the far right under view
results so when you click that scroll
down and it shows 16 statements process
successful and most importantly 0 with
errors so now if i go back to SQL
workshop at the high level and go into
object browser and looking tables you
now see things that weren't there before
attendances evaluations about items and
so on and so forth
let's take just a second and look at the
data model that these tables are based
on so the scenario for the tutorial is
that there's a teacher who wants to do a
better job of keeping track of
information about teens projects are
assigned to teams and then students are
assigned to teams and students after
working together for a while on the team
can do evaluations of each other so
students as an evaluator or and evaluate
II can complete an evaluation they would
score on about items and about items
would be things like listens well worked
well with the others attends meetings on
time prompting meetings that kind of
thing
technical skills so the student can do
an evaluation on another student on the
team and enter the score which is
represented here
students can also attend professional
development workshops and that's
represented by these entities here so we
have several one-to-many relationships
so interestingly what we have here is a
situation where we have two entities
into different relationships between
those entities one rep relationship
represents the evaluate or in one
relationship represents the evaluate e
but these are all one-to-many
relationships and that's the basis for
the tables that you just built with your
scripts and back in our capex
development environment and object
browser i'm going to change the view to
sequences and you'll see that sequences
have been created for the tables a
sequence is a special table that
generates a unique identifier for the
particular table that its assigned to
work with so we have a sequence to give
us the attendance ID we have a sequence
to give us a team ID and these will be
unique
we need in order to get those values and
put them into a record any time we add a
team or add a student we are going to
need triggers and what we see here are
not the triggers for our application so
as we need is going to go back to the
SQL scripts and up
load another script and that would be
for the triggers and then
that no this one here and run
now and then check
the results
aight statements all successful no
errors so if we go back to object
browser and look under triggers will see
that we now have those triggers so those
triggers are little bits of code that
are going to when you try to add a
record to one of these tables the
trigger will then go to the sequence
grab the current value which then gets
incremented and the trigger will insert
that value as the primary key value of
for that row that's being added you can
actually see the code by clicking the
code tab and seeing the pl/sql code that
would do what I just described will get
a value from the sequence and insert it
into the new primary key field for the
record that's being added to the table
we're going to go back now to SQL
scripts and upload and add that so we'll
upload another script and will end
that'll be the insert script now it was
very important that i have the sequence
in the triggers created before i run
this insert into script because as I was
just saying the unique ID value is going
to be inserted automatically with a
combination of the sequence table and
the trigger code so i'll go ahead and
upload and click run run now and you
salt and scroll down no errors so let me
just mention that if something went
wrong if you did things out of sequence
out of the incorrect order there is a
drop script that will drop everything
that's been created and so you could
start this over if you needed to because
if you're getting any errors you need to
make sure that those scripts run without
errors so don't proceed without having
everything run successfully without
errors we can see that we have data in
the tables by going back to SQL workshop
an object browser and then selecting the
table which I didn't do before to show
you that it was empty but now that I
select one I can select the data tab and
you'll see that i already have data in
that table so that's what that last
script did for us in the next video
we'll look at how to create a table
without having a script to manually
create a table in apex