Re: what's wrong with this DYNAMIC SQL ???

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 May 1998 17:08:01 GMT
Message-ID: <3550f5ef.5968542_at_192.86.155.100>

There are a couple of things wrong with your routine

• vyl_id -- undefined variable?
• you parse the CURSOR_COALESCE with a value contained in "FID". FID is not initialized to anything and you can't use a bind variable there.

If you have a version of the database less then 7.3, you can use the following procedure (which should be owned by SYS and execute granted to whomever should have execute) to coalesce contigous free extents. In 7.3, you can just use the coalesce option of the alter tablespace command directly.

create or replace procedure coalesce( ts_name in varchar2 default NULL ) as

    number_of_extents number default 255 * 16 * 16 * 16 * 16;     exec_cursor integer default 0;     rows_processed number default 0;     stmt varchar2( 255 );
begin

    dbms_output.enable( 1000000 );
    for x in ( select number_of_extents + ts# ts#, name

                 from ts$
                 where ( name = upper(ts_name) OR ts_name is NULL )
                   AND (  online$ != 3 ) ) loop

        stmt := 'alter session set events ' ||
                '''immediate trace name coalesce level ' || x.ts# || '''';
        exec_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
        rows_processed := dbms_sql.execute(exec_cursor);
        dbms_sql.close_cursor( exec_cursor );
        dbms_output.put_line( 'Coalesced Tablespace ' || x.name );
        dbms_output.put_line( stmt );

    end loop;

exception

    when others then

         dbms_output.put_line( substr( stmt, 1, 200 ) );
         dbms_output.put_line( 'SQLCODE = ' || sqlcode );
         dbms_output.put_line( 'SQLERRM = ' || sqlerrm );
         if dbms_sql.is_open(exec_cursor) then
           dbms_sql.close_cursor(exec_cursor);
         end if;
         raise;

end coalesce;
/

A copy of this was sent to _ronr__at__wxs.nl_ (Ronald) (if that email address didn't require changing) On Mon, 04 May 1998 18:14:29 +0200, you wrote:

>This should work I suppose.
>Can anyone tell me what's wrong, or how I can get the exact text that's
>been parsed and contains an error ?
>
>Any hint/tip is very much apreciated.
>
>
>create or replace procedure coa is
> cursor_coalesce integer;
> cursor_files integer;
> fid number;
> file_id number;
> ignore integer;
>BEGIN
> cursor_files := dbms_sql.open_cursor;
> dbms_sql.parse ( cursor_files, 'select file_id from
>sys.dba_data_files', dbms_sql.v7 );
> dbms_sql.define_column (cursor_files, 1, vyl_id);
> ignore := dbms_sql.execute (cursor_files);
>
> cursor_coalesce := dbms_sql.open_cursor;
> dbms_sql.parse (cursor_coalesce,
> 'alter session set events ''immediate trace name coalesce level
>'|| FID|| '''', dbms_sql.v7 );
> LOOP
> if dbms_sql.fetch_rows (cursor_files) > 0 then
> -- get column value of the row
> dbms_sql.column_value (cursor_files, 1, file_id);
> dbms_sql.bind_variable (cursor_coalesce, 'FID', file_id);
> ignore := dbms_sql.execute (cursor_coalesce);
> else
> exit; -- loop
> end if;
> END LOOP;
>
> dbms_sql.close_cursor(cursor_coalesce);
> dbms_sql.close_cursor(cursor_files);
>EXCEPTION
> when others then
> if dbms_sql.is_open (cursor_coalesce) then
> dbms_sql.close_cursor(cursor_coalesce);
> end if;
> if dbms_sql.is_open (cursor_files) then
> dbms_sql.close_cursor(cursor_files);
> end if;
> raise;
>END;
>/
>
>
>Ronald
>
>---------------------------------------------------------------------------
>mailto:ronr_at_wxs.nl
>http://home.wxs.nl/~ronr/RonR.html (last update: dec 31, 1997)
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon May 04 1998 - 19:08:01 CEST