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
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.
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?
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?
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.
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!
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!
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
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.
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
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
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;
/
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
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>