Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\NamespaceServiceImpl.pkb

CREATE OR REPLACE PACKAGE BODY NamespaceServiceImpl AS

TYPE TTYPE is REF CURSOR;
TYPE TAB_TYPE is table of number index by binary_integer;
reservingParentID Reservation_Store.parentID%TYPE ;
reservingDomain Reservation_Store.domain%TYPE ;
reservingName Reservation_Store.reservedName%TYPE ;
reservingMode Reservation_Store.isPhys%TYPE ;
reservingISOID Reservation_Store.languageISOID%TYPE;
reservingElemId Reservation_Store.elementId%TYPE;

-- following representation of parentId, domain, name, mode As one key
reservingUK VARCHAR2(4000); -- As owb max name length is 2000
reservingSessionID NUMBER;

reservingInstanceID number;

reservingUser Reservation_Store.osUSer%TYPE;
--need to be inited
reservingSessionSerial Reservation_Store.sessSerial%TYPE ;
reservingSessionTime Reservation_Store.sessLogonTime%TYPE;

upperCasePhyName NUMBER;
-- pysical/logical name distinguisher CONTANTS
n_PhysicalName CONSTANT NUMBER(1) := 1;
n_LogicalName CONSTANT NUMBER(1) := 2;
n_PropagationRequired CONSTANT NUMBER(1) := 1;
n_RepositoryCheckRequired CONSTANT NUMBER(1) := 1;
n_UpperCasePhysicalName CONSTANT NUMBER(1) := 1;

-- MDL performance related CONTANTS
n_BulkMode CONSTANT NUMBER(1) := 1;
n_SingleUserLock CONSTANT NUMBER(1) := 1;
n_SingleUserLockIgnore CONSTANT NUMBER(1) := 0;
v_Constraint CONSTANT VARCHAR2(10) := 'Constraint';

--reserve prefix
TYPE t_ReservePrefix_List IS VARRAY(1) OF VARCHAR2(100) NOT NULL;
v_ReservePrefix_List t_ReservePrefix_List := t_ReservePrefix_List('OWB$');

-- reserve list names
-- PLEASE NOTE 210 COUNT OF RESERVED WORD LIST AND
-- isItOWBReservedWordChecker depends upon this sbscripts
-- so adding or deleting from this list please make sure
-- this count is in SYNC with reservedWordChecker

TYPE t_Reserve_List IS VARRAY(212) OF VARCHAR2(100) NOT NULL;
-- total number of reserved words 120+92 = 212
v_Reserve_List t_Reserve_List := t_Reserve_List('ACCESS', 'ADD', 'ALL', 'ALTER', 'AND',
    'ANY', 'AS', 'ASC', 'AUDIT', 'BETWEEN', 'BY', 'CHAR', 'CHECK', 'CLUSTER', 'COLUMN',
    'COMMENT', 'COMPRESS', 'CONNECT', 'CREATE', 'CURRENT', 'DATE', 'DECIMAL', 'DEFAULT',
    'DELETE', 'DESC', 'DISTINCT', 'DROP', 'ELSE', 'EXCLUSIVE', 'EXISTS', 'FILE', 'FLOAT',
    'FOR', 'FROM', 'GRANT', 'GROUP', 'HAVING', 'IDENTIFIED', 'IMMEDIATE', 'IN', 'INCREMENT',
    'INDEX', 'INITIAL', 'INSERT', 'INTEGER', 'INTERSECT', 'INTERVAL', 'INTO', 'IS', 'LEVEL', 'LIKE',
    'LOCK', 'LONG', 'MAXEXTENTS', 'MINUS', 'MLSLABEL', 'MODE', 'MODIFY', 'NOAUDIT', 'NOCOMPRESS',
    'NOT', 'NOWAIT', 'NULL', 'NUMBER', 'OF', 'OFFLINE', 'ON', 'ONLINE', 'OPTION', 'OR', 'ORDER',
    'PCTFREE', 'PRIOR', 'PRIVILEGES', 'PUBLIC', 'RAW', 'RENAME', 'RESOURCE', 'REVOKE', 'ROW',
    'ROWID', 'ROWNUM', 'ROWS', 'SELECT', 'SESSION', 'SET', 'SHARE', 'SIZE', 'SMALLINT',
    'START', 'SUCCESSFUL', 'SYNONYM', 'SYSDATE', 'TABLE', 'THEN', 'TO',
    'TRIGGER','UID', 'UNION','UNIQUE', 'UPDATE', 'USER', 'VALIDATE', 'VALUES', 'VARCHAR',
    'VARCHAR2', 'VIEW', 'WHENEVER', 'WHERE', 'WITH', 'NAME_ALREADY_RESERVED_BY_YOU',
    'NAME_RESERVED_BY_OTHER_SESSION', 'PEER_RESERVATION', 'NAME_ALREADY_COMMITTED',
    'OWB_NS_FATAL_ERROR' , 'OWBNOOP','MATERIALIZED_VIEW','WB_CUSTOM_TRANS','WB_PREDEFINED_TRANS',
    --plsqls reserved words
    'ARRAY', 'AT', 'AUTHID', 'AVG', 'BEGIN', 'BINARY_INTEGER', 'BODY', 'BOOLEAN',
    'BULK', 'CHAR_BASE', 'CLOSE', 'COLLECT', 'COMMIT', 'CONSTANT', 'CURRVAL', 'CURSOR',
    'DECLARE', 'DO', 'ELSIF', 'END', 'EXCEPTION', 'EXECUTE', 'EXIT', 'EXTENDS',
    'FALSE', 'FETCH', 'FORALL', 'FUNCTION', 'GOTO', 'HEAP', 'IF', 'INDICATOR',
    'INTERFACE', 'INTERVAL', 'ISOLATION', 'JAVA', 'LIMITED', 'LOOP', 'MAX', 'MIN',
    'MOD', 'NATURAL', 'NATURALN', 'NEW', 'NEXTVAL', 'NOCOPY',
    'NUMBER_BASE', 'OCIROWID', 'OPAQUE', 'OPEN', 'OPERATOR', 'ORGANIZATION',
    'OTHERS', 'OUT', 'PACKAGE', 'PARTITION', 'PLS_INTEGER', 'POSITIVE', 'POSITIVEN',
    'PRAGMA', 'PRIVATE', 'PROCEDURE', 'RAISE', 'REAL', 'RECORD', 'REF', 'RELEASE',
    'RETURN', 'REVERSE', 'ROLLBACK', 'ROWTYPE', 'SAVEPOINT', 'SECOND', 'SEPARATE',
    'SPACE', 'SQL', 'SQLCODE', 'SQLERRM', 'STDDEV', 'SUBTYPE', 'SUM',
    'TIMESTAMP', 'TRUE', 'TYPE', 'USE', 'VARIANCE', 'WHEN', 'WHILE', 'WORK',
    'WRITE', 'ZONE'
    );

--log Error happend during flow
PROCEDURE logErrorMessage(errCode NUMBER, errorMessage VARCHAR2, information VARCHAR2)
IS
v_ErrorTime Owb_Error_Log_Table.WhenHappend%TYPE;
PRAGMA AUTONOMOUS_TRANSACTION; --needed 
BEGIN
	SELECT to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') INTO v_ErrorTime FROM dual;
	INSERT INTO Owb_Error_Log_Table(errcode, errtext, info, whenhappend)
		VALUES(errCode, errorMessage,
		information ,
		v_ErrorTime);
	COMMIT;
EXCEPTION
	-- even error might happen when inserting into error log
	WHEN OTHERS THEN
		ROLLBACK;
		RETURN;

END;

PROCEDURE sochen_debug(information VARCHAR2)
IS

PRAGMA AUTONOMOUS_TRANSACTION; --needed 
BEGIN

null;--    INSERT INTO sochen_debug(info) values(information);

COMMIT;
EXCEPTION
	-- even error might happen when inserting into error log
	WHEN OTHERS THEN
		ROLLBACK;
		RETURN;

END;

-- following methods are intended to debug only
PROCEDURE NamespaceDebug(debugMessage VARCHAR2)
IS
BEGIN
	null;
    --DBMS_OUTPUT.PUT_LINE('NSDEBUG : '|| debugMessage);
END NamespaceDebug;

PROCEDURE NamespaceDebugSetOff
Is
BEGIN
	DBMS_OUTPUT.DISABLE;
END NamespaceDebugSetOff;

PROCEDURE NamespaceDebugSetOn
IS
BEGIN
	NamespaceDebugSetOff();
	DBMS_OUTPUT.ENABLE(1000000);
END NamespaceDebugSetOn;

--un do reservation for a session
PROCEDURE unReserveSession(
	currSession IN Reservation_Store.sessID%TYPE, currInstance in number)	
	IS
	v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
	v_ErrorText Owb_Error_Log_Table.errText%TYPE;
	PRAGMA AUTONOMOUS_TRANSACTION; --needed 
	
BEGIN
	NamespaceDebug('running delete for session ID : ' || currSession);
	DELETE FROM Reservation_Store WHERE sessID = currSession and instanceID = currInstance;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN
		-- need to separate transaction
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while unreserving for : ' || to_char(currSession));
		ROLLBACK;
		return;
	--NOOP , As deletion from Reservation table is fatal exception
	-- but it is not fatal for OWB , its just few names are reservaed wrongly
	-- for a client but shouldnt stop OWB client to stop working with OWB
END unReserveSession;

-- check if reservation already exist by some other session
FUNCTION doesReservationExist(
		whoReservedIt out VARCHAR2,
		whatNameToCheck IN Reservation_Store.reservedName%TYPE,
		whatRunningMode IN Reservation_Store.isPhys%TYPE,
		whichSessionReserved out NUMBER,
	        whichInstanceReserved out number,
		singleUserLock IN NUMBER)
	RETURN BOOLEAN IS

CURSOR Reservation_cache IS
		SELECT * from Reservation_Store;
	v_Reservation_cache_record Reservation_cache%ROWTYPE;
	v_existReservation VARCHAR2(4000) ; --stores 2000 bytes long logicalname
	v_ErrorText Owb_Error_Log_Table.errText%TYPE;
	v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;

