More

    Examples for using Stored Procedure with Arrays in Oracle DB

    First we will create an array (nested table array or varray), use it in a stored procedure and IN and/or OUT parameters and then populate and retrieve data through a JDBC program.

    CREATING THE ARRAY

    Creating the array

    We can create a nested table array as:

    CREATE TYPE array_table AS TABLE OF VARCHAR2 (50);

    Or

    We can create a VARRAY as:

    CREATE TYPE array_table AS VARRAY(5) OF VARCHAR2(50);

    Note: In a nested table array (AS TABLE OF), you don’t specify an upper bound whereas in VARRAY, you specify an upper bound. If you pass more elements to a VARRAY than specified, you will get an exception as: Exceeded maximum VARRAY limit  

    CASE 1: ORACLE STORED PROCEDURE WITH ARRAY IN AND OUT PARAMETERS

    CREATE OR REPLACE PROCEDURE procarrayinout (p_array IN array_table,

                                               len        OUT NUMBER,

                                               p_arr_out  OUT array_table)

    AS

    BEGIN

       len := p_array.COUNT;

       p_arr_out := NEW array_table ();

       p_arr_out.EXTEND (len);

     FOR i IN 1 .. p_array.COUNT

       LOOP

       DBMS_OUTPUT.put_line (p_array (i));

       p_arr_out (i) := p_array (i);

       END LOOP;

    END;

    You can find a sample program for inserting and retrieving arrays from stored procedures @ http://javajee.com/jdbc-program-to-insert-and-retrieve-arrays-into-and-from-an-oracle-stored-procedure

    CASE 2: ORACLE STORED PROCEDURE EXAMPLE WITH ONLY ARRAY OUT PARAMETERS

    CREATE OR REPLACE PROCEDURE procarrayout ( len OUT NUMBER,

                                               p_arr_out  OUT array_table)

    AS

       v_count   NUMBER;

    BEGIN

       len := 5;

       p_arr_out := NEW array_table ();

       p_arr_out.EXTEND (len);

      v_count := 0;

      FOR i IN 1 .. len

       LOOP

       p_arr_out (i) := ‘dummy data’;

       v_count := v_count + 1;

       END LOOP;

    END;

    You can find a sample program for retrieving arrays from stored procedures @ http://javajee.com/jdbc-program-to-retrieve-arrays-from-an-oracle-stored-procedure-with-only-array-out-parameters.

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox