Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Gunjeet.

Asked: January 31, 2001 - 9:19 pm UTC

Last updated: December 13, 2018 - 4:42 am UTC

Version: v8.1.6

Viewed 10K+ times! This question is

You Asked


Hi Tom,

Are there any issues with issuing a "create or replace procedure"
command when the application that uses the database
is up and running in a production environment ?

What happens when you fire this script when this
same procedure is acutually getting executed at the same time.

Will it generate any errors ?

I vaguely remember coming across some "resource
unavailable/busy" kind of situation (the creation of stored procedure probably hung for a while) while doing
a similar thing with an older version of Oracle
but can't remember the details.

Please let me know if a copy of the procedure is cached when
it's getting executed and if it's OK to recreate
it while it's getting executed.

Thanks,

Gunjeet





and Tom said...

YES -- big time. You can really get yourself in a crunch. You (doing the create or replace) will have to wait for the current executions to finish -- new executions have to wait for you to finish. If your procedure can take a long time -- it can make it look like a database hang.

I never replace code like that during "up hours". It is like replacing a binary in the file system -- you have to wait until people are not running your program to replace it (else they will all get page faults). If you use packages with stateful variables, there can be other side effects from the apparent hang - errors such as "existing state of packages discarded" -- causing them to "restart".


You might get the resource busy/unavailable after a while if you were not able to get the lock -- but remember other people have been waiting with you as well.

I would avoid it if at all possible.

Rating

  (15 ratings)

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

Comments

How to identify objects that are being executed

Logan Palanisamy, April 18, 2003 - 12:17 pm UTC

Tom,

Is there an easy way to find out if the stored procedure that we are trying to re-create is in fact being used or not at that moment?

Is V$ACCESS the right table to get this information? or are there better ways?

Thanks

Tom Kyte
April 18, 2003 - 12:41 pm UTC

v$access would be appropriate. dba_ddl_locks can also be useful to see who is "blocking" a create or replace.

(hopefully you use PACKAGES and not stored procedures!)

Stored Procedures

Logan Palanisamy, April 18, 2003 - 1:45 pm UTC

Tom,

Thanks for your quick and great answer.

Yes. We always use packages. Needless to say, I am a regular reader of this forum.

I used the term 'stored procedures' to collectively mean functions, procedures and packages. That's all.



Tom Kyte
April 18, 2003 - 3:51 pm UTC

just checking ;)

"adding" new procedures/functions to existing packages possible?

A reader, April 19, 2003 - 1:42 am UTC

Is it possible to just "add" new pocedures and functions to a package, without creating or replacing the whole package/package body all over again?

Tom Kyte
April 19, 2003 - 12:05 pm UTC

you can add new procedures and functions to a body -- and not change the spec.

But, in order to add new externally exposed functions/procedures, you need to recompile the spec.


And you either compile a body -- or not (same with a spec). You cannot "alter" it and add some code, you have to submit the code.

Sort of like "you either compile a source code file -- or not" in a 3gl.

would be nice to have

A reader, May 06, 2003 - 2:13 pm UTC

Not necessary an enhancement request, but would it be nice to have something like

alter package this_pkg add procedure proc
or
alter package this_pkg add function func

as a mechanism to "recruit" standalone procedures and functions into existing packages? I think it can be done code-wise by oracle (i.e. procedure/function spec goes to package spec; body goes to package body) behind the scenes. Is there a logic reason not to have this?



Tom Kyte
May 06, 2003 - 2:27 pm UTC

why would it be "nice to have"

package must be invalidated
dependent code would have to be changed

what would be the "perceived benefit"


A reader, May 06, 2003 - 10:15 pm UTC

Hi Tom,

Couple of questions:

-Is there any advantage of using packages as against stand-alone procedures in this context? (I konw about the modularity and performance advantages as you have mentioned here earlier. But how does it help recompiling in a production environment?)

--Secondly, I did not understand the following:

"you can add new procedures and functions to a body -- and not change the spec.

But, in order to add new externally exposed functions/procedures, you need to recompile the spec."

Can you please elaborate with an example?

If what I understand correctly, adding a procedure/function to an existing and widely used package in a live environment is a big issue. It involves recompiling the entire package and invalidates all the existing code for a shortwhile. It sounds that this is a big arguement for NOT using packages. For, if we use stand-alone procs/functions then a new proc/function can be compiled without touching any of the existing code. Very curious to know what is your counter arguement to this.

Thanks verymuch for your time.


Tom Kyte
May 07, 2003 - 7:30 am UTC

o this is the MAIN reason for using packages -- to break the dependency chain. You can implement and re-implement the body over and over and over and not affect any dependent objects.

You cannot do that with a procedure

o just add "procedure I_am_only_in_the_body is begin null; end;" to any package body, compile. There you go.



My 2 cents -- if you are adding code, you are adding new functionality -- you are doing an upgrade, you are adding new functionality -- you are doing something major league. You are doing this off hours.

