Help/Trivia Question RE: DBMS_SQL

Hello all,
I am having a hell of a time trying to figure out how to exec a stored procedure using DBMS_SQL. The stored proc I am writing checks to see the version of the database and then depending on the version should execute dbms_sql and have it execute dbms_space with the appropriate args (v7 doesn't have partition_name as an arg.) Here is an example

create or replace foo <snip>
<variables defined here>
<dbms_sql cursor opened here.>

  if substr(vbanner,instr(vbanner,'Release ',1)+8,3) = '8.1' then

     for seg_rec in seg_cur loop
      
sys.dbms_sql.PARSE(cnum1,'sys.dbms_space.unused_space(SEGMENT_OWNER=>'||         
seg_rec.owner||',SEGMENT_NAME=>'||seg_rec.segment_name||',         
SEGMENT_TYPE=>'||seg_rec.segment_type||',
TOTAL_BLOCKS=>'||vtotal_blocks||',         
TOTAL_BYTES=>'||vtotal_bytes||', UNUSED_BLOCKS=>'
||vunused_blocks||',                  
UNUSED_BYTES=>'||vunused_bytes||',LAST_USED_EXTENT_FILE_ID=>'                  
||vLAST_USED_EXTENT_FILE_ID||',LAST_USED_EXTENT_BLOCK_ID=>'||         
vLAST_USED_EXTENT_BLOCK_ID||',LAST_USED_BLOCK=>'
||vLAST_USED_BLOCK||',         
PARTITION_NAME=>'||seg_rec.partition_name||');',sys.dbms_sql.native);
	 exec1 := sys.dbms_sql.execute(cnum1);

    END LOOP;
<and then I end properly>

I am only having a problem using dbms_sql to execute the stored procedure dbms_space.unused_space, all of my cursors and such are opening fine. I have used dbms_sql alot and I was very surprised to find that I hadn't ever used it to exec a stored proc! I also was very surprised to have such a hard time finding any doc on it. Replies by email would be much appreciated. (don't forget to take the NOSPAM out.)

Thanks in advance for any insight you can provide, Glen

--
|-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-| Glen Upreti
Senior Oracle DBA
Northern Arizona University
Phone (520)523-8393 Fax(520)523-7407 Glen.Upreti_at_NOSPAMnau.edu
'We do only quality disasters!'

Received on Fri Jan 28 2000 - 09:15:23 CST