The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_COL_STEP_DATA(
P_APPID IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAME IN VARCHAR2,
P_TESTSTEPNAME IN VARCHAR2,
P_COLNAMES_ARRAY IN JTF_VARCHAR2_TABLE_4000,
P_LOGOP_ARRAY IN JTF_VARCHAR2_TABLE_4000,
P_VAL1_ARRAY IN JTF_VARCHAR2_TABLE_4000,
P_VAL2_ARRAY IN JTF_VARCHAR2_TABLE_4000,
ISUPDATE IN VARCHAR2,
P_LUBID IN NUMBER) IS
V_INDEX NUMBER;
if isupdate = 'TRUE' then
delete from jtf_diagnostic_decl_step_cols
where
appid = P_APPID
and groupname = p_groupname
and testclassname = p_testclassname
and teststepname = p_teststepname;
insert into jtf_diagnostic_decl_step_cols
(
APPID,
GROUPNAME,
TESTCLASSNAME,
TESTSTEPNAME,
COLUMN_NAME,
LOGICAL_OPERATOR,
VALIDATION_VAL1,
VALIDATION_VAL2,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
SECURITY_GROUP_ID
)
values
(
P_APPID,
P_GROUPNAME,
P_TESTCLASSNAME,
P_TESTSTEPNAME,
P_COLNAMES_ARRAY(V_INDEX),
P_LOGOP_ARRAY(V_INDEX),
P_VAL1_ARRAY(V_INDEX),
P_VAL2_ARRAY(V_INDEX),
1,
P_LUBID,
SYSDATE,
P_LUBID,
NULL,
SYSDATE,
NULL
);
END INSERT_COL_STEP_DATA;
v_selectValue JTF_VARCHAR2_TABLE_4000;
select COLUMN_NAME, LOGICAL_OPERATOR,
VALIDATION_VAL1, VALIDATION_VAL2
from jtf_diagnostic_decl_step_cols
where APPID = appshtname
and GROUPNAME = grpname
and TESTCLASSNAME = testclsname
and TESTSTEPNAME = teststpname;
v_sqlstr := 'select ';
v_selectValue := JTF_VARCHAR2_TABLE_4000();
v_selectValue.extend;
DBMS_SQL.DEFINE_COLUMN(v_cursorID, v_count2, v_selectValue(v_count2), 3000);
dbms_sql.column_value(v_cursorID, v_count2, v_selectValue(v_count2));
if v_selectValue(v_count2) <> v_validVal1(v_count2) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
if v_selectValue(v_count2) = v_validVal1(v_count2) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
if to_number(v_selectValue(v_count2)) < to_number(v_validVal1(v_count2)) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
if to_number(v_selectValue(v_count2)) > to_number(v_validVal1(v_count2)) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
if to_number(v_selectValue(v_count2)) >= to_number(v_validVal1(v_count2)) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
if to_number(v_selectValue(v_count2)) <= to_number(v_validVal1(v_count2)) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
if to_number(v_validVal1(v_count2)) <= to_number(v_selectValue(v_count2))
AND to_number(v_selectValue(v_count2)) <= to_number(v_validVal2(v_count2)) then
addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
|| v_selectValue(v_count2) || ' (**FAILED**) '
|| '', DETAILS_CLOB);
v_sqlstr := 'select count(*) from ' || table_view_name;
PROCEDURE UPDATE_STEP_SEQ(
P_APPID IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAME IN VARCHAR2,
P_STEPSEQARRAY IN JTF_VARCHAR2_TABLE_4000,
P_LUBID IN NUMBER) IS
v_numofrows NUMBER;
SELECT COUNT(*)
INTO v_numofrows
FROM jtf_diagnostic_decl_test_steps
WHERE APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME;
RAISE_APPLICATION_ERROR(-20000, 'Cant Update Step Sequences - Mismatch in number of sequences received');
UPDATE jtf_diagnostic_decl_test_steps
SET EXECUTION_SEQUENCE = v_index,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
WHERE appid = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_STEPSEQARRAY(v_index);
'Cant Update Step Sequence, Record Not Found'
|| P_APPID || ' '
|| P_GROUPNAME || ' '
|| P_TESTCLASSNAME || ' ' || 'OLD EXEC SEQ: ' || v_index);
END UPDATE_STEP_SEQ;
PROCEDURE insert_core_steps(
qAppID IN VARCHAR2,
newTestName IN VARCHAR2,
addToGroupName IN VARCHAR2,
stepType IN VARCHAR2,
newStepName IN VARCHAR2,
newStepDesc IN VARCHAR2,
errorType IN VARCHAR2,
newStepErrMsg IN VARCHAR2,
newStepFixInfo IN VARCHAR2,
newStepTableName IN VARCHAR2,
newStepQuery IN VARCHAR2,
logicalOperator IN VARCHAR2,
val1 IN VARCHAR2,
val2 IN VARCHAR2,
isUpdate IN VARCHAR2,
P_LUBID IN NUMBER) IS
v_ordernumber jtf_diagnostic_decl_test_steps.EXECUTION_SEQUENCE%TYPE;
select count(*) into v_temp
from jtf_diagnostic_decl_test_steps
where appid = qAppID
and groupname = addToGroupName
and testclassname = newTestName
and TESTSTEPNAME = newStepName;
if v_temp > 0 and not isupdate = 'TRUE' then
raise_application_error(-20000, 'Step name already exists in testcase');
elsif isupdate = 'TRUE' and v_temp > 0 then
-- first cleanup all information from the
-- jtf_diagnostic_arg and jtf_diagnostic_decl_step_cols
v_temp_char := newTestName || '/' || newStepName || '{-STEP/CLASS-}%';
delete from jtf_diagnostic_arg where
APPID = qAppID
and GROUPNAME = addToGroupName
and TESTCLASSNAME like v_temp_char;
delete from jtf_diagnostic_decl_step_cols
where appid = qAppID
and groupname = addToGroupName
and testclassname = newTestName
and TESTSTEPNAME = newStepName;
update jtf_diagnostic_decl_test_steps
set
STEP_TYPE = stepType,
STEP_DESCRIPTION = newStepDesc,
ERROR_TYPE = errorType,
ERROR_MESSAGE = newStepErrMsg,
FIX_INFO = newStepFixInfo,
TABLE_VIEW_NAME = newStepTableName,
WHERE_CLAUSE_OR_SQL = newStepQuery,
LOGICAL_OPERATOR = logicalOperator,
VALIDATION_VAL1 = val1,
VALIDATION_VAL2 = val2,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_LUBID
where
APPID = qAppID and
GROUPNAME = addToGroupName and
TESTCLASSNAME = newTestName and
TESTSTEPNAME = newStepName;
elsif not isupdate = 'TRUE' and v_temp = 0 then
-- insert the record to the database
-- getting the next sequence number for
-- inserting into the DB
select count(*) into v_ordernumber
from jtf_diagnostic_decl_test_steps
where appid = qAppID
and groupname = addToGroupName
and testclassname = newTestName;
insert into jtf_diagnostic_decl_test_steps
(
APPID,
GROUPNAME,
TESTCLASSNAME,
TESTSTEPNAME,
EXECUTION_SEQUENCE,
STEP_TYPE,
STEP_DESCRIPTION,
ERROR_TYPE,
ERROR_MESSAGE,
FIX_INFO,
MULTI_ORG,
TABLE_VIEW_NAME,
WHERE_CLAUSE_OR_SQL,
LOGICAL_OPERATOR,
VALIDATION_VAL1,
VALIDATION_VAL2,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE
)
values
(
qAppID,
addToGroupName,
newTestName,
newStepName,
v_ordernumber,
stepType,
newStepDesc,
errorType,
newStepErrMsg,
newStepFixInfo,
'N',
newStepTableName,
newStepQuery,
logicalOperator,
val1,
val2,
1,
P_LUBID,
SYSDATE,
P_LUBID,
NULL,
SYSDATE
);
END insert_core_steps;
select TESTSTEPNAME, STEP_DESCRIPTION
from jtf_diagnostic_decl_test_steps
where APPID like p_appid
and GROUPNAME = p_GROUPNAME
and TESTCLASSNAME = p_TESTCLASSNAME
order by EXECUTION_SEQUENCE;
PROCEDURE DELETE_STEPS(
P_APPID IN VARCHAR2,
P_GROUPNAME IN VARCHAR2,
P_TESTCLASSNAME IN VARCHAR2,
P_DELSTEPARRAY IN JTF_VARCHAR2_TABLE_4000) IS
v_index BINARY_INTEGER := 1;
select distinct EXECUTION_SEQUENCE into v_execution_sequence
from jtf_diagnostic_decl_test_steps
where APPID = P_APPID and groupname = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME and
TESTSTEPNAME = P_DELSTEPARRAY(v_index);
select step_type into v_step_type
from jtf_diagnostic_decl_test_steps
where APPID = P_APPID and groupname = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME and
TESTSTEPNAME = P_DELSTEPARRAY(v_index);
select table_view_name into v_diagnostic_testname
from jtf_diagnostic_decl_test_steps
where APPID = P_APPID and groupname = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME and
TESTSTEPNAME = P_DELSTEPARRAY(v_index);
delete from jtf_diagnostic_arg where
APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = v_diagnostic_testname;
delete from jtf_diagnostic_decl_test_steps where
APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_DELSTEPARRAY(v_index);
RAISE_APPLICATION_ERROR(-20000, 'Cant Delete Step, Record Not Found: '
|| P_APPID
|| ' '
|| P_GROUPNAME
|| ' '
|| P_TESTCLASSNAME
|| ' '
|| P_DELSTEPARRAY(v_index));
update jtf_diagnostic_decl_test_steps
set EXECUTION_SEQUENCE = (EXECUTION_SEQUENCE - 1),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE
where EXECUTION_SEQUENCE > v_execution_sequence
and APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME;
delete from jtf_diagnostic_decl_step_cols where
APPID = P_APPID
and GROUPNAME = P_GROUPNAME
and TESTCLASSNAME = P_TESTCLASSNAME
and TESTSTEPNAME = P_DELSTEPARRAY(v_index);
END DELETE_STEPS;
select vsize(tmpReportStr) into strSize from dual;