DECLARE
   V_COMANDO VARCHAR2(5000);
BEGIN
   V_COMANDO := 'CREATE TABLE MUNICIPIROTA_BKP AS (SELECT * FROM MUNICIPIROTA)'; 
   EXECUTE IMMEDIATE V_COMANDO;  
   COMMIT;
   V_COMANDO := 'DROP TABLE MUNICIPIROTA';
   EXECUTE IMMEDIATE V_COMANDO;
   COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         V_COMANDO := '';
END;
/

CREATE TABLE MUNICIPIROTA
(
   CDROTATRANS         	VARCHAR2(6)            	NOT NULL,
   CDPAIS               VARCHAR2(4)            	NOT NULL,
   SGESTADO            	VARCHAR2(2)            	NOT NULL,
   CDMUNICIPIO         	VARCHAR2(4)            	NOT NULL,
   CDBAIRRO            	VARCHAR2(10),
   NRORDMUNROTA        	VARCHAR2(3)            	NULL,
   VRFRETEROTA         	FLOAT                  	NULL,
		CONSTRAINT PK_MUNICIPIROTA PRIMARY KEY (CDROTATRANS, CDPAIS, SGESTADO, CDMUNICIPIO),
   NRORG             	NUMBER 	     			DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO           	VARCHAR2(1)     		DEFAULT 'S' NOT NULL
		CONSTRAINT CKC_IDATIVO_MUNICIPIROTA	 CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO        	DATE                  			NULL,
   DTULTATU          	DATE                  			NULL,
   NRORGINCLUSAO     	NUMBER          DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')		NULL,
   CDOPERINCLUSAO    	VARCHAR2(12)    DEFAULT '1' 		NULL,
   NRORGULTATU       	NUMBER          DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')		NULL,
   CDOPERULTATU      	VARCHAR2(12)    DEFAULT '1'
)
/

DECLARE
  V_COMANDO VARCHAR2(5000);
BEGIN 
   V_COMANDO := 'INSERT INTO MUNICIPIROTA SELECT * FROM MUNICIPIROTA_BKP';
   EXECUTE IMMEDIATE V_COMANDO;
   COMMIT;  
   V_COMANDO := 'DROP TABLE MUNICIPIROTA_BKP';
   EXECUTE IMMEDIATE V_COMANDO;
   EXCEPTION
      WHEN OTHERS THEN
         V_COMANDO := ''; 
END;
/