Solved

Oracle Package and Procedure

Posted on 2014-03-02
17
355 Views
Last Modified: 2014-03-05
Dear all,

What is the different between Package and Procedure? give some example on the way to use it.
0
Comment
Question by:marrowyung
  • 8
  • 5
  • 4
17 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 39898834
A procedure is a single procedure.  A package is a group of functions and/or procedures.  Advantages of a package is that procedures/functions that are used together can be loaded at once.  There really is no difference in their usage, other than to call a function/procedure within a package, you need to preface it with the package name.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39899165
Packages can also be helpful for security (granting permissions to a package grants permission to all members of the package).

see Oracle 11.2 documentation on package:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS009
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899531
johnsone,

So now Package is just the BOX to include all function, SP inside a single place ?

so can we execute the package and it will automatically execute the SP inside? or SP/function specify by us ?

or this is what you mean " preface it with the package name. "?

PortletPaul,

"Packages can also be helpful for security"

this seems to me that package is just the envelope to package something and one of the usage is to assign the SAME permission to everything inside easlier ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39899623
>>"package is just the envelope to package something"
precisely

>>"is to assign the SAME permission to everything inside easier ?"
correct (if this is convenient)
note I say "can be"
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899683
thanks.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899701
so someone tell me that Procedure is just the same as the Store procedure in MySQL and M SSQL ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39899813
Yes, this too is correct.

"procedures" are "stored procedures" (or vice versa)

There are dbms object types in Oracle that don't occur in other databases however
e.g. a "sequence" is one that springs to mind
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899836
"e.g. a "sequence" is one that springs to mind"

yeah, I heard that, sequence id, right?

 starting from MS SQL 2012, it has ! yeah, same thing as Oracle.

in Oralce 12c, Oracel will learn back from MS SQL and MySQL, one instance with many database.

but one thing, other than assigning permission of all item inside the package, it seems the package can be ignore as the procedure can do the job too! for exmaple, assign API/interface ! procedure can do the same thing too ! agree?
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39899934
Hi.

I would never agree that a single procedure could be the equal of a single package.

As you mentioned before, a package is an "envelope" that can contain many many other database objects. For example, if you read that Oracle documentation reference you will see that they try to indicate that a package could be as broad as a whole "module".

There is no direct equivalent of a package (or 'package body') in MSSQL to my knowledge.

If you are confused by packages and feel you can achieve what you need using procedures, then I suggest you do just use procedures.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39900157
In it's simplest form, yes a package is simply a container.  However it does have advantages over a single procedure.

They get into more advanced topics than the general simple questions you are asking.

Packages can have variables that can be accessed from outside the package.  The variables are persistent across the session.  You can also hide these from outside and have them private to the package.

There can be private functions/procedures that are not exposed outside the package.  This can actually be done in a function/procedure as well, but I never found a use for it.

You can overload functions/procedures.

Packages can contain initialization routines that run when loaded.

Those are just the highlights that I can think of right now.

I highly recommend that you take some formal training.  There is no way you are going to become an Oracle professional by asking vague question after vague question.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39900703
johnsone,

"In it's simplest form, yes a package is simply a container.  However it does have advantages over a single procedure."

Yeah, it seems package and procedure is just like the C++'s OO concept ! it allows DBA change code and application developer do application code without impacting each other and it provide much more.

"Packages can have variables that can be accessed from outside the package.  The variables are persistent across the session.
"

That's what I mean.

"Packages can contain initialization routines that run when loaded.

What is it for ?

"I highly recommend that you take some formal training.  "

taking but it is more about admin workshop I and II, not programming course .
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39900827
I don't see how DBA and/or developer can make a code change without impacting each other or users.  In any environment let alone within the database.  No changes should be taking place without everyone's knowledge and thorough testing.

Initialization code can contain anything.  Most of the time they aren't used.  When they are used, I typically see them setting up variables.

The questions that you are asking is like you are trying to cram years of experience and training out of a Q&A site.  You aren't going to get the answers you are looking for without some sort of formal training and then experience on top of that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39902319
"I don't see how DBA and/or developer can make a code change without impacting each other or users. "

just because the package interface is the same and we only change procedure code and application, only talk to the package see no change at all!

but as I said, use procedure can do the same thing.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 39906085
In that sense, I can hide even more using a package because I could add a parameter to the procedure.  Leave the old procedure in the package and create a new procedure with the same name and different signature in the package and the correct procedure would be called based on the parameters passed.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39906724
"Leave the old procedure in the package and create a new procedure with the same name and different signature in the package and the correct procedure would be called based on the parameters passed. "

this one good.

Any example on how complicate it is ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39908334
This is a Q & A site, and the practice is to ask a question then close it once answered.

This is progressing through a conversation, with every comment made leading to a new question branch, and it could have no end if that persists.

Are you now comfortable with the distinction Package -v- Procedure ?
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39908470
absolutely !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query question 8 93
form builder not starting 3 56
minium over 4 numeric columns for each row in oracle 2 29
subtr returning incorrect value 8 33
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

830 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