DML in Procedure

From: Marc Eggenberger <nw1_at_devnull.ch>
Date: Fri, 1 Aug 2003 12:06:54 +0200
Message-ID: <MPG.19945a2e8931430989691@news.cis.dfn.de>

Hi there.

I made a procedure for an import I have to do. As a requirement I have to create a backup of this table. So I inserted an drop table, create table at the beginning. But this doesnt seem to work. The procedure does not compile.

Are such statements (DML) not valid in procedures?

The system is 8.1.7.0.0 on Windows 2000 ADV SP3 The code is:

CREATE OR REPLACE PROCEDURE Import_from_Metadir IS

--Declarations
CURSOR metadir_cur IS

	   SELECT sobjUniqueKey, sPersonalNumber, sLastName, 
	   sFirstName, sNetzwerkLogin, sDepartment, sKST, 
sOfficePhoneNumber
	   FROM metadir_import.TBLPERSONALDATA;

	   

BEGIN
--Make backup of user_def

	 DROP TABLE ams.user_def_save;
	 CREATE TABLE ams.user_def_save AS SELECT * FROM ams.user_def;
	 


	 --Delete entries with no sNetzwerklogin (Username) from 

Importtable first
	 DELETE FROM metadir_import.TBLPERSONALDATA WHERE snetzwerklogin 

IS NULL;          
--Loop over all remaining entries and update the AMS Table
user_def

	 FOR metaupdate_rec IN metadir_cur
	 LOOP
	 	 UPDATE ams.user_def SET NAME = SUBSTR
(metaupdate_rec.sLastName,1,25) 
		 , Vorname = SUBSTR(metaupdate_rec.sFirstName,1,25) , 
Bereich = 
		 metaupdate_rec.sDepartment, Stammnr = 
metaupdate_rec.sPersonalNumber, 
		 Tel_Int = SUBSTR(metaupdate_rec.sOfficePhoneNumber,1,15) , 
Kostenstl = 
		 SUBSTR(metaupdate_rec.sKST,1,10), Unique_ID = 
		 metaupdate_rec.sobjUniqueKey
		 WHERE Username = metaupdate_rec.sNetzwerklogin;
	 END LOOP;
	 COMMIT;

END;
/

thanks for any hints.

-- 
mfg
Marc Eggenberger

Received on Fri Aug 01 2003 - 05:06:54 CDT