Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Tips for making packages, procedures, other schema objects in Oracle Database

Posted on 2013-10-25
8
Medium Priority
?
635 Views
Last Modified: 2013-10-27
I am making a project for my portfolio using PL/SQL.

I am using Virtualbox with Oracle Developer Days installed so the schema I have is this:The ERD of what tables I have to work with
Basically I need to make as many different things using PL/SQL as possible.
Please give me tons and tons of tips for different packages, procedures, functions, etc that I could make for this db to show I have PL/SQL knowledge.

I'm not asking for code. I'm asking for ideas of what would be good for me to make from experienced DB people. Thanks
0
Comment
Question by:Mark_Co
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39601092
Off the top of my head:
Procedures to do things like insert a new employee, job, etc...
Procedures to update employees, jobs, etc...

The applications we have in our shop don't have direct access to the database tables.  Everything they do is performed through stored procedures.

Then there is different types of reports.

Procedures that return a ref_cursor, list of values, XML for various types of reports.

For example: All employees, All jobs, an employees job history, etc...
0
 

Author Comment

by:Mark_Co
ID: 39601166
right,I should have said stored procedures.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601172
>>right,I should have said stored procedures.

What you posted implied stored procedures...  I'm not following?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 39601253
The possibilities are practically endless. As mentioned, you would most likely want procedures for insert, update, and deletes for all of the tables (keeping in mind things like deleting a location would require changes to the departments table and may require changes to the country and/or region tables as well). You would also likely want procedures that do validations, verifications, and diagnostic analysis (e.g find a job with no employees or a department with no location). Furthermore, you may want stored procedures that produce information commonly sought after through various join conditions. Procedures that perform similar tasks can be put in packages (e.g. find_emp_pkg with procedures like find_by_job, find_by_department, find_by_location, etc.). Let your mind think of any possible information anyone might want to know about this HR and you can put that in a procedure. Good luck.
0
 

Author Comment

by:Mark_Co
ID: 39601960
@slightwv sorry, my mistake. I was thinking that you could make a procedure and not store it.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39601969
>> thinking that you could make a procedure and not store it

Nope.  You can execute an anonymous pl/sql block that isn't saved in the data dictionary but as soon as you add a 'create' to just about anything, it is stored in the dictionary.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39603500
Perhaps a package involving a pipelined table function - in lieu of a proc for a report .

You'll probably want a trigger or 2 as part of your portfolio, maybe consider "auditing" some changes to some other table?
0
 

Author Closing Comment

by:Mark_Co
ID: 39604604
Thank you. This is a good start. Please post more ideas for me if you think of others. Thanks so much!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question