Solved

Oracle Package and Procedure

Posted on 2014-03-02
17
352 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"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
Comment Utility
thanks.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
Comment Utility
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
Comment Utility
"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
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

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

10 Experts available now in Live!

Get 1:1 Help Now