-1

I want to pass a multiple values like '1,2,3,4,5,6,7,8,9,10,11,12' on as a single parameter and insert them into a table.

I have a scenario like saving a bill for particular customer, the customer might have purchased multiple items.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3099684
  • 1
  • 2
  • 3

1 Answers1

4

You can pass the values comma separated. This PL/SQL procedure breaks up a comma-separated string (passed as P_VALUE) and inserts each token into a table as a separate row, along with some other values.

PROCEDURE XXXXGL_INST_PARAMS (P_VALUE VARCHAR2) IS
    i          number;
    n          number;
    r          number;
    pos        number;
    L_NAT_ACCT varchar2(10);
    L_UID      number;
BEGIN
    L_UID := fnd_global.user_id;
    n := length(P_VALUE);
    i := 1;
    r := 1;

    WHILE (i < n) LOOP
        pos := (INSTR(P_VALUE,',',1,r)-i);
        IF pos < 0 THEN
            pos := n;
        END IF;
        SELECT substr(P_VALUE,i,pos) INTO L_NAT_ACCT FROM dual;

        INSERT INTO xxXXX.xxXXXgl_natural_accts_t
            (NATURAL_ACCT, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, 
             LAST_UPDATE_DATE, LAST_UPDATED_BY)
          VALUES(L_NAT_ACCT,SYSDATE,L_UID,L_UID,SYSDATE,L_UID);

        i := i + length(L_NAT_ACCT)+1;
        r := r+1;
    END LOOP;
END;

Note: I found this here.

zwol
  • 135,547
  • 38
  • 252
  • 361
Winner Crespo
  • 1,644
  • 15
  • 29