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.
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
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.
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.
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 ????
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
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
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
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?
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.
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
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
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.
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,
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?
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?
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
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.
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
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
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
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..
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.
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.
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?
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!
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 dont 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.
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.
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!
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!
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..
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
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
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??
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.
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
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
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.
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,
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
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
April 29, 2005 - 6:41 pm UTC
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
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,
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!
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?
May 02, 2005 - 2:05 pm UTC
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 ??
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
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
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
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??
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.
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"?
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...
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.
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
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
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
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.
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.
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;
/
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?
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
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.
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.
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.
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.
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,
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,
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,
February 27, 2007 - 1:56 pm UTC
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.
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
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
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
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?
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?
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).
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,
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
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?
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?
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
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.
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!
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.
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 okSince you don't write this code, it is generated, the modularity bit doesn't hold any weight.
Net - a tieYou 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 niceOverloading - doesn't really matter for a web page. All of the inputs are always character strings
Net - a tieSession 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 clutterAll 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.
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
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
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.
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
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
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,
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.
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.
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.
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.
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
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
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.
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 awhere 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...)
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.)
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 :-)