Re: Help/Trivia Question RE: DBMS_SQL

You seem to need to embed the procedure call in an anonymous block. Try to wrap it in BEGIN END and look what happens.

Hth,

--
Sybrand Bakker, Oracle DBA
Glen Upreti <Glen.Upreti_at_nau.edu> wrote in message news:3891B28B.255FD2B2_at_nau.edu...
> 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);
> <more junk here>
> 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 - 12:11:19 CST