0

I have a employees table having less than 15 records , Table has below three columns

EmpId Emp_name department

I want to get the index value by user. According to this index value the plsql block of code that takes the first name value from the records in the Employee table between 1 and the index put by user, and converts it to the table index structure.

1- Emp 1
2- Emp 2
3- Emp 3
4- Emp 4

I am trying this to get it done by using plslq array only by considering Index of array as searching criteria.

Adeel Aslam
  • 1,285
  • 10
  • 36
  • 69
  • what do you mean by "index value"? the order in which the rows are stored in the table, or the order in which they are returned by a query, a range of "EmpId" values, or something else? – pmdba Jun 24 '20 at 17:25
  • i want to load data into array and then out of array i want to search from Index 1 to '&indexbyuser' – Adeel Aslam Jun 24 '20 at 17:30

1 Answers1

0

I have tried something like this and works perfect

DECLARE
    TYPE r_emp_type IS RECORD(
        emp_name employee.FNAME%TYPE
    ); 

    TYPE t_emp_type IS VARRAY(100) 
        OF r_emp_type;
    
    t_employee t_emp_type := t_emp_type();

    CURSOR c_emp IS 
        SELECT fname 
        FROM employee;
BEGIN
--    -- fetch data from a cursor
    FOR r_customer IN c_emp LOOP
        t_employee.EXTEND;
        t_employee(t_employee.LAST).emp_name := r_customer.fname;
    END LOOP;

    -- show all customers
    FOR l_index IN t_employee.FIRST..'&indexbyuser'
    LOOP
        dbms_output.put_line(t_employee(l_index).emp_name);
    END LOOP;

END;
/
Adeel Aslam
  • 1,285
  • 10
  • 36
  • 69
  • 1
    In the end, how is that any different than simply 'SELECT EMP_NAME FROM EMP' – EdStevens Jun 24 '20 at 18:23
  • i have to do that via array – Adeel Aslam Jun 24 '20 at 19:21
  • Unless you order the results in some way, the index will be meaningless as Oracle will return the results in random (or at least unpredictable) order. They will _not_ be returned in the order they were entered or any other specific order unless you force it. Limiting the range of the index will limit the total number of results returned, but that's it. You could have accomplished the same thing with a "ROWNUM <= &Indexbyuser" on your search and not bothered with the array overhead. Like Ed observed, I see no value in the array processing as described. – pmdba Jun 24 '20 at 22:04
  • _WHY_ do you "have" to do it via arry? Especially given that there are far simpler solutions, and your "required" solution simply will not give the results you seem to think you want. This appears to be another 'x-y problem'. – EdStevens Jun 25 '20 at 12:14