[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'select TYPE_ID
from IES_META_OBJECT_TYPES
where TYPE_NAME = :type' into typeId using x_object_type;
procedure InsertMetaObject (
x_object_uid in varchar2,
x_object_name in varchar2,
x_object_type in varchar2,
x_user_id in number,
x_date in date
) is
typeId NUMBER;
insertStmt varchar2(2000);
execute immediate 'select IES_META_OBJECTS_S.NEXTVAL from dual' into seqval;
insertStmt := 'insert into IES_META_OBJECTS (
OBJECT_ID,
OBJECT_UID,
NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TYPE_ID)
values (
:seq,
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8)';
execute immediate insertStmt using seqval,
x_object_uid,
x_object_name,
x_user_id,
x_date,
x_date,
x_user_id,
120,
typeId;
end InsertMetaObject;
select OBJECT_ID
into objectId
from IES_META_OBJECTS
where OBJECT_UID = x_object_uid
and rownum < 2; -- see comments #2 above for details
select count(B.PROPVAL_ID)
into propValCount
from IES_META_OBJECTS A, IES_META_OBJECT_PROPVALS B,
IES_META_PROPERTY_VALUES C,
IES_META_PROPERTIES D
where A.OBJECT_UID = X_OBJECT_UID
and A.OBJECT_ID = B.OBJECT_ID
and C.PROPERTY_ID = D.PROPERTY_ID
and b.PROPVAL_ID = C.PROPVAL_ID
AND D.NAME = x_prop_name;
procedure deletePropVals(x_propval_id in number,
x_prop_name in varchar2) IS
deleteStmt varchar2(2000);
deleteStmt := 'delete from IES_META_PROPERTY_VALUES
where propval_id = :1
and property_id IN (select property_id
from IES_META_PROPERTIES
where NAME = :2)';
execute immediate deleteStmt using x_propval_id, x_prop_name;
procedure deleteObjectPropVals(x_object_uid in varchar2,
x_prop_name in varchar2) IS
TYPE obj_prop_val_type IS REF CURSOR;
'select B.PROPVAL_ID
from IES_META_OBJECTS A, IES_META_OBJECT_PROPVALS B,
IES_META_PROPERTY_VALUES C,
IES_META_PROPERTIES D
where A.OBJECT_UID = :1
and A.OBJECT_ID = B.OBJECT_ID
and C.PROPERTY_ID = D.PROPERTY_ID
and b.PROPVAL_ID = C.PROPVAL_ID
AND D.NAME = :2' using x_object_uid, x_prop_name;
deletePropVals(propValId, x_prop_name);
execute immediate 'delete from IES_META_OBJECT_PROPVALS where propval_id = :id' using propValId;
END deleteObjectPropVals;
procedure UpdateMetaObject (
x_object_uid in varchar2,
x_object_name in varchar2,
x_object_type in varchar2,
x_user_id in number,
x_date in date
) is
typeId NUMBER;
updateStmt varchar2(2000);
updateStmt := 'update IES_META_OBJECTS set
NAME = :name,
TYPE_ID = :id,
LAST_UPDATE_DATE = :updateDate,
LAST_UPDATED_BY = :last_updated_by
where OBJECT_UID = :objUid';
execute immediate updateStmt using x_object_name, typeId, x_date, x_user_id, x_object_uid;
end UpdateMetaObject;
x_last_update_date in varchar2,
x_custom_mode in varchar2
) is
f_luby number;
select last_updated_by, last_update_date
from ies_meta_objects
where object_uid = x_object_uid
and rownum < 2;
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
insertMetaObject(x_object_uid,
x_object_name,
x_object_type,
f_luby,
f_ludate);
updateMetaObject(x_object_uid,
x_object_name,
x_object_type,
f_luby,
f_ludate);
procedure InsertMetaLibrary (
x_object_uid in varchar2,
x_user_id in number,
x_date in date
) is
objId NUMBER;
insertStmt varchar2(2000);
execute immediate 'select IES_META_LIBRARY_S.NEXTVAL from dual' into seqval;
insertStmt := 'insert into IES_META_LIBRARY (
LIBOBJ_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_ID)
values (
:seq,
:1,
:2,
:3,
:4,
:5,
:6)';
execute immediate insertStmt using seqval, x_user_id, x_date, x_date, x_user_id, 120, objId;
end InsertMetaLibrary;
x_last_update_date in varchar2
) is
f_luby number;
SELECT libobj_id
FROM ies_meta_library
WHERE object_id = (select OBJECT_ID
from IES_META_OBJECTS
where OBJECT_UID = x_object_uid
and rownum < 2);
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
insertMetaLibrary(x_object_uid,
f_luby,
f_ludate);
deleteObjectPropVals(x_object_uid, x_prop_name);
select B.PROPVAL_ID
into propValId
from IES_META_OBJECTS A, IES_META_OBJECT_PROPVALS B,
IES_META_PROPERTY_VALUES C,
IES_META_PROPERTIES D
where A.OBJECT_UID = X_OBJECT_UID
and A.OBJECT_ID = B.OBJECT_ID
and C.PROPERTY_ID = D.PROPERTY_ID
and b.PROPVAL_ID = C.PROPVAL_ID
AND D.NAME = x_prop_name;
select PROP_LOOKUP_ID
into lookupId
from IES_META_PROPERTY_LOOKUPS
where PROPERTY_ID = x_prop_id
and LOOKUP_KEY = x_lookup_key;
select A.PROPERTY_ID
into propid
from IES_META_OBJ_TYPE_PROPERTIES A,
IES_META_PROPERTIES B
where A.PROPERTY_ID = B.PROPERTY_ID
and B.NAME = X_PROP_NAME
and A.OBJTYPE_ID IN (select TYPE_ID
from IES_META_OBJECT_TYPES
connect by prior PARENT_ID = TYPE_ID
start with TYPE_ID = (select TYPE_ID
from IES_META_OBJECTS c
where C.OBJECT_UID = X_OBJECT_UID));
procedure updateMetaPropertyValues (
x_object_uid in varchar2,
x_prop_name in varchar2,
x_prop_value in varchar2,
x_lookup_key in number,
x_user_id in number,
x_date in date)
is
propValId number;
execute immediate 'update IES_META_PROPERTY_VALUES set
LAST_UPDATE_DATE = :1,
LAST_UPDATED_BY = :2,
STRING_VAL = :3,
LOOKUP_ID = :4
where PROPVAL_ID = :5' using x_date, x_user_id, x_prop_value, lookupId, propValId;
end updateMetaPropertyValues;
procedure insertMetaObjPropValues (
x_object_uid in varchar2,
x_propValId in number,
x_user_id in number,
x_date in date)
is
objId number;
insertStmt varchar2(2000);
execute immediate 'select IES_META_OBJECT_PROPVALS_S.NEXTVAL from dual' into seqVal;
insertStmt := 'insert into IES_META_OBJECT_PROPVALS(
OBJPROPVAL_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROPVAL_ID,
OBJECT_ID)
values (
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8)';
execute immediate insertStmt using seqval, x_date, x_user_id, x_date, x_user_id, 120, x_propValId, objId;
end insertMetaObjPropValues;
procedure insertMetaPropertyValues (
x_object_uid in varchar2,
x_prop_name in varchar2,
x_prop_value in varchar2,
x_lookup_key in number,
x_user_id in number,
x_date in date)
is
propId number;
insertStmt varchar2(2000);
execute immediate 'select IES_META_PROPERTY_VALUES_S.NEXTVAL from dual' into seqval;
insertStmt := 'insert into IES_META_PROPERTY_VALUES (
PROPVAL_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROPERTY_ID,
STRING_VAL,
LOOKUP_ID)
values (
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9) returning propval_id INTO :10';
execute immediate insertStmt using seqval, x_date, x_user_id, x_date, x_user_id, 120, propId, x_prop_value, lookupId returning into propValId ;
insertMetaObjPropValues(x_object_uid, propValId, x_user_id, x_date);
end insertMetaPropertyValues;
x_last_update_date in varchar2,
x_custom_mode in varchar2)
is
f_luby number;
select b.last_updated_by, b.last_update_date
from IES_META_OBJECTS A, IES_META_OBJECT_PROPVALS B,
IES_META_PROPERTY_VALUES C,
IES_META_PROPERTIES D
where A.OBJECT_UID = X_OBJECT_UID
and A.OBJECT_ID = B.OBJECT_ID
and C.PROPERTY_ID = D.PROPERTY_ID
and b.PROPVAL_ID = C.PROPVAL_ID
AND D.NAME = x_prop_name;
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
insertMetaPropertyValues(x_object_uid,
x_prop_name,
x_prop_value,
x_lookup_key,
f_luby,
f_ludate);
updateMetaPropertyValues(x_object_uid,
x_prop_name,
x_prop_value,
x_lookup_key,
f_luby,
f_ludate);
select OBJREL_ID
into objRelId
from IES_META_OBJ_RELATIONSHIPS C,
IES_META_RELATIONSHIP_TYPES D
where PRIMARY_OBJ_ID = (select OBJECT_ID
from IES_META_OBJECTS
where OBJECT_UID = x_prim_obj_uid
and rownum < 2)
and SECONDARY_OBJ_ID = (select OBJECT_ID
from IES_META_OBJECTS
where OBJECT_UID = x_sec_obj_uid
and rownum < 2)
and C.TYPE_ID = D.TYPE_ID
and D.TYPE_NAME = x_type_name;
select TYPE_ID
into typeId
from IES_META_RELATIONSHIP_TYPES
where TYPE_NAME = x_type_name;
procedure updateMetaObjRelationships (
x_prim_obj_uid in varchar2,
x_sec_obj_uid in varchar2,
x_type_name in varchar2,
x_obj_order in number,
x_user_id in number,
x_date in date
) is
typeId number;
execute immediate 'update IES_META_OBJ_RELATIONSHIPS set
LAST_UPDATE_DATE = :1,
LAST_UPDATED_BY = :2,
DELETED_STATUS = :3,
TYPE_ID = :4,
OBJ_ORDER = :5
where OBJREL_ID = :6' using x_date, x_user_id, 0, typeId, x_obj_order, relId;
end updateMetaObjRelationships;
procedure insertMetaObjRelationships (
x_prim_obj_uid in varchar2,
x_sec_obj_uid in varchar2,
x_type_name in varchar2,
x_obj_order in number,
x_user_id in number,
x_date in date
) is
typeId number;
insertStmt varchar2(2000);
execute immediate 'select IES_META_OBJ_RELATIONSHIPS_S.NEXTVAL from dual' into seqval;
insertStmt := 'insert into IES_META_OBJ_RELATIONSHIPS (
OBJREL_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TYPE_ID,
PRIMARY_OBJ_ID,
SECONDARY_OBJ_ID,
OBJ_ORDER,
DELETED_STATUS)
values (
:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10,
:11)';
execute immediate insertStmt using seqval, x_date, x_user_id, x_date, x_user_id, 120, typeId, primObjId, secObjId, x_obj_order, 0;
end insertMetaObjRelationships;
x_last_update_date in varchar2,
x_custom_mode in varchar2
)
IS
objRelId number;
select c.last_updated_by, c.last_update_date
from IES_META_OBJ_RELATIONSHIPS C,
IES_META_RELATIONSHIP_TYPES D
where PRIMARY_OBJ_ID = (select OBJECT_ID
from IES_META_OBJECTS
where OBJECT_UID = x_prim_obj_uid
and rownum < 2)
and SECONDARY_OBJ_ID = (select OBJECT_ID
from IES_META_OBJECTS
where OBJECT_UID = x_sec_obj_uid
and rownum < 2)
and C.TYPE_ID = D.TYPE_ID
and D.TYPE_NAME = x_type_name;
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
insertMetaObjRelationships(x_prim_obj_uid,
x_sec_obj_uid,
x_type_name,
x_obj_order,
f_luby,
f_ludate);
updateMetaObjRelationships(x_prim_obj_uid,
x_sec_obj_uid,
x_type_name,
x_obj_order,
f_luby,
f_ludate);