How to read multiple values at one time as an input to single variable in PLSQL?

Question

Could you help me to pass the input values (at execution time: i mean to enter multiple values for single variable at once).

Here is my code for which i am giving one input at a time either hard coded input or single input at time.

declare
  type TEmpRec is record (
    EmployeeID EMPLOYEES.EMPLOYEE_ID%TYPE,
    LastName   EMPLOYEES.LAST_NAME%TYPE
  );
  type TEmpList is table of TEmpRec;
  vEmpList TEmpList;
  ---------
  function EmpRec(pEmployeeID EMPLOYEES.EMPLOYEE_ID%TYPE,
                  pLastName  EMPLOYEES.LAST_NAME%TYPE default null) return TEmpRec is
  -- Effective "Record constructor"
    vResult TEmpRec;
  begin
    vResult.EmployeeID := pEmployeeID;
    vResult.LastName  := pLastName;
    return vResult;
  end;
  ---------
  procedure SearchRecs(pEmpList in out nocopy TEmpList) is    -- Nocopy is a hint to pass by reference (pointer, so small) rather than value (actual contents, so big)
    vIndex PLS_integer;
  begin
    if pEmpList is not null then
      vIndex := pEmpList.First;
      while vIndex is not null -- The "while" approach can be used on sparse collections (where items have been deleted)
      loop
        begin
          select LAST_NAME
          into  pEmpList(vIndex).LastName
          from  EMPLOYEES
          where  EMPLOYEE_ID = pEmpList(vIndex).EmployeeID;
        exception
          when NO_DATA_FOUND then
            pEmpList(vIndex).LastName := 'F'||pEmpList(vIndex).EmployeeID;
        end;
        vIndex := pEmpList.Next(vIndex);
      end loop;
    end if;
  end;
  ---------
  procedure OutputRecs(pEmpList TEmpList) is
    vIndex PLS_integer;
  begin
    if pEmpList is not null then
      vIndex := pEmpList.First;
      while vIndex is not null
      loop
        DBMS_OUTPUT.PUT_LINE ( 'pEmpList(' || vIndex ||') = '|| pEmpList(vIndex).EmployeeID||', '|| pEmpList(vIndex).LastName);
        vIndex := pEmpList.Next(vIndex);
      end loop;
    end if;
  end;
begin
  vEmpList := TEmpList(EmpRec(100),
                       EmpRec( 34),
                       EmpRec(104),
                       EmpRec(110));
  SearchRecs(vEmpList);
  OutputRecs(vEmpList);
end;
/

Above program takes input value one at time. However, i tried as below but unable to succeed.

i tried to give input from console at once like (100,34,104,100) in place of either hard coding the input (or) giving one input at time.

Snippet in DECLARE section:

declare
  type TEmpRec is record (
    EmployeeID EMPLOYEES.EMPLOYEE_ID%TYPE,
    LastName   EMPLOYEES.LAST_NAME%TYPE
  );
  type TEmpList is table of TEmpRec;
  v_input TEmpList := TEmpList(&v_input); -- to read multiple input at once
  vEmpList TEmpList;

In the final BEGIN section:

BEGIN
FOR j IN v_input.FIRST .. v_input.LAST LOOP
  vEmpList := TEmpList(EmpRec(v_input(j).EmployeeID)); --to assign input values to vEmptList

  SearchRecs(vEmpList);
  OutputRecs(vEmpList);
end loop;
end;
/

Error in DECLARE section: PLS-00306: wrong number or types of arguments in call to 'TEMPLIST' Error in LAST BEGIN section: PLS-00320: the declaration of the type of this expression is incomplete or malformed

As an example: at time, i am able to read multiple input values for same variable but i am unable to pass this as an input but unable to figure out how can make this as an input my main program.

DECLARE
TYPE t IS TABLE OF VARCHAR2(100);
ORDERS t := t(&ORDERS);
BEGIN
FOR j IN ORDERS.FIRST .. ORDERS.LAST LOOP
dbms_output.put_line(ORDERS(j));
END LOOP;
END;
/

Output: PL/SQL procedure successfully completed. Enter value for orders: 321,153,678

321 153 678

Thank You.

Answer 1

Since You have a collection of record variable, you need to pass employee_ids and employee last_names separately. How are you planning to pass them in a single shot?. Here is a sample script which accomplishes something you want with 2 inputs for 3 collection elements.

First, create a collection TYPE and a PIPELINED function to convert comma separated values into Collections - f_convert2.

CREATE TYPE test_type AS TABLE OF VARCHAR2(100);


CREATE OR REPLACE FUNCTION f_convert2(p_list IN VARCHAR2)
  RETURN test_type
PIPELINED
AS
  l_string       LONG := p_list || ',';
  l_comma_index  PLS_INTEGER;
  l_index        PLS_INTEGER := 1;
BEGIN
  LOOP
    l_comma_index := INSTR(l_string, ',', l_index);
    EXIT WHEN l_comma_index = 0;
    PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
    l_index := l_comma_index + 1;
  END LOOP;
  RETURN;
END f_convert2;
/

Then in your anonymous blocks pass values for employee_ids and last_name separately.

SET SERVEROUTPUT ON
DECLARE
    TYPE temprec IS RECORD ( employeeid    employees.employee_id%TYPE,
    lastname      employees.last_name%TYPE );
    TYPE templist IS
        TABLE OF temprec;
    vemplist      templist;
    v_no_of_rec   NUMBER := 10;

    v_empl_ids VARCHAR2(100) := '&empl_ids'; 
    v_empl_lnames VARCHAR2(100) := '&empl_lnames'; 
BEGIN
SELECT employee_id,last_name
    BULK COLLECT
INTO
    vemplist
FROM
    (
        SELECT
            ROWNUM rn,
            column_value employee_id
        FROM
            TABLE ( f_convert2(v_empl_ids) )
    ) a
    JOIN (
        SELECT
            ROWNUM rn,
            column_value last_name
        FROM
            TABLE ( f_convert2(v_empl_lnames) )
    ) b ON a.rn = b.rn;

        FOR i in 1..vemplist.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(vemplist(i).employeeid || '  ' ||vemplist(i).lastname);
        END LOOP;
END;
/

Instead of simple JOIN above if you use OUTER JOIN ( FULL or LEFT ), you can handle missing values without writing logic to check each value.