using nested table as in Parameter to a Procedure

Question

I have a table T1 and I want to insert multiple rows at a time through a procedure using collection. I have written the code but when I'm trying to execute it throws an error. Please advise.

create table t1 ( id number , name varchar2(10));
/

create or replace PACKAGE PKG1 AS
   TYPE TAB_LIST IS TABLE OF T1%ROWTYPE;
   PROCEDURE PROC1 (p_val IN TAB_LIST);
END PKG1;
/

create or replace PACKAGE BODY PKG1 AS
PROCEDURE PROC1 (P_VAL IN TAB_LIST
                )
IS
BEGIN
    FOR i IN p_val.FIRST..p_val.LAST
    LOOP
        insert  INTO T1
            (
              id, name
            )
        VALUES
            (
              p_val(i).id,
              p_val(i).name
            );
        END LOOP;
    END;
END;

error after executing

DECLARE
    p_val PKG1.TAB_LIST;
BEGIN
    p_val := PKG1.TAB_LIST(123,'XYZ');
END;

Error report -
ORA-06550: line 5, column 11:
PLS-00306: wrong number or types of arguments in call to 'TAB_LIST'
ORA-06550: line 5, column 11:
PLS-00306: wrong number or types of arguments in call to 'TAB_LIST'
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

A `tab_list` is a collection of `T1%ROWTYPE`, so each item in it needs to be a `T1%ROWTYPE`, but you are passing `123` and `XYZ`. — William Robertson, Feb 17 '18 at 10:34
Thanks Hekko .. how can i pass multiple records at a time ... DECLARE P_VAL PKG1.TAB_LIST := PKG1.TAB_LIST(); BEGIN P_VAL.extend; P_VAL(1).id := 123,456; P_VAL(1).name := 'XYZ','ABC'; PKG1.PROC1( P_VAL ); END; — kashi, Feb 17 '18 at 12:49

hekko · Answer 1 · 2018-02-17T13:16:27.657

2

DECLARE
    P_VAL PKG1.TAB_LIST := PKG1.TAB_LIST();
BEGIN
    P_VAL.extend;
    P_VAL(1).id := 123;
    P_VAL(1).name := 'XYZ';
    PKG1.PROC1( P_VAL );
END;

example for multiple records:

DECLARE
    P_VAL PKG1.TAB_LIST := PKG1.TAB_LIST();
BEGIN
    for i in 1 .. 10
    loop
        P_VAL.extend;
        P_VAL(P_VAL.LAST).id := i;
        P_VAL(P_VAL.LAST).name := 'XYZ' || i;
    end loop;
    PKG1.PROC1( P_VAL );
END;

edited Feb 17 '18 at 13:16

answered Feb 17 '18 at 12:10

hekko

292
1
2
6

edit the answer with example for pass multiple records – hekko Feb 17 '18 at 13:17
Thanks again .. but i want to pass something like this so that all three or more should get inserted into table t1.. 123 'AAA' 345 'VVV' 678 'XXX' – kashi Feb 17 '18 at 15:20
Can you post format of your source data with multiple records.Is it DB table or maybe string? – hekko Feb 17 '18 at 16:22
@kashi : The value you show that you need to pass is not a nested table, It is a string. If you wish to send that way for your convenience and expect Oracle to explicitly convert a string to nested table on it's own, that is not possible. You must write the code like above to set the values for id and name or alternatively use a REFCURSOR. Be clear with what exactly you want to achieve ultimately with the data that's available with you and don't keep changing the question every time. – Kaushik Nayak Feb 17 '18 at 16:23
In other ways you can: 1) call proc with plain params multiple times for each row. It's slow but sometimes normal 2)insert data in temp table when make finally insert in production table 3) pass as clob param structured data for example in xml or json when parse it in your proc. – hekko Feb 17 '18 at 16:39
which language on client you use? – hekko Feb 18 '18 at 04:00

score 0 · Answer 2 · answered Feb 17 '18 at 16:46

@hekko": its not a string .sorry about formatting . The multiple values (n) can be passed from application like this and all should be inserted into table at once.

123  'XYZ'
456  'DFK'
866  'HKK'

@Kaushik :it is not a string but formatting issue.. the question remains the same "I have a table T1 and I want to insert multiple rows at a time through a procedure using collection.

using nested table as in Parameter to a Procedure

2 Answers2