Solved

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

Posted on 2013-10-25
8
614 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 77

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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date show only hh:mm 2 37
Need to Understand Resolution to Oracle Error ORA-00600 2 38
oracle query 3 25
Database Owner 3 12
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

679 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