niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
How to recompile all my stored procedures ?
Post new topic   Reply to topic Page 1 of 1 [4 Posts] View previous topic :: View next topic
Author Message
DA Morgan
*nix forums Guru


Joined: 06 Mar 2005
Posts: 1042

PostPosted: Thu Feb 24, 2005 3:39 pm    Post subject: Re: How to recompile all my stored procedures ? Reply with quote
fmarchioni@libero.it wrote:
Quote:
Hi Oracle users,
I'm again asking your opinion about pl-sql.
Does anybody know how to recompile all stored procedures
in one shot using a batch file ?

One step back. We have all stored procedures versioned on CVS.
Every time there's a new release of our application
I'd like to validate all pl-sql packages found on CVS
because it can happen that somebody forgot to "check in" changes to
procedures.

So this is the scenario. How would you validate in batch all procedures
?

(Ps I cannot simply import all .sql files in toad and validate them.
I'd like rather, if possible, to integrate it with the batch file we
use to build Java classes)

Thanks
Francesco

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Don't reinvent the wheel.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
Back to top
Mark D Powell
*nix forums Guru


Joined: 23 Apr 2005
Posts: 701

PostPosted: Thu Feb 24, 2005 1:29 pm    Post subject: Re: How to recompile all my stored procedures ? Reply with quote
Oracle provides a script to revalidate all stored code with the
database. It is named utlrp and stored in $ORACLE_HOME/rdbms/admin.

HTH -- Mark D Powell --
Back to top
Rauf Sarwar
*nix forums Guru


Joined: 03 May 2005
Posts: 353

PostPosted: Thu Feb 24, 2005 8:43 am    Post subject: Re: How to recompile all my stored procedures ? Reply with quote
fmarchioni@libero.it wrote:
Quote:
Hi Oracle users,
I'm again asking your opinion about pl-sql.
Does anybody know how to recompile all stored procedures
in one shot using a batch file ?

One step back. We have all stored procedures versioned on CVS.
Every time there's a new release of our application
I'd like to validate all pl-sql packages found on CVS
because it can happen that somebody forgot to "check in" changes to
procedures.

So this is the scenario. How would you validate in batch all
procedures
?

(Ps I cannot simply import all .sql files in toad and validate them.
I'd like rather, if possible, to integrate it with the batch file we
use to build Java classes)

Thanks
Francesco

I posted this in another thread also. Put this in a sql script and run
it as many times as you like.

SET HEADING OFF
SELECT 'There are [ ' || TO_CHAR(count(*)) ||
' ] invalid objects. Recompiling objects, Please wait . . .'
FROM user_objects
WHERE status = 'INVALID';
SET HEADING ON

DECLARE
obj_name_ User_Objects.object_name%TYPE;
obj_type_ User_Objects.object_type%TYPE;
str_run_ VARCHAR2(200);
cid_ INTEGER;
ret_ INTEGER;
CURSOR Invalid_Objects_ IS
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type ASC;
BEGIN
FOR Get_Rec_ IN Invalid_Objects_ LOOP
BEGIN
obj_name_ := Get_Rec_.object_name;
obj_type_ := Get_Rec_.object_type;
IF (obj_type_ = 'FUNCTION') THEN
str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'JAVA SOURCE') THEN
str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
COMPILE';
ELSIF (obj_type_ = 'JAVA CLASS') THEN
str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
RESOLVE';
ELSIF (obj_type_ = 'PACKAGE') THEN
str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'PACKAGE BODY') THEN
str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
BODY';
ELSIF (obj_type_ = 'PROCEDURE') THEN
str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'TRIGGER') THEN
str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';

ELSIF (obj_type_ = 'VIEW') THEN
str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
COMPILE';
ELSIF (obj_type_ = 'DIMENSION') THEN
str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'TYPE') THEN
str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
END IF;
--
cid_ := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
ret_ := DBMS_SQL.EXECUTE(cid_);
DBMS_SQL.CLOSE_CURSOR(cid_);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF (DBMS_SQL.IS_OPEN(cid_)) THEN
DBMS_SQL.CLOSE_CURSOR(cid_);
END IF;
END;
END;
END LOOP;
END;
/

SET HEADING OFF
SELECT 'There are [ ' || TO_CHAR(count(*)) ||
' ] invalid objects after recompile.'
FROM user_objects
WHERE status = 'INVALID';
SET HEADING ON

Regards
/Rauf
Back to top
Francesco M.
*nix forums beginner


Joined: 01 Apr 2005
Posts: 36

PostPosted: Thu Feb 24, 2005 7:59 am    Post subject: How to recompile all my stored procedures ? Reply with quote
Hi Oracle users,
I'm again asking your opinion about pl-sql.
Does anybody know how to recompile all stored procedures
in one shot using a batch file ?

One step back. We have all stored procedures versioned on CVS.
Every time there's a new release of our application
I'd like to validate all pl-sql packages found on CVS
because it can happen that somebody forgot to "check in" changes to
procedures.

So this is the scenario. How would you validate in batch all procedures
?

(Ps I cannot simply import all .sql files in toad and validate them.
I'd like rather, if possible, to integrate it with the batch file we
use to build Java classes)

Thanks
Francesco
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [4 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 5:18 am | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts postfix smtp authentication using mysql stored user/pass rtresidd Postfix 0 Fri Oct 03, 2008 5:58 am
No new posts How do I render JPEG Data stored in char* buffer? On the Sparrow C++ 2 Thu Jul 20, 2006 8:44 pm
No new posts calling stored procedure using ECPG Jasbinder Bali PostgreSQL 1 Thu Jul 20, 2006 7:53 pm
No new posts Error while calling an Oracle Stored Procedure from VB us... macca Server 5 Thu Jul 20, 2006 9:32 am
No new posts Calling stored procedure in PRO C abhi147@gmail.com Tools 1 Wed Jul 19, 2006 5:07 am

Credit Counseling | Hackers | Watch Anime Online | Debt Consolidation | Remortgages
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1237s ][ Queries: 20 (0.0382s) ][ GZIP on - Debug on ]