CODEWITHSUNDEEP
jmeter
user16923013
user16923013

Reputation:

How to execute a stored procedure in a multiple step using JDBC sampler

I have the following query which I need to execute step by step (mentioned in the sample below), is it giving me ORA-00922: missing or invalid option, but same thing working on my Oracle SQL developer tool.

Step1

SET ECHO ON
SET FEEDBACK ON
SET VERIFY ON
SET SERVEROUTPUT ON SIZE 1000000;
EXEC FWA_BATCH_PROCESSOR.SETUTLFILEOFF;

Step2

DELETE FROM FWA_ERRORS;
DELETE FROM FWA_BATCH_CONTROL;
INSERT INTO FWA_BATCH_CONTROL VALUES(10, 'UT1', 0, SYSDATE);
UPDATE FWA_PROCESSING SET PROCESS_FLAG = 'N';
UPDATE FWA_PROCESSING SET PROCESS_FLAG   = 'Y' WHERE PACKAGE_NAME = 'FWA_TU' AND PROCEDURE_NAME = 'DOTU';
DELETE FROM FWA_STAGE_TU;

Step 3:

DECLARE 
j number(4);
BEGIN 
FOR j in 11 .. 12 LOOP
      INSERT INTO FWA_STAGE_TU 
      (ID,
      EXT_ID,
      BATCH_NO,
      OPERATION,
      BLOCK_ROLL_UP_LAST_RECORD,
      LAST_UPDATED_DATE,
      ERROR_FLAG,
      TRIAL_NO,
      TRIAL_ALIAS_CODE,
      COUNTRY_CODE,
      DISPLAY_UNIT_NO,
      GROUP_NO,
      TRIAL_UNIT_REFERENCE,
      PRIMARY_INVESTIGATOR,
      PRIMARY_CENTRE,
      LOCATION_NO,
      PURPOSE_CODE,
      MANAGING_MED_UNIT_CODE,
      UPDATING_MED_UNIT_CODE,
      FINANCE_MED_UNIT_CODE,
      PATIENTS_ALL_SET_UP_FLAG,
      PATIENT_MONITORING_FLAG,
      COMMENTS,
      CONFIRMED_FLAG,
      CONFIRMED_BY,
      CONFIRMED_DATE,
      ROLLUP_ROLLDOWN_PLANNED_FIGS,
      DOC_COLLECTION_INDICATOR,
      VALIDATED_PAT_CAP,
      MAXIMUM_PAT_CAP,
      PREFERRED_LANGUAGE_CODE,
      NEW_DISPLAY_UNIT_NO,
      TOTAL_OPEN_DCF_ISSUES_NO,
      ORDER_INTERVAL,
      ORDER_INTERVAL_UNITS,
      DATA_SOURCE_CODE,
      RANK_SEQ)                  
      VALUES 
      (j, 1, 10, 'I', NULL, SYSDATE, 'N', 102922, 'TESTTRIAL4', 'BEL', 
      j, NULL, 'BELTU'||j, 130262, 124236, NULL, 'PATTR', 'FP', 'FP', 'FP', 'N', 'Y', 'TESTTU'||j, 
      'Y', 999999, SYSDATE, NULL, 'P', 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL); 
  END LOOP;
END;

Step4:

UPDATE fwa_processing SET process_flag = 'Y' WHERE procedure_name = 'DOTU';
UPDATE fwa_processing SET process_flag = 'N' WHERE procedure_name != 'DOTU';
UPDATE fwa_batch_control set batch_status = 0;

EXEC FWA_BATCH_PROCESSOR.SETUTLFILEOFF;
exec fwa_batch_processor.process_batch;
commit;

Connection Configuration

enter image description here

Request Configuration

enter image description here

Error Details:

enter image description here

tried with update statement as well, nothing is working ? any clue how we can resolve this issue?

After changes: I tried with keeping FWA_BATCH_PROCESSOR.SETUTLFILEOFF or EXEC FWA_BATCH_PROCESSOR.SETUTLFILEOFF

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 281

Answers (1)

Dmitri T
Dmitri T

Reputation: 168197

  1. You need to remove everything else and leave only FWA_BATCH_PROCESSOR.SETUTLFILEOFF
  2. One JDBC Request sampler - one SQL statement
  3. Either use JMeter Loop Controller and individual JDBC Request samplers per statement or wrap everything into EXECUTE IMMEDIATE
  4. The same as 2, one JDBC Request sampler per statement

Additionally:

More information:

Upvotes: 1

Related Questions