PRAGMA AUTONOMOUS_TRANSACTION;	 -- needed 
BEGIN
-- FULL SCAN WAS IMPORTANT becoz of logical names to be 2000 characters
-- and UK of more than 2k size would be required bigger DB block size
-- which is little too much to ask for...
/*

OPEN Reservation_cache;
	LOOP
	    FETCH Reservation_cache INTO v_Reservation_cache_record;
	    EXIT WHEN Reservation_cache%NOTFOUND;
	    v_existReservation := v_Reservation_cache_record.parentID
 			|| '.' || v_Reservation_cache_record.domain
			|| '.' || v_Reservation_cache_record.reservedName
			|| '.' || v_Reservation_cache_record.isPhys;
	    IF(v_existReservation = keyToCheck) THEN
                whoReservedIt := v_Reservation_cache_record.osUser;
                whichSessionReserved := v_Reservation_cache_record.sessID;
		CLOSE Reservation_cache;
		return TRUE;
	    END IF;
	END LOOP;
     	CLOSE Reservation_cache;
        return FALSE;
*/
	IF (singleUserLock = n_SingleUserLock) THEN RETURN FALSE;
	ELSE
	 BEGIN
		INSERT INTO Reservation_Store (
			parentID, domain, reservedName,
			isPhys, sessID,instanceID, sessSerial,
			sessLogonTime, osUser,languageISOID)
		VALUES(reservingParentID, reservingDomain, whatNameToCheck,
					whatRunningMode, reservingSessionID,reservingInstanceID, reservingSessionSerial,
					reservingSessionTime, reservingUser,reservingISOID);
		/* -- Why DELETE when ROLLBACK follows anyway?
		DELETE
		FROM Reservation_Store
		WHERE   parentID = reservingParentID AND
			domain = reservingDomain AND
			reservedName = whatNameToCheck AND
			isPhys = whatRunningMode;
		*/
		ROLLBACK;
		RETURN FALSE;
		EXCEPTION
			-- some session wins race and does insert before
			-- current session , then oracle wont let PK to occur in
			-- Reservation Store
			WHEN DUP_VAL_ON_INDEX THEN
				SELECT osUser, sessID,instanceID INTO whoReservedIt, whichSessionReserved, whichInstanceReserved
				FROM Reservation_Store
				WHERE parentID = reservingParentID
					AND domain = reservingDomain
					AND reservedName = whatNameToCheck
					AND isPhys = whatRunningMode
					AND languageISOID = reservingISOID;
				ROLLBACK;
				RETURN TRUE;
			WHEN OTHERS THEN
				v_ErrorCode := SQLCODE;
				v_ErrorText := SUBSTR(SQLERRM, 1, 500);
				logErrorMessage(v_ErrorCode, v_ErrorText,
					'Oracle Error occured, while reserving for : ' || reservingUK );
				ROLLBACK;
				RETURN FALSE;
	 END;
	END IF;
END doesReservationExist;

--return classification
FUNCTION getClassification(
		givenClassName IN VARCHAR2)
	RETURN VARCHAR2 IS
	retValue Classification_Store.domainName%TYPE;
BEGIN
	SELECT domainName INTO retValue FROM Classification_Store WHERE className = givenClassName;
	return retValue;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	return givenClassName;
	WHEN OTHERS THEN
	return givenClassName;
	
END getClassification;

-- check if reservation already exist by some other session
-- note: previously used INSERT/ROLLBACK technique.
--       relaxed to use SELECT existence check due to bug 4263404
--may 1st, 08: this is not check on commited anymore, it actualy check is name in global db cache , which includes commited and this session's uncommitted data
FUNCTION isAlreadyInGlobalCache( theName IN VARCHAR2, whatmode IN NUMBER, singleUserLock IN NUMBER )
	RETURN BOOLEAN IS

v_ErrorText Owb_Error_Log_Table.errText%TYPE;
	v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
	v_Exists NUMBER(1);
	tempHolder VARCHAR2(4000) ;

-- PRAGMA AUTONOMOUS_TRANSACTION; -- sochen: remove autonomous since we need to see the uncommitted info from parent txn	
BEGIN
 IF (singleUserLock = n_SingleUserLock) THEN RETURN FALSE;
 ELSE
  v_Exists := 0;
  BEGIN
	SELECT 1 INTO v_Exists
	from CMPALLFOLDERFCONAMES
	where parentID = reservingParentID and domain = reservingDomain and
	      name = theName and isPhys = whatmode and languageISOID = reservingISOID;

if (v_Exists = 1) then
               -- sochen_debug('-->isCommited!! on:'||'pid:'||reservingParentID||', domain:'||reservingDomain||',name:'||theName||', languageisoid:'||reservingISOID||', isPhys:'||whatmode);

RETURN TRUE;
	else

--sochen_debug('-->is NOT Commited!! on:'||'pid:'||reservingParentID||', domain:'||reservingDomain||',name:'||theName||', languageisoid:'||reservingISOID||', isPhys:'||whatmode);

RETURN FALSE;
	end if;
	EXCEPTION
		WHEN OTHERS THEN
			v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);
			tempHolder := reservingParentID || '.' || reservingDomain|| '.' || theName || '.' || whatmode ||'.'||reservingISOID;

logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while checking for Already Committed data : ' || tempHolder);
			RETURN FALSE;
  END;
 END IF;
END isAlreadyInGlobalCache;

PROCEDURE cleanSIDs
IS
	v_serial Reservation_Store.sessSerial%TYPE;
	v_login Reservation_Store.sessLogonTime%TYPE;
        v_session Reservation_Store.sessID%TYPE;
	v_instanceID number;
	CURSOR sql_sessID IS
		SELECT DISTINCT sessID,instanceID  FROM Reservation_Store;
	CURSOR reservation_sess_info(currSession Reservation_Store.sessID%TYPE,currInstance number) IS
		SELECT 	sessSerial, sessLogonTime
		FROM Reservation_Store WHERE sessID=currSession and instanceID = currInstance;
	
	alreadyReservedSID Reservation_Store.sessID%TYPE;
	alreadyReservedInstID number;
	DBlogin_time Reservation_Store.sessLogonTime%TYPE;
	whatOp VARCHAR2(40);
	DBserialNumber Reservation_Store.sessSerial%TYPE;
BEGIN
	OPEN sql_sessID;
	LOOP
		FETCH sql_sessID INTO alreadyReservedSID, alreadyReservedInstID;
		EXIT WHEN sql_sessID%NOTFOUND;
	    BEGIN
		NamespaceDebug('Already Reserved Sid : '||to_char(alreadyReservedSID));
        	SELECT serial#, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss')
			INTO DBserialNumber,DBlogin_time
			FROM gv$session
			WHERE sid = alreadyReservedSID and inst_id = alreadyReservedInstID;
		BEGIN

NamespaceDebug('DB Serail Number : ' ||  to_char(DBSerialNumber));
			NamespaceDebug('DB Login Time : ' || DBlogin_time);
			NamespaceDebug('Serial Number : ' || to_char(v_serial));
			NamespaceDebug('DB Login Time : ' || v_login);

OPEN reservation_sess_info(alreadyReservedSID, alreadyReservedInstID);
			FETCH reservation_sess_info INTO v_serial, v_login;
			NamespaceDebug('After fetching Serial Number : ' || to_char(v_serial));
			NamespaceDebug('After fetching DB Login Time : ' || v_login);

IF SQL%NOTFOUND THEN
				NamespaceDebug('cleanSIDS SQL NOT FOUND');
				whatOp := 'NOOP';
			END IF;
			IF SQL%ROWCOUNT = 0 THEN
				NamespaceDebug('cleanSIDs ROWCOUNT is 0');
				whatOp := 'NOOP';
			END IF;
			IF(DBserialNumber = v_serial) THEN
				NamespaceDebug('Serial Nmber is Same ');
				IF(DBlogin_time = v_login) THEN
					NamespaceDebug('Login Time is Same');
					whatOp := 'NOOP';
				ELSE
					NamespaceDebug('Login Name is different - calling unreserve');
					unReserveSession(alreadyReservedSID,alreadyReservedInstID );
				END IF;

ELSE
				NamespaceDebug('Serial number different - calling unreserve');
				unReserveSession(alreadyReservedSID, alreadyReservedInstID);
			END IF;
			CLOSE reservation_sess_info;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
			whatOp := 'NOOP';
		END;
	    EXCEPTION
			WHEN NO_DATA_FOUND THEN
			NamespaceDebug('sid doesnt exist in gv$session any more ');
			unReserveSession(alreadyReservedSID, alreadyReservedInstID);	
	    END;	
	
	END LOOP;
	CLOSE sql_sessID;
END cleanSIDs;

FUNCTION doesItStartWithRsvdPrefix(nameToCheck VARCHAR2)
	RETURN BOOLEAN IS	
	nameUpper VARCHAR2(200);
	namePrefixUpper VARCHAR2(200);

len INTEGER;
BEGIN
	nameUpper := Upper(nameToCheck);
		
	FOR i in 1..v_ReservePrefix_List.COUNT LOOP
		
		IF (v_ReservePrefix_List(i) IS NOT NULL ) THEN
			len := LENGTH(v_ReservePrefix_List(i));
			IF(LENGTH(nameUpper) >= len) THEN

namePrefixUpper:= SUBSTR(nameUpper,0,len);

IF(v_ReservePrefix_List(i)=namePrefixUpper) THEN		
					return TRUE;
				END IF;
			END IF;
	        END IF;
	END LOOP;
	return FALSE;
END doesItStartWithRsvdPrefix;

FUNCTION isItOWBreservedWord(nameToCheck IN VARCHAR2, modeToCheck NUMBER)
	RETURN BOOLEAN IS
	nameUC VARCHAR2(4000);
BEGIN

IF (modeToCheck = n_LogicalName) THEN
		return FALSE;
	END IF;

nameUC := UPPER(nameToCheck);
	NamespaceDebug('checking name ' || nameUC || ' is reserved OWB word ?');

--not 210 hardcode
	--FOR i IN 1..210 LOOP
	FOR i in 1..v_Reserve_List.COUNT LOOP
		IF (v_Reserve_List(i) IS NOT NULL ) THEN
			NamespaceDebug('ReserveWord['||to_char(i)||']=' || v_Reserve_List(i));
			IF(LENGTH(v_Reserve_List(i)) > 0) THEN
				IF (nameUC = v_Reserve_List(i)) THEN
					return TRUE;
				END IF;
			END IF;
		ELSE
			NamespaceDebug(' NULL ReserveWord['||to_char(i)||']=' || v_Reserve_List(i));
		END IF;
	END LOOP;
	return FALSE;
END isItOWBreservedWord;

/*
when generate unique name, if found reserved by other session, then append _i, if found reserved by this very session and hte name is not in global namespace cache(cmpfolderfconames) then this name can be used; if found not reserved  and not in global namespace cache, this name can be used; if not reserved but in global namespace cache this name can not be used and must append _i 
*/
PROCEDURE generateUniqueName(
		oppName OUT VARCHAR2,
		value IN VARCHAR2,
		givenmode IN NUMBER,
		checkRepos IN NUMBER,
		singleUserLock IN NUMBER, 
		     db_platform_name in varchar2

) IS
	genedName VARCHAR2(4000);
	i NUMBER := 0;
	newLength NUMBER;
	maxLength NUMBER;
--	pos NUMBER;
	v_found BOOLEAN := FALSE;
	checkKey VARCHAR2(4000);
        currMode NUMBER;
	tempWhoReservedIt Reservation_Store.osUser%TYPE;
	tempWhichSession Reservation_Store.sessID%TYPE;
	tempWhichInstance number;
	input_value VARCHAR2(250);

