Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 22, 2003 - 12:18 pm UTC

Last updated: September 01, 2016 - 2:37 am UTC

Version: 9.2

Viewed 50K+ times! This question is

You Asked

Which is the best to user for code and when is it best to use a package rather than a procedure

and Tom said...

Always use a package.

Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing)

Rating

  (125 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Procedure vs Package

Craig Richards, January 22, 2003 - 1:38 pm UTC

Thanks very much it answered my questions OK

Why?

reader, January 22, 2003 - 4:38 pm UTC

Can you please justify your statement.

Tom Kyte
January 22, 2003 - 5:39 pm UTC

because packages

o break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database)

o support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures

o increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding

o support overloading

o support session variables when you need them

o promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together....

If you are a programmer - you would see the benefits of packages over a proliferation of standalone procedures in a heartbeat.

Follwup Why?

Jim Shang, January 22, 2003 - 4:59 pm UTC

Followup to A Reader
Why?

Use Tom's search facility to look for articles
which have "standalone procedure" or "dependency chain"

heres just one of the previous discussion

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:286816015990 <code>


How to see the code of package body

Ajeet, February 05, 2003 - 11:43 pm UTC

Tom - If a user A is the owner of a package then what privilegs a user B need in order to view the code of that package body.Assuming that Package has a public synonym.
Also user B is a normal user having looker and updater roles on the objects owned by user A and execute on the proecdures,functions and packages.
The reason I asked this question is -- user B can see the code of procedure and functions owned by A (as B has execute privilleges on them) , but in case of packages it can only see package specification not the body.
can you please let me know how user B can see the package body as well.

Tom Kyte
February 06, 2003 - 7:54 am UTC

from the definition of ALL_OBJECTS:

  and
  (
    o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
    or
    (.....
    or
    (
       o.type# = 11 /* pkg body */
       and
       exists (select null from v$enabledprivs
           where priv_number =   -141 /* CREATE ANY PROCEDURE */
              )
    )


so, either you OWN it or you have create any procedure, then you can see the body.

alternatively, DBA_OBJECTS shows you all code regardless of whether you can execute it or not.

B cannot see the package body -- although A can show it to them....  Consider:

ops$tkyte@ORA817DEV> drop user a cascade;
User dropped.

ops$tkyte@ORA817DEV> drop user b cascade;
User dropped.

ops$tkyte@ORA817DEV> grant connect, resource to a identified by a;
Grant succeeded.

ops$tkyte@ORA817DEV> grant connect to b identified by b;
Grant succeeded.

ops$tkyte@ORA817DEV> @su a

a@ORA817DEV> create or replace package demo_pkg
  2  as
  3          type rc is ref cursor;
  4
  5          procedure show_me_the_code( p_pkg_name in varchar2, p_code in out rc );
  6  end;
  7  /
Package created.

a@ORA817DEV> create or replace package body demo_pkg
  2  as
  3
  4  procedure show_me_the_code( p_pkg_name in varchar2, p_code in out rc )
  5  is
  6  begin
  7          open p_code
  8          for
  9          select text
 10            from user_source
 11           where name = upper(p_pkg_name)
 12           order by type, line;
 13  end;
 14
 15  end;
 16  /
Package body created.

a@ORA817DEV> grant execute on demo_pkg to b;
Grant succeeded.

a@ORA817DEV> @su b

b@ORA817DEV> select text from all_source where name = 'DEMO_PKG' order by type, line;

TEXT
----------------------------------------------------------------------
package demo_pkg
as
        type rc is ref cursor;

        procedure show_me_the_code( p_pkg_name in varchar2, p_code in out rc
);

end;

6 rows selected.

<b>Now you can't see it ....</b>


b@ORA817DEV> variable x refcursor
b@ORA817DEV> exec a.demo_pkg.show_me_the_code( 'demo_pkg', :x )

PL/SQL procedure successfully completed.

b@ORA817DEV> print x

TEXT
----------------------------------------------------------------------
package demo_pkg
as
        type rc is ref cursor;

        procedure show_me_the_code( p_pkg_name in varchar2, p_code in out rc
);

end;
package body demo_pkg
as

procedure show_me_the_code( p_pkg_name in varchar2, p_code in out rc )


is
begin
        open p_code
        for
        select text
          from user_source
         where name = upper(p_pkg_name)
         order by type, line;
end;

end;

21 rows selected.


<b>and now you can...</b>
 

why execute works for procedures and function

Ajeet, February 06, 2003 - 7:26 pm UTC

Tom -- Thanks so much for your answer.
I am not able to understand the logic behind the fact that
if we grant B execute on the procedures and function created by A -- it let them see the code,but why it is not applicable for packages.
Appreciate your all help.

Tom Kyte
February 06, 2003 - 9:36 pm UTC

that is one of the advantages of packages actually.

it is called "encapsulation"

you see the spec, the interface, the inputs and outputs.

The body -- meaningless. It is just the implementation. You only need be aware of the interface.

About package specification

Tanmoy Datta Choudhury, April 30, 2003 - 8:25 am UTC

Tom can you tell me the reason behind the package specification..why we do that ..we can do everything in the package body itself ..then why specification .. why oracle think that a specification is necessary ????

Tom Kyte
April 30, 2003 - 8:32 am UTC

it is all about encapsulation, modular coding (yes, we can still call it that), and scoping.


I have a global variable. If I put it in a spec, EVERYONE can read and write it directly. That is in general "not a good thing". Hence, we put it in a package body. now, only my package body can read and write it. I can "protect" it. The only way to modify that variable is via my code which knows what to do with it and does it properly.


I have 10 procedures. 4 of which are meant to be called by others. 6 of which are internal routines no one but me has any business knowing about -- let alone calling. The spec exposes the 4, but not the other 6. You can only call what I let you call.

The spec is what OTHER pieces of code are dependent on (read the link above about breaking the dependency chain)

why in out ref cursor

John, April 30, 2003 - 8:53 am UTC

Tom,

Thanks for the answers. everything was to the point.

I have question on followup to the Ajeet's first question,

for the demo_pkg, you have in out ref cursor..
In our applications we allways use "out" ref cursor..

is there any benefit that we can leverage with "in out" ref cursor.

thanks

Tom Kyte
April 30, 2003 - 9:10 am UTC

it is just what I always type -- a holdover from the days when IN OUT was the mode ref cursors were documented to be passed as. today OUT suffices.

Grants?

Kashif, August 01, 2003 - 12:08 pm UTC

Hi Tom,

In The Application Developer's Guide, one of the benefits of using packages mentioned is:

"Let you grant privileges more efficiently."

I'm not clear on what they mean here, and I thought you might know. How can packages be used to grant privileges, short of using execute immediate 'grant select, insert...' type statements? Thanks.

Kashif

Tom Kyte
August 01, 2003 - 12:19 pm UTC

Makes sense...

Kashif, August 01, 2003 - 1:59 pm UTC

Thanks Tom, I guess I misread it totally. Though it's not really a benefit associated with packages only, as they claim, but all stored procedures/functions/packages.

Kashif

Tom Kyte
August 01, 2003 - 2:45 pm UTC

well, they are just alluding to the fact that all good, real, production code would be in a package anyway. we all know you wouldn't use standalong procedures/functions in a "real" system for real code -- just for small utilities.

Developing Packages

Brian, August 01, 2003 - 7:09 pm UTC

You say to use a package over a procedure. But if you are experimenting and only have one to two procedures, does it make sense to dump them into a package to start with or does it make more sense to develop procedures and then dump those that are similiar in scope into a package once you have further tested them?

I ask because I approach the writing of procedures and SQL to answer questions that I am asked. Thus, after I receive a lot of similiar questions, I would then be able to wrap up those procedures created into a package.

The problem that I am faced with is that I am asked questions that need immediate responses (within 15-30 minutes). I do not have the luxury of being able to have those asking the questions to define a project and prepare questions to develop from.

What are your thoughts?




Tom Kyte
August 01, 2003 - 7:41 pm UTC

remember what I said:

all good, real, roduction code
would be in a package anyway. we all know you wouldn't use standalong
procedures/functions in a "real" system for real code -- just for small
utilities.



testing
experimenting
demoing


use procedures (i do, all over the place)

going to production, using real code, in the real world -- it is all about packages and nothing but.

A reader, August 02, 2003 - 1:20 am UTC

"I have 10 procedures. 4 of which are meant to be called by others. 6 of which
are internal routines no one but me has any business knowing about -- let alone
calling. The spec exposes the 4, but not the other 6. You can only call what I
let you call.
"

Well when I describe a package I can see all the 10 procedure. How can you hide the 6. I'm missing something here. Please illustrate your claim.


Tom Kyte
August 02, 2003 - 7:59 am UTC

<b>well, then you must have put all 10 in the specification!</b>



ps$tkyte@ORA920> create or replace package demo_pkg
  2  as
  3          procedure p1;
  4          procedure p2;
  5          procedure p3;
  6          procedure p4;
  7  end;
  8  /

Package created.

ops$tkyte@ORA920> create or replace package body demo_pkg
  2  as
  3          procedure p10 as begin null; end;
  4          procedure p9 as begin null; end;
  5          procedure p8 as begin null; end;
  6          procedure p7 as begin null; end;
  7          procedure p6 as begin null; end;
  8          procedure p5 as begin null; end;
  9          procedure p4 as begin null; end;
 10          procedure p3 as begin null; end;
 11          procedure p2 as begin null; end;
 12          procedure p1 as begin null; end;
 13  end;
 14  /

Package body created.

ops$tkyte@ORA920> desc demo_pkg
PROCEDURE P1
PROCEDURE P2
PROCEDURE P3
PROCEDURE P4

ops$tkyte@ORA920> 

Another view on packages

Ninoslav Cerkez, September 23, 2003 - 10:27 am UTC

Hi Tom,
thanx for Your efforts.
When i read this message, i have recalled one view on packeges i was thinking about for a while. Well, these years, object oriented approach is in. and i was thinking about developing packages for all relevant tables in scheme.
Every package might be used for getting information from table and also for inserting in it. That would be object approach, because we dont deal with data directly.
That's the approach just for the packages for getting/setting data in tables. Of, course, i would have many packages for business logic-processes purposes.
Generating TAPI from designer is one way of object approach i was thinking about, but, i think not the general solution.
I would appreciate Your opinion
nino

Tom Kyte
September 23, 2003 - 11:16 am UTC

depends -- if you need bulk access to this table (forall processing, bulk collect), this is quite confining.

I think the sql should be done in PLSQL -- and the clients just call optimized, well written, efficient stored procedures.

rather then the client calling "my_pkg.do_insert( ..... )" they call "my_pkg.hire_employee( ... )"

Another view on packages2

Ninoslav, September 23, 2003 - 2:36 pm UTC

Hi Tom,
forgive me for my lack of understanding of Your example.
You said:
"....
rather then the client calling "my_pkg.do_insert( ..... )" they call
"my_pkg.hire_employee( ... )"
..."
Well, i didn't catch the difference,except in names :-).
or I missed the point.
We could agree that in every relevant system there are at least dozen of repeted lines of code " insert into table". Some tables are more frequently in use, some not. Don't we get modular system when in every part of system developer just call procedure for inserting data in these 'important' tables?
Isn't it faster and reliable than seperate calling of insert into? Isn't that the same that You said in the second
part "I think the sql should be done in PLSQL -- and the clients just call optimized,
well written, efficient stored procedures." ?
To be honest, i have no experiance with bulk processing, and can't say anything than: agree :-).
the best regards
nino

Tom Kyte
September 23, 2003 - 2:47 pm UTC

to hire someone, say it takes 3 inserts and an update.

client would have to have the logic to do this row by row.
my_table1.do_insert( ... );
my_table2.do_insert( ... );
my_table3.do_insert( ... );
my_table4.do_update( .... );

I suggest, the way to do it is like this:

my_pkg.hire_emp( .... );


period. put the logic into the server, do not put the logic into the client.

it is less work for the server (it would have to response 4 times to the client instead of just once), it is more resuable (everyone can "hire" someone now), it is more "safe" (i can audit, security check, whatever), it is more of everything -- less of nothing. put your transactions in the database.



Another view on packages

Ninoslav, September 23, 2003 - 4:09 pm UTC

Oh, yes..of, course the package should go in the database. I had no doubt about that. I was just curious about (db) package-ing insert statements, and functions for each table.
Sth very similar TAPI.
Table emp --> I have pck_emp and inside procedure for insert and various functions for data i need from table temp.

Process hire_emp could go in that package or in just separate one (for some preocesses in application), and it would call pck_tem.insert_emp for insert.

Just object approach, that was my question.

Tom Kyte
September 23, 2003 - 6:21 pm UTC

i would probably just do the inserts, avoid the overhead of yet another procedure call, unless there was a compelling reason to do so (eg: some logic that when beyond a simple "insert into")

packages

mo, November 04, 2003 - 4:20 pm UTC

Tom:

1. In a web app where you do not really have procedure dependency and each page is considered separate other than it passed data to the other would you still use a packages.

2. let us say you have 100 standalone procedures for the real production app. Would you try to create like 10 packages and stick the procedures in there and then delete the procedures and keep the package.

3. Is not easier to edit a standalone procedure rather than a package because when you open the package you will have 1000 lines of code rather than 100 for a procedure.

4. When you design an app, do you create one package per link on the main menu (3 or 4 pages) or you do one package per subject area like shipping where you have several links or you do one package for the whole app (100 - 150 procedures).

Thanks,

Tom Kyte
November 05, 2003 - 7:42 am UTC

1) yes, I in fact do. Absolutely. 100%. It is a good modular coding practice. asktom is basically a package (the api is anyhow)

ask_tom@ASKUS> desc wwc_ask_api
FUNCTION ACCEPTING_NEW_QUESTIONS RETURNS BOOLEAN
PROCEDURE ADD_BAD_EMAIL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL VARCHAR2 IN
PROCEDURE ADMIN_UPDATE_QUESTION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN
P_SUBJECT VARCHAR2 IN
P_ANSWER VARCHAR2 IN
P_STATUS VARCHAR2 IN
PROCEDURE ASSIGN_TO_FAQ
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DISPLAYID VARCHAR2 IN
P_FAQ_LIST VARCHAR2 IN
FUNCTION FETCH_ACTIVITY_COOKIE_VAL RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_VAL_NAME VARCHAR2 IN
.........

2) why 10? it might be 1, it might be 100 packages. It will probably be some number in between. You group things based on "am i related to the others". Asktom -- a "real" app -- has basically that single package:

