Home » SQL & PL/SQL » SQL & PL/SQL » how to call ALTER from procedure (Oracle 10.1.0.4.0, Windows XP Professional)
how to call ALTER from procedure [message #405556] Thu, 28 May 2009 06:52 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
I need to call alter statement from the procedure. I used EXECUTE IMMEDIATE as:

SELECT  'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
INTO    V_AL_QUERY
FROM    SYS.ALL_OBJECTS;
EXECUTE IMMEDIATE V_AL_QUERY;

but of no use.
Re: how to call ALTER from procedure [message #405557 is a reply to message #405556] Thu, 28 May 2009 06:53 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

but of no use.



Means what ? did u get any errors ?
Re: how to call ALTER from procedure [message #405559 is a reply to message #405557] Thu, 28 May 2009 06:58 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/forum/m/404921/136607/#msg_404921

Hope the above will suggest you...

Sriram
Re: how to call ALTER from procedure [message #405561 is a reply to message #405556] Thu, 28 May 2009 07:00 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unless there's something seriously wrong with your database that query is going to return more than one row.
Therefore you're going to need a loop and a where clause - do really want to try and compile every object in the database (and some object types are uncompilable - tables for starters).
Re: how to call ALTER from procedure [message #405562 is a reply to message #405556] Thu, 28 May 2009 07:04 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

You would get error if you do like following,

DECLARE
   v_al_query   VARCHAR2 (2000);
   sql_stmt		varchar2(2000);
BEGIN
   SELECT    'ALTER '
          || object_type
          || ' '
          || owner
          || '.'
          || object_name
          || ' COMPILE;'
     INTO v_al_query
     FROM SYS.all_objects;

  EXECUTE IMMEDIATE V_AL_QUERY;
END;


Error
ORA-01422: exact fetch returns more than requested number of rows


Have a Where Clause or use Loop
Re: how to call ALTER from procedure [message #405563 is a reply to message #405561] Thu, 28 May 2009 07:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And what's the use of re-inventing the wheel?

dbms_utility.compile_schema
Re: how to call ALTER from procedure [message #405566 is a reply to message #405563] Thu, 28 May 2009 07:17 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/node/2008
So many ways to compile invalid objects...

Sriram
Re: how to call ALTER from procedure [message #405568 is a reply to message #405556] Thu, 28 May 2009 07:19 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
thanks for the reply
Yes i am creating alter statement in loop as:
.....
FOR OBJ IN OBJECTS LOOP
            V_OBJECT_NAME := OBJ.OBJECT_NAME;
            SELECT  'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
            INTO    V_AL_QUERY
            FROM    SYS.ALL_OBJECTS
            WHERE   STATUS = 'INVALID'
            AND     OBJECT_NAME = V_OBJECT_NAME
            AND     OBJECT_TYPE IN ('PACKAGE','FUNCTION','PROCEDURE');
            EXECUTE IMMEDIATE V_AL_QUERY;
    END LOOP;--END OBJECTS LOOP
....
EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error...'||sqlerrm||'----'||sqlcode);

There is no complie time error but when it comes to EXECUTE statement, it thorws exception
ORA-00911: invalid character-----911
Re: how to call ALTER from procedure [message #405572 is a reply to message #405556] Thu, 28 May 2009 07:34 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
o should i go for
dbms_utility.exec_ddl_statement(ALTER STATEMENT);

..???
Re: how to call ALTER from procedure [message #405582 is a reply to message #405568] Thu, 28 May 2009 07:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's complaining about the trailing semi-colon in your sql - you don't need one with Execute Immediate.

Personally, I'd go with DBMS_UTILITY.COMPILE_SCHEMA('<schema name>');

Re: how to call ALTER from procedure [message #405585 is a reply to message #405556] Thu, 28 May 2009 08:05 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
sorry the actual error was
Error...ORA-01031: insufficient privileges-----1031

Now as error suggest, we need to grant the access to the table, rite? Doing that will solve the problem, rite?
I want to know which is the best way to do that. Either i should use exec_ddl_statement OR execute immediate.
As far as DBMS_UTILITY.COMPILE_SCHEMA is concerned i don't want to go for it because i only need to compile certain objects.
Re: how to call ALTER from procedure [message #405594 is a reply to message #405585] Thu, 28 May 2009 08:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your problem is that you're looking at ALL_OBJECTS, rather than USER_OBJECTS.

That will show you objects in other peoples schemas, including (probably) a lot of things owned by SYS that you shouldn't touch.

You'd need to grant the user the ALTER ANY PROCEDURE, and that's something that you should think twice about handing out, (and then probably say no anyway).

If you run DBMS_UTILITY.COMPILE_SCHEMA('schema_name',FALSE); then that will only compile the invalid procedures,functions, packages and triggers in the schema - pretty much what you're doing, with the addition of triggers.
Re: how to call ALTER from procedure [message #405595 is a reply to message #405556] Thu, 28 May 2009 09:08 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:

You'd need to grant the user the ALTER ANY PROCEDURE


yes that's what i don't wanna do.

Quote:

Either i should use exec_ddl_statement


what about this?
Re: how to call ALTER from procedure [message #405600 is a reply to message #405595] Thu, 28 May 2009 09:46 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
DECLARE
   v_al_query   VARCHAR2 (2000);
   sql_stmt		varchar2(2000);
BEGIN
   SELECT    'ALTER '
          || object_type
          || ' '
          || owner
          || '.'
          || object_name
          || ' COMPILE;'
     INTO v_al_query
     FROM SYS.all_objects;

  EXECUTE IMMEDIATE V_AL_QUERY;
END



Use this code with loop condition..

Thanks
Re: how to call ALTER from procedure [message #405601 is a reply to message #405600] Thu, 28 May 2009 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gentlebabu wrote on Thu, 28 May 2009 16:46
Use this code with loop condition..

Thanks

You are a couple of hours too late.

Regards
Michel

Re: how to call ALTER from procedure [message #405604 is a reply to message #405556] Thu, 28 May 2009 09:58 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
well no problem gentlebabu
thanks atleast for visting the post
Please can someone reply to my last post
Re: how to call ALTER from procedure [message #405606 is a reply to message #405556] Thu, 28 May 2009 10:24 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt exec_ddl_statement is going to do anything different to execute immediate.

You do seem to be making life hard for yourself. If you script this you need to worry about dependency chains - compiling one procedure might invalidate another that you've already compiled.

DBMS_UTILITY.COMPILE_SCHEMA takes care of that for you and is the standard way of doing this. So why don't you want to use it?
Re: how to call ALTER from procedure [message #405608 is a reply to message #405606] Thu, 28 May 2009 10:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Thu, 28 May 2009 16:24
DBMS_UTILITY.COMPILE_SCHEMA takes care of that for you and is the standard way of doing this. So why don't you want to use it?

I smell homework.
Re: how to call ALTER from procedure [message #405711 is a reply to message #405604] Fri, 29 May 2009 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have coded yourself into a corner.

The cursor that you use, looking at all invalid objects from ALL_OBJECTS will pick up every invalid object in the database.
Unless the user executing the code has the ALTER ANY PROCEDURE privilege, the code will error as soon as it attempts to recompile an object outside of the executing users schema.
The three easiest solutions to this are:

1) Grant ALTER ANY PROCEDURE to the user - probably a bad move.

2) Change ALL_OBJECTS to USER_OBJECTS - this means that the code will just compile objects in the current schema, and will simply be a poor quality version of DBMS_UTILITY.COMPILE_SCHEMA, so you might as well use that instead, as it's better than your code.

3) Create a new user, grant that user ALTER ANY PROCEDURE, create a procedure in that users schema that does what your code does, grant execute on that procedure to PUBLIC, create a public synonym for it and lock the new users account.
That way you get the ability to call that procedure and recompile everything without having to let people make any other alterations to the database.
Re: how to call ALTER from procedure [message #405741 is a reply to message #405711] Fri, 29 May 2009 05:09 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:

1) Grant ALTER ANY PROCEDURE to the user - probably a bad move.


indeed it is.
Quote:

2) Change ALL_OBJECTS to USER_OBJECTS - this means that the code will just compile objects in the ....


I need to access other schema objects as well
Quote:

3) Create a new user, grant that user ALTER ANY PROCEDURE...


this option seems to be good
Re: how to call ALTER from procedure [message #405744 is a reply to message #405556] Fri, 29 May 2009 05:22 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:

create a public synonym for it and lock the new users account


does locking the account mean that we can not access the procedure created in the account?
Re: how to call ALTER from procedure [message #405776 is a reply to message #405744] Fri, 29 May 2009 07:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you'd need to do a bit of thinking about that.

Do you think that I'd spend the time typing that solution in, and then deliberately tell you to do something that would mean that the whole idea didn't work?
Previous Topic: selecting the data from Partitioned table
Next Topic: PL/SQL integer null value or NVL?
Goto Forum:
  


Current Time: Sun Dec 04 18:25:04 CST 2016

Total time taken to generate the page: 0.16938 seconds