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

Posted on 2013-10-25
Medium Priority
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
Question by:Mark_Co
LVL 78

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

Author Comment

ID: 39601166
right,I should have said stored procedures.
LVL 78

Expert Comment

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

What you posted implied stored procedures...  I'm not following?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 32

Accepted Solution

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.

Author Comment

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

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.
LVL 49

Assisted Solution

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?

Author Closing Comment

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

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

588 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