BEGIN

input_value:=value;

--sochen:9/26/02 first check whether input name starts with rsvd prefix
	--if does,we append A_ to it
	IF(givenmode =n_PhysicalName) THEN
		IF(doesItStartWithRsvdPrefix(value)) THEN
			input_value := 'A_'||value;	
		END IF;
	END IF;

genedName := input_value;
	if(NamespaceRules.getNameLengthSemantics(db_platform_name)='CHARACTER') then 
	newLength := LENGTH(input_value);
        else
	newLength := LENGTHB(input_value);
	end if;

IF(givenmode = n_PhysicalName) THEN
		maxLength := NamespaceRules.getNameMaxLength(db_platform_name);----30 ; -- hard code , As physical DB object NOT > 30
		currMode := n_PhysicalName;
	ELSE
		maxLength := 200 ;
		currMode := n_LogicalName;
	END IF;
        IF(newLength > maxLength) THEN
		if(NamespaceRules.getNameLengthSemantics(db_platform_name)='CHARACTER') then 
			genedName := SUBSTR(genedName,0,maxLength);
                else
			genedName := SUBSTRB(genedName,0,maxLength);

end if;
	END IF;

--fix bug: 2595419
	--since in Asia lang, the SUBSTRB may cause end of left str are not
	--a complete character,seems DB will cause it to be blank
	IF(givenmode = n_PhysicalName) THEN
		genedName := REPLACE(genedName,' ','_');
	
	END IF;
        input_value := genedName;
	LOOP -- loop through until find uniq name
 		IF(i > 0) THEN
                    genedName := input_value || '_' || i;
                    IF(NamespaceRules.getNameLengthSemantics(db_platform_name)='CHARACTER') then 
                       if (LENGTH(genedName) > maxLength) THEN
                          genedName := SUBSTR(
                            input_value,
                            0,
                            LENGTH(input_value) - (LENGTH(genedName) - maxLength))  || '_' || i;
                       END IF;
                    ELSE
                       if (LENGTHB(genedName) > maxLength) THEN
                          genedName := SUBSTRB(
                            input_value,
                            0,
                            LENGTHB(input_value) - (LENGTHB(genedName) - maxLength)) || '_' || i;
                       END IF;
                    END IF;
-- rspaul bug 7006241
-- this did not work with larger numbers. With i 300, pos becomes 30 - 30 - 2 = (-2)
--                      pos := maxLength -i/10 -2;
--			IF(pos >= newLength) THEN
--				genedName := input_value || '_' || i;
--			ELSE
--				genedName := SUBSTRB(input_value,0,pos) || '_' || i;
--			END IF;
		END IF;
		NamespaceDebug('gened name ' || genedName);
	
		--fix bug: 2595419
		IF(givenmode = n_PhysicalName) THEN
			genedName := REPLACE(genedName,' ','_');
	
		END IF;
	
		IF(isItOWBreservedWord(genedName, currMode)) THEN
			i:= i+1;
		NamespaceDebug('Reserved Word');
		ELSE
			NamespaceDebug('Not a Reserved Word');
			checkKey := reservingParentID || '.' || reservingDomain ||
				'.' || genedName || '.' || currMode;
			NamespaceDebug('name to check : ' || checkKey);

IF(doesReservationExist(tempWhoReservedIt,genedName, currMode,tempWhichSession, tempWhichInstance,singleUserLock)) THEN

--fix bug 7428602 
				-- if reserved by this very session and also does not in global cache, this session can use this name 
                               IF(tempWhoReservedIt = reservingUser and tempWhichSession = reservingSessionID and tempWhichInstance = reservingInstanceID and isAlreadyInGlobalCache(genedName, currMode, singleUserLock)= FALSE ) THEN
                                     v_found := TRUE;

ELSE
				-- need to continue;
				NamespaceDebug('Already Reserved by other session or used by this session although not committed');
				i := i + 1;

END IF;
			ELSE
				NamespaceDebug('Not Reserved');

IF(checkRepos = n_RepositoryCheckRequired) THEN
				    IF(isAlreadyInGlobalCache(genedName, currMode, singleUserLock)) THEN
					-- need to continue
					i := i +1;	
					NamespaceDebug('Already Commited');
				    ELSE
					-- okay to use this name
					NamespaceDebug('found oked name : ' || checkKey);
					v_found := TRUE;
				    END IF;
				ELSE
				    NamespaceDebug('found oked name : ' || checkKey);
				    v_found := TRUE;
				END IF;
			END IF;	
	 				
		END IF;
			
	EXIT WHEN v_found = TRUE;	
	END LOOP;

oppName := genedName;
	NamespaceDebug(' returning gened name : ' || oppName);
		
END generateUniqueName;

-- generate physical name from logical name
FUNCTION genePhyNameFromLogName(
		givenLogicalName IN VARCHAR2,db_platform_name in varchar2)
	RETURN VARCHAR2 IS
	oppName VARCHAR2(4000);
	tempHolder VARCHAR2(4000);
	okExit BOOLEAN := FALSE;
        len number;
        max_len number;
