snapshot using dynamic query

From: Avnish Rastogi DTA <avnish_at_TULNEPZ.UNISYS.COM>
Date: Fri, 9 Feb 1996 18:28:45 +0530
Message-Id: <9602091237.AA06990@alice.jcc.com>

i am creating snapshot using dynamic sql . below is the code

create or replace procedure dta_snapshot(snapName in varchar2,

                                         db_Link    in  varchar2) is
source_cursor   integer;
new_cursor      integer;
new_snapshot    integer;
drop_cursor     integer;
rows_processed  integer;
query           varchar2(2000);

begin

    source_cursor := dbms_sql.open_cursor;     dbms_sql.parse(source_cursor,'select query from all_snapshots where

                                  name = ''|| snapName || ''',2);

    dbms_sql.define_column(source_cursor,1, query, 2000);

    dbms_output.put_line(source_cursor);
    rows_processed := dbms_sql.execute(source_cursor);
    dbms_output.put_line(source_cursor);

    dbms_output.put_line(to_char(rows_processed));

    dbms_output.put_line('After Execute');     if dbms_sql.fetch_rows(source_cursor) > 0

    then                                                               create or
 replace procedure dta_snapshot(snapName   in  varchar2,
                                         db_Link    in  varchar2) is
source_cursor   integer;
new_cursor      integer;
new_snapshot    integer;
drop_cursor     integer;
rows_processed  integer;
query           varchar2(2000);

begin

    source_cursor := dbms_sql.open_cursor;     dbms_sql.parse(source_cursor,'select query from all_snapshots where

                                  name = ''|| snapName || ''',2);

    dbms_sql.define_column(source_cursor,1, query, 2000);

    dbms_output.put_line(source_cursor);
    rows_processed := dbms_sql.execute(source_cursor);
    dbms_output.put_line(source_cursor);

    dbms_output.put_line(to_char(rows_processed));

    dbms_output.put_line('After Execute');     if dbms_sql.fetch_rows(source_cursor) > 0

utput.put_line('In If Loop');

        dbms_sql.column_value(source_cursor,1,query);
        dbms_output.put_line(query);
    else
        dbms_output.put_line('No Data Found');
        new_cursor := dbms_sql.open_cursor;
        dbms_output.put_line('Open Cursor');
        dbms_output.put_line(snapName);
        dbms_sql.parse(new_cursor,'create snapshot avnish
                                   refresh  fast start with sysdate next
                                   sysdate+1 as select * from ' ||
                                   snapName,2);

        dbms_output.put_line(new_cursor);

        rows_processed := dbms_sql.execute(new_cursor);

    end if;
end;
/

during execution i am getting followin error

ROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "DTA.DTA_SNAPSHOT", line 29
ORA-06512: at line 2

                if anybody have solution then please mail


                                        avnsih

Received on Fri Feb 09 1996 - 07:37:54 CST