CODEWITHSUNDEEP
sqloracle-databaseplsql
John Wick
John Wick

Reputation: 745

Creating a stored procedure that loops around multiple values passed in for parameters

So i am having a JAVA program call a stored procedure and passing in data from an XML file to parameters set in my stored procedure. I have tried calling the stored procedure from within oracle and they work for single values. Would this stored procedure work even if there were multiple values? Do i need to include some kind of FOR loop to ensure that all of the values are inserted? Below is my code:

CREATE OR REPLACE PROCEDURE pega_submission_value (
    rsubmission_id   IN NUMBER,
    rvalue_tx        IN VARCHAR,
    rutc_offset      IN NUMBER,
    rdata_date       IN VARCHAR,
    rhr_utc          IN VARCHAR,
    rhr              IN TIMESTAMP,
    rhr_num          IN NUMBER,
    rdata_code       IN VARCHAR,
    rdata_type       IN VARCHAR
) IS
    v_value_id   value.value_id%TYPE;
BEGIN
    NULL;
    INSERT INTO value (
        value_id,
        product_id,
        data_source_id,
        unit_cd,
        value_tx,
        utc_offset,
        data_date,
        hr_utc,
        hr,
        hr_num,
        data_code,
        create_dt,
        create_user_id
    ) VALUES (
        NULL,
        555,
        3,
        'NA',
        rvalue_tx,
        rutc_offset,
        rdata_date,
        rhr_utc,
        rhr,
        rhr_num,
        rdata_code,
        SYSDATE,
        '15'
    ) RETURNING value_id INTO v_value_id;

    INSERT INTO submission_value (
        submission_id,
        value_id,
        form_field_id,
        create_dt,
        create_user_id,
        modify_dt,
        modify_user_id,
        effective_dt,
        inactive_dt
    ) VALUES (
        rsubmission_id,
        v_value_id,
        (
            SELECT
                form_field_id
            FROM
                form_field
            WHERE
                form_field_tx = rdata_type
        ),
        SYSDATE,
        '777',
        NULL,
        NULL,
        NULL,
        NULL
    );

    COMMIT;
END pega_submission_value;
/

Upvotes: 0

Views: 639

Answers (1)

adilkun
adilkun

Reputation: 41

If you want to insert the multiple values to table you should call procedure in loop in your Java or you should send an array as input parameter by stored procedure.

Firstly create a type for the record:

CREATE OR REPLACE TYPE submission_value_rec AS OBJECT
(
  rsubmission_id NUMBER,
  rvalue_tx      VARCHAR,
  rutc_offset    NUMBER,
  rdata_date     VARCHAR,
  rhr_utc        VARCHAR,
  rhr            TIMESTAMP,
  rhr_num        NUMBER,
  rdata_code     VARCHAR,
  rdata_type     VARCHAR
)
/

Then create a type as table of type:

CREATE OR REPLACE TYPE submission_value_table FORCE AS TABLE OF submission_value_rec
/

After then change your stored procedure like that:

CREATE OR REPLACE PROCEDURE pega_submission_value(submission_values submission_value_table) IS
  v_value_id value.value_id%TYPE;
BEGIN
 FOR i IN 1 .. submission_value_table.count LOOP
INSERT INTO VALUE
  (value_id,
   product_id,
   data_source_id,
   unit_cd,
   value_tx,
   utc_offset,
   data_date,
   hr_utc,
   hr,
   hr_num,
   data_code,
   create_dt,
   create_user_id)
VALUES
  (NULL,
   555,
   3,
   'NA',
   submission_value_table(i).rvalue_tx,
   submission_value_table(i).rutc_offset,
   submission_value_table(i).rdata_date,
   submission_value_table(i).rhr_utc,
   submission_value_table(i).rhr,
   submission_value_table(i).rhr_num,
   submission_value_table(i).rdata_code,
   SYSDATE,
   '15')
RETURNING value_id INTO v_value_id;

INSERT INTO submission_value
  (submission_id,
   value_id,
   form_field_id,
   create_dt,
   create_user_id,
   modify_dt,
   modify_user_id,
   effective_dt,
   inactive_dt)
VALUES
  (submission_value_table(i).rsubmission_id,
   v_value_id,
   (SELECT form_field_id
      FROM form_field
     WHERE form_field_tx = submission_value_table(i).rdata_type),
   SYSDATE,
   '777',
   NULL,
   NULL,
   NULL,
   NULL);

COMMIT;
  END LOOP;
END pega_submission_value;

As you can see I used a loop for inserting values to table. Now you can create an array in Java program, and you can call procedure with new input parameter.

Upvotes: 1

Related Questions