PL SQL Insert List

Question

i am trying to insert a list of integers in procedure using pl/sql but unfortunately am not able to find a way, can anyone guide me that how to pass a list of integers into procedure in PL/SQL.

Answer 1

Use a schema-wide type

CREATE TYPE TY_INT_ARRAY IS TABLE OF INTEGER;

or a package type

CREATE OR REPLACE PACKAGE PKG_INT_TEST AS

  TYPE TY_INT_ARRAY IS TABLE OF INTEGER;

  /*
    ... more stuff
  */

END PKG_INT_TEST;
/

Attention : you can't use a package type in SQL! Only inside PLSQL.

Please try following example:

SET SERVEROUTPUT ON;
SET FEEDBACK OFF;
CLEAR;

-- create a schema type of table of integers
CREATE TYPE TY_INT_ARRAY IS TABLE OF INTEGER;
/

-- Create a dumping procedure with array "list" parameter
CREATE OR REPLACE PROCEDURE DUMP_INT_ARRAY(P_ARRAY IN TY_INT_ARRAY) IS
BEGIN
  FOR L_I IN P_ARRAY.FIRST .. P_ARRAY.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(P_ARRAY(L_I)));
  END LOOP;
END;
/

-- Test the procedure:
DECLARE
  -- Declare an int array variable
  V_MY_ARRAY TY_INT_ARRAY;

BEGIN
  -- Assign values
  V_MY_ARRAY := TY_INT_ARRAY(1, 2, 3, 4, 5, 6, 7, 8, 9);

  DUMP_INT_ARRAY(V_MY_ARRAY);
END;
/

DROP PROCEDURE DUMP_INT_ARRAY;
DROP TYPE TY_INT_ARRAY;

You can fill-in values with the BULK COLLECT INTO Statement or with "Object"-Function accessors like .EXTEND and V_MY_ARRAY(V_MY_ARRAY.LAST) := 10;

Answer 2

What about using XMLTYPE, create the following procedure,

CREATE OR REPLACE PROCEDURE sp_list_n (
    list_n IN XMLTYPE
) AS
BEGIN
    FOR vl_idx IN (
        SELECT
            item_number
        FROM
            XMLTABLE ( '/r/n' PASSING list_n COLUMNS item_number NUMBER PATH 'text()' )
    ) LOOP
        dbms_output.put_line(vl_idx.item_number);
    END LOOP;
END;
/

Then execute,

begin
sp_list_n(xmltype('<r>
   <n>1</n>
   <n>2</n>
   <n>3</n>
   <n>4</n>
   <n>5555</n>
</r>'));
end;

You have the following output:

Procedimiento PL/SQL terminado correctamente.
1
2
3
4
5555

You could add numbers as well

Answer 3

This code uses C# and MS-SQL SERVER

Create your User Defined Table Type:

CREATE TYPE [dbo].[IntegersList] AS TABLE(
    [Item] INT NULL
);

//Store Procedure

CREATE PROCEDURE [dbo].[sp_UserIntegersList]
    @IntList IntegersList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @IntList l;
END

C# code

using (var con = new SqlConnection(constr))
{
    con.Open();
    using (SqlCommand cmd = new SqlCommand("exec sp_UserIntegersList @IntList", con))
    {
        var table = new DataTable();
        table.Columns.Add("Item", typeof(int));

        for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i);

        var pList = new SqlParameter("@IntList", SqlDbType.Structured);
        pList.TypeName = "dbo.StringList";
        pList.Value = table;

        cmd.Parameters.Add(pList);

        using (var dr = cmd.ExecuteReader())
        {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
        }
    }