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

Also, pay attention to specify OUT for OUT parameters in USING clause. UNUSED_SPACE() has only 3 first parameters IN, all others are OUT, so your call should be like this:

v_stmt := 'begin dbms_space.unused_space(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); end;'; execute immediate v_stmt using v_owner, v_cur_tab, v_segment_type,  out v_total_blocks, out v_total_bytes, out v_unused_blocks, out v_unused_bytes,  out v_last_used_efid, out v_last_used_ebid, out v_last_used_block;

Also, since this is PL/SQL, you can call dbms_space.unused_space directly, no need for Dynamic SQL here.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Andy Hassall" <andy_at_andyh.org> wrote in message news:511sbugamknkmqje3rkv1pp5bri56hngh3_at_4ax.com...


> 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