Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: textManager_pkgbodys.sql 03-jul-2006.01:57:38 ganessub Exp $
Rem
Rem textManager_pkgbodys.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      textManager_pkgbodys.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ganessub    06/26/06 - Created
Rem
CREATE OR REPLACE PACKAGE body MGMT_TEXT AS
-- Package level Type Definition
   TYPE cursorType IS REF CURSOR;

/*
Store a log entry for a text index.
If appendMode is true then the log start time will
not be overwritten if the log record already exist.
*/
procedure addTextLog(
        i_indexName  IN VARCHAR2,
        i_schemaName IN VARCHAR2,
        i_targetName IN VARCHAR2,
        i_targetType IN VARCHAR2,
        i_logDir     IN VARCHAR2,
        i_logFile    IN VARCHAR2,
        i_appendMode IN BOOLEAN,
        i_actionType IN VARCHAR2
)
IS
v_textGUID    RAW(50);
v_targetGUID  RAW(16);
v_logFound    BOOLEAN;
v_startDate   MGMT_TEXTINDEX_LOGS_INFO.START_DATE%TYPE;
v_sysDate     MGMT_TEXTINDEX_LOGS_INFO.START_DATE%TYPE;
BEGIN

   v_textGUID := NULL;
   v_targetGUID := NULL;
   v_logFound := FALSE;
 --getTarget GUID

 BEGIN
  SELECT TARGET_GUID
  INTO v_targetGUID
  FROM mgmt_targets
  WHERE TARGET_NAME = i_targetName
  AND   TARGET_TYPE = i_targetType;
 EXCEPTION
    WHEN NO_DATA_FOUND
       THEN
         raise MGMT_GLOBAL.target_does_not_exist;
 END;     

 createUniqueTextIndexRecord(i_indexName,i_schemaName,v_targetGUID,v_textGUID);

 /*IF v_textGUID IS  NULL THEN
  raise;
 END IF; 
 */

 SELECT SYSDATE
 INTO v_sysDate
 FROM DUAL;
 v_startDate := v_sysDate;
  DBMS_OUTPUT.put_line('before lock');

 BEGIN
   SELECT START_DATE
   INTO v_startDate
   FROM MGMT_TEXTINDEX_LOGS_INFO 
   WHERE TEXTINDEX_GUID = v_textGUID
   AND  LOG_DIRECTORY = i_logDir 
   AND  LOG_FILE = i_logFile
   FOR UPDATE;
   v_logFound := TRUE;
 EXCEPTION  
  WHEN NO_DATA_FOUND THEN
     v_logFound := FALSE; 
 END; 
  
  DBMS_OUTPUT.put_line('after lock');
--if append mode is true then keep the old start date
 IF i_appendMode = FALSE THEN
  v_startDate := v_sysDate;
 END IF; 

 IF v_logFound THEN
  DBMS_OUTPUT.put_line('updating log');
  UPDATE MGMT_TEXTINDEX_LOGS_INFO
  SET LOG_DIRECTORY = i_logDir,
      LOG_FILE = i_logFile,
      START_DATE = v_startDate
  WHERE TEXTINDEX_GUID = v_textGUID
  AND  LOG_DIRECTORY = i_logDir 
  AND  LOG_FILE = i_logFile;
 ELSE 
  DBMS_OUTPUT.put_line('inserting log');
  INSERT INTO MGMT_TEXTINDEX_LOGS_INFO(TEXTINDEX_GUID,LOG_DIRECTORY,LOG_FILE,START_DATE,TEXTINDEX_ACTION_TYPE)
              VALUES (v_textGUID,i_logDir,i_logFile,v_startDate,i_actionType);
 END IF;
 COMMIT;     
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  raise;
END addTextLog;

procedure createUniqueTextIndexRecord(
       i_indexName  IN VARCHAR2,
       i_schemaName IN VARCHAR2,
       i_targetGUID IN RAW,
       o_textIndexGUID OUT RAW)
IS
v_textGUID    RAW(50);
BEGIN
      v_textGUID := NULL; 
      
  BEGIN
    SELECT TEXTINDEX_GUID
    INTO v_textGUID
    FROM mgmt_textindex
    WHERE TEXTINDEX_NAME = i_indexName
    AND   TEXTINDEX_SCHEMA_NAME = i_schemaName
    AND   TARGET_GUID = i_targetGUID; 
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      SELECT SYS_GUID()
      INTO  v_textGUID
      FROM DUAL;
      INSERT INTO mgmt_textindex(TEXTINDEX_GUID,TARGET_GUID,TEXTINDEX_NAME,TEXTINDEX_SCHEMA_NAME)
      VALUES(v_textGUID,i_targetGUID,i_indexName,i_schemaName); 
     
  END; 
  
  COMMIT;     
  o_textIndexGUID := v_textGUID;
EXCEPTION
WHEN OTHERS THEN
 ROLLBACK;
 raise; 
END createUniqueTextIndexRecord;

end MGMT_TEXT;
/
show errors;