Solved

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

Posted on 2013-10-25
8
611 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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

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.

Question has a verified solution.

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

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.
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

825 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