ask_tom@ASKUS> select distinct object_name from user_arguments where package_name = 'WWC_ASK_API';

OBJECT_NAME
------------------------------
ACCEPTING_NEW_QUESTIONS
ADD_BAD_EMAIL
ADMIN_UPDATE_QUESTION
ASSIGN_TO_FAQ
FETCH_ACTIVITY_COOKIE_VAL
FETCH_ALL_VALUES_CHAR
FETCH_ASKCOOKIE_VAL
FETCH_QUESTION_ROW
FETCH_VALUE_CHAR
FETCH_VALUE_DATE
FETCH_VALUE_NUMBER
GET_PREFERENCE
IS_ADMINISTRATOR
NOTIFY
NOTIFY_OF_FOLLOWUP
PARSE_SEARCH_STRING
QUESTION_COUNT
READ_ACTIVITY_COOKIE
READ_ASKCOOKIE
REFRESH_TOP_QUESTIONS
REMOVE_BAD_EMAIL
SAVE_QUESTION
SAVE_REVIEW
SAVE_REVIEW_FOLLOWUP
SET_PREFERENCE
UPDATE_ADMINISTRATORS
UPDATE_APPLICATIONS
UPDATE_CATEGORIES
UPDATE_FAQ_CATEGORIES
UPDATE_QUESTION
URLENCODE
WORD_WRAP
WRITE_ACCESS_LOG
WRITE_ACTIVITY_COOKIE
WRITE_ACTIVITY_LOG
WRITE_ASKCOOKIE
WRITE_SEARCH_LOG

37 rows selected.

3) i don't see it that way. I see a package as a collection of 60/80 line subroutines.

Look at it this way -- how long would you make a procedure if:

a) each procedure was a separate object
b) each procedure was just a little snippet in a larger package.

I find people that write 100 standalone procedures have what would be to me MANY more smaller, more modular, easier to code/debug procedures in a package.

the 37 procedures you see above? that is just the TIP of the iceberg there are other functions and procedures in there you cannot even see -- they are not relevant outside of the package and in a standalone procedure world -- they would not even be functions or procedures - they would be inlined code.

My subroutines fit on a screen (without paging up and down), how about yours?

4) again, they are grouped by "what the heck do they do", not by "how does a not very relevant user interface that will change over time appear to organize them"

Lines doesn't make sense

Ashiq Shamsudeen A, November 05, 2003 - 8:17 am UTC

Hi Tom,

From mo question " 3.Is not easier to edit a standalone procedure rather than a package because when you open the package you will have 1000 lines of code rather than 100 for a procedure"

I sincerely believe number of line doesn't make sense going for procedure instead of package. Say even if we've 100 procedure's in a package, its not going tough in debugging or to modify a procedure in it ,because every procedure(or function) will be a separate subroutine in the package.

Maintainability will be difficult if we've hundred's standalone procedure instead of packages.

what do you say ,Tom?




Tom Kyte
November 05, 2003 - 9:41 am UTC

i agree with you.




packages

mo, November 25, 2003 - 2:23 pm UTC

Tom:

Is this true about using packages:

1. You can increase security if you make the procedure private thus it can't be called from the URL. On the other hand all procedures can be called from URL.

2. If we declare a variable in the package body declaration section, then the variable is global to all procedures in the package. If we call proc A which creates Web Page 1 and it set x := 2, then as we go to proc B which creates Web Page B, X is still read by proc B as being 2. This sounds to me that the application is becoming stateful since you can maintain values for variables in one package but not across packages? Am I correct.

3. If you have an app grouped by function like shipping, receiving, reports etc. and each group has about 10 links relation to the group. would you be doing one package per group (about 30 procedures/package) or one package per link (4 procedures/package)?

4. With packages you can have the same procedure name used as many times as long as each belongs to a package. With standalone procedures you can not?

5. Packages will speed up the application because if you call the package all procedures in the package get cached? However since there are dynamic pages we should never run them from cache and always go to database?

Tom Kyte
November 25, 2003 - 3:26 pm UTC

1) only procedures that have been GRANTED on can be called from a URL -- grants can do this as well -- but yes, procedure hiding in package bodies is one of the really nice features (don't really think of it as a security thing tho personally)

2) the session state should not be persisted across web pages -- so this is "not true". x will not be 2 in proc B.

3) none of the above. the laws of modular coding kick in and my FUNCTIONALITY will tell me how many packages I'll have. I might have UI packages, application logic packages, and common utilities .... (for example)

4) you have overloading with packages, yes. not with standalone procedures.

5) there are others who would say the converse it true. then you have me that says "don't care really, it is noise and the advantages of packages far outstrip any percieved difference")

don't understand your last point -- we are talking about cached CODE, not data in the database at this point.

How to list package bodies having internal procedure routines

Dilip, January 05, 2004 - 4:25 pm UTC

Tom,
Thank You for your excellent site. I was trying to find the list of package bodies that uses internal procedure routines , i.e. procedure not declared in package specification. Is there any Dictionary view that will help me write SQL to list all those package bodies?

Thanks

Tom Kyte
January 06, 2004 - 7:53 am UTC

trick question.

the ONLY package that can use an internal routine is the one that defined it.

the list is only 1 item long and it is exactly the name of the package you are currently looking at. no need for the DD here.

Ok

Ram, February 18, 2004 - 12:49 am UTC

Dear Tom,
Does Oracle allow us to create an alias for a package atleast for dbms defined packages?I mean similar to the
import in java and #include in c or c++.Like
with a for dbms_output
with b for dbms_shared_pool
so that
I can use in the procedure body like
a.put_line(..)
b.keep(...)
Calls can be many in the body of the procedure or function
rather than each time using FQON.
Does Oracle have any facility like that?
Thanks in advance.
Please do reply.






Tom Kyte
February 18, 2004 - 8:35 pm UTC

create synonym a for dbms_output;




Continuation

Natasha, February 19, 2004 - 12:51 pm UTC

Tom,

I have a big package and recently I have modified in that. When I compile the package and execute it...I'll get the accurate result. But When I try to execute it 2nd time soon after the first execution, it gives me wrong values.

Please explain me why it behaves like that....I have lots of Pl/Sql tables and global variables in that.

Pls. explain why it gives wrong results when it's executed for the second time soon after the compilation. For the first attempt after compilation...it gives accurate results.

Pls. advice ASAP

Natasha

Tom Kyte
February 19, 2004 - 2:31 pm UTC



I would guess you have programmed a "bug" into your code. PLSQL general only does that which we tell it to. Perhaps if you have a small, concise example -- we can take a look at it.

Probably related to your use of global variables -- perhaps you need to "reset them" or just stop using them all together (many program bugs are due to "globals" and improper use thereof)

In Continum

Natasha, February 20, 2004 - 8:05 am UTC

Tom,

Thxs for ur reply...

Currently I am in a state of confusion.

When i executed the same package from "SQL PROMPT", it gives me perfect results. But when executed from "PL/SQL DEVELOPER - Test Window", It gives me wrong values. Even though I have checked the default Date Format of PL/SQL DEVELOPER. Still it gives me wrong output.

But I wonder how come the same procedure executed with other tools like "SQL-NAVIGATOR", "TOAD", "EMBAR'CO-RAPID SQL"....it give me accurate output.

I have noticed couple of bugs in PL/SQL DEVELOPER. What do u suggest TOM? Was this a genuine reason...

Pls. advice

Thxs
Natasha

Tom Kyte
February 20, 2004 - 10:00 am UTC

this falls into the category of "my car won't start -- why?"


all any of these tools do is submit the code to the database, the database runs the code.

I'd have to see a small, concise, yet 100% complete example -- else I cannot comment.

privileges on packages

A reader, March 29, 2004 - 4:59 am UTC

I have a user that I only want execute one of the functions in a package. The only way I can see is to have a wrapper aound the package function.

can I .. grant execute on p_account.insert to user1

thanks


Tom Kyte
March 29, 2004 - 10:03 am UTC

no, you can only grant on top level objects (packages, procedures, functions, .....)

Packages, contexts and Source Safe

Mohini, May 01, 2004 - 2:23 pm UTC

Oracle 9.2.0.5
In our shop we use Packages and contexts..
We create a context for a package and use it in all the procedures of
that package..
We are hiring another pl/sql developer and start using visual source safe
(from microsoft) to have version control in place.
Now..There are couple of issues:

1. A package will have lot of procedures..and if I want the developers to
work on different parts of a package...it is not possible..since the whole
package has to be checked out by a developer..so the other developer can not
work on any procedure from that package.

2. To consider the flip side of above aproach...i.e. having lots of procedures
and then call them from different packages..takes care the problem in 1.
but now every procedure will have it's own context (more to maintain)
also..we have lot of stand alone code...and will loose all the advantages
of packages...

What's your take/suggestions on this?

Thanks..


Tom Kyte
May 01, 2004 - 7:49 pm UTC

1) yes, that would be true of any single source code file.....


2) i don't see the "problem", since a package is a group of RELATED functions.


packages, while larger than standalone procedures, should still be MODULAR and SMALL. therein lies the solution.

Perhaps all code to set context values "is a package", one context, one package.

Functions in Packages

Shreyas, June 16, 2004 - 6:00 am UTC

Hi Tom,

I face one problem in using "function"s inside packages. The function internal to the package and should not be accessed directly from outside; however the function needs to be used in some of the SELECT clauses in the package

SQL> CREATE OR REPLACE PACKAGE pk_demo AS
  2  
  3  PROCEDURE p_proc_1;
  4  
  5  END pk_demo;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pk_demo IS
  2  
  3  FUNCTION f_func_1
  4     RETURN NUMBER
  5  AS
  6  
  7  BEGIN
  8     RETURN 10;
  9  END f_func_1;
 10  
 11  PROCEDURE p_proc_1 IS
 12     li_num NUMBER;
 13  
 14  BEGIN
 15     SELECT f_func_1
 16     INTO   li_num 
 17     FROM DUAL;
 18     
 19  END p_proc_1;
 20  
 21  END pk_demo;
 22  /

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PK_DEMO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/4     PL/SQL: SQL Statement ignored
15/11    PLS-00231: function 'F_FUNC_1' may not be used in SQL
15/11    PL/SQL: ORA-00904: "F_FUNC_1": invalid identifier

Is there a way to overcome this problem other than making the function visible in the specification?

Thanks,
Shreyas.
 

Tom Kyte
June 16, 2004 - 12:43 pm UTC

"The function internal
to the package and should not be accessed directly from outside; however the
function needs to be used in some of the SELECT clauses in the package"


conflicts with itself.


sort of like saying "i don't want to go, I am going"


the functions should not be accessed from the outside.
yet sql (outside) must access them.



it is a "you cannot get there from here" problem. They must be exposed if you want SQL to access them.

Functions in Packages

Shreyas, June 17, 2004 - 1:43 pm UTC

Hi Tom,

Can you please help me understand why the SQL which is "physically inside the package" considered "outsite" the package? That would clarify all my doubts.

Thanks,
Shreyas.



Tom Kyte
June 17, 2004 - 2:56 pm UTC

sql is sql -- it lives in the shared pool -- not in or out of a package. in the package - you just have strings that represent some SQL.

SQL execution happens way down in the database, outside of plsql.

How a package is loaded into memory

Nilanjan Ray, July 20, 2004 - 1:16 am UTC

Suppose I have a package consisting of 50 procedures. Is the entire package body loaded into memory whenever a call is made to any one of the procedures? Or only the concerned procedure(s) are loaded as and when required?

Tom Kyte
July 20, 2004 - 8:49 am UTC

they are page in in 4k chunks and stored in the shared pool. supposing it is the entire thing -- so be it, you will not notice as they are loaded once and used over and over and over just like an operating system does.

code is not managed by "procedures" but by 'pages'.

OK

Siva, January 10, 2005 - 12:33 pm UTC

Dear Sir,
If the database has numerous procedures,How to find out the
procedures for which I have Execute privilege?

Please do reply.
Bye!


Tom Kyte
January 10, 2005 - 2:02 pm UTC

select * from user_tab_privs;

Execute procedure in parellel

Laxman Kondal, January 11, 2005 - 4:23 pm UTC

Hi Tom

There is bulk collect/insert, and parallel all related to a table and I don’t know if any thing bulk/parallel execute same procedure with different parameter value in bulk/parallel.

Say I have over 50 different ids/parameter values and I need to execute same procedure which takes 30 seconds to executed, in regular way it will take 50*30 seconds.

Is there any way same procedure can be executed more then one time without waiting for previous one to finish from same session?

Thanks and regards.


Tom Kyte
January 11, 2005 - 4:38 pm UTC

not in the "same session", you need a session per process.

Managing package with multiple developers

Veera Iruku, January 12, 2005 - 8:17 am UTC

Sometimes it's difficult to manage/coordinate packages with many developers adding procedures into packages. We are developing a report package in PL/SQL each procedure as a report. Many developers are working on this. So how to manage or coordinate this while promoting to production.

Tom Kyte
January 12, 2005 - 9:52 am UTC

plsql is not any different from C, from Java, from VB.

use the same techniques development teams have been using for decades. Source code control with check in, check out.



Nice

Raju, January 19, 2005 - 12:36 pm UTC

Hi Tom,

I have 2 simple questions.

1)Suppose if I am executing another's user's procedure, will it get executed in my schema or Definer's schema?

2)Suppose if a package has some 10 procedures,when we make a single procedural call,will all the 10 procedures get loaded or only the called procedure gets loaded?

Please do reply.
Bye!


Tom Kyte
January 20, 2005 - 9:29 am UTC

1) it gets executed with the BASE (no roles enabled) set of privileges of the definers schema.

2) plsql is paged in chunks, like binary files in the OS.

USE PACKAGES - period. nothing else even comes close to making sense -- regardless of how it is loaded.

OK

A reader, January 20, 2005 - 12:05 pm UTC

Hi Tom,
Thanks for your Time and prompt reply.
For the first question If the definer
uses the clause "authid current_user" will Roles be enabled
for that procedure?Please clarify this.

For the second question I forgot to mention that I use a
package.Even if all the procedures are clubbed into a package,will all procedures be loaded into Shared Pool??