Your argument only applies the first time, marginally. When you fix a bug in that procedure -- it's all over.

There is no reason to use a stored procedure by itself for real production code. simple utilities, standalone things that you call from sqlplus directly (no other code calls it) -- they can be procedures but that is about it.

Even if you take it to the illogical extreme -- a procedure per package -- that is better then a standalone procedure. You can zap the stored procedure in (replace the body) as a bug fix without affecting anything else.

OK

Siva, April 30, 2004 - 2:36 am UTC

Dear Tom,
Can a procedure or a function return an *object type* as
an OUT parameter?Could you please show a simple example for
that?
Please do reply.
Bye!


Tom Kyte
April 30, 2004 - 7:32 am UTC

create or replace procedure p( x OUT any_type_you_feel_like_creating )
as
begin
null;
end;
/


it is *not any different* than returning a DATE.

(and this has to do with 'create or replace' how exactly???)

OK

Raju, August 25, 2004 - 1:54 pm UTC

Dear Tom,
Is it possible to create a procedure of the form 

SQL>create or replace procedure p(tab_name in varchar2,
col_names in varchar2,rows)
as
 begin
 insert tab_name(col_names )values (rows)
 ..
What I am trying to say is "Can a procedure be created with
arguments that must be  table_name,column names of that table and rows to be inserted into that table by calling that procedure"?
How to get this kind of functionality?
Please do reply.
Bye!
 

Tom Kyte
August 25, 2004 - 2:00 pm UTC

yes, but it would be an utter waste of time, energy, and resources and the wrongest way to do it.


so, therefore, that's all i'll say.

yet how to replace a stored package?

A reader, May 05, 2006 - 4:06 pm UTC

Hi Tom,

I understand that replacing a stored package in a production database during "up hours" is not a good idea, but what if sometimes I do need that and there is no much difference between "up" and "down" hours because the database is worldwide accessible. Even during "down hours" I cannot do that because there are still many users and I always get this timeout error following a "create or replace .." command. I wouldn't like to restart the database just to replace one package, neither to kill user sessions one by one. So, what is the best technique to replace a package/package body in a running database? For example, is it possible to increase this timeout value so that my "create or replace" command would wait patiently and finish successfully in an hour or two instead of quick failure because of this timeout? The database is 9.2.0.7.

Thanks,
Igor


Tom Kyte
May 05, 2006 - 5:01 pm UTC

you could look into quiesce (part of resource manager) to sort of "pause" the database.

Create / Replace Procedure in 24 x 7 database

Tim, May 06, 2006 - 9:24 pm UTC

Not knowing the different possibilities for backup / standby database (assume current version Oracle database server) - I am wondering if some of these techniques could be used to apply upgrades to one database server (not the one currently being used for live production - and after apply is done - failover (or something like it) the live server to the standby/backup server.
Some kind of "rolling upgrade" - however not for upgrading the Oracle binaries - rather for upgrading the stored PLSQL.
If this is possible - this would perhaps also benefit the people responsible for recovery as this would then become a rather normal operation for them - rather than waiting for a disaster to do a failover - it would be done on a fairly regular basis when code upgrades are required.


Tom Kyte
May 07, 2006 - 11:30 am UTC

you could use this in a logical standby perhaps (have not actually tried this, but the concept is sound)

a) set up logical standby
b) install new packages on standby site (just copying data over there)
c) switchover from primary to standby


step C does involve a bit of "downtime" as you are moving the user connections from database "prod" to "standby"

Stand-alone function, package functions, and synonyms

Kurt Look, May 07, 2006 - 10:55 pm UTC

Taking your advice, I (almost) never create stand-alone functions/procedures. I tried to create a global synonym for a function in a package, and found I couldn't do this. The docs seem to confirm this: "Because the procedures defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual procedures within a package."

The only work-around I could figure out was to write a little stand-alone wrapper function, call the function the package, and create the synonym on the stand-alone function.

Do you have a better solution?

Kurt

Tom Kyte
May 08, 2006 - 8:00 am UTC

Yes, call package.function

what is the issue with that? you would create a synonym for the package - not the function - and then call the very meaningfully named package.function


Just like you call dbms_output.put_line for example

Breakable Parse Lock

Arvind Mishra, March 23, 2009 - 8:01 pm UTC

Hello Tom,

In your book(Expert Oracle Database Architecture) while discussing breakable parse locks on page 220 you write that if someone else is executing a procedure then you can not recompile it. I want to know why? Because you also write that breakable parse lock are breakable and they do not restrict other DDL. These two statement seems to be contradictory.

Thanks,

ARVIND

Tom Kyte
March 26, 2009 - 1:40 pm UTC

you cannot overwrite code that is being used - that is why....


if I am in the middle of executing code - what would happen if you erased it? bad things


there is no contradiction here - you have a new situation "you are RUNNING CODE", when you are running code, you cannot have that code erased.

just like if you update a table, you cannot let someone else modify the structure of the table - until you commit.

Breakable Perse Lock

