Hiernate oracle function

 

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="cn.ccb.metadata.app.mdbo.MdNamespaceXML" table="MD_NAMESPACE">
        <id name="uuid" type="java.lang.String">
            <column name="UUID" length="32" />
            <generator class="uuid.hex" />
        </id>
         <property name="fullnamespace" type="java.lang.String">
            <column name="FULLNAMESPACE" length="1024" />
        </property>
                <property name="fullpath" not-null="true"  formula="getfullpath(fullnamespace)"  />

 

    </class>

</hibernate-mapping>

/* Formatted on 2012/11/9 10:04:26 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE FUNCTION MDMS.getfullpath (fullname IN VARCHAR2)
   RETURN VARCHAR2
IS
   Result          VARCHAR2 (1000);

 

   v_fullpath      VARCHAR2 (1000) := ' ';
   tmp             VARCHAR2 (33) := '';
   v_i             NUMBER := 0;
   v_displayname   VARCHAR2 (1000) := '';
BEGIN
   Result := '';
   v_fullpath := fullname || '/';
   v_i := INSTR (v_fullpath, '/');
   v_displayname := '';

   --  DBMS_OUTPUT.PUT_LINE(v_fullpath);
   WHILE (v_i > 0)
   LOOP
      --  DBMS_OUTPUT.PUT_LINE( v_i);
      tmp := SUBSTR (v_fullpath, 0, v_i - 1);

      --DBMS_OUTPUT.PUT_LINE(tmp);
      IF (LENGTH (tmp) >= 32)
      THEN
         SELECT UNIQUE displayname
           INTO v_displayname
           FROM md_instance mdi
          WHERE mdi.enddatetime =
                   TO_DATE ('9999-12-31 23:59:59', 'yyyy-mm-dd hh24:MI:ss')
                AND mdi.instanceid = tmp;

         -- DBMS_OUTPUT.PUT_LINE( v_displayname);
         Result := Result || '/' || v_displayname;
      --DBMS_OUTPUT.PUT_LINE( Result);
      END IF;

      v_fullpath := SUBSTR (v_fullpath, v_i + 1);
      --DBMS_OUTPUT.PUT_LINE(v_fullpath);
      v_i := INSTR (v_fullpath, '/');
   --DBMS_OUTPUT.PUT_LINE(v_i);
   END LOOP;

   RETURN Result;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      Result := Result || '/';
      RETURN Result;
   WHEN OTHERS
   THEN
      Result := Result || '/';
      RAISE;
END getfullpath;
/