Bye!


Tom Kyte
January 20, 2005 - 7:16 pm UTC

roles CAN be enabled, they may or may not be -- depends on the environment that called it.... if you want the entire story -- "Expert one on one Oracle" has it from soup to nuts.

why does it matter for the second question -- honestly. why does it matter since not using packages is *wrong*.

if you put code together in the package -- tell me, why wouldn't or shouldn't it be there? did you write code in there that you won't use? why did you group it together then?

packages - only right way to do it.

are these declarations valid

Drumil Narayan, January 30, 2005 - 10:50 am UTC

hello tom,

this is about 10gIDS and 10g and 10gAS under RHAS 3.0, can we use following constructs or we have to use somthing else...whenever required such types..

create or replace procedure set_blks(reset_blk number := chk_blk.enable_disable_butt.count(1))
begin
..my code...
end;
/


will it increase any network traffic or load in pl/sql shared area ven if any one is called or what..

kindly inform..thanaking you in advance..drumil

PS :chk_blk is package, enable_disable_butt is procedure local to chk_blk, finally set_blks will be kept in a package..


Tom Kyte
January 30, 2005 - 12:23 pm UTC

why would that increase network traffic? everything is in the server there.

everything would be self contained in the server itself.

synonym for functions/procedures inside a package

thiru, March 10, 2005 - 3:05 pm UTC

is there a way to access a function of a package without giving the package name prefixed to the function?

Thanks

Tom Kyte
March 10, 2005 - 7:35 pm UTC

no, not without creating a function that simply returns the packaged function

Procedure Name

OraUser, April 26, 2005 - 3:40 pm UTC

If this is not the right thread, please direct me to the correct one.

Tom,
Is there any way i can find in what procedure i'm in? Like in Java or other Object oriented languages, i can use "this." to indicate this object. Like that is there any thing for Oracle that'll just return me the procedure name currently i'm in?

Thanks for all the help you are providing to Oracle community

Tom Kyte
April 26, 2005 - 4:23 pm UTC

OK

Raju, April 27, 2005 - 12:35 pm UTC

Hi Tom,
When to use procedures and when to use functions?
Can they replace each other??
Performance wise, which one is better??


Tom Kyte
April 27, 2005 - 12:44 pm UTC

use them as they naturally should be used.

a function is nothing more than a procedure that returns a value..

if the code is better written as:


if ( check_something( inputs.... ) )
then
....

versus:

check_something( inputs...., l_result );
if ( l_result )
then
....


I would prefer for check_somthing to be a function. but:

process_something( inputs....... );


is better than:

l_whatever := process_something( inputs..... );

given that process something is a process and if it got an error, would raise an exception (no return codes, that is so 1990). So a return value is not so useful there.

performance issue

Christine Guo, April 28, 2005 - 4:05 pm UTC

I agree using package instead of stand alone stored procs. But are there any performance difference between the two?

I worked with a query returning over 40 million records. There are two functions in this query. When I tried to put the functions into a package, the performance slowed down dramatically. The query calling a function returns half million rows in less then 1 minute, and the query calling a function inside a package returns the half million rows in 6 minutes.
What might be the problem?
BTW, I also noticed that if I put an exception block in the function, it slows down the performance a lot as well.
Your input would be greatly appreciated.

Tom Kyte
April 28, 2005 - 4:25 pm UTC

not that I don't believe you but...

set up a simple example, don't need your tables, just need to understand the difference between your two implementations.

Stand alone procedures

Arya, April 28, 2005 - 4:33 pm UTC

Hi Tom,

I have implemented VPD on our database and I'm generating a procedure dynamically whenever admin adds new policy. Users adds a record to policy table which has a trigger that creates stand alone procedure. I wanted to have a package instead of stand alone procedures but the problem is I can create package dynamically but I cannot edit it to include more procedures as and when they are created. 


SQL> desc policies
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PLCY_ID                                   NOT NULL NUMBER(10)
 NAME                                      NOT NULL VARCHAR2(50)
 DB_SCHEMA                                          VARCHAR2(30)
 POLICY_PROCEDURE                          NOT NULL VARCHAR2(100)
 DB_OBJECT_NAME                            NOT NULL VARCHAR2(30)
 WHERE_CLAUSE                              NOT NULL VARCHAR2(1000)
 STATEMENT_TYPE                            NOT NULL VARCHAR2(20)
 CREATE_DT                                 NOT NULL DATE
 CREATED_BY                                NOT NULL VARCHAR2(50)
 UPDATE_DT                                 NOT NULL DATE
 UPDATED_BY                                NOT NULL VARCHAR2(50)

As and when user adds records to the policy table which fires the trigger and creates policy with a stand alone procedure with the same name as policy.

PACKAGE BODY VPD_POLICIES
AS
PROCEDURE add_policy(p_policy_name IN VARCHAR2,
                     p_schema_name IN VARCHAR2,
                     p_function_name IN VARCHAR2,
                     p_object_name IN VARCHAR2,
                     p_statement_type IN VARCHAR2)

IS
PRAGMA AUTONOMOUS_TRANSACTION;
/******************************************************************************
NAME:       add_policy
PURPOSE:    This procedure adds policy against a table called from DB trigger.
REVISIONS:
Date        Author           Description
----------  ---------------  ------------------------------------
07/10/2004  Sarma            Created this Procedure.
ASSUMPTIONS:
LOGIC:
******************************************************************************/

lv_enable BOOLEAN := TRUE;
lv_update_check_false BOOLEAN := FALSE;
lv_update_check_true BOOLEAN := TRUE;
lv_role_name  VARCHAR2(32);
lv_policy_name  VARCHAR2(32);
lv_schema_name  VARCHAR2(32);
lv_function_name  VARCHAR2(100);
lv_object_name  VARCHAR2(32);
lv_statement_type  VARCHAR2(32);
lv_static_policy BOOLEAN := FALSE;

BEGIN

BEGIN
dbms_rls.drop_policy(p_schema_name,p_object_name,p_policy_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR WHILE DROPPING POLICY '||SQLERRM);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ('INSIDE ADD POLICY PROCEDURE '||p_POLICY_NAME);

            dbms_rls.add_policy(p_schema_name,
                                p_object_name,
                                p_policy_name,
                                p_schema_name,
                                p_function_name,
                                p_statement_type,
                                CASE WHEN p_statement_type = 'UPDATE' THEN
                                    lv_update_check_true
                                ELSE
                                    lv_update_check_false
                                END,
                                lv_enable,
                                lv_static_policy);

DBMS_OUTPUT.PUT_LINE ('IN ADD POLICY PROCEDURE AFTER DBMS_RLS '||LV_STATEMENT_TYPE);

create_policy_procedure(p_policy_name);

EXCEPTION
    WHEN OTHERS THEN
         dbms_output.put_line (' Error adding policy  '|| SQLERRM);

END;

EXCEPTION
    WHEN OTHERS THEN
         dbms_output.put_line (' Error IN ADD POLICIES PROCEDURE '|| SQLERRM);
END;

PROCEDURE create_policy_procedure(p_policy_name IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
/******************************************************************************
NAME:       add_policy
PURPOSE:    This procedure adds policy against a table called from DB trigger.
REVISIONS:
Date        Author           Description
----------  ---------------  ------------------------------------
07/10/2004  Sarma            Created this Procedure.
ASSUMPTIONS:
LOGIC:
******************************************************************************/
lv_sql varchar2(4000);
BEGIN


lv_sql := 'CREATE OR REPLACE FUNCTION '||p_policy_name||' (p_schema_name  varchar2, p_object_name varchar2) RETURN VARCHAR2
           IS
           lv_sql varchar2(4000);
           BEGIN
                lv_sql := vpd_predicates.select_predicate('''||p_policy_name||''');
           RETURN lv_sql;
           EXCEPTION
                WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE (''ERROR''||SQLERRM);
           END;';
--DBMS_OUTPUT.PUT_LINE ('IN CREATE POLICY PROCEDURE 1 '||substr(LV_sql,1,199));
--DBMS_OUTPUT.PUT_LINE ('IN CREATE POLICY PROCEDURE 2 '||substr(LV_sql,200));
EXECUTE IMMEDIATE lv_sql;
    --dbms_output.put_line( 'Success');
EXCEPTION
WHEN others THEN
    dbms_output.put_line ('Error IN CREATE POLICY PROCEDURE '||sqlerrm);
END;
END;

Can you give me some idea/work around to achieve the same.

Thanks
Arya 

Tom Kyte
April 28, 2005 - 4:41 pm UTC

I totally urge you to rethink that approach. atrans in triggers are almost always pure evil and this is one of them.

Use dbms_job to queue a job to create a procedure AFTER the transaction commits, do not create the procedure in the trigger.

A package is either

a) entirely created from start to finish or
b) not

you cannot add a procedure to a package, you have to create a package with the procedure already in it.


but.... why don't you have a single STATIC package that all use -- have it query and cache in plsql index by table the predicates it needs -- it looks like you want a SINGLE package created yesterday that all policies use. When invoked, it takes the schemaname/objectname and looks up in a index by varchar2(100) table for the predicate. If not found, query it and cache it in the package.

in short, i don't think you need, want or desire to create a procedure on the fly at all

Stand Alone Procedure

Arya, April 28, 2005 - 5:12 pm UTC

Hi Tom,

Thanks for your quick reply. Right now we have 60 tables and whenever user logs on to the application depending on his role we have to set the policy so we have used table driven approach to acheive this. Based on user role we will query relevant tables and apply the relavant policy.

I did not quite understand your idea of having static package can you please kindly elaborate with an example.

Thanks
Arya

Tom Kyte
April 28, 2005 - 5:29 pm UTC

I don't understand what you are doing here then at all.

if you flip the policy when I log in, that affects the policy in place for you who are already logged in.

what are you doing exactly?

table driven usually means "code at runtime uses a table to figure out what to do"

Not "we insert into a table and write code"

Table driven code is static code that everyone uses and it figures out what to do based on data in tables.

Seems you want ONE procedure. When the guy logs in, you set up their application context with the necessary information. The SINGLE policy procedure looks at that context and decides what predicate to return (perhaps from a table). That would be table driven

performance issue continue

Christine Guo, April 28, 2005 - 5:20 pm UTC

The query is something like:

select /*+ full(d) parallel(d 8) */domainid, domainname,get_stat1(domainid), get_stat2(domainid)
from domain d;

It uses function like:

FUNCTION get_stat1 (pdomainid NUMBER)
RETURN VARCHAR2
IS
statuses VARCHAR2 (50) := NULL;

CURSOR cur_rrp_status
IS
SELECT statuscode
FROM domainstatus
WHERE domainid = pdomainid;
BEGIN
FOR rrp_status IN cur_rrp_status
LOOP
statuses := statuses || ' ' || rrp_status.statuscode;
END LOOP;

statuses := TRIM (' ' FROM statuses);
RETURN statuses;
/*EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000,
'Error in get_domstat: DomainId '
|| pdomainid
|| ' has following error: '
|| SQLERRM
);
*/
END get_domstat;
==================================
Then I put these functions in a package like:

CREATE OR REPLACE PACKAGE pkg1
AS
FUNCTION get_stat1 (pdomainid NUMBER)
RETURN VARCHAR2;

FUNCTION get_stat2 (pdomainid NUMBER)
RETURN VARCHAR2;

END pkg1;

and the query would be like:

select /*+ full(d) parallel(d 8) */domainid, domainname,pkg1.get_stat1(domainid), pkg1.get_stat2(domainid)
from domain d;
==========================================
I use utl_file to write every 50,000th record in a log, and its timestamp. The performance drops dramatically with the package, even though the functions are exactly the same.

Thanks for your input.

Tom Kyte
April 28, 2005 - 5:30 pm UTC

do you only care about the every 50,000 record? (might as well just write out the first few random records??? I mean no order by -- with is the 50,000th record frmo an unordered result set, why is that any more special then the 1st record or the 12th?)

performance issue continue2

Christine, April 29, 2005 - 10:03 am UTC

The query returns all the records in the application. So the order is not an issue. It is during the tuning process I write out a timestamp for every 500,000th record so that I know the progress of each query. This is the way I check performance of different queries, and how I found out that package performed lot worse then stand alone functions.

This log is the result of using functions, it shows that the query dumps very 500000 record in about 1 minute.
500000 04-28-2005 10:20:16 738639^TEFCOINC.COM
1000000 04-28-2005 10:21:09 1997969^PALBEACHINTERACTIVE.COM
1500000 04-28-2005 10:22:02 1363915^WWKIDS.COM
2000000 04-28-2005 10:22:57 16241528^JAEDERFELDT.COM
...
39000000 04-28-2005 11:34:27 138979517^RINK-FINDER.COM
39500000 04-28-2005 11:35:26 140674618^RRTEEMD00.COM
40000000 04-28-2005 11:36:23 138669127^BOATINGMATES.COM
40500000 04-28-2005 11:37:25 140819747^CHRINGLESVILLAGE.COM

If I use package then the performance is worse, and the query dumps very 500000 record in about 6 minute.

Thanks,

Tom Kyte
April 29, 2005 - 10:20 am UTC

I cannot imagine how this could be true, sorry -- I've never seen it be that way, ever.


(but parallel query for that would be a waste wouldn't it -- a simple full scan? fed to a serial process?)

Table driven approach

Arya, April 29, 2005 - 10:37 am UTC

Hi Tom,

Sorry for being unclear. Table driven approach meaning we have stored policies information on each table in "policies" table. when user logs on to the application using his email-id we query our tables and based on his role we set context and policy accordingly. Every thing is working fine except that there are many number of stand alone procedures hanging around.

Thanks
Arya

Tom Kyte
April 29, 2005 - 10:46 am UTC

so, why don't you have a single stored procedure that queries the table to figure out what the predicate should be for the given user? look at the procedures you generate, they are all basically the same. make it truly table driven.

I do not see the need to generate code on the fly.
I do not see how this is working in a multi-user environment.

I see the need for exactly ONE predicate policy function here



Thanks

Arya, April 29, 2005 - 12:43 pm UTC

Hi Tom,

yes you are right. I'll try to change the code to accommodate one stored procedure which sets policy based on the log in user.

Thanks
Arya

Procedure with different parameters,same functionality

Kan, April 29, 2005 - 3:04 pm UTC

Tom,
I have a requirement from web application that users will do a search on different scenarios (examples: Staff search,Customer Search,Privilege search etc..) and we need to create some procedures that take in searched parameters and send the results back to client (web application). We currently have around 30-35 searches and some of them use same no. or type of parameters others use different parameters. Client passes, for each parameter, an "OR" or an "AND" condition with that. eg: for searching on a particular customer, they can search on (Customer location AND customer first name) or customer last name .
So my parameters will be p_cust_loc,p_AND,p_cust_first_name,P_AND,p_cust_last_name,P_OR
I need to build a SQL dynamically and depending on AND /OR for each parameter and send this listing to client.

Ok, here is my question:
->Is there any way i can put functionality in one procedure and use only one other procedure that's called from client with different parameters?
for 35 searches i think if we can build some kind of dynamic parameters thing for Procedure and make procedure understand what are those parameters, that'll really help us manage the code.

Thanks for taking my question


It was helpful but..

Kan, April 30, 2005 - 12:05 am UTC

Tom, I looked at the link (for sys_context) but that will be possible if i have known no. of parameters to build a where clause. If you know max n columns are passed and columns names are known(empno,sal and hiredate..) you can build like you shown there. But what if i get different parameters eg: for customer search i can get customer first name,last name,location and for another search like staff search, i may get staff name,staff id and/or staff last name.
So how do i build this ??
If i'm missing anything from the link you sent me about context's, then please point me to that part .
Thanks and appreicate your help


Tom Kyte
April 30, 2005 - 10:31 am UTC

arrays -- just like you would in any language. If you pass an array of names, an array of operations, an array of values......

Arrays from Client??

Kan, April 30, 2005 - 4:18 pm UTC

Thanks for your response. When you said, array of names,array of operations ..you mean, client will pass arrays? Never thought about it..mmmmm need to talk with java developers about this..
If not,and you meant creating arrays after client sends its information, i still have to create 40 seperate procedures for 40 searches and form array of names..depending on the search criteria.
Is there any alternative way?
Thanks,

Tom Kyte
April 30, 2005 - 4:33 pm UTC

java can bind and send either plsql index by tables OR collections (nested tables or varrays).

OK

Kumar, May 02, 2005 - 11:00 am UTC

Hi Tom,
Do packages support dynamic loading??
I mean suppose we have a package with some 10 procedures
p1,p2..pn and if an application needs only the p2 procedure,can it be dynamically loaded into memory while
discarding the other procedures??
Please do reply.
Bye!


Tom Kyte
May 02, 2005 - 11:40 am UTC

the code is paged in in about 4k chunks, but pretty much it'll all be there - smeared across the shared pool in pieces.

Index By??

John, May 02, 2005 - 12:37 pm UTC

" java can bind and send either plsql index by tables OR collections (nested tables or varrays). "

We're converting index by tables to collections to accommodate the Java crew.

Got an example using index by tables?

Diff between results of Procedure & Package

Mita, May 19, 2005 - 7:02 pm UTC

create table x (a number);

begin
for a in 1..5
loop
insert into x values (a);
end loop;
end;
/

create procedure test as
b number := 10;
begin
insert into x(a)
select b
from (select b
from x
where a = 2);
end;
/

create package test1 as
procedure ins(z number) ;
end;
/

create package body test1 as
procedure ins(z number) is
b number := 20;
begin
insert into x(a)
select b
from (select b
from x
where a = z);
end;
end;
/

begin
test;
test1.ins(1);
test1.ins(2);
end;
/



DDMMGR@DTSTGDOD> select * from x ;

A
----------
1
2
3
4
5
10
1
2

8 rows selected.

why did it insert 10 in once case and 1 & 2 in case of package ??

Tom Kyte
May 20, 2005 - 7:44 am UTC

in 9i -- it must be coded differently (does not want you to use b twice)

in 8i, IF you make the two routines *the same* (pass Z) they do the same (wrong) thing. Please contact support

no difference between package and procedure, but B is handled wrong.

Results

Mita, May 20, 2005 - 2:03 pm UTC

I made stand alone procedure same as packaged procedure and result it same.

I will contact support.
Thanks.

Packages or sql scripts

AD, May 27, 2005 - 3:57 pm UTC

Tom,

I have several sql scripts, which basically does the similar thing, uses a set of tables and insert records into set a target tables.

e.g.
1) insert into A ...
select * from B
2) insert into C
select * from D
3) insert into E
select * from F

etc.

At the moment I have separate scripts to accomplish 1, 2, and 3 etc. They are part of the same functionality. Do you suggest that I should write a package to accomplish the above steps or leave them as individual scripts.

Regards







Tom Kyte
May 27, 2005 - 4:13 pm UTC

insufficient data.

IF this is something you do over and over in the same database, by all means package it up.

IF this is an install script, probably just as well.

Packages vs sql scripts

AD, May 27, 2005 - 4:18 pm UTC

Tom,

Thanks for such a prompt reply. Yes, this is to be run over and over from the same database. In that case I take your advise of packaging them up.

Regards


Changing the boss's mind

EB, July 05, 2005 - 11:34 am UTC

Tom,

I've long been an advocate of packages over standalone procs and functions. Unfortunately on starting my new job the main application has close to 1000 standalone procedures and functions.

One of my boss's main gripes with packages is that if he does a desc on it, it scrolls off the screen and he has trouble finding what he wants.

Is there anyway to desc just a single procedure within the package?

p.s. He also refuses to use any kind of GUI tool that would make it so much easier.

Thanks
EB

Tom Kyte
July 05, 2005 - 12:11 pm UTC

sure, we can do that, descsp -- send it

owner.package.procedure
package.procedure
procedure

and it'll print it out (whatever you want it to...) 

ops$tkyte@ORA9IR2> @descsp put_line
 
                                                  Data
NAME                              ARGUMENT_NAME   Type
--------------------------------- --------------- --------------------
OPS$TKYTE..PUT_LINE               X               NUMBER
OPS$TKYTE..PUT_LINE               Y               DATE
OPS$TKYTE..PUT_LINE               Z               VARCHAR2
 
 
SYS.DBMS_METADATA_UTIL.PUT_LINE   STMT            VARCHAR2
 
 
SYS.DBMS_OUTPUT.PUT_LINE          A               VARCHAR2
 
SYS.DBMS_OUTPUT.PUT_LINE          A               NUMBER
 
 
SYS.UTL_FILE.PUT_LINE             FILE            PL/SQL RECORD
SYS.UTL_FILE.PUT_LINE             ID              BINARY_INTEGER
SYS.UTL_FILE.PUT_LINE             DATATYPE        BINARY_INTEGER
SYS.UTL_FILE.PUT_LINE             BUFFER          VARCHAR2
SYS.UTL_FILE.PUT_LINE             AUTOFLUSH       PL/SQL BOOLEAN
 
 
 
11 rows selected.
 
ops$tkyte@ORA9IR2> @descsp dbms_output.put_line
 
                                                  Data
NAME                              ARGUMENT_NAME   Type
--------------------------------- --------------- --------------------
SYS.DBMS_OUTPUT.PUT_LINE          A               VARCHAR2
 
SYS.DBMS_OUTPUT.PUT_LINE          A               NUMBER
 
 
 
2 rows selected.

ops$tkyte@ORA9IR2> @descsp sys.utl_file.put_line
 
                                                  Data
NAME                              ARGUMENT_NAME   Type
--------------------------------- --------------- --------------------
SYS.UTL_FILE.PUT_LINE             FILE            PL/SQL RECORD
SYS.UTL_FILE.PUT_LINE             ID              BINARY_INTEGER
SYS.UTL_FILE.PUT_LINE             DATATYPE        BINARY_INTEGER
SYS.UTL_FILE.PUT_LINE             BUFFER          VARCHAR2
SYS.UTL_FILE.PUT_LINE             AUTOFLUSH       PL/SQL BOOLEAN
 
 
 
5 rows selected.


--------------- descsp.sql ---------------------------------------
set linesize 100
variable x$x refcursor
variable y$y varchar2(1000)
set feedback off
exec :y$y := '&1'
                                                                                                                                         
declare
    l_data  varchar2(100) := :y$y;
    l_owner varchar2(30) := '%';
    l_pname varchar2(30) := '%';
    l_name  varchar2(30);
    l_periods number;
    l_where   long;
begin
    l_periods := length(l_data)-length(replace(l_data,'.',''));
    if ( l_periods = 2 )
    then
        l_owner := substr( l_data, 1, instr(l_data,'.')-1 );
        l_data := substr( l_data, instr(l_data,'.')+1 );
        l_where := ' owner = :owner ';
    else
        l_where := ' :owner is not null ';
    end if;
    if ( l_periods >= 1 )
    then
        l_pname := substr( l_data, 1, instr(l_data,'.')-1 );
        l_data := substr( l_data, instr(l_data,'.')+1 );
        l_where := l_where || ' and package_name = :pname ';
    else
        l_where := l_where || ' and :pname is not null ';
    end if;
    l_name := l_data;
                                                                                                                                         
    open :x$x for '
    select owner||''.''||package_name||''.''||object_name name, overload, argument_name, data_type
      from all_arguments
     where ' || l_where || '
       and object_name = :name
      order by owner, package_name, object_name, overload, position'
      using upper(l_owner), upper(l_pname), upper(l_name);
end;
/
set feedback on
break on name skip 1 dup on overload skip 1
column overload noprint
print :x$x
column overload print
 

Excellent tool

Jairo Ojeda, July 05, 2005 - 1:18 pm UTC

Thanks for share to us!!!

fantastic answer

EB, July 06, 2005 - 5:38 am UTC

Tom,

what a great solution.

Thanks
EB

Public program units with private variable defaults?

John, July 08, 2005 - 4:30 am UTC

Tom,

Sorry if I've missed this, or am just having a bad morning, but I'm struggling to find how I can declare a package which has an externally accessible function which has a parameter defaulted to an internally accessible variable.

E.g.

CREATE PACKAGE mypack IS

FUNCTION myfunc( p_msg IN VARCHAR2 DEFAULT ppv_msg )
RETURN VARCHAR2;

END mypack;

CREATE PACKAGE BODY mypack IS

ppv_msg VARCHAR2(50) := 'Secret message';

FUNCTION myfunc( p_msg IN VARCHAR2 DEFAULT ppv_msg )
RETURN VARCHAR2 IS
BEGIN
RETURN p_msg;
END;

END mypack;

I've been able to do it by creating a publically acessible function to return the variable and then using that in the DEFAULT clause, but can't help feeling I'm over complicating it. I'm sure, as usual you'll know the simple way right off the top of your head!

Thanks

John





Tom Kyte
July 08, 2005 - 7:45 am UTC

You cannot hide the variable, but you can hide the initialization

ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3          I_dont_want_you_to_see   varchar2(50);
  4
  5          function myfunc( p_msg in varchar2 default I_dont_want_you_to_see ) return varchar2;
  6  end;
  7  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3          function myfunc( p_msg in varchar2 default I_dont_want_you_to_see ) return varchar2
  4          as
  5          begin
  6                  return p_msg;
  7          end;
  8
  9  begin
 10          I_dont_want_you_to_see := 'Secret Message';
 11  end;
 12  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_output.put_line( '=> ' || demo_pkg.myfunc );
=> Secret Message
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>  exec dbms_output.put_line( '=> ' || demo_pkg.myfunc('xxx') );
=> xxx
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>  exec dbms_output.put_line( '=> ' || demo_pkg.myfunc );
=> Secret Message
 
PL/SQL procedure successfully completed.
 
 

OK

Kumar, July 10, 2005 - 1:30 pm UTC

Hi Tom,
Can multiple procedures be run in parallel??


Tom Kyte
July 10, 2005 - 1:42 pm UTC

sure, you can open as many sessions as you like and run a procedure in each.

if you are asking "is plsql a multi-threaded programming language", no, that would be no.

but you run procedures in parallel every day, you just have multiple sessions running the procedures (typically by many users) but no reason your application cannot open more than one session.

another technique is to use the job queues, set job_queue_process to N and submit M jobs, up to N of them might be running concurrently.

Thanks Tom

John, July 11, 2005 - 5:22 am UTC

Thanks Tom. I guess though I'll have to stick with what I have.

