REM Copyright (c) 2003 Oracle Corporation. All rights reserved.
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
CREATE OR REPLACE PACKAGE BODY OTNCMS_XMLCONTENT IS
-- package global variables
  
  acc_denied   VARCHAR2(140) := '<EXC>Access Denied</EXC>';
  no_resfound  VARCHAR2(140) := '<EXC>No Resource Found</EXC>';
  not_a_vcr    VARCHAR2(140) := '<EXC>Not a Version Controlled Resource</EXC>';
  not_chkdout  VARCHAR2(140) := '<EXC>Resource has not been Checked Out</EXC>';
  ald_chkdout  VARCHAR2(140) := '<EXC>Resource is already Checked Out</EXC>';
  successmsg   VARCHAR2(140) := '<MSG>Operation Successful</MSG>';
  invalidres   VARCHAR2(140) := '<EXC>Invalid resource handle or path name</EXC>';
  xslrefmsg    VARCHAR2(140) := '<EXC>Cannot delete/rename XSL ' || 
                                     'referenced by other resources</EXC>';
  
  FUNCTION checkin (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose :
  -- 1. Call the package function to checkin the input resource
  -- 2. Commit the checkin.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
    resid XDB.DBMS_XDB_VERSION.RESID_TYPE;
  
  BEGIN
    --
    -- Checkin the input resource and commit
    --
    resid := XDB.DBMS_XDB_VERSION.CheckIn(pathname => abspath);
    COMMIT; 
    --
    -- return the success message 
    --
    return successmsg;
  EXCEPTION
  WHEN OTHERS THEN
    IF (SQLCODE = '-31190') THEN
      --
      -- return the message that the resource is 
      -- Not a version controlled resource 
      --
      return not_a_vcr; 
    ELSIF (SQLCODE = '-31192') THEN
      --
      -- return the message that the resource is 
      -- Not checked out 
      --
      return not_chkdout; 
    ELSIF (SQLCODE = '-31001') THEN
      --
      -- return the message that the resource has 
      -- Invalid resource handle or pathname
      --
      return invalidres; 
    ELSIF (SQLCODE = '-31050') THEN
      --
      -- return the message that access is denied
      --
      return acc_denied; 
    ELSE
      --
      -- Other SQL Error Messages
      --
      RAISE;
    END IF;
  END checkin;

  FUNCTION checkout (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose :
  -- 1. Call the package procedure to checkout the input resource
  -- 2. Commit the checkout.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  
  BEGIN
  
    --
    -- Checkout the input resource and commit
    --
    XDB.DBMS_XDB_VERSION.CheckOut(pathname => abspath);
    COMMIT; 
    --
    -- return the success message 
    --
    return successmsg;
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31190') THEN
        --
        -- return the message that the resource is 
        -- Not a version controlled resource 
        --
        return not_a_vcr; 
      ELSIF (SQLCODE = '-31191') THEN
        --
        -- return the message that the resource is
        -- already checked out
        --
        return ald_chkdout; 
      ELSIF (SQLCODE = '-31001') THEN
        --
        -- return the message that the resource has 
        -- Invalid resource handle or pathname
        --
        return invalidres; 
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END checkout;

  FUNCTION checkresourcestatus (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Query RESOURCE_VIEW to check the resource status.
  --Return : 
  -- 1. Version Id : if resource exists and is version controlled
  -- 2. corresponding message : if the resource does not exist or 
  -- if the resource is not versioned
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  CURSOR c_version(p_path VARCHAR2) IS
  SELECT extractvalue(res, '/Resource@VersionID') ,
         extractvalue(res, '/Resource@ActivityID')
  FROM   XDB.resource_view 
  WHERE  any_path = p_path;
  
  versionId   NUMBER := 0;
  activityId  NUMBER := 0;
  returnmsg   VARCHAR2(140) := NULL;
  BEGIN
    --
    -- Loop through the cursor to fetch all the records
    --
    OPEN c_version(abspath);
    LOOP
      FETCH c_version INTO versionId, activityId;
      IF c_version%NOTFOUND THEN
        --
        -- If the cursor has no data, 
        -- return No Resource Found
        --
        returnmsg := no_resfound;
      ELSE 
        IF versionId IS NULL THEN
          --
          -- If version id is null, 
          -- return Not a version controlled resource
          --
          returnmsg := not_a_vcr;
        ELSE 
          --
          -- Else return the version id
          --
          returnmsg := 'Version Id : ' || versionId;
        END IF;
      END IF;

      EXIT;
    END LOOP;
  
    return returnmsg;

  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END checkresourcestatus;

  FUNCTION changeuser (abspath IN VARCHAR2, newuser IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Update the owner of the resource with the input newuser 
  -- in RESOURCE_VIEW 
  -- 2. Commit the operation.
  --Return : 
  -- 1. SUCCESS : if the operation goes through fine
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  BEGIN
    --
    -- Update the Owner of the resource and commit
    --
    UPDATE XDB.resource_view
    SET res = updatexml(res, '/Resource/Owner/text()', UPPER(newuser))
    WHERE any_path LIKE abspath;
  
    COMMIT;
  
    --
    -- return success message
    --
    RETURN '<MSG>SUCCESS</MSG>';

  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
 
  END changeuser; 

  FUNCTION ResourceExists(path IN VARCHAR2) RETURN NUMBER IS
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Check for the existence of the resource with the input path
  -- by querying RESOURCE_VIEW.
  --Return : 
  -- 1. count : if the query returns value
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  result NUMBER;
  BEGIN 
  
    --
    -- Select the resource count in the input path
    --
    SELECT COUNT(*) 
    INTO   result 
    FROM   XDB.resource_view
    WHERE  equals_path(res, path) = 1;
  
    --
    -- return the count
    --
    RETURN result;
 
  EXCEPTION
    
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END ResourceExists; 

  FUNCTION createfolder (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. If the folder does not already exist, Create folder in the 
  -- XMLDB Repository with the input path.
  -- 2. Set the ACL owner_cmsadmin_acl.xml to the new folder.
  -- 3. Commit the transaction if creation successful.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  status BOOLEAN       := FALSE;
  chkres VARCHAR2(140) := null;
  
  BEGIN
    --
    -- Check the resource status 
    --
    chkres := checkresourcestatus(abspath => abspath);
  
    IF (chkres = no_resfound) THEN
      --
      -- If no resource found, create the folder
      --
      status := XDB.dbms_xdb.CreateFolder(abspath => abspath);
  
      --
      -- Set the ACL owner_cmsadmin_acl.xml 
      --
      XDB.dbms_xdb.setAcl( res_path => abspath, 
                           acl_path => '/sys/acls/owner_cmsadmin_acl.xml' );
  
      IF (status) THEN
        --
        -- Commit if the create folder was successful
        --
        COMMIT;
        --
        -- return the success message
        --
        return '<MSG>Folder created successfully</MSG>';
      ELSE 
         --
         -- return the message that folder creation was not successful
         -- 
         return '<EXC>Folder could not be created</EXC>';
      END IF;
    ELSE 
      --
      -- If resource already exists, return the message
      --
      return '<EXC>Folder already exists</EXC>';
    END IF;
  
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END createfolder;
  FUNCTION checkuser (inusername IN VARCHAR2) RETURN NUMBER IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Check if the user already exists.
  --Return : 
  -- 1. -1 : if the user already exists
  -- 2. 0 : if the user does not exist
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  
    user VARCHAR2(30) := NULL; 
  
  BEGIN
    -- 
    -- Select the username from all_users with the input username
    --
    SELECT username
    INTO   user
    FROM   all_users
    WHERE  UPPER(username) = UPPER(inusername);
  
    --
    -- If the user already exists, return -1
    --
    return -1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      --
      -- If the user does not exist, return 0
      --
      return 0;
    WHEN OTHERS THEN
      --
      -- Raise Exception
      --
      RAISE;
  END checkuser;

  FUNCTION insertResourceInfo(abspath IN VARCHAR2, resdir IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Insert a record in OTNCMS_CONTENT_VIEW 
  --Return : 
  -- 1. SUCCESS : if the operation goes through fine
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  BEGIN
    --
    -- Insert a new record into the OTNCMS_CONTENT_VIEW 
    --
    INSERT INTO CMSADMIN.OTNCMS_CONTENT_VIEW ( OWNER, 
                                               RESOURCEDIR, 
                                               RESOURCEURI )
                                      VALUES ( UPPER(USER), 
                                               resdir, 
                                               SYS.XDBURITYPE(abspath));
  
    --
    -- return the success message
    -- 
    return '<MSG>SUCCESS</MSG>'; 
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END insertResourceInfo; 

  FUNCTION createresource (abspath IN VARCHAR2, resdir IN VARCHAR2, data IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. If the resource does not already exist, Create resource with data 
  -- of type VARCHAR2 in the XMLDB Repository with the input path 
  -- 2. Insert resource info in OTNCMS_CONTENT_VIEW.
  -- 3. Set the ACL owner_cmsadmin_acl.xml to the new resource.
  -- 4. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  status BOOLEAN := FALSE;
  chkres VARCHAR2(140) := null;
  inssts VARCHAR2(140) := null; 
  BEGIN
    --
    -- Check the resource status 
    --
    chkres := checkresourcestatus(abspath => abspath);
  
    IF (chkres = no_resfound) THEN
      --
      -- If no resource found, create the resource
      --
      status := XDB.dbms_xdb.CreateResource(abspath => abspath, 
                                            data    => data);
  
      IF (status) THEN
        --
        -- If resource is created successfully, 
        -- Insert the resource info in OTNCMS_CONTENT_VIEW
        --
        inssts := insertResourceInfo(abspath => abspath, 
                                     resdir  => resdir);
        --
        -- Set the ACL owner_cmsadmin_acl.xml 
        --
        XDB.dbms_xdb.setAcl( res_path => abspath, 
                             acl_path => '/sys/acls/owner_cmsadmin_acl.xml' );
  
        COMMIT;
  
        --
        -- return the success message
        --
        return '<MSG>Resource created successfully</MSG>';
  
      ELSE 
  
        --
        -- return the message that the resource could not be created 
        --
        return '<EXC>Resource could not be created</EXC>';
      END IF;

    ELSE 
      --
      -- return the message that the resource already exists
      --
      return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
    END IF; 
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31003') THEN
        --
        -- return the message that the resource already exists
        --
        return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        -- 
        ROLLBACK;
        RAISE;
      END IF;
  END createresource;

  FUNCTION createresource (abspath IN VARCHAR2, resdir IN VARCHAR2, data IN OUT CLOB) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. If the resource does not already exist, Create resource with data 
  -- of type CLOB in the XMLDB Repository with the input path 
  -- 2. Insert resource info in OTNCMS_CONTENT_VIEW.
  -- 3. Set the ACL owner_cmsadmin_acl.xml to the new resource.
  -- 4. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  status BOOLEAN := FALSE;
  chkres VARCHAR2(140) := null;
  inssts VARCHAR2(140) := null; 
  absext VARCHAR2(140) := null; 
  BEGIN
    --
    -- Check the resource status 
    --
    chkres := checkresourcestatus(abspath => abspath);
  
    IF (chkres = no_resfound) THEN
      --
      -- If no resource found, create the resource
      --
  
      --
      -- Check the file extension
      --
      absext := LOWER( SUBSTR(abspath, (LENGTH(abspath) - 3) ));
      --
      -- If the extension indicates an XML file, use SYS.XMLTYPE
      -- Else directly create the resource as a CLOB
      --
      IF ( absext IN ('.xml','.xsl','.xsd','.rdf' ) ) THEN
        status := XDB.dbms_xdb.CreateResource(abspath => abspath, 
                                                 data => SYS.XMLTYPE(data));
  
      ELSE 
  
        status := XDB.dbms_xdb.CreateResource(abspath => abspath, 
                                                 data => data);
  
      END IF;
      IF (status) THEN
        --
        -- If resource is created successfully, 
        -- Insert the resource info in OTNCMS_CONTENT_VIEW
        --
        inssts := insertResourceInfo(abspath => abspath, 
                                      resdir => resdir);
        --
        -- Set the ACL owner_cmsadmin_acl.xml 
        --
        XDB.dbms_xdb.setAcl(res_path => abspath, 
                            acl_path => '/sys/acls/owner_cmsadmin_acl.xml');
        COMMIT;
  
        --
        -- Free Temporary LOB
        --
        DBMS_LOB.FREETEMPORARY(data);
        --
        -- return the success message
        --
        return '<MSG>Resource created successfully</MSG>';

      ELSE 
        --
        -- return the message that the resource could not be created 
        --
        return '<EXC>Resource could not be created</EXC>';
      END IF;

    ELSE 
      --
      -- return the message that the resource already exists
      --
      return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
    END IF; 

  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31003') THEN
        --
        -- return the message that the resource already exists
        --
        return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        --
        ROLLBACK;
        RAISE; 
      END IF;
  END createresource;

  FUNCTION createblobresource (abspath IN VARCHAR2, resdir IN VARCHAR2, data IN OUT BLOB) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. If the resource does not already exist, Create resource with data 
  -- of type BLOB in the XMLDB Repository with the input path 
  -- 2. Insert resource info in OTNCMS_CONTENT_VIEW.
  -- 3. Set the ACL owner_cmsadmin_acl.xml to the new resource.
  -- 4. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  status BOOLEAN := FALSE;
  chkres VARCHAR2(140) := null;
  inssts VARCHAR2(140) := null; 
  BEGIN
    --
    -- Check the resource status 
    --
    chkres := checkresourcestatus(abspath => abspath);
  
    IF (chkres = no_resfound) THEN
      --
      -- If no resource found, create the resource
      --
      status := XDB.dbms_xdb.CreateResource(abspath => abspath, 
                                               data => data);
  
      IF (status) THEN
        -- 
        -- If resource is created successfully, 
        -- Insert the resource info in OTNCMS_CONTENT_VIEW
        --
        inssts := insertResourceInfo(abspath => abspath, 
                                      resdir => resdir);
        --
        -- Set the ACL owner_cmsadmin_acl.xml 
        --
        XDB.dbms_xdb.setAcl(res_path => abspath, 
                            acl_path => '/sys/acls/owner_cmsadmin_acl.xml');
        COMMIT;
        --
        -- Free Temporary LOB
        --
        DBMS_LOB.FREETEMPORARY(data);
        --
        -- return the success message
        --
        return '<MSG>Resource created successfully</MSG>';
      ELSE 
        --
        -- return the message that the resource could not be created 
        --
        return '<EXC>Resource could not be created</EXC>';
      END IF;
    ELSE 
      --
      -- return the message that the resource already exists
      --
      return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
    END IF; 
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31003') THEN
        --
        -- return the message that the resource already exists
        --
        return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        --
        ROLLBACK;
        RAISE; 
      END IF;
  END createblobresource;

  FUNCTION createresource (abspath IN VARCHAR2, resdir IN VARCHAR2, data IN SYS.XMLTYPE) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. If the resource does not already exist, Create resource with data 
  -- of type XMLTYPE in the XMLDB Repository with the input path 
  -- 2. Insert resource info in OTNCMS_CONTENT_VIEW
  -- 3. Set the ACL owner_cmsadmin_acl.xml to the new resource.
  -- 4. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  status BOOLEAN := FALSE;
  chkres VARCHAR2(140) := null;
  inssts VARCHAR2(140) := null; 
  BEGIN
    --
    -- Check the resource status 
    --
    chkres := checkresourcestatus(abspath => abspath);
  
    IF (chkres = no_resfound) THEN
      --
      -- If no resource found, create the resource
      --
      status := XDB.dbms_xdb.CreateResource(abspath => abspath, 
                                               data => data);
  
      IF (status) THEN
        --
        -- If resource is created successfully, 
        -- Insert the resource info in OTNCMS_CONTENT_VIEW
        --
        inssts := insertResourceInfo(abspath => abspath, 
                                      resdir => resdir);
        --
        -- Set the ACL owner_cmsadmin_acl.xml 
        --
        XDB.dbms_xdb.setAcl(res_path => abspath, 
                            acl_path => '/sys/acls/owner_cmsadmin_acl.xml');
        COMMIT;
        --
        -- return the success message
        --
        return '<MSG>Resource created successfully</MSG>';
      ELSE 
        --
        -- return the message that the resource could not be created 
        --
        return '<EXC>Resource could not be created</EXC>';
      END IF;
    ELSE 
      --
      -- return the message that the resource already exists
      --
      return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
    END IF; 
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31003') THEN
        --
        -- return the message that the resource already exists
        --
        return '<EXC>Resource with same name already exists in this path</EXC>'; 
  
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        --
        ROLLBACK;
        RAISE; 
      END IF;
  END createresource;

  FUNCTION xslcheck(resloc IN VARCHAR2) RETURN BOOLEAN IS
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 11-Mar-2003
  --
  --Purpose:
  -- 1. Query the number of personalizations where the 
  -- input XSLLOC has been set for XML documents.
  --Return : 
  -- 1. TRUE : if personalizations do not exist
  -- 2. FALSE : if personalizations exist
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  reccount NUMBER;
  BEGIN
    SELECT count(*)
    INTO   reccount
    FROM   CMSADMIN.otncms_pers_view op
    WHERE  op.XSLLOC.geturl() = resloc;
    IF (reccount = 0) THEN
      return TRUE;
    ELSE 
      return FALSE;
    END IF;
  END xslcheck;

  FUNCTION deleteresource (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. If the input abspath refers to an XSL resource, 
  -- check if it is refered in XSLLOC for personalization.
  -- If it is referenced, return the message. Else Continue.
  -- 2. Delete the resource located at the input path.
  -- 3. Delete resource info from OTNCMS_CONTENT_VIEW
  -- 4. Delete resource info from OTNCMS_PERS_VIEW
  -- 5. Commit the transactions.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  updflg BOOLEAN := FALSE;
  BEGIN
    updflg := xslcheck(resloc => abspath);
  
    IF (NOT updflg) THEN
      --
      -- Return the message
      --
      return xslrefmsg;
    END IF;
    --
    -- Delete the resource at the input path
    --
    XDB.dbms_xdb.DeleteResource(abspath => abspath);
  
    --
    -- Delete the corresponding entry from OTNCMS_CONTENT_VIEW 
    --
    DELETE FROM CMSADMIN.OTNCMS_CONTENT_VIEW e
    WHERE  e.RESOURCEURI.geturl() = abspath
    AND    UPPER(e.owner) = UPPER(USER);
    --
    -- Delete the corresponding entry from OTNCMS_PERS_VIEW 
    -- Commit the transactions
    --
    DELETE FROM CMSADMIN.OTNCMS_PERS_VIEW op
    WHERE  op.RESOURCELOC.geturl() = abspath
    AND    UPPER(op.username) = UPPER(USER);
  
    COMMIT;
  
    --
    -- return the success message
    --
    return '<MSG>Successfully deleted</MSG>';
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31007') THEN
        --
        -- return the message that the resource 
        -- is non empty and cannot be deleted
        --
        return '<EXC>Cannot delete non-empty resource</EXC>'; 
      ELSIF (SQLCODE = '-31001') THEN
        --
        -- return the message that the resource has 
        -- Invalid resource handle or pathname
        --
        return invalidres; 
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        --
        ROLLBACK;
        RAISE; 
      END IF;
  END deleteresource;

  FUNCTION makeversioned (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose :
  -- 1. Call the package function to make the resource version controlled
  -- 2. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  resid XDB.DBMS_XDB_VERSION.RESID_TYPE;
  BEGIN
    --
    -- Add the resource to version control and commit
    --
    resid := XDB.DBMS_XDB_VERSION.MakeVersioned(pathname => abspath);
    COMMIT;
  
    --
    -- return success message
    --
    return '<MSG>Version Control successful</MSG>';
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END makeversioned;

  FUNCTION undocheckout (abspath IN VARCHAR2) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose :
  -- 1. Call the package function to undo checkout of the input resource
  -- 2. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  resid XDB.DBMS_XDB_VERSION.RESID_TYPE;
  BEGIN
    --
    -- Undo checkout the input resource
    --
    resid := XDB.DBMS_XDB_VERSION.UncheckOut(pathname => abspath);
    COMMIT;
  
    --
    -- return the success message 
    --
    return successmsg;
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31190') THEN
        --
        -- return the message that the resource is 
        -- Not a version controlled resource 
        --
        return not_a_vcr; 
      ELSIF (SQLCODE = '-31192') THEN
        --
        -- return the message that the resource is 
        -- Not checked out 
        --
        return not_chkdout; 
      ELSIF (SQLCODE = '-31001') THEN
        --
        -- return the message that the resource has 
        -- Invalid resource handle or pathname
        --
        return invalidres; 
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
      return acc_denied; 
    ELSE
      --
      -- Other SQL Error Messages
      --
      RAISE; 
    END IF;
  END undocheckout;

  FUNCTION rename(abspath IN VARCHAR2, oldname IN VARCHAR2, 
                  newname IN VARCHAR2, chkmsg IN VARCHAR2 DEFAULT 'NONE') RETURN VARCHAR2 IS
  
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose :
  -- 1. If the resource exists and if the input abspath refers to an 
  -- XSL resource, check if it is refered in XSLLOC for personalization.
  -- If it is referenced and input chkmsg is NONE (indicating that 
  -- message is not yet conveyed to the User), return the message. 
  -- If it is referenced and input chkmsg is UPDATE (indicating that 
  -- message is conveyed to the User), Continue.
  -- 2. Rename the resource by calling the package function 
  -- 3. Update OTNCMS_CONTENT_VIEW, OTNCMS_PERS_VIEW
  -- 4. Commit the transactions.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  chkres VARCHAR2(140) := null;
  updflg BOOLEAN       := FALSE;
  
  BEGIN
    --
    -- Check the resource status 
    --
    chkres := checkresourcestatus(abspath => abspath || '/' || newname);
  
    IF (chkres = no_resfound) THEN
      --
      -- If no resource found with the newname, only then rename the resource
      --
  
      updflg := xslcheck(resloc => abspath || '/' || oldname);
  
      IF ((NOT updflg) AND (chkmsg = 'NONE')) THEN
        --
        -- Return the message
        --
        return xslrefmsg;
      END IF;
  
      IF (updflg OR chkmsg = 'UPDATE') THEN
        --
        -- Rename the Resource using the API
        --
        XDB.DBMS_XDB.renameresource(   srcpath => abspath || '/' || oldname,
                                    destfolder => abspath,
                                       newname => newname);
  
        --
        -- Update OTNCMS_PERS_VIEW
        --
        UPDATE CMSADMIN.OTNCMS_PERS_VIEW op
        SET    resourceloc = SYS.XDBURITYPE(abspath || '/' || newname)
        WHERE  UPPER(op.username) = UPPER(USER)
        AND    op.resourceloc.geturl() = (abspath || '/' || oldname);
  
        --
        -- Update OTNCMS_CONTENT_VIEW 
        --
        UPDATE CMSADMIN.OTNCMS_CONTENT_VIEW oc
        SET    resourceuri = SYS.XDBURITYPE(abspath || '/' || newname)
        WHERE  UPPER(oc.owner) = UPPER(USER)
        AND    oc.resourceuri.geturl() = (abspath || '/' || oldname); 
  
        IF (chkmsg = 'UPDATE') THEN
          --
          -- Update OTNCMS_PERS_VIEW 
          --
          UPDATE CMSADMIN.OTNCMS_PERS_VIEW op
          SET    xslloc = SYS.XDBURITYPE(abspath || '/' || newname)
          WHERE  UPPER(op.username) = UPPER(USER)
          AND    op.xslloc.geturl() = (abspath || '/' || oldname);
  
        END IF;
  
        --
        -- Commit the transactions
        --
        COMMIT;
  
      END IF; 
  
      --
      -- return the success message
      --
      return '<MSG>Rename successful</MSG>';
    ELSE 
      --
      -- return the message that another resource exists with the new name
      --
      return '<EXC>Failed to Rename, another resource exists with this name</EXC>';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END rename; 

  FUNCTION updateresource (abspath IN VARCHAR2, data IN SYS.XMLTYPE) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Update the Contents and ModificationDate fields in RESOURCE_VIEW 
  -- with the input data and current timestamp for input resource path
  -- 2. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  BEGIN
    --
    -- Update the Contents and Modification Data in RESOURCE_VIEW with the 
    -- input data and current timestamp for input resource path and commit
    --
    UPDATE XDB.resource_view
    SET    res = updateXML(res, '/Resource/Contents/*', data,
                                '/Resource/ModificationDate/text()',
                                    to_char (CURRENT_TIMESTAMP, 
                                             'SYYYY-MM-DD"T"HH24:MI:SS.FF') )
    WHERE  any_path = abspath;
  
    COMMIT;
  
    --
    -- return the success message
    --
    RETURN '<MSG>Successfully updated the Resource</MSG>';
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31001') THEN
        --
        -- return the message that the resource has 
        -- Invalid resource handle or pathname
        --
        return invalidres; 
      ELSIF (SQLCODE = '-31192') THEN
        --
        -- return the message that the resource is 
        -- Not checked out 
        --
        return not_chkdout; 
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        --
        ROLLBACK;
        RAISE; 
      END IF; 
  END updateresource;

  FUNCTION updateresource (abspath IN VARCHAR2, data IN CLOB) RETURN VARCHAR2 IS 
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Update the Contents and ModificationDate fields in RESOURCE_VIEW 
  -- with the input data and current timestamp for input resource path
  -- 2. Commit the transaction.
  --Return : 
  -- 1. success message : if the operation goes through fine
  -- 2. corresponding error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  BEGIN
    --
    -- Update the Contents and Modification Data in RESOURCE_VIEW with the 
    -- input data and current timestamp for input resource path and commit
    --
    UPDATE XDB.resource_view
    SET    res = updateXML(res, '/Resource/Contents/*', data,
                                '/Resource/ModificationDate/text()',
                                    to_char (CURRENT_TIMESTAMP, 
                                             'SYYYY-MM-DD"T"HH24:MI:SS.FF') )
    WHERE  any_path = abspath;
  
    COMMIT;
  
    --
    -- return the success message
    --
    RETURN '<MSG>Successfully updated the Resource</MSG>';
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31001') THEN
        --
        -- return the message that the resource has 
        -- Invalid resource handle or pathname
        --
        return invalidres; 
      ELSIF (SQLCODE = '-31192') THEN
        --
        -- return the message that the resource is 
        -- Not checked out 
        --
        return not_chkdout; 
      ELSIF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        return acc_denied; 
      ELSE 
        --
        -- Other SQL Error Messages
        --
        ROLLBACK;
        RAISE; 
      END IF; 
  END updateresource;

  FUNCTION getprevcontents(abspath IN VARCHAR2, version IN VARCHAR2, 
                            action IN VARCHAR2) RETURN SYS.XMLTYPE IS
  --------------------------------------------------------------------------
  --Created by : Pushkala S
  --Date created: 10-Dec-2002
  --
  --Purpose:
  -- 1. Get the predecessors of the input resource path 
  -- 2. Get the contents of the input version of the resource
  -- 3. If the requested action is 'EDITVERSION',
  -- CheckOut the resource and return the value
  -- 4. Else, just return the value.
  --Return : 
  -- 1. XMLTYPE Contents of the requested version of the resource
  -- 2. XMLTYPE Formatted error message : if the operation fails
  --
  --Change History: (who, when, what:!)
  --Who When What
  --------------------------------------------------------------------------
  residlist XDB.DBMS_XDB_VERSION.resid_list_type;
  resid     XDB.DBMS_XDB_VERSION.resid_type;
  value     SYS.XMLTYPE   := NULL;
  ressts    VARCHAR2(140) := NULL;
  vercount  NUMBER := 0; 
  tempnum   NUMBER := 0; 
  
  BEGIN
    --
    -- Get the count of number of versions of the resource
    --
    SELECT extractvalue(res, '/Resource@VersionID')
    INTO   vercount
    FROM   XDB.resource_view
    WHERE  any_path = abspath;
    IF (TO_NUMBER(version) < vercount) THEN
      --
      -- Continue to get the contents of the required version only if the 
      -- required version is less than the total number of versions
      --
  
      --
      -- Get the resource id of the immediate predecessor
      --
      residlist := XDB.DBMS_XDB_VERSION.GetPredecessors(pathname => abspath);
      resid := residlist(1);
      --
      -- Set to immediate predecessor
      --
      tempnum := vercount - 1; 
      IF (tempnum = TO_NUMBER(version)) THEN
        --
        -- If this is the required version, then
        -- Get the contents of the resource with the above resource id
        --
        value := XDB.DBMS_XDB_VERSION.GETCONTENTSXMLBYRESID(resid => resid);
      ELSE 
        --
        -- Else, Loop until the required version is reached
        --
        WHILE (tempnum > TO_NUMBER(version)) LOOP
          --
          -- Decrement counter
          --
          tempnum := tempnum - 1;
          --
          -- Get the resource id of the next immediate predecessor
          --
          residlist := XDB.DBMS_XDB_VERSION.GETPREDSBYRESID(resid => resid); 
          resid := residlist(1);
          IF (tempnum = TO_NUMBER(version)) THEN
            --
            -- If this is the required version, then
            -- Get the contents of the resource with the above resource id
            --
            value := XDB.DBMS_XDB_VERSION.GETCONTENTSXMLBYRESID(resid => resid);
          END IF;
        END LOOP;
      END IF;
  
    ELSIF ((TO_NUMBER(version) >= vercount) OR (TO_NUMBER(version) = 0)) THEN
      --
      -- If the input version id is greater than the count obtained,
      -- set value as NULL indicating error
      --
      value := NULL;
    END IF;
  
    IF (value IS NOT NULL) THEN
      --
      -- If value is obtained above
      --
      IF (action = 'EDITVERSION') THEN
        --
        -- If the input action is 'EDITVERSION', checkout the resource
        --
        ressts := CMSADMIN.otncms_xmlcontent.checkout(abspath => abspath);
        IF (NOT(ressts = successmsg)) THEN
          --
          -- If checkout is not successful, format the return XMLTYPE value
          --
          value := SYS.XMLTYPE('<GPCERROR>'||ressts||'</GPCERROR>');
        END IF;
      END IF;
    ELSE
      --
      -- If value is NULL, format the return XMLTYPE value
      --
      value := SYS.XMLTYPE('<GPCERROR>' ||
                           'Failed to get contents of this version' ||
                           '</GPCERROR>');
    END IF;
  
    --
    -- return the formatted value or contents
    --
    RETURN value; 
  EXCEPTION
    WHEN OTHERS THEN
      IF (SQLCODE = '-31050') THEN
        --
        -- return the message that access is denied
        --
        value := SYS.XMLTYPE('<GPCERROR>' || acc_denied || '</GPCERROR>');
        return value; 
      ELSE
        --
        -- Other SQL Error Messages
        --
        RAISE;
      END IF;
  END getprevcontents; 

  FUNCTION checkPrivileges(respath IN VARCHAR2) RETURN VARCHAR2 IS
  ------------------------------------------------------------------
  --Created by : Elangovan
  --Date created: 03-Mar-2003
  --
  --Purpose: Checks if the user has read,write, write-acl and all privileges on
  -- the specified resource path. Returns a comma (,) separated list of
  -- privileges held.
  --
  --Change History: (who, when, what:!)
  --Who When What
  -------------------------------------------------------------------
  haspriv       VARCHAR2(2000);
  status        PLS_INTEGER;
  privnamespace VARCHAR2(1000);
  priv          VARCHAR2(4000);
  
  TYPE VARLIST IS TABLE OF VARCHAR2(100);
  privlist VARLIST;
  
  BEGIN
    --
    -- List of privileges to be checked
    --
    privlist := VARLIST('<dav:read/>','<dav:write/>','<dav:write-acl/>','<dav:all/>');
  
    privnamespace := '<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd" 
                          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                          xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd 
                                              http://xmlns.oracle.com/xdb/acl.xsd 
                                              DAV: http://xmlns.oracle.com/xdb/dav.xsd" 
                          xmlns:xdbacl="http://xmlns.oracle.com/xdb/acl.xsd" 
                          xmlns:dav="DAV:">';

    --
    -- Loop through list of privileges and check if the user is authorised with
    -- these privileges, if so, append it to the return list.
    --
    FOR i IN 1..privlist.count LOOP
  
      priv := privnamespace || privlist(i) || '</privilege>'; 
  
      status := DBMS_XDB.checkPrivileges( respath, SYS.XMLTYPE.createXML( priv ) );
  
      --
      -- DBMS_XDB.checkPrivilege return a positive integer if current user has 
      -- the given privilege
      --
      IF status > 0 THEN
        IF i = privlist.count THEN
          haspriv := haspriv || privlist(i);
        ELSE
          haspriv := haspriv || privlist(i) || ','; 
        END IF;
      END IF;
    END LOOP; 
  
    RETURN haspriv;
  
  END checkPrivileges;
  
END OTNCMS_XMLCONTENT; 
/
SHOW ERRORS;
COMMIT;
REM EXIT;