Home » SQL & PL/SQL » SQL & PL/SQL » create procedure problem
create procedure problem [message #7811] Thu, 10 July 2003 05:47 Go to next message
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 #7815 is a reply to message #7811] Thu, 10 July 2003 08:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
-- make sure the user have been granted the create view priv exclusively
-- But, why do u need to drop and recreate the view?
-- why not just recompile it?
dbadmin@mutation_mutation > get dr
  1      create or replace procedure drop_view
  2      as
  3      begin
  4      execute immediate ('drop view myview');
  5      execute immediate ('create view myview as select * from emp');
  6*    end;
dbadmin@mutation_mutation > /

Procedure created.

dbadmin@mutation_mutation > exec drop_view;

PL/SQL procedure successfully completed.

Re: create procedure problem [message #7853 is a reply to message #7815] Sat, 12 July 2003 22:54 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Oracle system features
Next Topic: Primary Key
Goto Forum:
  


Current Time: Thu Apr 18 08:04:19 CDT 2024