My reason for having the variable private (which I didn't state in my question!) was to stop it being able to be set without validation so I can't really use what you've suggested.

At least I know I'm not missing something silly.

Thanks again.

Tom Kyte
July 11, 2005 - 8:39 am UTC

hmm, seems strange doesn't it?

IF you send a default, I don't want YOU to change said default value

BUT, you can override the default, sending ANYTHING you like.


so why limit them from changing the default??  They can override it anyway after all?


Use "getter" and "setter" functions to protect the variable then.  Here is the example with the "getter" portion, if you need, you would write a "setter" function to set the value with validation:

ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3          function I_dont_want_you_to_see return varchar2;
  4          function myfunc( p_msg in varchar2 default I_dont_want_you_to_see() ) return varchar2;
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> show errors
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3
  4  function I_dont_want_you_to_see return varchar2
  5  is
  6  begin
  7          return 'Secret Message';
  8  end;
  9
 10  function myfunc( p_msg in varchar2 default I_dont_want_you_to_see() ) return varchar2
 11  as
 12  begin
 13          return p_msg;
 14  end;
 15  end;
 16  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_output.put_line( '=> ' || demo_pkg.myfunc );
=> Secret Message
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( '=> ' || demo_pkg.myfunc('xxx') );
=> xxx
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( '=> ' || demo_pkg.myfunc );
=> Secret Message
 
PL/SQL procedure successfully completed.
 

Thanks Tom!

John, July 13, 2005 - 5:13 am UTC

This is what I've done (Setter and Getter) and it does indeed work. It just felt like it was an overhead that I may not need - but you've cleared it up - I do need it.

It's a bit unusual perhaps, but not strange!!

I'm happy for the caller to change the 'default' value, but only through a setter that performs validation.

I realise that they are able to pass anything instead of the default when they call the routine. In fact at this point I validate again.

The reason for not wanting them to be able to set the default without validation is *only* to simplify any debugging when they use the 'API' I'm writing.

If a bad value is passed in at the time of the call, then that's where the error will show up. Easy to find.

However, if I let them set a bad value into the 'default' package variable, that set could be *anywhere*, by *anything* that executed within the same session. Finding that bad setter could be a nightmare, because the error would not show up until the time of the next call with a defaulted value.

I know I've not explained to any where near the level you can, but hopefully I've done it well enough.

Thanks very much for all your help - on all issues. AskTom is an absolutely invaluable resource.

access a packaged function without prefixing the function

Duke Ganote, September 20, 2005 - 9:16 am UTC

Reviewer thiru wrote:
"is there a way to access a function of a package without giving the package name prefixed to the function?"

You wrote: "no, not without creating a function that simply returns the packaged function" (March 10, 2005)

Another reader wrote: "I have a user that I only want execute one of the functions in a package." (March 29, 2004)

For these 2 reasons, I occasionally wrap a packaged function with a stand alone function. I get all the benefits of packages while handling the rare "exception to the rule", for example:

CREATE OR REPLACE PACKAGE dw_0_2_global_pkg
AS
... lotsa specs for functions and procedures here AND ....
FUNCTION numericize
( instring IN VARCHAR2
, if_nonnumeric_default_number IN NUMBER := NULL )
RETURN NUMBER DETERMINISTIC;

END dw_0_2_global_pkg;
/
CREATE OR REPLACE PACKAGE BODY dw_0_2_global_pkg
AS
... lotsa functions and procedures here AND ....
FUNCTION numericize
( instring IN VARCHAR2
, if_nonnumeric_default_number IN NUMBER := NULL )
RETURN NUMBER
IS
v_out NUMBER;
BEGIN
BEGIN
v_out := TO_NUMBER(instring);
EXCEPTION
WHEN OTHERS THEN v_out := if_nonnumeric_default_number;
END;
RETURN (v_out);
END numericize;

END dw_0_2_global_pkg;
/
CREATE OR REPLACE FUNCTION numericize
( instring IN VARCHAR2
, if_nonnumeric_default_number IN NUMBER := NULL )
RETURN NUMBER
IS BEGIN RETURN dw_0_2_global_pkg.numericize
( instring, if_nonnumeric_default_number );
END;
/
grant execute on NUMERICIZE to SOMEUSER
/

The stand alone function is essentially just a spec with a shorter moniker.

OK

Kumar, November 02, 2005 - 6:39 am UTC

Hi Tom,
Is it possible to find "when a procedure was last executed"?

Tom Kyte
November 03, 2005 - 4:58 am UTC

if you have auditing enabled, yes.

Packages!!!!

A reader, November 08, 2005 - 7:01 am UTC

Hi Tom,
I told my developers the benefit of packages.
They took my advice and created one.... a HUGE one.
With 75 odd procedures and functions and 3000 lines of code.
I tols them that the package will be loaded in the memory once irrespective of the procedure being called and hence save parsing and such issues.
Guess they took it tooooo eriously.
I am now worried about the downside.
Now if every user will access that package and I am worried that it will involve a huge scanning of the package body to satisfy there call. As a result degrading the performance.
It is on the demo server soon to be passed on to the clients.

Now, even the developers are having second thoughts.
What if the Client servers do not have enough memory. I am worried about the Large Pool error, Ora-4031 error...

My question: Are my fears justified?
Thanks as always...


Tom Kyte
November 08, 2005 - 10:10 pm UTC

your fears of your developers not knowing how to design and implement modular code are founded....

3,000 lines of code however is pretty small compared to some monsters I've seen. should not be a problem.

SP2-0552: Bind variable "EXIT_CODE" not declared

sat, November 30, 2005 - 3:39 pm UTC

How do you resolve this error .SP2-0552: Bind variable "EXIT_CODE" not declared. This code will be called from unix shell script.

VARIABLE ax_exit_code NUMBER
EXECUTE :ax_exit_code := -1;

VARIABLE cx_exit_code NUMBER
EXECUTE :cx_exit_code := -1;

VARIABLE dx_exit_code NUMBER
EXECUTE :dx_exit_code := -1;

VARIABLE exit_code NUMBER
EXECUTE :exit_code := -1;

set serveroutput on;
DECLARE

p_rtn_cd NUMBER := 0;
p_portal_rtn NUMBER := 0;
p_err_msg VARCHAR2(64);
sup_name VARCHAR2(100);
abend_error EXCEPTION;

BEGIN
sup_name := 'ACCENT';
DBMS_OUTPUT.PUT_LINE('CSS Supplier Data Extract: ' || SYSDATE);

/* Get today's new accounts */
SV_Account_Extract.ProcessNewAccounts(sup_name,:ax_exit_code);
DBMS_OUTPUT.PUT_LINE('NEW ACCOUNT EXTRACT COMPLETE');
end;


SP2-0552: Bind variable "EXIT_CODE" not declared.

Tom Kyte
November 30, 2005 - 8:56 pm UTC

need to help me reproduce this, I cannot:


ops$tkyte@ORA10GR2> VARIABLE ax_exit_code NUMBER
ops$tkyte@ORA10GR2> EXECUTE  :ax_exit_code := -1;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> VARIABLE cx_exit_code NUMBER
ops$tkyte@ORA10GR2> EXECUTE  :cx_exit_code := -1;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> VARIABLE dx_exit_code NUMBER
ops$tkyte@ORA10GR2> EXECUTE  :dx_exit_code := -1;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> VARIABLE exit_code NUMBER
ops$tkyte@ORA10GR2> EXECUTE :exit_code := -1;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set serveroutput on;
ops$tkyte@ORA10GR2> DECLARE
  2  p_rtn_cd     NUMBER := 0;
  3  p_portal_rtn NUMBER := 0;
  4  p_err_msg    VARCHAR2(64);
  5  sup_name    VARCHAR2(100);
  6  abend_error  EXCEPTION;
  7  procedure x( a varchar2, b out number )
  8  is
  9  begin
 10          b := 0;
 11  end;
 12  BEGIN
 13   sup_name := 'ACCENT';
 14  DBMS_OUTPUT.PUT_LINE('CSS Supplier Data Extract: ' || SYSDATE);
 15  /* Get today's new accounts */
 16   x(sup_name,:ax_exit_code);
 17  DBMS_OUTPUT.PUT_LINE('NEW ACCOUNT EXTRACT COMPLETE');
 18  end;
 19  /
CSS Supplier Data Extract: 30-NOV-05
NEW ACCOUNT EXTRACT COMPLETE

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
 

question on

sa, April 03, 2006 - 4:18 pm UTC

we have two databases .
I have package.procedure called pkg1.proc1 in sch1.datbase1 returns the pl/sql table. I called pkg1.proc1 from sch1 returned 30 rows.


I created another package in database2 say pkg2.proc2 and calling the procedure1 of database1 as pkg1.proc1@database1 . I complied sucessfully , but the procedure is returning o rows from the pk1.proc1.
Could you tell me why it is returning 0 rows. Where as if i pass same values to and excuted from the database1 it is returning 15 rows .
Thankl you


sat, April 03, 2006 - 4:44 pm UTC

Again the pkg1.proc1 is returning rows if I executed outside of the database2. but returning no rows if i the call from procedure2 of database2.

Ananymous Block in a Package!

Reader, April 17, 2006 - 5:52 am UTC

Hi Tom,

Small doubt, Can a ananymous block be a part of package body. is it valid ?

Thanks


Tom Kyte
April 17, 2006 - 8:10 am UTC

only if you executed it dynamically

begin
...
execute immediate 'begin null; end;'; /* anonymous block... */


but I could argue that the anonymous block isn't really part of the package body at that point anymore, it is just an anonymous block - not associated with any top level object in the dictionary. (only somewhat indirectly in this case)

global variable in package

omer, June 03, 2006 - 7:08 am UTC

Hi!
i have declared a variable (l_check varchar 2 default NN) in specification of package named pkg_cm and assigning it's value in same package body like
if v_lc = 'F' then
l_check := 'YY';
else
l_check := 'N';
end if;
i am using this variable in other package body. e.g.
if pkg_cm.l_check= 'YY' then
--
else
--
end;

but sometimes not everytime , it shows that it has default value although i have assigned it YY (it shows me that YY has been assigned it in pkg_cm body) but after when this variable is referenced in other package body it shows that it has default value NN rather than YY (value should N or YY as only these values are assigned in body).
i am using 9i and java is frontend and all the calls are from java.
plz give me the solution

thanks

Tom Kyte
June 03, 2006 - 8:45 am UTC

PLZ? What do German Postal codes have to do with this? That is totally confusing.


Anyway - the only answer is that the code in the package body HASN'T done that assignment. Sounds almost like you are using a connection pool, getting different sessions every time and hence - you certainly cannot rely on the session state since you grab and release connections.

Could that be "it"

package/procedure in production

amit, June 07, 2006 - 5:23 pm UTC

Hi Tom,
am great fan of your logic and Concepts. Frequently visiting your web site but its first time asking you any query.
Question :
If Package or Procedure is in Production and customers are using these heavily and Now we want to put new version of package or procedure into prodcution.

Is the above scenerio possible without outage. If yes can you please suggest some with examples-
(main consideration here is that somebody or other is always using these procedures/packages and they have very long running queries inside them)

Thanks


Tom Kyte
June 07, 2006 - 5:39 pm UTC

You will have some downtime

You will be trying to replace code that is actually running, consider the implications here.

how to get the list of procedures/functions refering the particular table

Sridhar.S, November 03, 2006 - 7:48 am UTC

Hi Tom,

Is there any data dictionary tables or any other ways to find the list of procedures/functions(in a plsql package) referring the particular table i.e. what I mean to ask is "any table(or a method) which gives relationship between procedures/functions and the referred tables list"???

Thanks for your time.

Tom Kyte
November 03, 2006 - 10:44 am UTC

packages refer to tables.

procedures (top level, standalone procedures) refer to tables

functions (top level, standalone) refer to tables.


there is nothing tracked at the "procedure/function WITHIN a package" level at all.

I guess Sridhar wants this..

A reader, November 03, 2006 - 7:43 pm UTC

Hi Sridhar,
If you want to know the dependency model, then you should try DBA_DEPENDENCIES/USER_DEPENDENCIES/ALL_DEPENDENCIES Data Dictionary Table. Depending on the privs you have.

That will give you the idea.

Hope it helps. Cheers.

Tom Kyte
November 04, 2006 - 12:25 pm UTC

they were pretty specific I thought:

.... to find the list of
procedures/functions(in a plsql package) ....


there is no such thing for the procedures and functions IN a plsql package.

thanks for your response

Sridhar.S, November 08, 2006 - 1:36 am UTC


Hi,
Thanks for your response.
USER_DEPENDENCIES/ALL_DEPENDENCIES data dictionary tables only give the list of tables referred by the package, but my question was specific to the functions/procedures inside the package, which Tom replied as "not possible".



A reader, November 13, 2006 - 11:35 am UTC

Package variables:

Why are they not directly accesible from SQL, what is the reasoning behind it. Following example suggests SQL intialises the package but why was it decide not to access these variables directly from SQL (Select, DML)

/* Formatted on 13/11/2006 15:19 (Formatter Plus v4.8.7) */
CREATE OR REPLACE PACKAGE mypack
AS
num1 NUMBER DEFAULT 10;
FUNCTION f1
RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY mypack
AS
PROCEDURE info_module (
mod_name IN VARCHAR2
, mod_action IN VARCHAR2
)
AS
BEGIN
DBMS_APPLICATION_INFO.set_module ( SUBSTR ( mod_name, 1, 48 )
, SUBSTR ( mod_action, 1, 32 ));
--- desc v$session dbms_application_info
END info_module;

FUNCTION f1
RETURN NUMBER
IS
BEGIN
RETURN 7;
END;
BEGIN
info_module ( 'MYPACK', ' Initialising ' );
END;
/

Tom Kyte
November 14, 2006 - 4:07 am UTC

In plsql, they most certainly are available in SQL. Not sure what you mean.

A reader, November 14, 2006 - 5:09 pm UTC

Sorry,

What I meant was if I do

Select package.variable from dual

I get an error "package.variable" undefined.

Its the same if I do it in an DML statement

The question is why?

Tom Kyte
November 15, 2006 - 6:52 am UTC

begin
select package.variable into l_something from dual;
end;
/


that works dandy (but obviously is silly, just use an assignment in real life).

plsql functions are visible to sql in all cases, the rest of "plsql" is not. It is just the way it works.

sql is "lower level" than plsql

ayan mukherjee, November 17, 2006 - 2:10 am UTC

Hi,

I would like to know if it is possible to add,modify or remove a single procedure from a package which was n number of procedure compiled. I do not want a recompilation of the entire package.

Your suggestion would be very helpful.
Thanks

Tom Kyte
November 17, 2006 - 3:22 am UTC

you have to do that which you say you don't want to do, it is the only way.

trigger and package

A reader, November 27, 2006 - 2:13 am UTC

why oracle do not behave trigger just like package in the part of shared pool usage (that trigger always come to hard parse)

Thanks.

Tom Kyte
November 27, 2006 - 7:59 am UTC

the trigger does NOT hard parse.

sql is hard parsed the first time it is executed, a trigger does not cause hard parses to happen more often.


where do you see that????

Alexander the ok, November 27, 2006 - 12:47 pm UTC

I'm sure he means that sql inside of triggers is always soft parsed. It will not parse once, execute many inside a trigger.

Tom Kyte
November 27, 2006 - 7:42 pm UTC

he said HARD, explicitly - I'll wait for them to say what they mean.

trigger behavior

Rudi, November 28, 2006 - 1:32 am UTC

I am sorry with above question. This is my clarification.

You said that SQL executed in trigger is cached for the duration of triggering statement.

What I mean with my question above is that Why oracle do not employ the technique as
the do to SQL executed in a procedure/package - cached for the life of the session.


Many thanks.

Tom Kyte
November 28, 2006 - 7:17 am UTC

because they didn't - but it is slated as an optimization in a later release.

It goes back to the fact that triggers didn't used to even be stored compiled - they were not "named compiled objects" - they were named anonymous blocks that would get compiled the first time they were used and not found in the shared pool.

It is there history of being an anonymous block

Thanks Tom

A reader, November 30, 2006 - 12:14 am UTC

Do I get your point:

In the database procedure/package stored as named compiled object in page of 4k chunks and trigger stored as anonymous compiled object in page of 4k chunks.

Thanks.

Tom Kyte
November 30, 2006 - 9:50 am UTC

it is put into the shared pool in chunks, it is not necessarily compiled that way.

to find out the contents of package body,

A reader, February 26, 2007 - 6:08 pm UTC

The user A has access to ALL_SOURCE view and has execute privilege on the package owned by a different user B.

If this user A wants to see the contents of the package body owned by user B, how to get that?

user A does not have read access to DBA_SOURCE nor he does not have CREATE ANY PROCEDURE privilege.

Thanks,
Tom Kyte
February 27, 2007 - 10:20 am UTC


they would have to

a) be granted access to DBA_SOURCE which doesn't have any filters on what can be seen

