Packaged procedures 1

[amolso]

What are the advantages of packaged procedures
over normal procedures ('OTHER THAN PROPER GROUPING')
Do they increase performance level.

How do I alter a package and add one more procedure into it

amol [sig][/sig]

 

[MikeLacey]

"Do they increase performance level?"

Not that I'm aware of -- but you shouldn't ignore the &quot;grouping&quot; advantage. It's much easier to write pl/sql procedures in a package as you don't have to worry so much about conflicting with existing procedures and functions. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[listener22]

Oracle claims that there is a performance advantage because of the
way the packages are stored, but it is probably not much, and the
advantantages Mike cites above are usually considered more important. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[carp]

If the functions and procedures you place into a package are logically related and typically executed in conjunction with one another, you will see some improvement in performance. This is because calling any of the procedures/functions/constants in the package causes the entire package to be loaded into the library cache. Consequently, calls to other items in the package will get a hit in the library cache. This also helps lower your incidence of fragmentation in the shared pool.
I believe the conflict Mike is referring to is the fact that changing the logic in your packaged procedures does not cause dependent objects to become invalid; they are compiled against the package specification.
Altering a package is similar to altering a procedure. Bring up the source code and edit it! If you are adding a procedure that you wish to be public, you must add the interface definition to the package specification, then the procedure definition to the package body. Then recompile the package (just recompile the package body if you haven't changed the package specification!). [sig][/sig]

 

[amolso]

Thank you
Mike, Jim, Carp

The information You provided is very helpful
to me .

Thank you very much
Amol [sig][/sig]

 

[Infy2000]

How can we create packages in SQL Server 2000?
Durgesh

 

[JJSmith]

One of the performance gains is to do with parsing. When you store a package/procedure the source is parsed and checks carried out for syntax, precence of refrenced objects - tables, views other package/procedures etc. On completion a valid 'parsed' version of the package/procedure is stored, sort of akin to a compiled version. When you subsequently execute the package/procedure the code can be executed without the over head of all those checks (HARD PARSING). If you submit code externally ie sqlplus script then this checking takes place every time, unless you are running exactly the same script that you recently ran, in which case oracle may have cached 'parsed' statements from the previous run ( one of the advantages of shared pool ). My rule of thumb is purely on size, if its a fair bit of code with lots of refrenced object/dependencies then store it in the database. If its a small bit then it's an ease of managment rather than performance issue in that a stored procedure can be used by many users where as a script may not be available as a single copy to a number of disbursed clients.

Hope this additional view helps

JJSmith

 

[layco]

You are able to declare environmental variables to be contained within a package. That is you can assign a value to a variable that is held by the package and reference it later from another procedure in the same package. You can constrict certain object naming to the package so that if you reference an object name in one package it will refer to one object and from another package it will refer to another.

Also you can group your grants to the package so you can create a package for one type of user and then grant priv's to those users while not granting them access to other procedures and functions.