In Oracle, the stored procedure of table DROP and table CREATE is determined.

Create or replace procedure PD_GENERATE_HELP_LAST_TABLES IS
-- Generate a local data table for the view vw_last_xxx generated from sqlserver
TabNocount NUMBER;
V_ SQL VARCHAR2 (200 );

BEGIN
-- Judge the table dat_help_last_scqy1)
SELECT count (*) INTO tabNocount FROM user_tables WHERE table_name = 'dat _ HELP_LAST_SCQY1 ';
IF tabNocount> 0 THEN
Dbms_output.put_line ('drop table dat_help_last_scqy1 ');
V_ SQL: = 'drop TABLE DAT_HELP_LAST_SCQY1 ';
Execute immediate v_ SQL;
End if;
Dbms_output.put_line ('create table dat_help_last_scqy1 ');
V_ SQL: = 'create TABLE DAT_HELP_LAST_SCQY1 AS SELECT * FROM VW_LAST_SCQY1 ';
Execute immediate v_ SQL;
-- Judge the table dat_help_last_scqy2 (monthly report of refined oil production)
 
SELECT count (*) INTO tabNocount FROM user_tables WHERE table_name = 'dat _ HELP_LAST_SCQY2 ';
IF tabNocount> 0 THEN
Dbms_output.put_line ('drop table dat_help_last_scqy2 ');
V_ SQL: = 'drop TABLE DAT_HELP_LAST_SCQY2 ';
Execute immediate v_ SQL;
End if;
Dbms_output.put_line ('create talbe dat_help_last_scqy2 ');
V_ SQL: = 'create TABLE DAT_HELP_LAST_SCQY2 AS SELECT * FROM VW_LAST_SCQY2 ';
Execute immediate v_ SQL;
-- Judge the table dat_help_last_shsndybb (Energy scheduling Monthly Report)
SELECT count (*) INTO tabNocount FROM user_tables WHERE table_name = 'dat _ HELP_LAST_SHSNDYBB ';
IF tabNocount> 0 THEN
Dbms_output.put_line ('drop table dat_help_last_shsndybb ');
V_ SQL: = 'drop TABLE DAT_HELP_LAST_SHSNDYBB ';
Execute immediate v_ SQL;
End if;
Dbms_output.put_line ('create table dat_help_last_shsndybb ');
V_ SQL: = 'create TABLE dat_help_last_shsndybb AS SELECT * FROM vw_last_shsndybb ';
Execute immediate v_ SQL;
END PD_GENERATE_HELP_LAST_TABLES;

 

Test

Begin
-- Call the procedure
Pd_generate_help_last_tables;
End;

 

If ORA-01031: insufficient Permissions
ORA-06512: In "SYSTEM. CCCCCC", line 6
ORA-06512: In line 1

The following authorization statement is executed:

Grant create table to distribute DC;
Grant resource to your DC;
Grant create procedure to your DC;