Solved

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

Posted on 2013-10-25
8
608 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
8 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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 76

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
 
LVL 32

Accepted Solution

by:
awking00 earned 125 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now