Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    10

    Unanswered: [Help] stored proc to take array inputs and output array

    I have an array to pass to the stored proc and expect to receive an array.
    The input array has two keys and the output array has five values.
    The process: take 2 keys and execute the select statements to retrieve corresponding 5 values.
    input[i].key1, input[i].key2
    output[i].value1, output[i].value2 output[i].value3, output[i].value4, output[i].value5
    SELECT DISTINCT value1, value2, value3, value4, value5, key1, key2 FROM tblData WHERE key1 = input[i].key1 AND key2 = input[i].key2
    Then assign the results to output array, like below:
    output[i].value1 = value1;
    output[i].value2 = value2;
    output[i].value3 = value3;
    output[i].value4 = value4;
    output[i].value5 = value5;

    How can I achieve all of this through a stored proc??? Code sample if possible...
    Thanks a lot..

    W

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: [Help] stored proc to take array inputs and output array

    Here is a method using a package.

    1) Package spec. This declares the input and output array TYPEs:

    CREATE OR REPLACE PACKAGE pkg IS

    TYPE input_rec IS RECORD( key1 NUMBER
    , key2 NUMBER );
    TYPE input_tab IS TABLE OF input_rec INDEX BY BINARY_INTEGER;

    TYPE output_rec IS RECORD( value1 NUMBER
    , value2 NUMBER
    , value3 NUMBER
    , value4 NUMBER
    , value5 NUMBER );
    TYPE output_tab IS TABLE OF output_rec INDEX BY BINARY_INTEGER;

    PROCEDURE proc
    ( p_input_tab IN input_tab
    , p_output_tab OUT output_tab
    );

    END pkg;
    /

    2) Package body. This contains the procedure to do the work:

    CREATE OR REPLACE PACKAGE BODY pkg IS

    PROCEDURE proc
    ( p_input_tab IN input_tab
    , p_output_tab OUT output_tab
    )
    IS
    v_index PLS_INTEGER := p_input_tab.FIRST;
    BEGIN
    WHILE v_index IS NOT NULL
    LOOP
    SELECT value1, value2, value3, value4, value5
    INTO p_output_tab(v_index).value1
    , p_output_tab(v_index).value2
    , p_output_tab(v_index).value3
    , p_output_tab(v_index).value4
    , p_output_tab(v_index).value5
    FROM tbldata
    WHERE key1 = p_input_tab(v_index).key1
    AND key2 = p_input_tab(v_index).key2;
    v_index := p_input_tab.NEXT(v_index);
    END LOOP;
    END proc;

    END pkg;
    /

    3) A test program to show how the package is used:

    SET SERVEROUT ON SIZE 1000000

    DECLARE
    v_input_tab pkg.input_tab;
    v_output_tab pkg.output_tab;
    BEGIN
    v_input_tab(1).key1 := 1;
    v_input_tab(1).key2 := 10;
    v_input_tab(2).key1 := 3;
    v_input_tab(2).key2 := 30;
    v_input_tab(3).key1 := 5;
    v_input_tab(3).key2 := 50;
    pkg.proc( v_input_tab, v_output_tab );
    FOR i IN 1..3
    LOOP
    DBMS_OUTPUT.PUT_LINE( 'output('||i||') = {'
    ||v_output_tab(i).value1||','
    ||v_output_tab(i).value2||','
    ||v_output_tab(i).value3||','
    ||v_output_tab(i).value4||','
    ||v_output_tab(i).value5||'}'
    );
    END LOOP;
    END;
    /

    4) The test data:
    tandrews@IDEV> select * from tbldata
    2 /

    KEY1 KEY2 VALUE1 VALUE2 VALUE3 VALUE4 VALUE5
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    1 10 100 1000 10000 100000 1000000
    2 20 200 2000 20000 200000 2000000
    3 30 300 3000 30000 300000 3000000
    4 40 400 4000 40000 400000 4000000
    5 50 500 5000 50000 500000 5000000
    6 60 600 6000 60000 600000 6000000
    7 70 700 7000 70000 700000 7000000
    8 80 800 8000 80000 800000 8000000
    9 90 900 9000 90000 900000 9000000
    10 100 1000 10000 100000 1000000 10000000

    10 rows selected.

    5) Output from test program:

    output(1) = {100,1000,10000,100000,1000000}
    output(2) = {300,3000,30000,300000,3000000}
    output(3) = {500,5000,50000,500000,5000000}

    I hope that helps. There are some issues I have not addressed. For example, if one of the rows in the input table contains key values that are not found in tbldata, then the procedure will abort with an exception.

  3. #3
    Join Date
    May 2002
    Posts
    10
    Thank you very much for the assistance.

    In your package, how can I throw out an exception as an output message if anything goes wrong - meaning pass this error message to the next step and let the application know this and handle properly at application level?

    Thanks a lot,

    W

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to decide what exactly you want to happen if there is an exception. Some possibilities:

    1) As is, the procedure could raise NO_DATA_FOUND or TOO_MANY_ROWS, which the calling program could trap.

    2) You could add an EXCEPTION handler to trap these errors and return the error some other way, e.g. via an OUT parameter.

    3) You could put BEGIN/EXCEPTION around the SELECT statement so that the procedure does not abort, but maybe records the errors in another element in the output array:

    BEGIN
    SELECT value1, value2, value3, value4, value5
    INTO p_output_tab(v_index).value1
    , p_output_tab(v_index).value2
    , p_output_tab(v_index).value3
    , p_output_tab(v_index).value4
    , p_output_tab(v_index).value5
    FROM tbldata
    WHERE key1 = p_input_tab(v_index).key1
    AND key2 = p_input_tab(v_index).key2;
    EXCEPTION
    WHEN OTHERS THEN
    p_output_tab(v_index).error_code := SQLCODE;
    END;

  5. #5
    Join Date
    May 2002
    Posts
    10
    eventually, I want to pass the error message to the next section of Java code and throw out an exception.

    This is very helpful.
    Thanks a lot.

    W

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •