Display Results from EXECUTE IMMEDIATE

[Sahib Singh]

I am having issues to display the results of the EXECUTE IMMEDIATE statements in loop.


The code below works perfectly well

Code: Select all

 begin
       for cur_str IN (select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||
                         ' $3 tablespace PART_DATA_' || PARTITION_YR || ' parallel $2'  ddl_stmt
                  from   dba_tab_partitions
                  where partition_name=part_rec.part_name
                  and table_owner = '$SCH'
                  and table_name=part_rec.tab_name)
       loop
        DBMS_OUTPUT.put_line (cur_str.ddl_stmt);
        execute immediate (cur_str.ddl_stmt)
       end loop;
        exception
         when no_data_found then
         NULL;
         when others then
        dbms_output.put_line('ERROR: failed due to '||sqlerrm) ;
     end;


but the problem is while its executing we never are aware of the progress. Hence we are trying to get the results of each statement executed by "EXECUTE IMMEDIATE". so meaning while code proceeds to second SQL we should somehow be able to know the what were the results of the first statement execution. DBMS_OUTPUT.put_line (cur_str.ddl_stmt) gives us the statement thats being executed but we never know what were the results even if we are spooling in the beginning of the code.

Pls assist.

I tried something like

Code: Select all

  begin
       for cur_str IN (select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||
                         ' $3 tablespace PART_DATA_' || PARTITION_YR || ' parallel $2'  ddl_stmt
                  from   dba_tab_partitions
                  where partition_name=part_rec.part_name
                  and table_owner = '$SCH'
                  and table_name=part_rec.tab_name)
       loop
        DBMS_OUTPUT.put_line (cur_str.ddl_stmt);
        execute immediate 'cur_str.ddl_stmt' into abc;
        dbms_output.put_line(abc);
       end loop;
        exception
         when no_data_found then
         NULL;
         when others then
        dbms_output.put_line('ERROR: failed due to '||sqlerrm) ;
     end;

It failed.


Pls assist.

[Tim...]

Hi.

You should use the DBMS_APPLICATION_INFO package for this.

http://www.oracle-base.com/articles/8i/ ... N_INFO.php

The progress can then be seen by querying the v$session view.

Cheers

Tim...

[Sahib Singh]

Tim,

Thanks.


As you see the code is all ready. As the scripts completes we get the following in spool


alter table UBSDB_EOD.SCENARIO_TRADE_LINK_EOD move partition P_20110614 compress
tablespace PART_DATA_2011 parallel 4
alter index UBSDB_EOD.IDX_SC_TRD_LNK_EOD_2 rebuild partition P_20110614
nocompress tablespace PART_INDEX_2011 parallel 4
alter index UBSDB_EOD.IDX_SC_TRD_LNK_EOD_1 rebuild partition P_20110614
nocompress tablespace PART_INDEX_2011 parallel 4



All we need is something like.

Code: Select all

alter table UBSDB_EOD.SCENARIO_TRADE_LINK_EOD move partition P_20110614 compress
tablespace PART_DATA_2011 parallel 4

SQL> Statement Executed successfully (Oracle's Default Message)



alter index UBSDB_EOD.IDX_SC_TRD_LNK_EOD_2 rebuild partition P_20110614
nocompress tablespace PART_INDEX_2011 parallel 4

SQL> Statement Executed successfully (Oracle's Default Message)




alter index UBSDB_EOD.IDX_SC_TRD_LNK_EOD_1 rebuild partition P_20110614
nocompress tablespace PART_INDEX_2011 parallel 4

SQL> Statement Executed successfully (Oracle's Default Message)

V$session wont be option as this Script will be executed in off hrs, all we need to see if results in the morning to cross verfiy. Pls suggest some option to do so withs ame code of EXECUTE IMMEDIATE

[Tim...]

Hi.

You know each statement is executed successfully because if it didn't an exception would fire, which would jump out of the loop and call your OTHERS exception handler. The presence of the statement in the log, without a following ERROR indicates success.

If this is not good enough, then manually put a success message in the log after each operation.

Cheers

Tim...

[Sahib Singh]

Thanks. Pls advise how we can manually add the message in the Spool File. You mean by ECHO ?

[Sahib Singh]

Tim,

I added the following

DBMS_OUTPUT.put_line ('SUCCESFULLY Executed');


and seem to do the work.


Anyoether issue, is that the Is there a way that we can get the spool populated while the procedure is working. In current scenerio we only get to the spool file populated when all the statements have been completed in the cursor.


Thanks

[Tim...]

Hi.

DBMS_OUTPUT writes your messages to a pipe. At the end of the execution, SQL*Plus reads all the data from that pipe and pushes it to screen. That's just how SQL*Plus works with DBMS_OUTPUT.

You can write another process, running in another session, to read from the pipe, using DBMS_OUTPUT.GET_LINES and manually push that out to a file, but this would have to be done from another session, so it is not very sensible.

What many people, including me, do is to write a wrapper over DBMS_OUTPUT. The one I wrote is called DSP.

http://www.oracle-base.com/dba/miscellaneous/dsp.pks
http://www.oracle-base.com/dba/miscellaneous/dsp.pks

On a real system, I never all DBMS_OUTPUT. Instead I call DSP to do the tracing. The nice thing about wrappers like this is I can direct the tracing wherever I want. In the case of a batch job where I want continuous output of the tracing, I direct the messages to a file, rather than the DBMS_OUTPUT pipe. For example.

Code: Select all

BEGIN
  DSP.show_output_on;
  DSP.show_date_on;
  DSP.file_output_on('MY_DIR', 'process.log');
  my_procedure;
END;
/

The contents of MY_PROCEDURE will include tracing using DSP, like this,

Code: Select all

CREATE OR REPLACE PROCEDURE my_procedure AS
BEGIN
  FOR i IN 1 TO 10 LOOP
    DSP.line('some tracing || i);
  END LOOP;
END;
/

So this is a direct replacement for DBMS_OUTPUT, even though it calls DBMS_OUTPUT if the trace is not redirected to a file.

Note. Writing to file is comparatively slow. You need to test the impact of adding such tracing to your process. It's fine to turn on while debugging, but it may not be the right solution to run with all the time.

There are several versions of this type of wrapper from other people, but this is one I wrote that contains the functionality I need.

Cheers

Tim...