Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE statements do not work
EXECUTE IMMEDIATE statements do not work [message #268751] Wed, 19 September 2007 10:37 Go to next message
johnNhaas
Messages: 4
Registered: September 2007
Location: Charlotte
Junior Member
Oracle 10g
I have refreshed a number of tables from one database to another
and have to update the sequences in the new database.

BEGIN  
  EXECUTE IMMEDIATE 'ALTER SEQUENCE DX_S_DRP_DETAIL_ID INCREMENT BY 74701';
  EXECUTE IMMEDIATE 'SELECT DX_S_DRP_DETAIL_ID.NEXTVAL FROM DUAL';
  EXECUTE IMMEDIATE 'ALTER SEQUENCE DX_S_DRP_DETAIL_ID INCREMENT BY 1';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('FAILED');
END;

Result: anonymous block completed

However, it doesn't do anything.
If I just highlight the string statements and execute them, they work.
Re: EXECUTE IMMEDIATE statements do not work [message #268756 is a reply to message #268751] Wed, 19 September 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Remove your exception block
2/ Put what you try to see it does nothing.

Regards
Michel
Re: EXECUTE IMMEDIATE statements do not work [message #268775 is a reply to message #268751] Wed, 19 September 2007 12:19 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And that's why you NEVER use a WHEN OTHERS exception.

My guess is that you don't have the permissions as they are granted through a role and not to the user directly.
Re: EXECUTE IMMEDIATE statements do not work [message #268776 is a reply to message #268756] Wed, 19 September 2007 12:20 Go to previous messageGo to next message
johnNhaas
Messages: 4
Registered: September 2007
Location: Charlotte
Junior Member
When I do that, all I get is 'anonymous block completed'.
But it doesn't change the value in the SEQUENCE.
Re: EXECUTE IMMEDIATE statements do not work [message #268777 is a reply to message #268776] Wed, 19 September 2007 12:22 Go to previous messageGo to next message
johnNhaas
Messages: 4
Registered: September 2007
Location: Charlotte
Junior Member
Are the permissions different inside an EXECUTE IMMEDIATE
than they are if I execute the statement directly?
Re: EXECUTE IMMEDIATE statements do not work [message #268778 is a reply to message #268751] Wed, 19 September 2007 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why are you doing this from inside PL/SQL; as opposed via SQL*Plus directly?

In some/many/most cases a COMMIT is required to "force" changes to be permanent.

>Are the permissions different inside an EXECUTE IMMEDIATE than they are if I execute the statement directly?

Permission obtained via ROLE do NOT apply within PL/SQL procedures.

you can test SQL execution as though it would be done within PL/SQL by doing:
SQL> SET ROLE NONE -- & the issue the SQL

[Updated on: Wed, 19 September 2007 12:33] by Moderator

Report message to a moderator

Re: EXECUTE IMMEDIATE statements do not work [message #268779 is a reply to message #268776] Wed, 19 September 2007 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just do what I said and you'll see why there is no change.

Regards
Michel
Re: EXECUTE IMMEDIATE statements do not work [message #268783 is a reply to message #268751] Wed, 19 September 2007 12:51 Go to previous messageGo to next message
johnNhaas
Messages: 4
Registered: September 2007
Location: Charlotte
Junior Member
I am using PL/SQL because I have 34 sequences to change on the fly and will be using a Stored Procedure. Since the numbers will be different each time I do a refresh from one database to another, I need the procedure.

I found something that works as follows:

DECLARE 
    nextval_string varchar(1000);
    v_holding int;
  BEGIN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE DX_S_DRP_DETAIL_ID INCREMENT BY 25378';
      nextval_string := 'BEGIN SELECT ' ||  'DX_S_DRP_DETAIL_ID'  || '.NEXTVAL INTO :a FROM DUAL; END;';      
      EXECUTE IMMEDIATE nextval_string USING OUT v_holding;      
      EXECUTE IMMEDIATE 'ALTER SEQUENCE DX_S_DRP_DETAIL_ID INCREMENT BY 1';
  END;


I'm not sure why, but it seems to have something to do with the structrue of the EXECUTE IMMEDIATE string.

Thanks to all for your help.
Re: EXECUTE IMMEDIATE statements do not work [message #268786 is a reply to message #268783] Wed, 19 September 2007 12:57 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check this.
This will also work.
DECLARE
   i NUMBER;
BEGIN  
     EXECUTE IMMEDIATE 'ALTER SEQUENCE DX_S_DRP_DETAIL_ID INCREMENT BY 74701';
     EXECUTE IMMEDIATE 'SELECT DX_S_DRP_DETAIL_ID.NEXTVAL FROM DUAL' INTO i;
     EXECUTE IMMEDIATE 'ALTER SEQUENCE DX_S_DRP_DETAIL_ID INCREMENT BY 1'
END;
By
Vamsi
Previous Topic: PL/SQL Records question
Next Topic: Metrialized View exceptions
Goto Forum:
  


Current Time: Sat Dec 10 10:53:49 CST 2016

Total time taken to generate the page: 0.09411 seconds