BEGIN
	IF(givenLogicalName IS NULL) THEN
		RETURN 'NewObject';
        END IF;
	oppName := givenLogicalName;
	oppName := REPLACE(oppName, ' ', '_');
	oppName := REPLACE(oppName, '~');
	oppName := REPLACE(oppName, '`');
	oppName := REPLACE(oppName, '!');
	oppName := REPLACE(oppName, '@');
	oppName := REPLACE(oppName, '%');
	oppName := REPLACE(oppName, '^');
	oppName := REPLACE(oppName, '&');
	oppName := REPLACE(oppName, '*');
	oppName := REPLACE(oppName, '(');
	oppName := REPLACE(oppName, ')');
	oppName := REPLACE(oppName, '+');
	oppName := REPLACE(oppName, '-');
	oppName := REPLACE(oppName, '=');
	oppName := REPLACE(oppName, ',');
	oppName := REPLACE(oppName, '<');
	oppName := REPLACE(oppName, '.');
	oppName := REPLACE(oppName, '>');
	oppName := REPLACE(oppName, '/');
	oppName := REPLACE(oppName, '?');
	oppName := REPLACE(oppName, ':');
	oppName := REPLACE(oppName, ';');
	oppName := REPLACE(oppName, '''');
	oppName := REPLACE(oppName,'"');
	oppName := REPLACE(oppName, '\');
	oppName := REPLACE(oppName, '|');
	oppName := REPLACE(oppName, ']');
	oppName := REPLACE(oppName, '}');
	oppName := REPLACE(oppName, '[');
	oppName := REPLACE(oppName, ' ');
	oppName := REPLACE(oppName, '{');
	oppName := REPLACE(oppName, '\t');
	oppName := REPLACE(oppName, '\n');
	NamespaceDebug(' Name before loop : '||oppName);
	LOOP -- loop through until all illegal start is gone
		IF(oppName IS NULL) THEN
			oppName := 'NewObject';
			tempHolder := SUBSTR(oppName,1,1);
			okExit := TRUE;	
		ELSIF (LENGTH(oppName) >= 1) THEN
			tempHolder := SUBSTR(oppName,1,1);
			okExit := FALSE;
		ELSE
			oppName := 'NewObject';
			tempHolder := SUBSTR(oppName,1,1);
			okExit := TRUE;
		END IF;
		IF (tempHolder IN ('_','#','$','1','2','3','4','5','6','7','8','9','0','')) THEN
			okExit := FALSE;
		ELSE
			okExit := TRUE;
		END IF;
		NamespaceDebug('Name under process : ' || oppName);
		NamespaceDebug('first character : ' || tempHolder);
	EXIT WHEN okExit = TRUE;
		oppName := LTRIM(oppName);
		oppName := RTRIM(oppName);
		oppName := LTRIM(oppName,'_');
		oppName := LTRIM(oppName,'#');
		oppName := LTRIM(oppName,'$');
		oppName := LTRIM(oppName,'1');
		oppName := LTRIM(oppName,'2');
		oppName := LTRIM(oppName,'3');
		oppName := LTRIM(oppName,'4');
		oppName := LTRIM(oppName,'5');
		oppName := LTRIM(oppName,'6');
		oppName := LTRIM(oppName,'7');
		oppName := LTRIM(oppName,'8');
		oppName := LTRIM(oppName,'9');
		oppName := LTRIM(oppName,'0');
	END LOOP;
       
        
	if(NamespaceRules.getNameLengthSemantics(db_platform_name)='CHARACTER') then 
	  len :=LENGTH(oppName);
        else
	  len :=LENGTHB(oppName);
	end if;
	max_len:=	 NamespaceRules.getNameMaxLength(db_platform_name);

IF(len > max_len)
	
	THEN 	
		if(NamespaceRules.getNameLengthSemantics(db_platform_name)='CHARACTER') then 
		oppName := SUBSTR(oppName, 0, max_len);
		else
		oppName := SUBSTRB(oppName, 0, max_len);
                end if;

oppName := REPLACE(oppName,' ','_'); ---- fix 2595419

return oppName;
	ELSE
		IF(LENGTH(oppName) > 0)
		THEN
			return oppName;
		ELSE
			return 'NewObject';
		END IF;
	END IF;
	
END genePhyNameFromLogName;

-- generate opp physical name
PROCEDURE generatePhysicalName(
		oppName OUT VARCHAR2, checkRepos IN NUMBER, singleUserLock IN NUMBER,db_platform_name in varchar2)

IS
perspectiveName VARCHAR2(4000);
BEGIN
	perspectiveName := genePhyNameFromLogName(reservingName, db_platform_name);
	IF (upperCasePhyName = n_UpperCasePhysicalName) THEN
		perspectiveName := UPPER(perspectiveName);	
	END IF;
 	NamespaceDebug('perspective name : -> ' || perspectiveName);
	generateUniqueName(oppName, perspectiveName, n_PhysicalName, checkRepos, singleUserLock,db_platform_name);

END generatePhysicalName;

-- generate opp Logical name
PROCEDURE generateLogicalName(
		oppName OUT VARCHAR2, checkRepos IN NUMBER, singleUserLock IN NUMBER, db_platform_name in varchar2)
	IS
BEGIN
	generateUniqueName(oppName, reservingName, n_LogicalName, checkRepos, singleUserLock, db_platform_name);
END generateLogicalName;

-- generate opp name
PROCEDURE generateOppName(
		oppName OUT VARCHAR2, checkRepos IN NUMBER, singleUserLock IN NUMBER, db_platform_name in varchar2)
	IS
BEGIN
	NamespaceDebug(' generating opp name reserving mode : '|| to_char(reservingMode));
	IF(reservingMode = n_PhysicalName) THEN

generateLogicalName(oppName, checkRepos, singleUserLock, db_platform_name);
	ELSE
		IF (reservingMode = n_LogicalName) THEN
			generatePhysicalName(oppName, checkRepos, singleUserLock, db_platform_name);
		END IF;
	END IF;
END generateOppName;

--if it's pkged proc, need to first delete from
  --reservation store for the element on the right mode and isoid

--may 1st,08: this is not only check on  "commited" objects, but also include the uncommitted object from this session, so we do not use PRAGMA AUTONOMOUS_TRANSACTION; 
FUNCTION commitedIsSameObject( theName IN VARCHAR2, whatmode IN NUMBER,singleUserLock IN NUMBER)
	RETURN BOOLEAN IS

v_ErrorText Owb_Error_Log_Table.errText%TYPE;
	v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
	tempHolder VARCHAR2(4000) ;
	elemId Reservation_Store.elementId%TYPE;

---PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
 IF (singleUserLock = n_SingleUserLock) THEN RETURN FALSE;
 ELSE
  BEGIN
	SELECT elementId INTO elemId from  CMPALLFOLDERFCONAMES where
	parentID = reservingParentID and  domain = reservingDomain and
	name =theName and
	isPhys =whatmode and languageISOID = reservingISOID;
	
	if(elemId = reservingElemId) then
		RETURN TRUE;
	else
		RETURN FALSE;

end if;

EXCEPTION

WHEN OTHERS THEN
			v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);

RETURN FALSE;
  END;
 END IF;
END commitedIsSameObject;
 
procedure check_db_platform_exist(db_platform_name varchar2) as

begin

--return;
	   NamespaceRules.check_db_platform_exist(db_platform_name);
exception 
	when others then 
	raise;

end check_db_platform_exist;

procedure    doReservation(outcome out varchar2, 
			    reservingParentID Reservation_Store.parentID%TYPE,
                            reservingElemId Reservation_Store.parentID%TYPE, 
                            reservingDomain Reservation_Store.domain%TYPE, 
                            reservingName Reservation_Store.reservedName%TYPE, 
                            reservingMode Reservation_Store.isPhys%TYPE, 
                            reservingSessionID Reservation_Store.sessID%TYPE, 			
                            reservingInstanceID in number, 
                            n_SessionSerial number, 
                            v_SessionLogonTime Reservation_Store.sessLogonTime%TYPE, 
                            the_osUser Reservation_Store.osUser%TYPE,
                             reservingISOID Reservation_Store.languageISOID%TYPE) as

v_whoReservedIt Reservation_Store.osUser%TYPE;
v_whichSessionReserved Reservation_Store.sessID%TYPE;
v_whichInstanceReserved Reservation_Store.instanceID%TYPE;

PRAGMA AUTONOMOUS_TRANSACTION; --needed 
begin

INSERT INTO Reservation_Store (
		parentID,elementId, domain, reservedName,
		 isPhys, sessID, instanceID, sessSerial,
		sessLogonTime, osUser,languageISOID)
		VALUES(reservingParentID, reservingElemId,reservingDomain, reservingName,
			reservingMode, reservingSessionID, reservingInstanceID,n_SessionSerial,
	               v_SessionLogonTime, the_osUser,reservingISOID);

outcome:='OWBNOOP';

commit; -- reservation needs immediately take effect
EXCEPTION
	-- some session wins race and does insert before
	-- current session , then oracle wont let PK to occur in
	-- Reservation Store
	WHEN DUP_VAL_ON_INDEX THEN
		SELECT osUser, sessID ,instanceID INTO v_whoReservedIt, v_whichSessionReserved,v_whichInstanceReserved
		   FROM Reservation_Store
		   WHERE parentID = reservingParentID
			AND domain = reservingDomain
			AND reservedName = reservingName
			AND isPhys=reservingMode
			AND languageISOID = reservingISOID;

IF(v_whoReservedIt = the_osUser) THEN
			IF(v_whichSessionReserved = reservingSessionID and v_whichInstanceReserved = reservingInstanceID) THEN
			        outcome := 'NAME_ALREADY_RESERVED_BY_YOU';	
			ELSE
				 outcome := 'NAME_RESERVED_BY_OTHER_SESSION' ||
					to_char(v_whichSessionReserved);
			END IF;
		ELSE
			outcome := 'PEER_RESERVATION' || v_whoReservedIt;
	        END IF;
		ROLLBACK;
		return;
		
 	WHEN OTHERS THEN
            rollback; --do nothing, some error happened
             raise; --let caller procedure handle it..

end doReservation;

--reserve a name
/*
the steps are: 
1. first check if there is anything in reserveation_store, if yes and it is reaserved by other session return things like: RESERVED_BY_PEER_SESSION ; but if exist in reservation_store but reserved bu THIS very session, need to continue with step2 ( check if this name already in globalCache: CMPALLFOLDERFCONAMES in case this session has already used it for some object and can not used it again)
2.if not in resevation_store or if in-reservation-store-by-this-session, check teh global cache CMPALLFOLDERFCONAMES this name exists ? if yes : need to error out by sending info: NAME_ALREADY_COMMITED(note this info is historical , and it does not only mean name is committed, it also include uncommited data by this session) 
3. if name not in teh global cache CMPALLFOLDERFCONAMES from step2, then we can use this name ,but need to do reservation now! Plus generate it's opposite name and reserve it too.

*/
PROCEDURE reserveName(outcome out VARCHAR2,
		      pID IN Reservation_Store.parentID%TYPE,
		      elemId IN Reservation_Store.parentID%TYPE,
		      classification IN Reservation_Store.domain%TYPE,
		      desiredName IN Reservation_Store.reservedName%TYPE,
		      isPhys IN Reservation_Store.isPhys%TYPE,
 		      isProp IN NUMBER ,
		      osUser IN Reservation_Store.osUser%TYPE,
		      sessID IN Reservation_Store.sessID%TYPE,
	 	      instanceID IN NUMBER,
		      isReposCheckRequired IN NUMBER,
		      upperCaseGenedPhyName IN NUMBER,
		      bulkMode IN NUMBER,
		      singleUserLock IN NUMBER,
		      ISOID IN Reservation_Store.languageISOID%TYPE, 
		      db_platform_name in varchar2

)  IS
n_SingleUserRule NUMBER;
n_SessionSerial NUMBER;
v_SessionLogonTime Reservation_Store.sessLogonTime%TYPE;
v_whoReservedIt Reservation_Store.osUser%TYPE;
v_whichSessionReserved Reservation_Store.sessID%TYPE;
v_whichInstanceReserved Reservation_Store.instanceID%TYPE;
v_OppositeName VARCHAR2(4000);
v_OppositeMode Reservation_Store.isPhys%TYPE;
v_ErrorText Owb_Error_Log_Table.errText%TYPE;
v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
v_ErrorTime Owb_Error_Log_Table.WhenHappend%TYPE;

objectTouched NUMBER:=0;

commitedSig VARCHAR2(200);

--PRAGMA AUTONOMOUS_TRANSACTION; -- take out, since this procedure contains too much info some may need to see the parent's txn info

BEGIN

--first check if db_platform_name is correct argument

check_db_platform_exist(db_platform_name);
	-- reserving a name is following control flow depending upon on
	-- input params
    	-- first and foremost assumption is client already has solved
    	-- concurrency and locks using OWB lock framworke to not let
    	-- two clients running same piece at same time , As this
    	-- section need to semaphored,
	
        -- this cleans all sessid's which are reassigned and sessID
	-- which no longer exist in oracle instance
	-- NamespaceDebugSetOn;
	outcome := 'OWBNOOP';
	IF (bulkMode = n_BulkMode) THEN null;
	ELSE
	  cleanSIDs;
	END IF;
	IF (classification = v_Constraint) THEN
	  n_SingleUserRule := n_SingleUserLockIgnore;
	ELSE
	  n_SingleUserRule := singleUserLock;
	END IF;

reservingUK := pID || '.' || classification ||
			'.' || desiredname || '.' || isPhys || '.' ||ISOID;
	reservingParentID := pID;
	reservingDomain := classification;
	reservingName := desiredName;
	reservingMode := isPhys;
	reservingSessionID := sessID;
	reservingInstanceID :=instanceID; --fix bug 5060857
	reservingUser := osUser;
	reservingISOID :=ISOID;	
	reservingElemId := elemId;
	upperCasePhyName := upperCaseGenedPhyName;
	IF (n_SingleUserRule = n_SingleUserLock) THEN null;
	ELSE
        SELECT serial#, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss')
		INTO n_SessionSerial, v_SessionLogonTime
		FROM gv$session
		WHERE sid = sessID and 
		      inst_id = instanceID ;
	reservingSessionSerial := n_SessionSerial;
	reservingSessionTime := v_SessionLogonTime;
	END IF;

BEGIN

-- doing full scan as our unique tupple <parent,IDDomain,Name,Mode,languageISOID> is more than
		-- 2k , As logical name is 2000 chars, so with DB of 2k block size
		-- we cant declare it as unique contraint, so doing full table scan
		-- else we could simplu INSERT , we could INSERT , okay go ahead to use this name
		-- else it already exist.
		IF(doesReservationExist(v_whoReservedIt, reservingName, reservingMode, v_whichSessionReserved,v_whichInstanceReserved, n_SingleUserRule)) THEN
			-- already reserved

IF(v_whoReservedIt = osUser) THEN
				IF(v_whichSessionReserved = sessID and v_whichInstanceReserved = instanceID) THEN	
					  outcome := 'NAME_ALREADY_RESERVED_BY_YOU'; -- need to continue to check if in global cache next since you may have already used this name for some object already and can not use it again in your session

ELSE
				  outcome := 'NAME_RESERVED_BY_OTHER_SESSION' ||
						to_char(v_whichSessionReserved);
                                  return;
				END IF;
			ELSE
			    outcome := 'PEER_RESERVATION' || v_whoReservedIt;
                            return;
			END IF;
              END IF;

-- control is here means, name is not reserved or is reserved by yourself , need to check against global namespace cache to see if it is usable
	                IF (isReposCheckRequired = n_RepositoryCheckRequired) THEN
				
				IF(isAlreadyInGlobalCache(reservingName, reservingMode, n_SingleUserRule)) THEN
					IF(reservingDomain='CMPFunction') THEN
						--the commited one just the same one
						IF(commitedIsSameObject(reservingName, reservingMode,n_SingleUserRule)) THEN
						   outcome :='OWB_NOCHANGE'; 							
						   return;	
						END IF;	
					END IF;

outcome := 'NAME_ALREADY_COMMITTED' ; -- this actually means: Name already used (include commited info and nont-commited info from this session)
					--ROLLBACK;
					return;
	         		END IF;
			END IF;	
			IF (n_SingleUserRule = n_SingleUserLock) THEN null;
			ELSE

-- make reservation
			 -- insert into reservation store

--sochen: if it's pkged fn, need to deletethe old rsv for the fn
			 --sochen: fix 2997061:should NOT delete the
			  --old reservation, other wise it willhang(n1[committed]->n2->n3->n2)
			 --when ask isCommitted() on unique constraint ck
			/* April 25,08: no need to do this...
			 IF(reservingDomain = 'CMPFunction')  THEN
				select count(*) into objectTouched
				from  reservation_store
				WHERE
					reservation_store.elementid = reservingElemId AND
					reservation_store.languageISOID	= reservingISOID AND
					reservation_store.isPhys = reservingMode;
					
				--sochen:whenever rename and oldname(commited),
				--need to reserve it to preventhanging when ask isCommitted due to delete from cmpallfolder does not reflect on this autonaumas transaction 	
				IF(objectTouched = 0) THEN
						BEGIN	
						select name into commitedSig from  cmpallfolderfconames
  						where cmpallfolderfconames.elementid= reservingElemId
						and cmpallfolderfconames.LANGUAGEISOID= reservingISOID
						and cmpallfolderfconames.isphys = reservingMode;	
	
						INSERT INTO Reservation_Store(
						parentID,elementId, domain, reservedName,
						isPhys, sessID, instanceID,
						sessSerial,sessLogonTime, osUser,
						languageISOID)
						VALUES(reservingParentID, reservingElemId,reservingDomain,
		         			 commitedSig,
					         reservingMode, reservingSessionID, reservingInstanceID,n_SessionSerial,
					         v_SessionLogonTime, osUser,reservingISOID);

EXCEPTION
							WHEN NO_DATA_FOUND THEN
							null;

END;

END IF;

END IF;
			*/
         
 			IF(outcome = 'NAME_ALREADY_RESERVED_BY_YOU') THEN outcome:='OWBNOOP'; -- make it seems reservation ok  
                        ELSE
		         doReservation(outcome, reservingParentID, reservingElemId, reservingDomain, reservingName, reservingMode, reservingSessionID, 			
                                       reservingInstanceID, n_SessionSerial, v_SessionLogonTime, osUser,reservingISOID);

END IF;

if(outcome != 'OWBNOOP') then 
                           return ; -- when do reservation get an error on unique key competence
                         end if;

END IF;

IF(isProp = n_PropagationRequired) THEN
		      
				generateOppName(v_OppositeName, n_RepositoryCheckRequired, n_SingleUserRule, db_platform_name);

IF(reservingMode = n_PhysicalName) THEN
					v_OppositeMode := n_LogicalName;
				ELSE
					v_OppositeMode := n_PhysicalName;
				END IF;

NamespaceDebug('Opp Name : '
					|| v_OppositeName || ' Opp Mode : ' || v_OppositeMode);
				IF (n_SingleUserRule = n_SingleUserLock) THEN outcome := v_OppositeName;
				ELSE

doReservation(outcome, reservingParentID, reservingElemId, reservingDomain, v_OppositeName, v_OppositeMode, reservingSessionID, 			
                        	         reservingInstanceID, n_SessionSerial, v_SessionLogonTime, osUser,reservingISOID);
				
				    --fix bug 	7428602 :here we could get name_already_reserved_by_you since we can re-use this name if it is not in global cache this has checked from generateOppName procedure already
				   		      
                 		     IF(outcome = 'NAME_ALREADY_RESERVED_BY_YOU') THEN outcome:='OWBNOOP'; -- make it seems reservation ok  
				     END IF;
	
        	                     if(outcome != 'OWBNOOP') then 
                	                return ; -- when do reservation get an error on unique key competence
                        	     end if;
                                     outcome:=v_OppositeName;
				END IF;
			ELSE
			    NamespaceDebug('isProp' || to_char(isProp) || ' CONS : ' || n_PropagationRequired)	;
			END IF;
		 --END IF; 
	EXCEPTION
				-- handle it any exception
		WHEN OTHERS THEN
			v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);
			logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while reserving for : ' || reservingUK );
			--ROLLBACK;
			outcome := 'OWB_NS_FATAL_ERROR_1:' || v_ErrorCode||v_ErrorText;
			return;
			-- fatal and can never happen As for reserving current session
			-- gv$session should always return valid values.	
	END;
	--COMMIT;
	
EXCEPTION
	-- handle it any exception
	WHEN NO_DATA_FOUND THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, ' ||
			'when reservation system attending request for ' || reservingUK ||
			' IN-OP -> was in qerying info for requesting reserving session : ' || to_char(reservingSessionID) );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_2:' || v_ErrorCode||v_ErrorText;
	WHEN OTHERS THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, while initing reservation system : ' || reservingUK );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_3:' || v_ErrorCode||v_ErrorText;
	-- fatal and can never happen As for reserving current session
	-- gv$session should always return valid values.	
	
END reserveName;

PROCEDURE generateAndReserveUniqueName(outcome out VARCHAR2,
		      pID IN Reservation_Store.parentID%TYPE,
		      classification IN Reservation_Store.domain%TYPE,
		      desiredName IN Reservation_Store.reservedName%TYPE,
		      isPhys IN Reservation_Store.isPhys%TYPE,
		      osUser IN Reservation_Store.osUser%TYPE,
		      sessID IN Reservation_Store.sessID%TYPE,
		      instanceID IN number,
		      isReposCheckRequired IN NUMBER,
		      bulkMode IN NUMBER,
		      singleUserLock IN NUMBER, 
		      db_platform_name in varchar2

)  IS
n_SingleUserRule NUMBER;
n_SessionSerial NUMBER;
v_SessionLogonTime Reservation_Store.sessLogonTime%TYPE;
v_whoReservedIt Reservation_Store.osUser%TYPE;
v_whichSessionReserved Reservation_Store.sessID%TYPE;
v_whichInstanceReserved number;
v_OppositeName VARCHAR2(4000);
v_OppositeMode Reservation_Store.isPhys%TYPE;
v_ErrorText Owb_Error_Log_Table.errText%TYPE;
v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
v_ErrorTime Owb_Error_Log_Table.WhenHappend%TYPE;

---PRAGMA AUTONOMOUS_TRANSACTION; remove because this proc covers too much stuff some may need to see the parnet txn's uncommitted stuff

BEGIN

--first check if db_platform_name is correct argument 
	check_db_platform_exist(db_platform_name);

-- this cleans all sessid's which are reassigned and sessID
	-- which no longer exist in oracle instance
	-- NamespaceDebugSetOn;
	outcome := desiredName;
	IF (bulkMode = n_BulkMode) THEN null;
	ELSE
	  cleanSIDs;
	END IF;
	IF (classification = v_Constraint) THEN
	  n_SingleUserRule := n_SingleUserLockIgnore;
	ELSE
	  n_SingleUserRule := singleUserLock;
	END IF;

reservingUK := pID || '.' || classification ||
			'.' || desiredname || '.' || isPhys;
	reservingParentID := pID;
	reservingDomain := classification;
	reservingName := desiredName;
	reservingMode := isPhys;
	reservingSessionID := sessID;
	reservingInstanceID :=instanceID;
	reservingUser := osUser;
        SELECT serial#, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss')
		INTO n_SessionSerial, v_SessionLogonTime
		FROM gv$session
		WHERE sid = sessID and inst_id = instanceID;
	reservingSessionSerial := n_SessionSerial;
	reservingSessionTime := v_SessionLogonTime;
	IF(doesReservationExist(v_whoReservedIt, reservingName, reservingMode, v_whichSessionReserved, v_whichInstanceReserved,n_SingleUserRule)) THEN
		-- already reserved
		IF(v_whoReservedIt = osUser ) THEN
			IF(v_whichSessionReserved = sessID and v_whichInstanceReserved = instanceID) THEN
				outcome := 'NAME_ALREADY_RESERVED_BY_YOU';	
				--ROLLBACK;
				RETURN;
			END IF;
		END IF;
	END IF;

BEGIN
		NamespaceDebug('before generattion');
	
		IF (reservingMode = n_PhysicalName) THEN
			reservingMode := n_LogicalName;
		ELSE
			reservingMode := n_PhysicalName;
		END IF;
		generateOppName(v_OppositeName, isReposCheckRequired, n_SingleUserRule, db_platform_name);

IF(reservingMode = n_PhysicalName) THEN
			v_OppositeMode := n_LogicalName;
		ELSE
			v_OppositeMode := n_PhysicalName;
		END IF;

NamespaceDebug('Opp Name : '
					|| v_OppositeName || ' Opp Mode : ' || v_OppositeMode);

doReservation(outcome, reservingParentID, -1,reservingDomain , v_OppositeName,v_OppositeMode,reservingSessionID,reservingInstanceID,n_SessionSerial, v_SessionLogonTime, osUser, 'en_US'  );

/*		
		BEGIN
			--insert name in reservation store
			INSERT INTO Reservation_Store (
					parentID, domain, reservedName,
					isPhys, sessID, instanceID,sessSerial,
					sessLogonTime, osUser)
			VALUES(reservingParentID, reservingDomain, v_OppositeName,
					v_OppositeMode, reservingSessionID, reservingInstanceID,n_SessionSerial,
					v_SessionLogonTime, osUser);
			outcome := v_OppositeName;
		EXCEPTION
			WHEN DUP_VAL_ON_INDEX THEN
			generateAndReserveUniqueName(outcome, pID, classification, desiredName, isPhys, osUser,sessID,instanceID, isReposCheckRequired, bulkMode, n_SingleUserRule, db_platform_name);
			--ROLLBACK; -- As this is propation and primary name already exist in DB
			RETURN;
		END;
             */

if(outcome = 'OWBNOOP') then 
                    outcome := v_OppositeName;
                end if;

EXCEPTION
				-- handle it any exception
		WHEN OTHERS THEN
			v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);
			logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while reserving for : ' || reservingUK );
			--ROLLBACK;
			outcome := 'OWB_NS_FATAL_ERROR_4:' || v_ErrorCode||v_ErrorText;
			return;
			-- fatal and can never happen As for reserving current session
			-- v$session should always return valid values.	
	END;
	--COMMIT;
	
EXCEPTION
	-- handle it any exception
	WHEN NO_DATA_FOUND THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, ' ||
			'when reservation system attending request for ' || reservingUK ||
			' IN-OP -> was in qerying info for requesting reserving session : ' || to_char(reservingSessionID) );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_5:' || v_ErrorCode||v_ErrorText;
	WHEN OTHERS THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, while initing reservation system : ' || reservingUK );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_6:' || v_ErrorCode||v_ErrorText;
	-- fatal and can never happen As for reserving current session
	-- gv$session should always return valid values.	
	
END generateAndReserveUniqueName;

-- TODO(old)
PROCEDURE generateNewUniqueName(outcome out VARCHAR2,
		      pID IN Reservation_Store.parentID%TYPE,
		      classification IN Reservation_Store.domain%TYPE,
		      desiredName IN Reservation_Store.reservedName%TYPE,
		      isPhys IN Reservation_Store.isPhys%TYPE,
		      osUser IN Reservation_Store.osUser%TYPE,
		      sessID IN Reservation_Store.sessID%TYPE,
		      instanceID in number,
		      isReposCheckRequired IN NUMBER,
		      bulkMode IN NUMBER,
		      singleUserLock IN NUMBER, 
		      db_platform_name in varchar2

)  IS
n_SingleUserRule NUMBER;
n_SessionSerial NUMBER;
v_SessionLogonTime Reservation_Store.sessLogonTime%TYPE;
v_whoReservedIt Reservation_Store.osUser%TYPE;
v_whichSessionReserved Reservation_Store.sessID%TYPE;
v_whichInstanceReserved number;
v_OppositeName VARCHAR2(4000);
v_OppositeMode Reservation_Store.isPhys%TYPE;
v_ErrorText Owb_Error_Log_Table.errText%TYPE;
v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
v_ErrorTime Owb_Error_Log_Table.WhenHappend%TYPE;
--PRAGMA AUTONOMOUS_TRANSACTION; --needed

BEGIN

--first check if db_platform_name is correct argument 
	check_db_platform_exist(db_platform_name);

BEGIN
		NamespaceDebug('before generattion');
		IF (reservingMode = n_PhysicalName) THEN
			reservingMode := n_LogicalName;
		ELSE
			reservingMode := n_PhysicalName;
		END IF;

generateOppName(v_OppositeName, isReposCheckRequired, n_SingleUserRule,db_platform_name);
	
         	IF(reservingMode = n_PhysicalName) THEN
			v_OppositeMode := n_LogicalName;
		ELSE
			v_OppositeMode := n_PhysicalName;
		END IF;
		--NamespaceDebug('Opp Name : '
			--		|| v_OppositeName || ' Opp Mode : ' || v_OppositeMode);
	
		
			 doReservation(outcome, reservingParentID, -1, reservingDomain, v_OppositeName, v_OppositeMode, reservingSessionID, 			
                                       reservingInstanceID, n_SessionSerial, v_SessionLogonTime, osUser,'en_US');

/*
		BEGIN
			--insert name in reservation store
			INSERT INTO Reservation_Store (
					parentID, domain, reservedName,
					isPhys, sessID, instanceID,sessSerial,
					sessLogonTime, osUser)
			VALUES(reservingParentID, reservingDomain, v_OppositeName,
					v_OppositeMode, reservingSessionID, reservingInstanceID,n_SessionSerial,
					v_SessionLogonTime, osUser, 'en_US');
			outcome := v_OppositeName;
		EXCEPTION
			WHEN DUP_VAL_ON_INDEX THEN
			generateNewUniqueName(outcome, pID, classification, desiredName, isPhys, osUser,sessID, instanceID,isReposCheckRequired, bulkMode, n_SingleUserRule,db_platform_name);
			--ROLLBACK; -- As this is propation and primary name already exist in DB
			RETURN;
		END;
		*/

if(outcome = 'OWBNOOP') then 
                    outcome := v_OppositeName;
                end if;

EXCEPTION
				-- handle it any exception
		WHEN OTHERS THEN
			v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);
			logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while reserving for : ' || reservingUK );
			--ROLLBACK;
			outcome := 'OWB_NS_FATAL_ERROR_7:' || v_ErrorCode||v_ErrorText;
			return;
			-- fatal and can never happen As for reserving current session
			-- gv$session should always return valid values.	
	END;
	--COMMIT;
	
EXCEPTION
	-- handle it any exception
	WHEN NO_DATA_FOUND THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, ' ||
			'when reservation system attending request for ' || reservingUK ||
			' IN-OP -> was in qerying info for requesting reserving session : ' || to_char(reservingSessionID) );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_8:' || v_ErrorCode||v_ErrorText;
	WHEN OTHERS THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, while initing reservation system : ' || reservingUK );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_9:'|| v_ErrorCode||v_ErrorText;
	-- fatal and can never happen As for reserving current session
	-- gv$session should always return valid values.	
	
END generateNewUniqueName;

--TODO(new one)
PROCEDURE generateNewUniqueName(outcome out VARCHAR2,
		      pID IN Reservation_Store.parentID%TYPE,
		      classification IN Reservation_Store.domain%TYPE,
		      desiredName IN Reservation_Store.reservedName%TYPE,
		      isPhys IN Reservation_Store.isPhys%TYPE,
		      osUser IN Reservation_Store.osUser%TYPE,
		      sessID IN Reservation_Store.sessID%TYPE,
                      instanceID in number,
		      isReposCheckRequired IN NUMBER,
	              ISOID IN Reservation_Store.languageISOID%TYPE, 
		      db_platform_name in varchar2

)  IS
n_SessionSerial NUMBER;
v_SessionLogonTime Reservation_Store.sessLogonTime%TYPE;
v_whoReservedIt Reservation_Store.osUser%TYPE;
v_whichSessionReserved Reservation_Store.sessID%TYPE;
v_OppositeName VARCHAR2(4000);
v_OppositeMode Reservation_Store.isPhys%TYPE;
v_ErrorText Owb_Error_Log_Table.errText%TYPE;
v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
v_ErrorTime Owb_Error_Log_Table.WhenHappend%TYPE;
---PRAGMA AUTONOMOUS_TRANSACTION; --needed

BEGIN

--first check if db_platform_name is correct argument 
	check_db_platform_exist(db_platform_name);

-- this cleans all sessid's which are reassigned and sessID
	-- which no longer exist in oracle instance
	-- NamespaceDebugSetOn;
	outcome := desiredName;
 	--cleanSIDs(sessID);
	cleanSIDs;

SELECT serial#, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss')
		INTO n_SessionSerial, v_SessionLogonTime
		FROM gv$session
		WHERE sid = sessID and inst_id = instanceID;
	reservingSessionSerial := n_SessionSerial;
	reservingSessionTime := v_SessionLogonTime;

BEGIN
		NamespaceDebug('before generattion');
		IF (reservingMode = n_PhysicalName) THEN
			reservingMode := n_LogicalName;
		ELSE
			reservingMode := n_PhysicalName;
		END IF;

generateOppName(v_OppositeName, isReposCheckRequired,n_SingleUserLockIgnore, db_platform_name);
		IF(reservingMode = n_PhysicalName) THEN
			v_OppositeMode := n_LogicalName;
		ELSE
			v_OppositeMode := n_PhysicalName;
		END IF;
		NamespaceDebug('Opp Name : '
					|| v_OppositeName || ' Opp Mode : ' || v_OppositeMode);

doReservation(outcome, reservingParentID, -1, reservingDomain, v_OppositeName, v_OppositeMode, reservingSessionID, 			
                                       reservingInstanceID, n_SessionSerial, v_SessionLogonTime, osUser,reservingISOID);

BEGIN
			--insert name in reservation store
			INSERT INTO Reservation_Store (
					parentID, domain, reservedName,
					isPhys, sessID, instanceID,sessSerial,
					sessLogonTime, osUser,languageISOID)
			VALUES(reservingParentID, reservingDomain, v_OppositeName,
					v_OppositeMode, reservingSessionID, reservingInstanceID,n_SessionSerial,
					v_SessionLogonTime, osUser,reservingISOID);
			outcome := v_OppositeName;
		EXCEPTION
			WHEN DUP_VAL_ON_INDEX THEN
			generateNewUniqueName(outcome, pID,classification, desiredName, isPhys, osUser,sessID, instanceID,
isReposCheckRequired,ISOID   ,db_platform_name  ); --why need to call it again ???
			--ROLLBACK; -- As this is propation and primary name already exist in DB
			RETURN;
		END;
              */

--fix bug 7428602 :here we could get name_already_reserved_by_you since we can re-use this name if it is not in global cache this has checked from generateOppName procedure already
				   		      
                IF(outcome = 'NAME_ALREADY_RESERVED_BY_YOU') THEN outcome:='OWBNOOP'; -- make it seems reservation ok  
                END IF;
 
                if(outcome = 'OWBNOOP') then 
                    outcome := v_OppositeName;
                end if;
	EXCEPTION
				-- handle it any exception
		WHEN OTHERS THEN
			v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);
			logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while reserving for : ' || reservingUK );
			--ROLLBACK;
			outcome := 'OWB_NS_FATAL_ERROR_10:' || v_ErrorCode||v_ErrorText;
			return;
			-- fatal and can never happen As for reserving current session
			-- v$session should always return valid values.	
	END;
	--COMMIT;
	
EXCEPTION
	-- handle it any exception
	WHEN NO_DATA_FOUND THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, ' ||
			'when reservation system attending request for ' || reservingUK ||
			' IN-OP -> was in qerying info for requesting reserving session : ' || to_char(reservingSessionID) );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_11:' || v_ErrorCode||v_ErrorText;
	WHEN OTHERS THEN
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
			'Oracle Error occured, while initing reservation system : ' || reservingUK );
		--ROLLBACK;
		outcome := 'OWB_NS_FATAL_ERROR_12:' || v_ErrorCode||v_ErrorText;
	-- fatal and can never happen As for reserving current session
	-- gv$session should always return valid values.	
	
END generateNewUniqueName;

--suppose we only reserve name for base lang's at this time...

PROCEDURE insertNameForDeletion(tempParentID IN Reservation_Store.parentID%TYPE,
                     tempDomainID IN Reservation_Store.domain%TYPE,
                     name IN Reservation_Store.reservedName%TYPE,
                     isPhysical IN Reservation_Store.isPhys%TYPE,
                     sessionID IN Reservation_Store.sessID%TYPE,
                     instID in number,
                     serialNum IN Reservation_Store.sessSerial%TYPE,
                     logonTime IN Reservation_Store.sessLogonTime%TYPE,
                     userInfo IN Reservation_Store.osUser%TYPE,
		     ISOID    IN CMPSupportedLanguage_v.ISOID%TYPE
		)
is
PRAGMA AUTONOMOUS_TRANSACTION;

--  this PROCEDURE is only used for reserve for deletion.
begin

insert into Reservation_Store(parentID, domain, reservedName, isPhys,
                sessID, instanceID, sessSerial, sessLogonTime, osUser,languageISOID)
    values(tempParentID, tempDomainID, name, isPhysical, sessionID, instID,
           serialNum, logonTime, userInfo,ISOID);
    commit;
exception
    when others then
     -- do nothing because it's for deletion
        null;
end insertNameForDeletion;

function getInstalledModule(id in CMPElement_v.elementID%TYPE)
return CMPElement_v.elementID%TYPE
is
    tempElemID CMPElement_v.elementID%TYPE := null;
    moduleID CMPElement_v.elementID%TYPE := null;
    tcursor TTYPE;
begin
    open tcursor for
        select elementid from FirstClassObject_v start with elementid = id
        connect by prior owningFolder = elementid;
    loop
        begin
            fetch tcursor into tempElemID;
            exit when tcursor%NOTFOUND;
            begin
                select elementid into moduleID from CMPInstalledModule_v
                where elementid = tempElemID;
            exception
                when others then
                    null;
            end;
            if moduleID is not null then
                exit;
            end if;
        exception
            when others then
                null;
        end;
    end loop;
    close tcursor;
    return moduleID;
end getInstalledModule;

function getWorkspaceNSParentID return number
is
ownerid number;
begin 
	select elementid into ownerid from cmpwbuser_v where IsWorkspaceOwner = '1';

return ownerid;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
	return null;
	WHEN OTHERS THEN
	return null;
end;

--sochen: Oct 15,03: if owningFolder is null, should return 0
--since now not only project but also other standalone fco such as
--user role etc need this method to support.
function getParentID(id in Reservation_Store.parentID%TYPE)
return Reservation_Store.parentID%TYPE
is
    parentID Reservation_Store.parentID%TYPE := null;
    className CMPElement_v.className%TYPE := null;
    isFCO BOOLEAN := FALSE;
    isSCO BOOLEAN := FALSE;
    owningFolder FirstClassObject_v.owningFolder%TYPE := null;
    firstClassObject SecondClassObject_v.firstClassObject%TYPE := null;
    logicalObject CMPPhysicalObject_v.logicalObject%TYPE := null;
    tempElemID CMPElement_v.elementID%TYPE := null;
begin

select className into className from CMPElement_v where elementID = id;
    if className is null then
        -- should not happen. each element should have a classname.
        return null;
    end if;

begin
        select owningFolder into owningFolder from FirstClassObject_v
        where elementid = id;
        isFCO := TRUE;
    exception
        when NO_DATA_FOUND then
            isFCO := FALSE;
        when others then
          -- should not happen
            isFCO := FALSE;
    end;

-- now proj has parent, which is workspace, treat it as normal FCO	
  -- if(className = 'CMPWBProject') then
	--isFCO :=TRUE;
	--owningFolder :=0;			
  -- end if;
   if classname = 'CMPWorkspace' then

return getWorkspaceNSParentID(); 
   end if;
    begin
        select firstClassObject into firstClassObject from SecondClassObject_v
        where elementid = id;
        isSCO := TRUE;
    exception
        when NO_DATA_FOUND then
            isSCO := FALSE;
        when others then
            -- should not happen
            isSCO := FALSE;
    end;

IF isFCO then

if(owningFolder is null) then
              owningFolder :=0;
         end if;   	
         return owningFolder;
     END IF;

--following should be isSCO, we should only interested in
    --constraints,other SCO, we do not care about their nsP since we do not
    --reserve them

if (className = 'CMPForeignKey' or
	className = 'CMPUniqueKey' or
	className = 'CMPWBCheckConstraint'
	) then

tempElemID := getInstalledModule(firstClassObject);
        return tempElemID;

end if;

--just in case
    if isSCO then
        return firstClassObject;
    else
        return null;
    end if;

exception
    --  invalid element id, error in any of the above queries
    when others then
        return null;
end getParentID;

/*
PROCEDURE reserveTranslationsFor(id IN reservation_Store.parentID%TYPE,
                         osUser IN Reservation_Store.osUser%TYPE,
                         sessID IN Reservation_Store.sessID%TYPE,
                         instID in number,
                         serialNum IN number,
                         logonTime IN Reservation_Store.sessLogonTime%TYPE)
is
		
    tempElemID CMPElement_v.elementID%TYPE;
    tempParentID Reservation_Store.parentID%TYPE;
    className CMPELement_v.className%TYPE;
    nsClassName CMPELement_v.className%TYPE;

tempDomain Reservation_Store.domain%TYPE;
    physicalName CMPElement_v.name%TYPE;
    logicalName CMPElement_v.logicalName%TYPE;
    ISOID CMPSupportedLanguage_v.ISOID%TYPE;
    tcursor TTYPE;

PRAGMA AUTONOMOUS_TRANSACTION;
begin
    open tcursor for
       select CMPTranslation_v.elementid, CMPTranslation_v.className,CMPElement_v.classname,
              CMPTranslation_v.name, CMPTranslation_v.logicalName,CMPTranslation_v.ISOID
        from CMPTranslation_v, CMPSupportedLanguage_v,CMPElement_v
        where MLSTranslatable = id
        and CMPTranslation_v.ISOID = cmpsupportedlanguage_v.ISOID and
             CMPElement_v.elementid = MLSTranslatable;    	
    loop
        begin
            fetch tcursor into tempELemID, className,nsClassname, physicalName, logicalName,ISOID;
	    exit when tcursor%NOTFOUND;

tempParentID := getParentID(id); --aggregateP's id
            if tempParentID is not null then
                tempDomain := getClassification(nsClassName);--use aggP's classname

insertNameForDeletion(tempParentID, tempDomain, logicalName, 2,
                                  sessID, instID,serialNum, logonTime, osUser,ISOID);
            end if;
        exception
            when others then
                null;        	
        end;
    end loop;
    close tcursor;
    commit;	
exception
    when others then
        rollback;

end reserveTranslationsFor;
*/

-- change: also reserve translations for the id
PROCEDURE reserveAllSCOsForDeletion(id IN FirstClassObject_v.elementID%TYPE,
                         osUser IN Reservation_Store.osUser%TYPE,
                         sessID IN Reservation_Store.sessID%TYPE,
                         instID in number,
                         serialNum IN number,
                         logonTime IN
			Reservation_Store.sessLogonTime%TYPE,
			baseISOID Reservation_Store.languageISOID%TYPE
	)
is
    tempElemID CMPElement_v.elementID%TYPE;
    tempParentID Reservation_Store.parentID%TYPE;
    className CMPELement_v.className%TYPE;
    tempDomain Reservation_Store.domain%TYPE;
    physicalName CMPElement_v.name%TYPE;
    logicalName CMPElement_v.logicalName%TYPE;

tcursor TTYPE;

--PRAGMA AUTONOMOUS_TRANSACTION; --needed..
begin

open tcursor for
        select elementid, className, name, logicalName
        from SecondClassObject_v where firstClassObject = id
        and className in ('CMPUniqueKey', 'CMPForeignKey',
        'CMPWBCheckConstraint');

loop
        begin
            fetch tcursor into tempELemID, className, physicalName, logicalName;
            exit when tcursor%NOTFOUND;
	    --first reserve all the translations for the contraints
	    reserveTranslationsFor(tempElemId,osUser,sessID,instID, serialNum,logonTime); -- procedure to reserve the translations
            tempParentID := getParentID(tempElemID); --SCO's ID, getNSPid
            if tempParentID is not null then
                tempDomain := getClassification(className);
                insertNameForDeletion(tempParentID, tempDomain, physicalName, 1,
                                  sessID, instID,serialNum, logonTime, osUser,baseISOID);
                insertNameForDeletion(tempParentID, tempDomain, logicalName, 2,
                                  sessID, instID,serialNum, logonTime, osUser,baseISOID);
            end if;
        exception
            when others then
                null;
        end;
    end loop;
    close tcursor;
  --  commit;

exception
    when others then
      null;
    --    rollback;
end reserveAllSCOsForDeletion;
*/

/* may 1st, 08: no need to do reservation when do deletion
--change it to reserve the translation of the FCO(contraints) too...
PROCEDURE reserveAllNamesForDeletion(pID IN Reservation_Store.parentID%TYPE,
                         osUser IN Reservation_Store.osUser%TYPE,
                         sessID IN Reservation_Store.sessID%TYPE,
	                 instanceID in number,
                         bulkMode IN NUMBER,
                         singleUserLock IN NUMBER)
is
    serialNum number;
    logonTime Reservation_Store.sessLogonTime%TYPE;
    tempElemID CMPElement_v.elementID%TYPE;
    tempParentID Reservation_Store.parentID%TYPE;
    className CMPElement_v.className%TYPE;
    tempDomain Reservation_Store.domain%TYPE;
    physicalName CMPElement_v.name%TYPE;
    logicalName CMPElement_v.logicalName%TYPE;
    baseISOID Reservation_Store.languageISOID%TYPE;

tcursor TTYPE;
    v_ErrorCode number;
    v_ErrorText varchar2(500);
----PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  IF (singleUserLock = n_SingleUserLock) THEN null;
  ELSE
    IF (bulkMode = n_BulkMode) THEN null;
    ELSE
      cleanSIDs;
    END IF;
    SELECT serial#, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss')
    INTO serialNum, logonTime
    FROM gv$session
    WHERE sid = sessID and inst_id = instanceID;

--sochen_debug('1. come here, pid:'||pID);

select ISOID into baseISOID from cmpsupportedLanguage_v where IsBaseLanguage = '1';
	
	--sochen:since we assume pId is a folder, it should not be of
	--any constaints, folloiwng code query SCO_V is not used actually
    begin
          --sochen_debug('2. on constraint');
        select elementid, className, name, logicalName
        into tempElemID, className, physicalName, logicalName
        from SecondClassObject_v where elementid = pID
        and className in ('CMPUniqueKey', 'CMPForeignKey',
        'CMPWBCheckConstraint');
        tempParentID := getParentID(tempElemID);
        if tempParentID is not null then
            tempDomain := getClassification(className);
            insertNameForDeletion(tempParentID, tempDomain, physicalName, 1,
                                  sessID,instanceID, serialNum, logonTime, osUser,baseISOID);
            insertNameForDeletion(tempParentID, tempDomain, logicalName, 2,
                                  sessID, instanceID,serialNum, logonTime, osUser,baseISOID);
            --commit;
        end if;
        return; -- why return at this time ? becoz it assume that the pID is kind of contraits
    exception
        when others then
            null;
    end;

-- sochen_debug('3. on elem in fco_v, ');

-- function names are not reserved because of overloading.
    open tcursor for
        select * from (select elementid, className, name, logicalName
        from FirstClassObject_v start with elementid = pID
        connect by prior elementid = owningFolder)
        where className<>'CMPFunction';

loop
        begin
   
            -- sochen_debug('4. begin in a loop, ');

fetch tcursor into tempElemID, className, physicalName, logicalName;
            exit when tcursor%NOTFOUND;
            tempParentID := getParentID(tempElemID);--tempElemId:elementid of FCO under pID
	    begin
             --sochen_debug('5. begin in a loop, and before  reseve all sco for delete');

reserveAllSCOsForDeletion(tempElemID, osUser,
                                      sessID, instanceID,serialNum, logonTime,baseISOID);
	    exception
              when others then
                  v_ErrorCode := SQLCODE;
			v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		 	logErrorMessage(v_ErrorCode, v_ErrorText,
				'Oracle Error occured, while reserving for : '
				|| tempelemid || osuser );
            end;
		
            if tempParentID is not null then
	       --first reserve all the translations for the contraints

--sochen_debug('6. begin in a loop, and before  reseve all trans for delete');

reserveTranslationsFor(tempElemID,osUser,sessID, instanceID,serialNum,logonTime);

tempDomain := getClassification(className);

---sochen_debug('7. begin in a loop, and before  reseve eacho obj for delete:'|| physicalName);

insertNameForDeletion(tempParentID, tempDomain, physicalName, 1,
                                  sessID, instanceID, serialNum, logonTime, osUser,baseISOID);
                insertNameForDeletion(tempParentID, tempDomain, logicalName, 2,
                                  sessID, instanceID, serialNum, logonTime, osUser,baseISOID);
            end if;
        exception
            when others then
                null;
        end;
    end loop;
    close tcursor;
	       --sochen_debug('8.  out of  a loop');

-- commit;
  END IF;
exception
    when others then
     --   rollback;
        null;
END reserveAllNamesForDeletion;
*/

--sochen: this one is specific for pkged function
PROCEDURE saveNameToDBCacheForFunction(
	isoid CMPALLFOLDERFCONAMES.languageISOID%TYPE,
	parentid 	CMPALLFOLDERFCONAMES.parentID%TYPE,
	domain CMPALLFOLDERFCONAMES.domain%TYPE,
	name CMPALLFOLDERFCONAMES.name%TYPE,
	isPhysical CMPALLFOLDERFCONAMES.isPhys%TYPE,
	elemId CMPALLFOLDERFCONAMES.elementid%TYPE,
	snapshot_view NUMBER
	)		
IS

----PRAGMA AUTONOMOUS_TRANSACTION; can't be auto, since this is the running connection, and the insert does not need to be committed here....

BEGIN
  --first delete the old name with the right naming mode if exists		
	IF(snapshot_view = 0) THEN
		DELETE FROM CMPALLFOLDERFCONAMES
        	WHERE CMPALLFOLDERFCONAMES.elementid = elemId AND
		      CMPALLFOLDERFCONAMES.isPhys = isPhysical AND
		      CMPALLFOLDERFCONAMES.languageISOID  = isoid;

ELSE
		DELETE FROM CMPALLFOLDERFCONAMES_X
        	WHERE CMPALLFOLDERFCONAMES_X.elementid = elemId AND
		      CMPALLFOLDERFCONAMES_X.isPhys = isPhysical AND
		      CMPALLFOLDERFCONAMES_X.languageISOID  = isoid;

END IF; 	
 -- then insert what we want
	IF(snapshot_view = 0) THEN
		Insert into CMPALLFOLDERFCONAMES(parentId, domain, name, isPhys, elementid, aggregateParent,languageISOID)
		VALUES(parentid, domain, name,isPhysical, elemId,parentid, isoid);

ELSE
		Insert into CMPALLFOLDERFCONAMES_X(parentId, domain, name, isPhys, elementid, aggregateParent,languageISOID)
		VALUES(parentid, domain, name,isPhysical, elemId, parentid, isoid);

END IF;

EXCEPTION
	WHEN OTHERS THEN

raise_application_error(-20001, 'NamespaceServiceImpl encountered the SQL error:'||SUBSTR(SQLERRM,1,200));
		RETURN;

END saveNameToDBCacheForFunction;

--fix bug:2997061 --april 25,08: not needed anymore since we do not use insert to check if a name is commited or not...

/*
PROCEDURE reserveNameForFnBeforeDelete(
	elemId CMPALLFOLDERFCONAMES.elementid%TYPE,
	isoid CMPALLFOLDERFCONAMES.LANGUAGEISOID%TYPE,
	classification CMPALLFOLDERFCONAMES.domain%type,
	snapshot_view NUMBER,
	theSessId NUMBER,
        instID number,
	theOsUser reservation_store.osuser%type
	)
IS
	dbCacheName varchar2(30);
	fn_parentId NUMBER;
	fn_reservingName varchar2(100);
	n_SessionSerial NUMBER;
	v_SessionLogonTime VARCHAR(100);
	PRAGMA AUTONOMOUS_TRANSACTION;--  needed ...

BEGIN
	IF(snapshot_view = 0) THEN
		dbCacheName := 	'CMPALLFOLDERFCONAMES';
	ELSE
		dbCacheName := 	'CMPALLFOLDERFCONAMES_X';

END IF;

SELECT serial#, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss')
		INTO n_SessionSerial, v_SessionLogonTime
		FROM gv$session
		WHERE sid = theSessId and inst_id = instID;

--first resv physical name
	select PARENTID, name into fn_parentId, fn_reservingName
	from CMPALLFOLDERFCONAMES where languageisoid = isoid and
	     isPhys=1 and elementId = elemId;

BEGIN
	INSERT INTO Reservation_Store (
					parentID,elementId, domain, reservedName,
					isPhys, sessID, instanceID,sessSerial,
					sessLogonTime, osUser,languageISOID)
	VALUES( fn_parentId,elemId,classification,fn_reservingName,1,theSessID, instID,
		n_SessionSerial, v_SessionLogonTime,theOsUser,isoid);

EXCEPTION
		WHEN DUP_VAL_ON_INDEX THEN	
			null; --already reserved
		
	END;

--then resv logical name
	select PARENTID, name into fn_parentId, fn_reservingName
	from CMPALLFOLDERFCONAMES where languageisoid = isoid and
	     isPhys=2 and elementId = elemId;

BEGIN
	INSERT INTO Reservation_Store (
					parentID,elementId, domain, reservedName,
					isPhys, sessID, instanceID,sessSerial,
					sessLogonTime, osUser,languageISOID)
	VALUES(fn_parentId,elemId,classification,fn_reservingName,2,theSessID, instID,
		n_SessionSerial, v_SessionLogonTime,theOsUser,isoid);

EXCEPTION
		WHEN DUP_VAL_ON_INDEX THEN	
			null; --already reserved
		
	END;

COMMIT;

EXCEPTION
	WHEN  NO_DATA_FOUND THEN
		null; --ignore ,probably the obj is newly created and not committed
	WHEN OTHERS THEN
		raise_application_error(-20002, 'NamespaceServiceImpl encountered the SQL error:'||SUBSTR(SQLERRM,1,200));
		RETURN;

END reserveNameForFnBeforeDelete;
*/

procedure  reserveNameForWorkspace(ws_name varchar2,ws_owner varchar2) as
	PRAGMA AUTONOMOUS_TRANSACTION; --needed

baseISOID varchar2(100);

begin

select isoid into baseISOID from cmpsupportedlanguage_v where ISBASELANGUAGE='1';
	insert into reservation_store(PARENTID, DOMAIN, RESERVEDNAME, ISPHYS, LANGUAGEISOID, SESSID, instanceid,SESSSERIAL,SESSLOGONTIME, OSUSER) values(0,'CMPWorkspace', CONCAT(CONCAT(upper(ws_owner),'.'),upper(ws_name)), '1',baseISOID,0,0,0,'0', user);

commit;

EXCEPTION
	WHEN OTHERS THEN
		raise_application_error(-20002, 'reserveNameForWorkspace encountered the SQL error:'||SUBSTR(SQLERRM,1,200));

end reserveNameForWorkspace;

FUNCTION isUniqueWorkspaceName(ws_name varchar2 ,ws_owner varchar2, toReserve boolean) return number

as 
existing number:=0;

begin

--first check name is OK
  if(instr(ws_name,'~') > 0 OR
  instr(ws_name,'~') > 0  OR
 instr(ws_name,'`') > 0 OR 
 instr(ws_name,'!') > 0 OR
 instr(ws_name,'@') > 0 OR
 instr(ws_name,'%') > 0 OR
 instr(ws_name,'^') > 0 OR
 instr(ws_name,'&') > 0 OR
 instr(ws_name,'*') > 0 OR
 instr(ws_name,'(') > 0 OR
 instr(ws_name,')') > 0 OR
 instr(ws_name,'-') > 0 OR
 instr(ws_name,'+') > 0 OR
 instr(ws_name,'=') > 0 OR
 instr(ws_name,',') > 0 OR
 instr(ws_name,'<') > 0 OR
 instr(ws_name,'.') > 0 OR
 instr(ws_name,'>') > 0 OR
 instr(ws_name,'/') > 0  OR 
 instr(ws_name,'?') > 0 OR
 instr(ws_name,':') > 0 OR
 instr(ws_name,';') > 0 OR
 instr(ws_name, '''') > 0  OR  
 instr(ws_name,'\') > 0 OR 
 instr(ws_name,'"') > 0  OR
 instr(ws_name,'\') > 0 OR
 instr(ws_name,'|') > 0 OR
 instr(ws_name,']') > 0 OR
 instr(ws_name,'}') > 0 OR
 instr(ws_name,'[') > 0 OR
 instr(ws_name,' ') > 0 OR
 instr(ws_name,'{') > 0 OR
 instr(ws_name,'\t') > 0 OR
 instr(ws_name,'\n') > 0 ) then

raise_application_error(-20002, 'Workskpace name <'||ws_name||'> contains illegal character.');

end if;

--check whether name is less than 30 byte ... 
  if(LENGTH(ws_name) >30) then 
 
	raise_application_error(-20002, 'Workspace name length should not be longer than 30. But the name <'||ws_name||'> has more than 30 bytes.');

end if;

--then check uniqueness
  select count(*) into existing from FCOFOLDERNamespace  where workspaceid = 0 and domain = 'CMPWorkspace' and name= CONCAT(CONCAT(upper(ws_owner),'.'),upper(ws_name)) and languageISOID = (select isoid from cmpsupportedlanguage_v where ISBASELANGUAGE='1') ;

if(existing >0) then 
  return 0;
 end if;

-- do reservation... 
 if(toReserve = true) then 
  reserveNameForWorkspace(ws_name,ws_owner);
 end if;

return 1;

EXCEPTION
  WHEN OTHERS THEN
	raise;
	--		raise_application_error(-20002, 'Function isUniqueWorkspaceName encountered the SQL error:'||SUBSTR(SQLERRM,1,400));
end isUniqueWorkspaceName;

PROCEDURE unReserveWorkspaceName(ws_name varchar2 ,ws_owner varchar2)
as 
PRAGMA AUTONOMOUS_TRANSACTION; --needed

begin

delete from Reservation_Store where reservedname = CONCAT(CONCAT(upper(ws_owner),'.'),upper(ws_name)) and DOMAIN='CMPWorkspace';
	commit;

EXCEPTION
  WHEN OTHERS THEN
		raise_application_error(-20002, 'Function unReserveWorkspaceName encountered the SQL error:'||SUBSTR(SQLERRM,1,400));

end unReserveWorkspaceName;

END NamespaceServiceImpl;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de