b) have create/debug ANY procedure (very bad idea, avoid the ANY privileges)

c) have a view created for them against DBA_SOURCE that provided this access

d) user B provide them a stored procedure that shows this information.

here is a demonstration of (d)

it lets A see ALL of B's code

ops$tkyte%ORA10GR2> create user a identified by a;

User created.

ops$tkyte%ORA10GR2> create user b identified by b;

User created.

ops$tkyte%ORA10GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create session, create procedure to b;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> create or replace package my_pkg
  2  as
  3     procedure p;
  4  end;
  5  /

Package created.

b%ORA10GR2> create or replace package body my_pkg
  2  as
  3    procedure p is begin null; end;
  4  end;
  5  /

Package body created.

b%ORA10GR2>
b%ORA10GR2> grant execute on my_pkg to a;

Grant succeeded.

b%ORA10GR2>
b%ORA10GR2> create or replace procedure show_my_code( p_name in varchar2, p_cursor in out sys_refcursor )
  2  as
  3  begin
  4          open p_cursor
  5          for
  6          select text
  7            from user_source
  8           where name = p_name
  9           order by type, line;
 10  end;
 11  /

Procedure created.

b%ORA10GR2> grant execute on show_my_code to a;

Grant succeeded.

b%ORA10GR2>
b%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> select text from all_source where owner = 'B' and name = 'MY_PKG' order by type, line;

TEXT
-------------------------------------------------------------------------------
package my_pkg
as
   procedure p;
end;

a%ORA10GR2> variable x refcursor
a%ORA10GR2> exec b.show_my_code( 'MY_PKG', :x );

PL/SQL procedure successfully completed.

a%ORA10GR2> print x

TEXT
-------------------------------------------------------------------------------
package my_pkg
as
   procedure p;
end;
package body my_pkg
as
  procedure p is begin null; end;
end;

8 rows selected.


option d worked,

A reader, February 27, 2007 - 10:52 am UTC

The demonstration you provided worked for me. However, it does not work to see the contents of the package owned by user C. In that case may be the same script you provided should be in schema C too.

I tried option c but it didn't work.

I tried by saying on user A(who has access to view dba_source): create view pkg_source as select * from dba_source where owner='A';

grant select on pkg_source to b;

Logged in as user B, the view did not work. Anything I am missing here?

Thanks,

Tom Kyte
February 27, 2007 - 11:08 am UTC

the stored procedure is a definers rights procedure.

it queries the USER_SOURCE table.

user_source will be the user_source set of data the OWNER of the procedure would see


for A to see B and C's code - both B and C would need to own their own copy of that routine, yes.


"the view did not work"

neither did my car this morning - anything I'm missing ? It always used to work, why didn't it work?


Nope, I will not tell you any error messages, give an example, explain what "did not work" means exactly.....

;)

dba_source

A reader, February 27, 2007 - 11:46 am UTC

About creating view, I am getting the following error even though the user I have logged in has DBA role.

SQL> create view pkg_source as select * from dba_source ;
create view pkg_source as select * from dba_source
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> desc dba_source
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

Thanks,
Tom Kyte
February 27, 2007 - 1:56 pm UTC

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

views are like procedures, you need a direct grant.

To: A reader

Michel Cadot, February 27, 2007 - 1:07 pm UTC


From SQL Reference, Prerequesites section of CREATE VIEW page:

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Regards
Michel

Procedure issues

Dilip, March 07, 2007 - 7:17 am UTC

Hi Tom,
I am calling a procedure from a unix script,
1) connecting to oracle by using sqlplus
2) spooling the file.
3) executing the procedure by using exec procedure name.
4) spool off
5) exit
spooling i need to fetch the successfull message and based on that i will do some other job.

But recently I ran the script, it displays on the SQL screen "PL/SQL procedure successfully completed.", but if I see the log there I find some table space related error
"Oracle error is -1654 - ORA-01654: unable to extend index MICWH.CURRENT_CONTRACT_INFO_XBM02 by 65 in tablespace USER_INDEX06
Procedure Abnormally Terminated at 03/05/2007 21:07:50 "

Could you please explain is this normaly happens.

Thank You.

Tom Kyte
March 07, 2007 - 10:46 am UTC

i have a feeling you have a

when others
then dbms_output.put_line( sqlerrm );
dbms_output.put_line( 'Procedure Abnormally Terminated at ' ||
to_char( sysdate, '....' ) );
end;


don't you - meaning YOU MADE THE ERROR BECOME....

not an error.

Procedure issues

Dilip, March 08, 2007 - 8:27 am UTC

Thanks Tom, It is correct. In procedure exception handling its handled.
Thank you
Tom Kyte
March 08, 2007 - 10:52 am UTC

THE DREADED WHEN OTHERS THEN NULL

strikes yet again............



new 11g feature coming: code that has:

when others
.... <no raise, no raise_application_error> ....
end;


will generate a COMPILE TIME WARNING!!!

PL/SQL Warning

Michel Cadot, March 08, 2007 - 11:44 am UTC


But how many enable PL/SQL compilation warnings?
Those who never use "when others then null".
sigh!

Regards
Michel

PL/SQL Warnings

A reader, March 08, 2007 - 12:47 pm UTC

I didn't even know they existed. I've a look at the docs on them and wondered if it was possible to surpress a particular warning being generated at a certain point in the code a la the @supress other languages tend to support.

The only reason I say is that some of the suggestions I see it giving me for some PL/SQL code are wrong. Specifically it says there are type conversions happening that I either don't think are or have no impact.

For Example

SQL> create table mike as select 1.5 a_number, 'Some Test' data from dual
  2  /

Table created.

SQL> create or replace procedure mike_test as
  2    A_BINARY_INTEGER constant binary_integer := 10;
  3  begin
  4    update mike
  5    set a_number = a_number + A_BINARY_INTEGER;
  6  end mike_test;  
  7  /

SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE MIKE_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/29     PLW-07202: bind type would result in conversion away from column
         type

SQL> select * from mike;

  A_NUMBER DATA
---------- ---------
       1.5 Some Test

SQL> exec mike_test;

PL/SQL procedure successfully completed.

SQL> select * from mike;

  A_NUMBER DATA
---------- ---------
      11.5 Some Test


The warning implies that I'll turn my number into an integer at some point (and so lose the decimal element) but clearly I don't.

A function for sysdate?

Steve, March 15, 2007 - 4:30 am UTC

Tom,

What is your opinion on using a stand alone function to replace calls to sysdate. That would enable us to alter the date returned in our test environments simply by replacing the function with something that, for example, returns sysdate+1. Would you use such a function? If yes would you still put it in some sort of utility package? Would there be performance issues in a heavily OLTP system?

Thanks

Steve

Tom Kyte
March 15, 2007 - 12:09 pm UTC

there is a cost in calling PLSQL from SQL - so, if you do that, do a two step:

l_date := your_sysdate;
insert into t values ( l_date ....);


it'll be hard to get every reference to sysdate everywhere (tedious is more likely the right word) - the overhead of a function call however in regular plsql code will be nominal. it will add to the runtime (obviously - well, until 11g :) but would likely be so fast as to not be noticed in all but the extreme cases.

PLW-07202

A reader, March 29, 2007 - 9:10 am UTC

The warning alerts you to a possible performance issue, not a possible "loss of data".
http://www.psoug.org/reference/dbms_warning.html




Tom Kyte
March 30, 2007 - 12:55 pm UTC

I'm afraid I don't understand what you were trying to point out?

Quick question on create syntax

Stephan, March 30, 2007 - 5:50 pm UTC

Hey Tom,

I haven't been able to find a definitive answer anywhere - is there any difference between

CREATE PACKAGE FOO AS...

and
CREATE PACKAGE FOO IS...


It seems to me that they are used interchangeably, but I just don't know for certain. Does it matter which I use?
Tom Kyte
March 31, 2007 - 12:48 pm UTC

they are the same, I like to use is and as from a "it sounds better" perspective.


create or replace function foo return number
AS
.....


versus

create or replace package body my_pkg
AS

function foo return number
IS
.....


that is, AS after CREATE, IS when there is no create..

Just "sounds" better - but they are entirely interchangeable.


OK

Rajkumar, March 05, 2008 - 7:16 am UTC

Hi Tom,
If I make a call to a packaged procedure,
whether the entire package gets loaded into memory
or that specific procedure alone?
Tom Kyte
March 05, 2008 - 8:11 am UTC

the package is loaded - in chunks, about 4k in size chunks.

but PLEASE do not let that in any way shape or form steer you away from packages - they ARE the thing you should use, standalone procedures/functions should be avoided in most all cases.

packages

mark, May 13, 2008 - 5:50 pm UTC

Tom:

I have a few standalone procedures that log errors, print exception blocks (ie print_no_data_found, print_invalid_login) etc.

Do you recommend putting all of those too into packages.
Also, functions for computing stuff.

What would you use standalone procedures for (just tests).


Tom Kyte
May 14, 2008 - 10:30 am UTC

always use a package for real code.

Package vs Procedures

Maverick, June 13, 2008 - 12:03 pm UTC

Tom, have a couple of questions related to Packages and how they are loaded in memory.

Oracle Version 9i. Calling some procedures from reports [different reporting tool]. For each report we have a procedure [that return data in ref cursor]. Most of the reports are identical [may be a change in one parameter or change in one where condition etc..]. We are planning to move all these procedures into one single package.
Package1
proc1; --for report1
proc2; --for report2
proc3; --for report3


But If report 1 is called , package1.proc1 is loaded in Memory, also, package1.proc2,package1.proc3[eventhough report 1 and report 2 are not called].

1)Is my understanding correct?
If so,
2)Is there any way to hint packages to load only called procedure?
3)Is the behaviour same in Oracle 10g?
4) Am I better of leaving them as seperate procedures?

Thanks,
Tom Kyte
June 13, 2008 - 1:20 pm UTC

do not worry about this, do not miss the forest for the trees.

Your goal is to develop well formed, logical, modular bits of code. You will use packages to clump together related bits of code - things that belong together.

bear in mind only ONE copy is loaded into the SGA and everyone shares it - so the fact that your session isn't calling proc1, but only calls proc2 is OK since some other session will invoke proc1 and will use the copy that was loaded into the SGA.

Now, if you are taking 1,000 sort of unrelated bits of code and putting them into a single package - that would not make sense - it is not a logical, well formed, thought out, modular grouping of procedures based on use - but just a "let's put them all together here".

Use your developers sense here - if you didn't think of these as stored procedures, if these were java routines or C routines or VB routines - what ones would you put into the same class/source code file.

Package vs procedure

Maverick, June 13, 2008 - 4:10 pm UTC

Thanks Tom, I'll put these three or four procedures all together in a package as they wil be called by some other user in another report. So it's good to have them in SGA already.

Also, when this package is loaded in memory, I read in previous questions that chunks of 4K are loaded and not entire package. If package is less than 4K then it's loaded entirely otherwise only 4K chunk is loaded at a time. So, If my procedure spawns more than 4K, wouldn't it be loaded in its entirety or Did I miss the point? Can you please clarify?

Thanks for all your help
Tom Kyte
June 16, 2008 - 11:22 am UTC

it is loaded in 4k, bite sized chunks so it doesn't need a large amount of contiguous memory. It is all loaded, just in bite sized pieces.

Amritansu, June 25, 2008 - 10:41 am UTC

Both subprograms and the packages are loaded in memory when we call them. This means that successive calls to the same object reduces I/O. (Kindly correct me if I am wrong).

Considering these facts how can we say which one is better. A subprogram or a package?


Tom Kyte
June 25, 2008 - 11:44 am UTC

A package is always better

no if and or buts about it.


read this page, we sort of discussed that.

A reader, June 26, 2008 - 7:53 am UTC

What happens if all the code it currently needs has been loaded in the first(or next few) chunks, will it STOP the next chunks being loaded?
Tom Kyte
June 26, 2008 - 4:10 pm UTC

see, I hesitate to answer this...
why?

Someone will see it and grab onto the answer forever as the reason not to use packages. And I will have failed.

Here is my answer and I'm sticking with it:

Always use a package.

Never use a standalone procedure except for demos, tests and standalone
utilities (that call nothing and are called by nothing)

A reader, June 26, 2008 - 4:19 pm UTC


Recursion calls to a function/procedure

V.Hariharaputhran, July 27, 2010 - 1:33 pm UTC

Mr.Oracle,

What is the best method to perform recursive calls to the same procedure/fuction i.e, a fuction calling the same function.

Example

CREATE OR REPLACE FUNCTION FN_REC (in_number IN NUMBER)
RETURN NUMBER
AS
BEGIN
CASE
WHEN in_number = 1 THEN RETURN 1;
WHEN in_number = 2 THEN RETURN 1;
ELSE RETURN (FN_REC (in_number - 1) + FN_REC (in_number - 2));
END CASE;
END FN_REC;

The above case is much simple, what would be the case where in, if we happen to store result of each function call and at the end return the entire result.


Please let me know the best way to perform recursive procedure calls. What care one should take with respect to locking while performing such recursive call, when the procedure has DML Statements.

Regards
V.Hari

Tom Kyte
July 28, 2010 - 7:32 am UTC

... What is the best method to perform recursive calls to the same
procedure/fuction i.e, a fuction calling the same function.
...

there is only one way to recursively call yourself. You call yourself - done. There is no 'best way', there is "the way"


... The above case is much simple, what would be the case where in, if we
happen to store result of each function call and at the end return the entire
result. ..

I could not parse that sentence. "if we happen to store each result of each function call.." - I don't know what that means - that sentence.


locking is not relevant when it comes to recursion. What would be "locked"? It is just code calling code. If your transaction locks a row - your transaction owns that row, recursion isn't relevant to the discussion. We don't care of procedure P tries to lock row Y 5,000 times or if 5,000 different procedures try to lock row Y 5,000 times - as long as they are in the same transaction.

