Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Are PL/SQL Package Body Constants in Shared Area or Private Area

884401Apr 4 2013 — edited Apr 11 2013
Based on this it not clear to me if PL/SQL Package Body Constants are stored in shared area or private area.

http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm

"PL/SQL Program Units and the Shared Pool

Oracle Database processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements. Oracle Database allocates a shared area to hold the parsed, compiled form of a program unit. Oracle Database allocates a private area to hold values specific to the session that runs the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user runs the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.

Individual SQL statements contained within a PL/SQL program unit are processed as described in the previous sections. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that runs the statement."


I am also curious what are the fine grained differences from a memory and performance perspective (multi-session) for the two examples below. Is one more efficient?


Example 1.

create or replace
package body
application_util
as

c_create_metadata constant varchar2(6000) := ...

procedure process_xxx
(
)
as
begin
...
end process_xxx;

end application_util;

vs.

Example 2.

create or replace
package body
application_util
as

procedure process_xxx
(
)
as

c_create_metadata constant varchar2(6000) := ...

begin
...
end process_xxx;

end application_util;
This post has been answered by Billy Verreynne on Apr 8 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 9 2013
Added on Apr 4 2013
18 comments
1,418 views