rem
rem Entry Backup/Recovery Package
rem
create or replace
PACKAGE entryBackupRecov IS
	 DnString 	ds_bkpattrstore.attrval%TYPE;
	 SUBTYPE DnType  IS DnString%TYPE;
    PROCEDURE entryBackup( 	inputdn IN  DnType);
    PROCEDURE entryRecovery( 	inputdn IN  DnType,
				version IN     INTEGER,
				report  IN     INTEGER);
    FUNCTION getEntryid ( inputdn IN DnType ) RETURN  INTEGER;
    FUNCTION getMaxVer ( inEntryId IN INTEGER ) RETURN  INTEGER;


END entryBackupRecov ;
/
create or replace 
PACKAGE BODY entryBackupRecov AS
 FUNCTION getEntryid ( inputdn IN DnType) RETURN INTEGER
 IS
	outEntryid  INTEGER;
 BEGIN
	IF inputdn is NULL then
		return 1;
	END IF;
	SELECT NVL(min(entryid), 0)
	INTO  outEntryid
	FROM  ds_bkpattrstore
	WHERE LOWER(REPLACE(attrval,' ','')) = LOWER(inputdn)
	AND attrname = 'orclentrydn';
	--dbms_output.put_line('DN specified ::'|| LOWER(inputdn) );
	--dbms_output.put_line('EntryId ::'|| outEntryid );
	 

	IF outEntryid = 0 then
	RAISE_APPLICATION_ERROR(-20101, 'DN specified not valid');
	END IF;

	RETURN outEntryid;

 END;
 FUNCTION getMaxVer ( inEntryid IN INTEGER) RETURN INTEGER
 IS
	outVersion  INTEGER;
 BEGIN
	SELECT NVL(max(to_number(attrver)), 0)
	INTO  outVersion
	FROM  ds_bkpattrstore
	WHERE entryid = inEntryid
	AND attrname = 'backupentryexist';
	 

	RETURN outVersion;
 END;
		 
 PROCEDURE entryBackup(	inputdn IN  DnType)
 IS
	newEntryId   INTEGER;
	origEntryId  INTEGER;
	version      INTEGER;
	CURSOR getEntries(id INTEGER) IS
		SELECT * from ds_attrstore
		WHERE entryid = id 
		AND attrname != 'backupentryexist';
 BEGIN
	SELECT ds_bkpattrstore_id.nextval INTO newEntryID FROM DUAL;
		origEntryId := getEntryid(inputdn);

	version := getMaxVer(origEntryId) - 4;

	FOR cur in getEntries(origEntryId)
	LOOP
		INSERT into ds_bkpattrstore 
                (entryid, attrname, attrval, attrkind, attrver, attrstype)
		values(newEntryId, cur.attrname,
			cur.attrval, cur.attrkind,
			cur.attrver, cur.attrstype);
	END LOOP;
	IF ( version > 0 ) THEN 
		DELETE from ds_bkpattrstore
		WHERE entryid = ( select to_number(b.attrval) 
		from ds_bkpattrstore b
		where b.entryid = origEntryId
		and b.attrname = 'backupentryexist'
		and b.attrver = to_char(version))
		AND attrname != 'backupentryexist';

		DELETE from ds_bkpattrstore
		WHERE entryid = origEntryId
		AND attrname = 'backupentryexist'
		AND attrver = to_char(version);
	END IF;
	--dbms_output.put_line('version::' || version);
			
	INSERT into ds_bkpattrstore
        (entryid, attrname, attrval, attrkind, attrver)
	values(origEntryId, 'backupentryexist', newEntryId,'b', 
		to_char(version + 5));
	COMMIT;
	EXCEPTION WHEN NO_DATA_FOUND THEN
	ROLLBACK;
	RAISE_APPLICATION_ERROR(-20102, 'Cannot Backup  the DN specified');
	WHEN OTHERS then
	ROLLBACK;
	RAISE_APPLICATION_ERROR(-20102, 'Cannot Backup  the DN specified');
 END entryBackup;

 PROCEDURE entryRecovery(inputdn IN  DnType,
			version IN     INTEGER,
			report  IN     INTEGER)
 IS
	 bkpEntry      INTEGER;
	 maxVer        INTEGER;	
	 origEntryId   INTEGER;
	 outofbound    EXCEPTION;
	 which         INTEGER;
	 attrtype      ds_bkpattrstore.attrname%TYPE;

	CURSOR recEntries(id INTEGER) IS
	SELECT * from ds_bkpattrstore
	WHERE entryid = id 
	AND attrname != 'backupentryexist'
	order by attrname;
 BEGIN
	origEntryId := getEntryid(inputdn);

	maxver := getMaxVer(origEntryId);
	which := version;
	IF ( (maxver - version) <= 0 ) THEN
		raise outofbound; 
	ELSE
		which := maxver - version ;
	END IF;

	SELECT to_number(attrval)
	INTO bkpEntry
	FROM ds_bkpattrstore 
	WHERE entryid = origEntryID
	and attrname = 'backupentryexist'
	and attrver = to_char(which);

				
	IF ( report = 0 ) then
		DELETE from ds_attrstore 
		where entryid = origEntryId;
	ELSE
		dbms_output.put_line('                        BACKUP VERSION DETAILS ');
		dbms_output.put_line('                        ====================== ');
		dbms_output.put_line('       For Dn  ::  ' || inputdn || '   Version ::'|| version);
		dbms_output.put_line('       ======================================');
		dbms_output.put_line('Attribut Name');
		dbms_output.put_line('-------------');
		dbms_output.put_line('Attribut Value');
		dbms_output.put_line('-------------');
	END IF;
	which := 0;

	FOR cur in recEntries(bkpEntry)
	LOOP
		IF ( report = 0 ) theN
		     INSERT into ds_attrstore 
                     (entryid, attrname, attrval, attrkind, attrver,attrstype)
		     values(origEntryId, cur.attrname,cur.attrval, cur.attrkind,
                                         cur.attrver, cur.attrstype);
		ELSE
			IF ( which = 0 ) then
			attrtype := cur.attrname;
			dbms_output.put_line(cur.attrname);
			END IF;
			IF ( attrtype = cur.attrname ) then
				dbms_output.put_line('	 '||cur.attrval);
		 	ELSE
				attrtype := cur.attrname;
				dbms_output.put_line(cur.attrname);
				dbms_output.put_line('	 '||cur.attrval);
			END IF;
		END IF;
		which := which + 1;
	END LOOP;

		IF ( report = 0 ) theN
			COMMIT;
		ELSE
		dbms_output.put_line('=============================================');
		dbms_output.put_line('=Total No of Records  ::'||which||'==========');
		dbms_output.put_line('=============================================');
		dbms_output.put_line('==============END OF REPORT==================');
		dbms_output.put_line('=============================================');
		END IF;

	EXCEPTION WHEN NO_DATA_FOUND THEN
		--dbms_output.put_line(' Could Not Backup Entry for the  specified DN::'|| inputdn  );
	ROLLBACK;
	RAISE_APPLICATION_ERROR(-20102, 'DN specified is not valid');
	WHEN OUTOFBOUND then
	ROLLBACK;
	RAISE_APPLICATION_ERROR(-20103, 'Backup version specified does not exist');

 END entryRecovery;
													
END entryBackupRecov;
/
rem
rem Done.
rem
