create procedure problem [message #7811] |
Thu, 10 July 2003 05:47 |
Abu Arbab
Messages: 13 Registered: June 2003
|
Junior Member |
|
|
Hi expert
I need to create pl/sql procedure in order to drop view and recreated again and running this procedure thru the job schedule.
I appreciate your answer
Thanks
|
|
|
|
Re: create procedure problem [message #7853 is a reply to message #7815] |
Sat, 12 July 2003 22:54 |
Abu Arbab
Messages: 13 Registered: June 2003
|
Junior Member |
|
|
Hi Mr.Mahesh
I need to drop And re create the view because I am not able to refresh it .the view is Materialized view but it is not refreshing automatically I try to enable
QUERY REWRITE but unfortunately the option is not instaled in the server . it is not accepting that is why I need to drop and recreate it again .Thanks for answering my question
|
|
|
Re: create procedure problem [message #7865 is a reply to message #7815] |
Mon, 14 July 2003 02:25 |
Abu Arbab
Messages: 13 Registered: June 2003
|
Junior Member |
|
|
I am trying to execute the procedure using toad. when i execute the statements using begin ,end command thru toad as following it is working fine:-
begin
EXECUTE IMMEDIATE 'drop materialized view QAP_MS_VIEW';
EXECUTE IMMEDIATE 'create materialized view QAP_MS_VIEW as select * from QAP_document ';
EXECUTE IMMEDIATE' grant select on QAP_MS_VIEW to qry_lims';
end ;
but when i put the same statements in the procedure as mentioned below and try to execute the procedure it is giving an error insufficient privileges
command
CREATE OR REPLACE procedure qap_run_MT_View
as
BEGIN
EXECUTE IMMEDIATE 'drop materialized view QAP_MS_VIEW';
EXECUTE IMMEDIATE 'create materialized view QAP_MS_VIEW as select * from QAP_document ';
EXECUTE IMMEDIATE' grant select on QAP_MS_VIEW to qry_lims';
END;
/
please any one can advise
thanks
|
|
|