packages

A reader, July 28, 2010 - 7:45 am UTC

Excellent!

i said

SSG, December 21, 2010 - 5:38 am UTC

Package is the best because once if you complied the package means when ever where ever you called that packagfe it won't recompile again but for procudure each and every time when you call the standalone procedure it will complire, so if you use the package means the performance will increase.
Tom Kyte
December 21, 2010 - 7:13 am UTC

... but for procudure
each and every time when you call the standalone procedure it will complire ...

that is 100% false, that is entirely false, there is nothing true in that statement.

Where did you ever get such an idea?


But - still - always use a package, but not for this reason. If you state this as a reason, someone who knows you are wrong will tend to ignore your advice.

Package

Mahesh, December 21, 2010 - 11:59 pm UTC

Applications gradually becomes like octopus then eventually like a hairball. Packages helps it to keep it logically together.

how to get the procedure feedback

jiang huang zheng, January 07, 2011 - 10:47 pm UTC

Hi Tom,

Could you please tell me how i can achive this:

I want to have the feedback of a procedure how many rows are updated by this procedure:
create or replace procedure test (loc in number,uid_low in number, uid_high in number) as
begin
update image set need=null where image_loc=loc and image_id between uid_low and uid_hig and need='YES';
end;
I expect to see 5 rows updated,but when I run in the sqlplus,no such output. Is there a way to achive this?

Thank you!
Tom Kyte
January 08, 2011 - 12:39 pm UTC

add

dbms_output.put_line( sql%rowcount || ' rows updated.');

right after the update. Then make sure you have issued:

set serveroutput on size unlimited

in sqlplus before running the stored procedure (i put that set command in my login.sql so it is always there)

Procedures

Brian McGinity, January 08, 2011 - 5:58 pm UTC

I think I’ve found the one place where procedures actually are better than packages.

For the last 10 years I’ve been building web applications using the web toolkit. I’ve written countless packages which produce web output with “htp.p” etc…

Lately I’ve switched to PSPs (Pl/Sql Server Pages) . Each PSP translates into a procedure. This is so much nicer than writing packages with htp.p all over the place.

The code library is much easier to manage and the modularity of each procedure is actually a benefit. Because of the nature of the web environment (ie. each procedure produces a single web page) it’s possible to change a single web page without taking down all the pages served by a package.

I use packages for shared library code so my procedures are not calling other procedures.

All in all, PSPs are so much nicer.


Tom Kyte
January 10, 2011 - 7:39 am UTC

but - what if the psp thing generated a packaged procedure rather than a stand alone procedure?

This doesn't equate to "procedure better than package", in this case you are NOT writing any code and there would be no difference in your life if it was a packaged procedure or a standalone procedure (other than the URL would be different).

Since nothing calls these procedures EXCEPT a URL - there are no dependencies in the database. Net - a tie, either a package or procedure would be ok

Since you don't write this code, it is generated, the modularity bit doesn't hold any weight. Net - a tie

You have cluttered up your namespace - now you need a procedure per page and they have to have unique names and they cannot be used (their names) for anything else. Net - a package with a couple of related pages would be nice

Overloading - doesn't really matter for a web page. All of the inputs are always character strings Net - a tie

Session variables - doesn't really matter for a web page, session state is reset on each call. #Net - a tie

Good coding techniques - doesn't apply in this case, it is generated code after all. #Net - a tie


Net-Net - a package would either tie with a procedure in implementation, or a package implementation would one up a procedure because of the reduced name space clutter


All in all, PSPs are so much nicer.


And APEX - http://apex.oracle.com/ - makes PSP's look like the dark ages...

Thanks!

jiang huang zheng, January 08, 2011 - 11:25 pm UTC


Brian McGinity, January 10, 2011 - 11:08 pm UTC

Tom, thanks for your help regarding PSPs, packages and procedures.

When you referred to PSPs as a “thing” it took me off guard as if you may not be familiar with it (which most likely isn’t the case).

PSP is an Oracle product: http://www.oracle.com/technology/sample_code/products/ias/files/psp/index.html

It’s actually a great product for writing very customized web pages. I’ve built a nice framework with it and now have powerful tool for rapid development.

It would be nice if Oracle invested some R&D and expanded PSP’s capabilities. Of course every time Oracle enhances PL/SQL, this enhances PSP too (associative arrays with varchar2 keys really helped).

I’m also well versed in ColdFusion and in many ways prefer PSP over CF. It’s been a while since I’ve messed around with apex. I will check it out again.


Tom Kyte
January 11, 2011 - 6:00 am UTC

I know what it is - I've used it.

It is like the dark ages compared to APEX. I doubt the existing PSP infrastructure will change at all in the future.


Packages Inside a Second package

Sachin, January 12, 2011 - 12:30 am UTC

Hi Tom,
One tricky question :
Can we have one package spec(A) inside other package spec(B)
like wise for package body .
Thanks in advance for this one.
Regards,
$achin
Tom Kyte
January 12, 2011 - 10:52 am UTC

no

packages

A reader, January 12, 2011 - 11:00 am UTC

Excellent

execute procedure within package privilage

Mohammed Matar, January 23, 2011 - 3:45 am UTC

Tom,
I have a package that has procedures related to other.
I want to grant some users execute on some of those procedures, and others execute on other procedures.
Say
create package holiday_pkg as
procedure ask_holiday(emp_no in number, period in number, start_date in date);
procedure approve_holiday(holiday_seq in number);
end;

I want user_employee to have the privilege to execute ask_holiday and not to execute approve_holiday. user_manager to have the privilege to execute both procedures.

Would you tell me the best way to do that?
Thanks
Tom Kyte
February 01, 2011 - 9:45 am UTC

You cannot do that, you would have to break the package into two.

A reader, March 23, 2011 - 11:06 am UTC


Number of procedures and functions in package

adinewbie, December 02, 2011 - 10:23 am UTC

Hi,
Is there any way that we can determine the number of procedures or functions to be added in a package where the performance would not be impacted.
Tom Kyte
December 06, 2011 - 11:06 am UTC

the number of functions/procedures in a package doesn't really have any bearing on the performance directly.


You should be looking at the overall code complexity - if there are hundreds/thousands of procedures in a package - could you, as a coder, as a human being - really get your head around it?

Use good old fashioned modular design coupled with good old fashioned common sense and develop code that is maintainable, understandable - code you would be proud to put on a projector and show to 100 people.

Package storage in buffer

Ramamoorthy.D, March 23, 2012 - 5:27 am UTC

Hi,
How will trace the packages are one time load in buffer for that first time execution.Is there any query to trace it?

Thanks
Ramamoorthy.D
India
Tom Kyte
March 23, 2012 - 8:31 am UTC

I do not know what you mean.


packages with parameters

Ramamoorthy.D, March 23, 2012 - 5:33 am UTC

Hi,
1) Please tell why not packages will not having parametere if any specific reason.
2)Hi,

I want to call oracle stored procedure in package.I used like this returns error.

Call PKG.GET_EMPLOYEES()

Then i remove the procedure from package and defined outside the package as global procedure .Then it works

Call GET_EMPLOYEES()

So how to call procedure with package name. Please help

Ramamoorthy
chennai
Tom Kyte
March 23, 2012 - 8:34 am UTC

1) they will have parameters if you code them to have parameters.

they will not have parameters if you code them to not have parameters.

it is totally up to you and the design of your code.


2) my car won't start.

why not? we both have the same amount of information here - basically "none". No example, no errors, no symptoms, no way for me to reproduce, nothing.

so, help me out here - take this example and make it "not work" to simulate the issue you are having, then we can discuss.

ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3          procedure foo;
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3  
  4  procedure foo
  5  is
  6  begin
  7          dbms_output.put_line( 'hello world' );
  8  end;
  9  
 10  end;
 11  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> call my_pkg.foo();
hello world

Call completed.

ops$tkyte%ORA11GR2> 

Why use procedures over function or why use function over procedure

SameerJ, December 13, 2012 - 11:04 pm UTC

Hello Tom,

I am regular veiwer of your comments,
I am big fan of your comments..I have a query,i am not able to figure out this one.
We have procedures and functions in oracle,some time we use functions and some time we use procedures?
can you please explain me, why we use function or procedure, if the working of both are same.
It is confusing for me,when some one ask me why oracle has both if working are same.

Thanks in Advanced,


Tom Kyte
December 17, 2012 - 4:06 pm UTC

you use one over the other because it pleases you mostly.

In Plsql, as in many languages, a procedure is simply a function that doesn't return anything. Or, a function is just a procedure that returns a value.


there are times when you have to use a function - like if you want to call the plsql from sql - but mostly, you choose one over the other when it pleases you.

need your view

Ranjan, May 29, 2013 - 5:03 am UTC

Hi Tom,

The Schema "ASKTOM" has a package named P1.
The schema "scott" has execute permision on asktom.p1.

As above explained by you that because of the encapsulation
scott cant see the asktom.p1 body directly.

But Now below query can show the package spec and body of
asktom.p1 package from scott schema.
################
SELECT DBMS_METADATA.GET_DDL('PACKAGE','P1','ASKTOM') FROM DUAL;
##############
I think still we can say encapsulation coz SCOTT can see it's body by the help of dbms_metadata but cant do operation on asktom.p1 body directly.(its like lion watching a outside goat, inside a cage :)).

But need your view.


Tom Kyte
May 29, 2013 - 7:22 pm UTC

you must have granted scott something more. If B just has execute on A.P1, B cannot dbms_metadata.get_ddl it.


ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> create user b identified by b;

User created.

ops$tkyte%ORA11GR2> grant create session, create procedure to a;

Grant succeeded.

ops$tkyte%ORA11GR2> grant create session to b;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create or replace package p1
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

a%ORA11GR2> create or replace package body p1
  2  as
  3          procedure p
  4          is
  5          begin
  6                  null;
  7          end;
  8  end;
  9  /

Package body created.

a%ORA11GR2> grant execute on p1 to b;

Grant succeeded.

a%ORA11GR2> 
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> select dbms_metadata.get_ddl( 'PACKAGE', 'P1', 'A' ) from dual;
ERROR:
ORA-31603: object "P1" of type PACKAGE not found in schema "A"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1



no rows selected

b%ORA11GR2> desc a.p1
PROCEDURE P

b%ORA11GR2> 

cont to my last post.

Ranjan, May 30, 2013 - 5:44 am UTC

Hi Tom,

When I create schema a and b and did the same as you did above ,I am getting the same.

But it is really strange with asktom and scott schema even asktom.p1 has not a single grant to any one not even execute(thought its becose of your name :)so friendly).
I am able to run the below query from scott(strange) but not from b.
#########
select dbms_metadata.get_dd( 'PACKAGE', 'P1', 'ASKTOM' ) from dual;
##############
I checked the grant for asktom.p and it has not been assigned to any schema.

Could you please tell me,whether I am doing wrong or its something else(even I tried with new package named "ZZZ" and immediately went to scott and able to see using that query)
#######
select dbms_metadata.get_ddl( 'PACKAGE', 'ZZZ', 'ASKTOM' ) from dual;
########

I dont remember what kind of permision I have give to scott schema.

And one more thing the recent schema A I created and unable to access package p1 from schema B but am able to access that from scott (which is a normal user).
without having any permision on it
.

Thanks as always.
Tom Kyte
May 30, 2013 - 2:44 pm UTC

get the full list of everything SCOTT has granted to them. You have a grant in SCOTT that is given them the ability to do this.


query up session_roles and session_privs, you'll see your SCOTT account has more than:

scott%ORA11GR2> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

scott%ORA11GR2> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE CLUSTER
CREATE TABLE
UNLIMITED TABLESPACE
CREATE SESSION

10 rows selected.





see, when scott has the default set of privileges - scott cannot do this:

ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> grant create session, create procedure to a;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create or replace package p1
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

a%ORA11GR2> create or replace package body p1
  2  as
  3          procedure p is begin null; end;
  4  end;
  5  /

Package body created.

a%ORA11GR2> grant execute on p1 to scott;

Grant succeeded.

a%ORA11GR2> 
a%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> desc a.p1
PROCEDURE P

scott%ORA11GR2> select dbms_metadata.get_ddl( 'PACKAGE', 'P1', 'A' ) from dual;
ERROR:
ORA-31603: object "P1" of type PACKAGE not found in schema "A"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1



no rows selected

scott%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'PACKAGE', 'P1', 'A' ) from dual;

DBMS_METADATA.GET_DDL('PACKAGE','P1','A')
-------------------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "A"."P1"
as
        procedure p;
end;
CREATE OR REPLACE PACKAGE BODY "A"."P1"
as
        procedure p is begin null; end;
end;







so, query up your scott user and see what privileges you have given him in addition to CONNECT AND RESOURCE.

con to my last post .

Ranjan, May 30, 2013 - 5:51 am UTC

there is a typo error its not asktom.p its asktom.p1 and
I am testing all in 10g release2.

thanks.
Tom Kyte
May 30, 2013 - 2:47 pm UTC

ops$tkyte%ORA10GR2> create user a identified by a;

User created.

ops$tkyte%ORA10GR2> grant create session, create procedure to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create or replace package p1
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

a%ORA10GR2> create or replace package body p1
  2  as
  3          procedure p is begin null; end;
  4  end;
  5  /

Package body created.

a%ORA10GR2> grant execute on p1 to scott;

Grant succeeded.

a%ORA10GR2>
a%ORA10GR2> connect scott/tiger
Connected.

scott%ORA10GR2> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

2 rows selected.

scott%ORA10GR2> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE CLUSTER
CREATE TABLE
UNLIMITED TABLESPACE
CREATE SESSION

10 rows selected.


scott%ORA10GR2> desc a.p1
PROCEDURE P

scott%ORA10GR2> select dbms_metadata.get_ddl( 'PACKAGE', 'P1', 'A' ) from dual;
ERROR:
ORA-31603: object "P1" of type PACKAGE not found in schema "A"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3149
ORA-06512: at "SYS.DBMS_METADATA", line 4787
ORA-06512: at line 1



no rows selected

scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'PACKAGE', 'P1', 'A' ) from dual;

DBMS_METADATA.GET_DDL('PACKAGE','P1','A')
-------------------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "A"."P1"
as
        procedure p;
end;
CREATE OR REPLACE PACKAGE BODY "A"."P1"
as
        procedure p is begin null; end;
