Re: Silly bindvar trouble in a dynamic plsql block within package.

On 17 Apr 2002 08:37:49 -0700, frankzaum_at_yahoo.de (Frank Zaum) wrote:

>I have some trouble with this dynamic plsql block inside a nested
>cursor loop in a package
>...
>for cursor1 in ...

 In what? How do we know the loop executes even once?

> for cursor2 in ...

 In what? How do we know the loop executes even once?

> v_stmt := 'BEGIN DBMS_SPACE.UNUSED_SPACE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10);';

 Where's the END of the block?

> execute immediate v_stmt using v_owner, v_cur_tab, v_segment_type,
> v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes,
>v_last_used_efid, v_last_used_ebid, v_last_used_block;
>
> insert into hist_stats values (
> 'dumb',
> v_timestamp,
> v_total_blocks,
> v_total_bytes,
> v_unused_blocks,
> v_unused_bytes,
> v_last_used_efid,
> v_last_used_ebid,
> v_last_used_block
> end loop;
>end loop2;

 Do you commit the values anywhere?

>the hole thing is compiled w/o error, but when i take a look at the
>table, its still empty. i have no idea whats going on. any help would
>be appreciated.

 The simplistic approach to debugging this is to have a dbms_output statment within the loop printing out the values you expect to have written to the table.

-- 
Andy Hassall (andy@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyh.uklinux.net/space | disk usage analysis tool