[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE deleteOldRelationships(element IN xmldom.DOMElement, objUID IN VARCHAR2) IS
CURSOR getRelationships IS
SELECT b.objrel_id, b.secondary_obj_id, a.object_uid, c.type_name
FROM ies_meta_objects a,
ies_meta_obj_relationships b,
ies_meta_objects p,
ies_meta_relationship_types c
WHERE p.object_uid = objUID
AND a.object_id = b.secondary_obj_id
AND p.object_id = b.primary_obj_id
AND c.type_id = b.type_id
AND c.type_name <> 'self';
sqlStmt := 'DELETE FROM ies_meta_obj_relationships
WHERE objrel_id = :objectrel_id';
sqlStmt := 'DELETE FROM ies_meta_obj_relationships
WHERE primary_obj_id = :objId
AND secondary_obj_id = :secObjId';
END deleteOldRelationships;
PROCEDURE updatePropertyValues(element IN xmldom.DOMElement, uid IN varchar2) IS
CURSOR getPropertiesForObject IS
SELECT b.name, c.propval_id , b.property_id
FROM ies_meta_objects o,
ies_meta_object_propvals a,
ies_meta_properties b,
ies_meta_property_values c
WHERE o.object_uid = uid
AND a.propval_id = c.propval_id
AND b.property_id = c.property_id
AND o.object_id = a.object_id;
sqlStmt := 'UPDATE ies_meta_property_values
SET string_val = :value,
last_update_date = sysdate
WHERE propval_id = :propvalue_id';
sqlStmt := 'UPDATE ies_meta_property_values
SET lookup_id = :lookupId,
last_update_date = sysdate
WHERE propval_id = :propvalue_id';
END updatePropertyValues;
PROCEDURE insertObjPropertyValues(propval_tab IN propval_table, objUID IN VARCHAR2) IS
propValId number;
execute immediate 'select ies_meta_object_propvals_s.nextval from dual' into seqval;
'INSERT INTO ies_meta_object_propvals
(objpropval_id,
object_id,
propval_id,
created_by) SELECT :seq,
object_id,
:propValId,
1 from ies_meta_objects where object_uid = :objuid';
END insertObjPropertyValues;
PROCEDURE insertNewProperties(element IN xmldom.DOMElement, objUID IN VARCHAR2) IS
type props_type IS REF CURSOR;
'SELECT b.name, a.property_id
FROM ies_meta_obj_type_properties a,
ies_meta_properties b
WHERE a.property_id = b.property_id
AND a.objtype_id IN (SELECT type_id
FROM ies_meta_object_types
CONNECT BY PRIOR parent_id = type_id
START WITH type_id IN (SELECT type_id
FROM ies_meta_objects
WHERE object_uid = :aUID))
AND b.property_id NOT IN (SELECT b.property_id
FROM ies_meta_objects o,
ies_meta_object_propvals a,
ies_meta_properties b,
ies_meta_property_values c
WHERE o.object_uid = :bUID
AND a.propval_id = c.propval_id
AND b.property_id = c.property_id
AND o.object_id = a.object_id)' USING objUID, objUID;
select ies_meta_property_values_s.nextval into propValue_id from dual;
sqlStmt := 'INSERT INTO ies_meta_property_values (propval_id,
property_id,
string_val,
created_by)
VALUES (:id,
:property_id,
:val,
1 )';
sqlStmt := 'INSERT INTO ies_meta_property_values (propval_id,
property_id,
lookup_id,
created_by)
VALUES (:id,
:property_id,
:lookupId,
1 )';
insertObjPropertyValues(propval_tab, objUID);
END insertNewProperties;
FUNCTION updateMetaObject(element IN xmldom.DOMElement) return NUMBER IS
objId NUMBER := -1;
execute immediate' UPDATE ies_meta_objects
SET name = :1,
last_update_date = :2,
type_id = :3
WHERE object_uid = :4 returning object_id INTO :5' using objname, sysdate, objTypeId, uid returning INTO objId;
updatePropertyValues(element, uid);
insertNewProperties(element, uid);
deleteOldRelationships(element, uid);
END updateMetaObject;