end;



1 row selected.




10g, same thing - you've granted something extra to scott that doesn't belong there.


oh my god ,I was not aware of it .

Ranjan, May 30, 2013 - 6:33 am UTC

Hi Tom,

Finally I found it. I created a new schema C and tried to access your A schema package p1 but failed.

I was just trying to acces auto trace from c and a error popup "grant select_catalog_role and grant select any dictionary".
I just granted thsoe two to schema C and after that am able to access schema A's package p1 :).

could you please tell whether those two grants lead to any
default admin permission so it is able to acces that package even if it has no permision on it.

thanks as always,
Ranjan.
Tom Kyte
May 30, 2013 - 2:48 pm UTC

they do not give you any admin permission, you cannot "do" anything you couldn't before. You can just see more of the dictionary.

suggest you revoke them.

cont to my last post

Ranjan, May 30, 2013 - 4:24 pm UTC

Hi Tom,

What I asked , by granting these below two privilege to any new schema I am able to access other schema's package using
that getddl procedure.
grant select_catalog_role to any_schema
and
grant select any dictionary to any_schema

I tested this in 10g r2 and that is making that access happen.
could you please give these to grants to schema B and check.

Thanks as always
Tom Kyte
May 30, 2013 - 7:36 pm UTC

why don't *you* test? I gave you *everything* you needed!

in any case, all it requires is select_any_catalog role - any privilege that includes the word ANY should be avoided - they give away far too much.


this is fully documented:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#i1016867


select_any_catalog is very powerful.



ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> create user b identified by b;

User created.

ops$tkyte%ORA11GR2> grant create session, create procedure to a;

Grant succeeded.

ops$tkyte%ORA11GR2> grant create session, select_catalog_role to b;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create package p1 as procedure p; end;
  2  /

Package created.

a%ORA11GR2> 
a%ORA11GR2> create package body p1 as procedure p is begin null; end; end;
  2  /

Package body created.

a%ORA11GR2> 
a%ORA11GR2> grant execute on p1 to b;

Grant succeeded.

a%ORA11GR2> 
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> select dbms_metadata.get_ddl( 'PACKAGE', 'P1', 'A' ) from dual;

DBMS_METADATA.GET_DDL('PACKAGE','P1','A')
-------------------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "A"."P1" as procedure p; end;
CREATE OR REPLACE PACKAGE BODY "A"."P1" as procedure p is begin null; end; end;



tahnks you for nice explanation :).

Ranjan, May 31, 2013 - 2:31 am UTC


Recompile upon addition of a new procedure

Geetha Nagarajan, June 06, 2013 - 6:13 am UTC

Hello Tom,

I hope you are doing well!

We have a Forms Application with a lot of PL/SQL code. We are working on upgrading from 10g to 11g Application Server. We have a pll (library in forms, package spec and package body). Added a new procedure to the BEGINNING of the package. (does it matter if we add in the beginning, end or middle in the DB, should we recompile dependencies). The 10g Forms executable does not work anymore in 11g, because of the new addition to the package spec we are forced to compile the code in 11g. Is it because of the addition of a new procedure to the forms package pll?

I wish you would be able to guide me on this Forms/PL/SQL package spec addition. I hope this is relevant to the original question here.

Thanks and Regards,
Geetha
Tom Kyte
June 06, 2013 - 1:43 pm UTC

I have not used forms since 1995. Please utilize the discussion forums on otn.oracle.com or suport.

Package vs Procedure

Rafsha, June 21, 2013 - 11:42 am UTC

Hi Tom,

When I suggested our Oracle developer to use package than procedure, he send me below points. I need your feedback on these points.


Large Size : Package‘s main disadvantages include potentially large size (offset by grouping lots of objects together) and complexity, ordering them with the prior references first , and setting up internal functions that call one another can be a little bit tricky.

Poor Design: The evolution of package was due to one factor, which is package Initialization or Locale Inheritance. It is the way where you initialize local variables, types, collections, cursors of any number which are going to be used or inherited by the procedures or functions of the same package within the program execution scope rather than having each procedure/function its own initialization mechanism with own variables, type etc. So far I got very little situations where it was required to initialize the package in asp.net application. Otherwise package is not more than an encapsulating object in practical.

Loads all the package units when calling sub-program of the package : As contrary to legacy procedure which loads in to database SGA when called, but does not loads dependable objects to cache unless being referred in the code during actual execution, packages load fully in to SGA along with all dependent objects(standalone procedure/functions or packages). That means any call to a 50 line procedure of the package will load all the 2000 line in to SGA and will compile it if instantiated for the 1st time. Even though already instantiated previously it will always load the would package(2000 line) from SGA’s Shared Pool to DB Buffer area. In a situation where more than 20 different modules are being used at a particular time and all the modules refers to 50 different packages then database would require huge amount of memory and the process time to manage the memory for loading and shuffling among the packages in the SGA.

Cache compiled program: Packages when loaded to SGA buffer, it always gets compiled if not instantiated before. For any reason if you have invalidated a package to keep it unusable, any call to the package will make it valid again if no programmatic error exists. Also if any package looks valid, it may get invalid at runtime when loaded to SGA if any dependable object is already invalid or becomes invalid. In such situation any unprecedented error is possible.

Difficult to maintain if package is become huge.

Web application & Package : As we know web applications may be accessed by users ranging from 10 to more than 100, every access to the application will have its own Instance. If web applications are not well programmed then any module may trigger several connections resulting several instances a. Also database keeps any instance alive for several minutes even though its already been closed from the client machine. So at the database end total number of instances would become more than 200 if 100 users are accessing asp.net application. In such a situation instantiating packages, compiling them to cache would require huge amount of memory and system time. Also any shortfall of memory for loading packages in to cache will replace less relevant or unused packages from the memory to occupy the space. Again when removed packages will be called they will also replace some of the less relevant packages and it will become an iterative process at any peak hour of using asp.net application. This will slower the response time of database server to the web server. Contrary to packages, procedures are small in size and load once to SGA and they are always precompiled only once at development time by the developer himself. So instead of loading whole package into memory a standalone procedure will perform better, faster as it does not conflicts with the memory management because of its small size.

Tom Kyte
July 01, 2013 - 4:45 pm UTC

a) you group related functions together. Look at the DBMS_SQL package for example. Or UTL_FILE. Or whatever.

All of that code needs to be available for that functionality to work. What would be better - having 50 small individual procedures that all need library cache entries, taking up more room in the library cache (and remember - you need all 50 loaded anyway.. regardless of whether they are in a package or not - they are all related functionality - they represent your application).

So actually - the large size is true of STANDALONE PROCEDURES, not true of packages. And plsql code is loaded up (sort of like paging in) in pieces. It doesn't consume nor need a contiguous chunk of memory.

But basically - your developer has this backwards - by using many small standalone procedures they are consuming many orders of magnitude more entries in the library cache, the shared pool, etc. They have polluted, literally flooded the shared pool with lots of tiny things, all that have to be managed.


Also, in the RARE RARE RARE instance where you need to, you can forward declare functions in PLSQL, just like you can in pretty much any robust 3gl programming language. But for a software developer (seriously) to say that this is going to be a problem??? really???

and setting up internal functions that call one another can be tricky? Ummmmm. I don't even know what to say (other than *modular design*, *bite sized pieces*, lots of little bits of code, things that fit on your screen from BEGIN to END (seriously, if you have a subroutine that does NOT fit on your screen, you need to refactor it).


b) poor design?????????????? you gotta be kidding me, you honestly cannot say that with a straight face. The evolution of the package was one of modularity, namespace control (just try to understand 100 standalone functions that interact with each other and probably DO NOT fit on a screen because of the "standalone function" mentality).


Stop with the inheritance word, plsql, ada before it, pl/i with libraries before it, many many hundreds of languages before it all used packages or some concept of "these go together in a compilation unit"

the package construct was designed so we wouldn't go crazy with thousands of tiny snippets of code floating all around (each of which cannot have the same name of course which leads to some really interesting names because of the namespace conflict)

Otherwise package is not more than an encapsulating object in
practical.


OF COURSE! that is the *point*. You group related functionality together in tiny bit sized pieces with good descriptive names (that can be reused since the package NAME is the object in the dictionary, not the 500 routines that go with it).

I don't know what to say - poor design???? really? Has this developer every had to work on a project with more than one developer?


and their thoughts as to how things are loaded are wrong. I addressed that above, there is nothing to be saved memory wise (in fact the OPPOSITE IS TRUE, we have so much more to book-keep with the millions of functions approach, rather than a handful of packages).

and we do not compile at instantiation time.

and we do not load anything from the shared pool to the buffer cache.

c) the entire invalidate thing in production is not a valid argument.

first of all - packages were the way to BREAK the dependency chain in the past. As of 11g - they are not needed for that anymore - but the reality of the situation here is your programmer has it BACKWARDS again. packages BROKE the dependency chain and made it so you invalidated *LESS OFTEN*.

Now, a package dependent on a spec of another package will only go invalid IF an existing subroutines signature is modified. meaning - since you tend to ADD functions to a package and RARELY change the signature of an existing function/procedure (backwards compatibility and all) - it will not go invalid.

Packages *lessen* the recompilation due to invalidation. They do not increase it.


And further more, in production - you just are not making changes every five seconds. You install a patch or an upgrade of your application, you validate the code and away you go. A package going invalid would be a rare thing indeed in production. And remember - the goal of the package is to have tiny bite sized pieces of code. Is your developer coding monolithic subroutines (things that do not fit on the screen now). Their routines are undoubtedly "big" and unwieldy to compile.



d) difficult to maintain?????? come on. Packages make it easier to manage large sets of code because you can have manageable sized routines without worrying about the namespace conflict. and if you spend a few seconds thinking about the design of your code - you will lay out packages of related functionality that have a certain scope to them.

I'm sorry, but packages make code maintenance possible - they do not make it harder.


e) If web applications are not well programmed then any
module may trigger several connections resulting several instances a


where I come from we call that "a bug in the developed code, fix it"


o at the database end total number of
instances would become more than 200 if 100 users are accessing asp.net
application.


that says to me "stop using asp.net if it does such stupid things".

#1 problem in 3 tier applications today:
http://tinyurl.com/RWP-OLTP-CONNECTIONS
http://www.oracle.com/technetwork/issue-archive/2012/12-may/o32asktom-1578049.html


instantiating a package takes no time at all (it is already loaded in the shared pool, we do NOT compile it to instantiate it, we do NOT copy it somewhere - it is shared code just like an operating system would do). If you are not using package variables (good idea not to in a web environment - stateless connection pools) - there is nothing to worry about memory or instantiation wise - there is nothing to "do".



all of this and more has been written many times....

Package

Sam, November 14, 2013 - 4:22 pm UTC


Hi Tom,

When we call package or procedure from some other sub-program,do it compile again. If yes then at what scenario it will compile again?
As we know when we call any proc within the package the whole package load into memory. Which part of the memory it stored.
Is it stored in UGA?

Thanks in Advance..

Always - never say never, always

Rick, September 20, 2014 - 4:32 pm UTC

Devils advocate: stateless wins over state

Packages have state. Stored procedures do not have state.

Packages can be abused - just as much as triggers.
Have seen packages the size of 747's. Very difficult to troubleshoot 747's.
Have seen packages with objects that are executed at different frequency - the whole package is loaded into memory - not optimum.

Nothing is more optimal than a well written ORACLE PL/SQL stored procedure. No reason not to use in production.


huihilhli

u8uyilyuh, November 21, 2014 - 12:30 am UTC

yugjjvjhhb,vgb,jvdsgrdjgyjgkugyukgyuggggggggg

stored procedure

Austin, March 01, 2015 - 5:07 am UTC

We have a requirement in which we are going to get new and changed/updated records from legacy IBM(Db2 tables) systems through a web interface(the logic to determine the new and changed records is done by web interface team) and need to be loaded into Oracle database. The web team asked us to build Oracle Stored Procedures with parameters of the target Oracle Columns so that they can invoke them for Insert and Update on Oracle 11g Release 2. They also have the ability to insert/update them directly through SQL statements. Can you suggest is creating Oracle Stored Procedures is the best approach for these scenarios which will get invoked for every call to insert/update on Oracle Database?

Procs AND packages?!

Joe Bloggs / Anon, August 30, 2016 - 12:40 pm UTC

Hi,

I know this is BAD, but can you tell me just HOW bad?

For legacy reasons, the company I work at has a practice of coding into packages (good), but for almost EVERY public procedure or function, there is a standalone wrapper proc/func (bad bad bad).

Eg:
One package, pkg1:
pkg1.proc1
pkg1.proc2
pkg1.proc3

THREE standalone procedures:
proc1 calls pkg1.proc1
proc2 calls pkg1.proc2
proc3 calls pkg1.proc3

And app code calls the standalone wrapper proc1, not pkg1.proc1 directly.

This practice persists. Some of the legacy code (external to the database) requires a standalone procedure - ok - no choice.

But even new code (not mine) is written following this same practice.

Please could you explain how overall database performance would be improved if all the unnecessary standalone procedure calls were replaced with direct pacakge.procedure calls?

Would this be an excellent target for improving overall database performance?

Thanks
Joe Bloggs
(Don't wanna give any clue as to the company I am working for...)
Chris Saxon
August 30, 2016 - 3:58 pm UTC

The overhead of the extra call is minimal. Calling the packages directly will help. But you're unlikely to notice any performance improvements.

The benefits of calling the packages directly are more to do with saving you time and making your application easier to understand!

Chris

Package & procedure caching

Joe Bloggs / Anon, August 31, 2016 - 8:57 am UTC

Hmm, ok, but...

It's a long time since I read Tom's book (back in Oracle 8 days), but do I recall correctly that packages are cached (in the UGA?), whereas procedures are NOT cached.

If so, surely the lack of caching with standalone procedures has some performance impact?

Maybe I have mis-remembered, or things have moved on...

(Oh, btw, clicking the 'Preview' button for this and my last post gives/gave me an NDF error; submission obviously works ok though.)
Connor McDonald
September 01, 2016 - 2:37 am UTC

Thanks for the feedback on "Preview", we'll check it out.

In terms of memory, the management of procedures and packages will be the same (notwithstanding the additional capabilities of global package variables and the like)

This doesnt change my recommendation that *everything* goes into a package :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library