> "Hagedorn, Linda" wrote: > > If someone has a few minutes to read through this, I'd be most > appreciative. I could use a second set of eyes looking this over. > > This procedure is designed to maintain a table/sequence map, executed > after an import and increment any sequences which have a lower nextval > that the max value in the column it's supposed to be matching. Often > the sequences are out-of-sync after an import (even full=y and > direct=y) and we have to manually adjust them. This is an effort to > automate the process. > > These are the displays and error from the procedure, and the code > follows. The problem is in the execute immediate which is doing DDL. > It's Oracle 8.1.7 on Solaris so DDL in execute immediate alter is > supposed to work. The execute immediate insert does work. > > Any suggestions or comments are welcome. Thanks, Linda >
Linda, DDL is forbidden in PL/SQL - except in some EXECUTE IMMEDIATE statement (or when using the older DBMS_SQL package) but as a SINGLE statement. When you are executing your statement you are trying to execute an anonymous block - which happens to contain a DDL statement, which is forbidden in PL/SQL (sorry, looping). I have never tried it but there is no reason why it shouldn't work, an EXECUTE IMMEDIATE within the EXECUTE IMMEDIATE is probably what you shoud try, i. e. something like : my_statement := 'begin' || chr(10) || 'execute immediate ''' || 'fancy DDL here' || '''; end;'; execute immediate :my_statement; Another solution would be to simplify the logic, but I am in Europe and currently a bit tired to suggest something reasonably intelligent. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).