Arvind Mishra, March 30, 2009 - 4:37 pm UTC

Thanks Tom

which job is using a procedure

kishore, February 19, 2010 - 12:11 am UTC

Hi Tom,

Thanks for helping us all out with your precise explainations .
Currently iam struck in a situation where the client wanted to know about a particualar pl/sql procedure is being executed by which job (in oracle).
How can we find that ?

The reason behind this is that the Development team is planning to deploy a new version of a pl/sql procedure in production Environment and this procedure is being executed by oracle jobs and we will have to disable this job by this command
exec dbms_scheduler.disable('JOBNAME');

But in order to do so , i need to know the job name.

Can you pls help me in this regard

Thanks in advance !



Create/Replace procedure command

liz, December 01, 2018 - 4:06 pm UTC

Hi Tom thank you for taking time to help people.
I tried to tried to execute the procedure below using dbms_scheduler.create_job package but it failed with the message ORA-06576: not a valid function or procedure name. Please help me know what is wrong with my code because i'm able to execute it outside of the job. Thanks in advance.

CREATE OR REPLACE PROCEDURE unioncontractleave
IS
BEGIN
UPDATE PRODDTA.F5608001
SET LBLEAVBAL = LBLEAVBAL + 175, LBLEAVCOM = LBLEAVCOM + 175, LBEFT = (SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYDDD'))-1900000 from dual), LBUSER = 'JDE', LBUPMJ = (SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYDDD'))-1900000 from dual)
WHERE LBLTTP = 'AL' AND LBAN8 IN
(SELECT YAAN8 FROM PRODDTA.F060116 WHERE YAPAST = '0' AND YAEST = 'C' AND YAPGRD IN ('B', 'C', 'D', 'E', 'F', 'G', 'H') );
END;
/


Connor McDonald
December 03, 2018 - 3:19 am UTC

Show us your complete example, including the scheduler stuff

Create/Replace procedure command

liz, December 03, 2018 - 5:23 am UTC

the scheduler script you asked to see is as below:

BEGIN
dbms_scheduler.create_job (
job_name => 'SYS_DBA.LEAVEUPDATE_UNIONCONTRACT',
job_type => 'STORED_PROCEDURE',
job_action => 'sys_dba.unioncontractleave',
start_date => '01-DEC-2018 02:00:00 AM',
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=02;BYMINUTE=0; BYSECOND=0',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'UPDATE UNIONISABLE CONTRACT EMPLOYEES LEAVE ON 1ST OF EVERY MONTH 2AM');
END;
/
and the dba_scheduler_job_run_detail gave the following output from the below query:

select log_date, job_name,error#, req_start_date, actual_start_date, run_duration, additional_info
from dba_scheduler_job_run_details
where status = 'FAILED' and owner = 'SYS_DBA' order by log_date desc


12/1/2018 2:00:02.753339 AM +03:00 LEAVEUPDATE_UNIONCONTRACT 6576 12/1/2018 2:00:00.000000 AM +03:00 12/1/2018 2:00:02.354614 AM +03:00 +00 00:00:00.000000 ORA-06576: not a valid function or procedure name



Connor McDonald
December 13, 2018 - 4:42 am UTC

My guess is a wrong schema was used, eg


SQL> CREATE OR REPLACE PROCEDURE unioncontractleave IS
  2   x int;
  3  BEGIN
  4   select 1 into x from dual;
  5  END;
  6  /

Procedure created.

SQL> BEGIN
  2  dbms_scheduler.create_job (
  3  job_name => 'ASKTOM.LEAVEUPDATE_UNIONCONTRACT',
  4  job_type => 'STORED_PROCEDURE',
  5  job_action => 'asktom.unioncontractleave',
  6  start_date => sysdate,
  7  enabled => TRUE,
  8  comments => 'demo');
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select status
  2  from   dba_scheduler_job_run_details
  3  where  owner = 'ASKTOM'
  4  and    job_name = 'LEAVEUPDATE_UNIONCONTRACT';

STATUS
------------------------------
FAILED

--
-- because I created the procedure in the wrong schema
--

SQL> CREATE OR REPLACE PROCEDURE asktom.unioncontractleave IS  
  2   x int;
  3  BEGIN
  4   select 1 into x from dual;
  5  END;
  6  /

Procedure created.

SQL>
SQL> BEGIN
  2  dbms_scheduler.create_job (
  3  job_name => 'ASKTOM.LEAVEUPDATE_UNIONCONTRACT',
  4  job_type => 'STORED_PROCEDURE',
  5  job_action => 'asktom.unioncontractleave',
  6  start_date => sysdate,
  7  enabled => TRUE,
  8  comments => 'demo');
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select status
  2  from   dba_scheduler_job_run_details
  3  where  owner = 'ASKTOM'
  4  and    job_name = 'LEAVEUPDATE_UNIONCONTRACT';

STATUS
------------------------------
FAILED
SUCCEEDED

SQL>
SQL>


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library