Oracle Package and Procedure

Dear all,

What is the different between Package and Procedure? give some example on the way to use it.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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
PortletPaulEE Topic AdvisorCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
>>"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
marrowyungSenior Technical architecture (Data)Author Commented:
thanks.
0
marrowyungSenior Technical architecture (Data)Author Commented:
so someone tell me that Procedure is just the same as the Store procedure in MySQL and M SSQL ?
0
PortletPaulEE Topic AdvisorCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
PortletPaulEE Topic AdvisorCommented:
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
johnsoneSenior Oracle DBACommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
johnsoneSenior Oracle DBACommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"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
PortletPaulEE Topic AdvisorCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
absolutely !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.