dbTalk Databases Forums  

Recompiling invalid objects with a batch file

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc Recompiling invalid objects with a batch file

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss Recompiling invalid objects with a batch file in the comp.databases.oracle.misc forum.



Reply
Page 5 of 5 < 1 2 3 4 5
 
Thread Tools Display Modes
  #41  
Old   
santosh.gaikwad@gmail.com
 
Posts: n/a

Default Re: Recompiling invalid objects with a batch file - 07-11-2008 , 09:20 AM





here is the code... Simple and easy...

BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
Quote:
| cur_rec.object_name || ' COMPILE BODY';
ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
Quote:
| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
Quote:
| cur_rec.object_name);
END;
END LOOP;
END ;
/


On Jun 2, 11:09*am, Ubiquitous <web... (AT) polaris (DOT) net> wrote:
Quote:
In article <1196834485.490... (AT) bubbleator (DOT) drizzle.com>, damor... (AT) psoug (DOT) org
wrote:





Ubiquitous wrote:
Is there a way to write a sqlplus batch file to "recompile all invalid
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...

No reason to make it difficult on yourself:

SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html

or if you prefer:

UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.

or if you prefer:

DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html

No need to reinvent the wheel.

Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?

Thanks again!- Hide quoted text -

- Show quoted text -


Reply With Quote
  #42  
Old   
santosh.gaikwad@gmail.com
 
Posts: n/a

Default Re: Recompiling invalid objects with a batch file - 07-11-2008 , 09:20 AM





here is the code... Simple and easy...

BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
Quote:
| cur_rec.object_name || ' COMPILE BODY';
ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
Quote:
| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
Quote:
| cur_rec.object_name);
END;
END LOOP;
END ;
/


On Jun 2, 11:09*am, Ubiquitous <web... (AT) polaris (DOT) net> wrote:
Quote:
In article <1196834485.490... (AT) bubbleator (DOT) drizzle.com>, damor... (AT) psoug (DOT) org
wrote:





Ubiquitous wrote:
Is there a way to write a sqlplus batch file to "recompile all invalid
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...

No reason to make it difficult on yourself:

SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html

or if you prefer:

UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.

or if you prefer:

DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html

No need to reinvent the wheel.

Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?

Thanks again!- Hide quoted text -

- Show quoted text -


Reply With Quote
  #43  
Old   
DA Morgan
 
Posts: n/a

Default Re: Recompiling invalid objects with a batch file - 07-11-2008 , 09:45 AM


santosh.gaikwad (AT) gmail (DOT) com wrote:
Quote:
here is the code... Simple and easy...

BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
|| cur_rec.object_name || ' COMPILE BODY';

ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
|| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
|| cur_rec.object_name);
END;
END LOOP;
END ;
/


On Jun 2, 11:09 am, Ubiquitous <web... (AT) polaris (DOT) net> wrote:
In article <1196834485.490... (AT) bubbleator (DOT) drizzle.com>, damor... (AT) psoug (DOT) org
wrote:





Ubiquitous wrote:
Is there a way to write a sqlplus batch file to "recompile all invalid
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...
No reason to make it difficult on yourself:
SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html
or if you prefer:
UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.
or if you prefer:
DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html
No need to reinvent the wheel.
Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?

Thanks again!- Hide quoted text -

- Show quoted text -
Why are you reinventing the wheel?

Use the built in UTL_RECOMP package.
http://www.psoug.org/reference/utl_recomp.html
as previously suggested.
The posted code, for example, won't recompile a type, type body, or view.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


Reply With Quote
  #44  
Old   
DA Morgan
 
Posts: n/a

Default Re: Recompiling invalid objects with a batch file - 07-11-2008 , 09:45 AM


santosh.gaikwad (AT) gmail (DOT) com wrote:
Quote:
here is the code... Simple and easy...

BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
|| cur_rec.object_name || ' COMPILE BODY';

ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
|| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
|| cur_rec.object_name);
END;
END LOOP;
END ;
/


On Jun 2, 11:09 am, Ubiquitous <web... (AT) polaris (DOT) net> wrote:
In article <1196834485.490... (AT) bubbleator (DOT) drizzle.com>, damor... (AT) psoug (DOT) org
wrote:





Ubiquitous wrote:
Is there a way to write a sqlplus batch file to "recompile all invalid
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...
No reason to make it difficult on yourself:
SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html
or if you prefer:
UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.
or if you prefer:
DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html
No need to reinvent the wheel.
Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?

Thanks again!- Hide quoted text -

- Show quoted text -
Why are you reinventing the wheel?

Use the built in UTL_RECOMP package.
http://www.psoug.org/reference/utl_recomp.html
as previously suggested.
The posted code, for example, won't recompile a type, type body, or view.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


Reply With Quote
  #45  
Old   
DA Morgan
 
Posts: n/a

Default Re: Recompiling invalid objects with a batch file - 07-11-2008 , 09:45 AM


santosh.gaikwad (AT) gmail (DOT) com wrote:
Quote:
here is the code... Simple and easy...

BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
|| cur_rec.object_name || ' COMPILE BODY';

ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
|| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
|| cur_rec.object_name);
END;
END LOOP;
END ;
/


On Jun 2, 11:09 am, Ubiquitous <web... (AT) polaris (DOT) net> wrote:
In article <1196834485.490... (AT) bubbleator (DOT) drizzle.com>, damor... (AT) psoug (DOT) org
wrote:





Ubiquitous wrote:
Is there a way to write a sqlplus batch file to "recompile all invalid
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...
No reason to make it difficult on yourself:
SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html
or if you prefer:
UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.
or if you prefer:
DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html
No need to reinvent the wheel.
Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?

Thanks again!- Hide quoted text -

- Show quoted text -
Why are you reinventing the wheel?

Use the built in UTL_RECOMP package.
http://www.psoug.org/reference/utl_recomp.html
as previously suggested.
The posted code, for example, won't recompile a type, type body, or view.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


Reply With Quote
  #46  
Old   
DA Morgan
 
Posts: n/a

Default Re: Recompiling invalid objects with a batch file - 07-11-2008 , 09:45 AM


santosh.gaikwad (AT) gmail (DOT) com wrote:
Quote:
here is the code... Simple and easy...

BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
|| cur_rec.object_name || ' COMPILE BODY';

ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
|| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
|| cur_rec.object_name);
END;
END LOOP;
END ;
/


On Jun 2, 11:09 am, Ubiquitous <web... (AT) polaris (DOT) net> wrote:
In article <1196834485.490... (AT) bubbleator (DOT) drizzle.com>, damor... (AT) psoug (DOT) org
wrote:





Ubiquitous wrote:
Is there a way to write a sqlplus batch file to "recompile all invalid
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...
No reason to make it difficult on yourself:
SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html
or if you prefer:
UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.
or if you prefer:
DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html
No need to reinvent the wheel.
Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?

Thanks again!- Hide quoted text -

- Show quoted text -
Why are you reinventing the wheel?

Use the built in UTL_RECOMP package.
http://www.psoug.org/reference/utl_recomp.html
as previously suggested.
The posted code, for example, won't recompile a type, type body, or view.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


Reply With Quote
Reply
Page 5 of 5 < 1 2 3 4 5

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top