Re: ALTER ROLBACK ... in a procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Dec 1999 06:53:00 -0500
Message-ID: <qpe16sglddusmbnmq4nvrnvhbmn0992mfe@4ax.com>

A copy of this was sent to Jenda_at_Krynicky.cz (Jenda Krynicky) (if that email address didn't require changing) On Wed, 22 Dec 1999 11:29:35 GMT, you wrote:

>I am probably missing something obvious, but don't know what.
>
>I need to shrink rollback segments in a procedure, but I do not seem
>to be able to find the correct syntax. If I use this in the ISQL editor
>
> ALTER ROLLBACK SEGMENT RBS01 SHRINK;
>
>it seems to work as expected, but if I include this line in a procedure I
>get :
>
> Encountered the symbol ALTER when expecting ...
>
>
>Am I missing a keyword or what?
>
>Thanks, Jenda
>http://Jenda.Krynicky.cz

You need to use dynamic sql (dbms_sql in 8.0 and before, execute immediate 'some_string' in 8.1 and up)....

for example:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;
/

begin

    execute_immediate( 'alter ....' );
end;
/

beware of roles and grants in procedures -- see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html to find out why you'll get "ORA-01031: insufficient privileges" in the procedure even though you can do the alter straight from sqlplus.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 22 1999 - 05:53:00 CST